Menu
I have a Entity Framework entity with a string Property named Description.
Searching for all entities where the Description contains a string is as simple as:
var res = from u ctx.Users where u.Description.contains(str) select u;
But suppose I want to support case insensitive search?
464
Case insensitive SQL SELECT: Use upper or lower functions or this: select * from users where lower(first_name) = 'fred'; As you can see, the pattern is to make the field you're searching into uppercase or lowercase, and then make your search string also be uppercase or lowercase to match the SQL function you've used.
For one thing, EF Core does know not which case-sensitive or case-insensitive collation should be used. More importantly, applying a collation would in most cases prevent index usage, significantly impacting performance for a very basic and commonly-used . NET construct.
To do a case-insensitive comparison, use the ILIKE keyword; e.g., column ILIKE 'aBc' and column ILIKE 'ABC' both return TRUE for 'abc' . In contrast, MySQL and MS SQL Server have case-insensitive behaviors by default. This means WHERE column = 'abc' returns TRUE for e.g., 'abc' , 'ABC' , or 'aBc' .
SQL keywords are by default set to case insensitive that means that the keywords are allowed to be used in lower or upper case. The names of the tables and columns specification are set to case insensitive on the SQL database server, however, it can be enabled and disabled by configuring the settings in SQL.
If you're using Linq to enties the search is done by the sql server so if the search is case sensitive or not depends on server settings.
149
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
