We filter data by text and suddenly no rows are displayed. Surprisingly, when we run the same SQL directly on the database, it returns a lot or rows that match the filter criteria. Further more, if we use the correct casing for the filter (i.e London), the application will happily display these rows. For example, in Northwind application, we have a lot of order where the ship city is London. Let's try to filter by the uppercase text "LONDON":
As you can see below, no rows are returned.
However, let's take the original SQL statement and run it using the SQL Executer developer tool. As you can see, we get a lot of rows that match the filter.
This is actually the behavior of the original application that was migrated to .NET. The migration to .NET must keep the same behavior of comparing texts. If the original application was case sensitive, the .NET application will be just as sensitive. Basically, the application validates the data that is returned from the server and exclude the rows that does not match the filter without ignoring casing. Fortunately, it is very easy to change this behavior.
One way we can bypass the application revalidation of the filter is to change the Where clause of the controller to SQL Where:
Another way to change this behavior of text compare globally is by calling the ENV.Common.SetAsCaseInsensitive() method in Program.Main()
Notice that this change the way the application compare texts, for example: Before the change, the application is case sensitive so:
After the change, the application is case insensitive so: