"Contains" in Entity Framework core should equivalent to the SQL %like% operator. Therefore "Contains" should be case insensitive however it is case sensitive! (at least in postgres????)
The following only outputs a result when the correct casing for keyword is used.
context.Counties.Where(x => x.Name.Contains(keyword)).ToList();
What am I doing wrong?
It used to be the case for older versions of EF core. Now string.Contains is case sensitive, and for exemple for sqlite it maps to sqlite function `instr()' ( I don't know for postgresql).
If you want to compare strings in a case-insensitive way, you have DbFunctions to do the jobs.
context.Counties.Where(x => EF.Functions.Like(x.Name, $"%{keyword}%")).ToList();
UPDATE to @Gert:
A part of the assumption in the question is incorrect. string.Contains does NOT convert into a LIKE expression even though it USED to be the case in ef core versions <= 1.0 (I think).
string.contains converts into CHARINDEX(), in oracle and sqlite into instr() which are case sensitive by default UNLESS db or column collation is defined otherwise ( Again, I don't know for postgresql ).EF.Functions.Like() converts into a SQL LIKE expression which is case-insensitive by default unless db or column collation is defined otherwise.So yes it all goes down to collation but - correct me if I'm wrong - in a way the code can have an influence on the case-sensitive/insensitive search depending on which one of the above method you use.
Now, I might not be completely up to date but I don't think EF core migrations deal with DB collation naturally and unless you've already created the table manually you will end up with the default collation (case-sensitive for sqlite and I honestly don't know for the others).
Getting back to the original question you have at least 2 options to perform this case-insensitive search if not 3 in a future release :
string.Contains by EF.Functions.Like()EF.Functions.Collate() function
My answer will concern NpgSQL.
EF.Functions.Like() in PostgreSQL is case-sensitive, but you can use EF.Functions.ILike() extension method located in Npgsql.EntityFrameworkCore.PostgreSQL assembly.
If you don't have reference to Entity Framework assembly in place where you build query, you can use combination ToLower() and Contains() methods, because Npgsql is able translate ToLower() method to correct SQL
Example:
context.Counties.Where(x => x.Name.ToLower().Contains(keyword.ToLower())).ToList();
About second method keep in mind: you may have performance problems and may encounter problems associated with encoding.
Use Explicit collation in a query
For example
var customers = context.Customers
.Where(c => EF.Functions.Collate(c.Name, "SQL_Latin1_General_CP1_CS_AS") == "John")
.ToList();
For more details see the msdn link
https://learn.microsoft.com/en-us/ef/core/miscellaneous/collations-and-case-sensitivity
With Entity Framework Core 3.1 and MySQL / MariaDB providers you can manually set the case (in)sensitiveness with StringComparison.InvariantCultureIgnoreCase in the following way:
items = items.Where(i =>
i.Name.Contains(value, StringComparison.InvariantCultureIgnoreCase));
The default behaviour seems to be case sensitive, however you can explicitly set it using StringComparison.InvariantCulture.
For additional info, check out this post on my blog.
I don't know if it works for previous versions as well (will check and update this answer accordingly).
Just try it :
You can Lower case field and search value
context.Counties.Where(x => x.Name.ToLower().Contains(keyword.ToLower())).ToList();
Or you can Upper Case filed and search value
context.Counties.Where(x => x.Name.ToUpper().Contains(keyword.ToUpper())).ToList();
IQueryable.Where is executed in the database, so it is most likely to be case insensitive.
IEnumerable.Where uses C# String.Contains, so it is case sensitive.
Read this answer: Returning IEnumerable vs. IQueryable
