Entity Framework core - Contains is case sensitive or case insensitive?

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).

• In SQLServer 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 ).
• In all cases 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 :

1. Specify the column collation on creation using DbContext.OnModelCreating() using this trick
2. Replace your string.Contains by EF.Functions.Like()
3. Or wait for a promising feature still in discussion : EF.Functions.Collate() function

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).

My answer will concern NpgSQL.

1. EF.Functions.Like() in PostgreSQL is case-sensitive, but you can use EF.Functions.ILike() extension method located in Npgsql.EntityFrameworkCore.PostgreSQL assembly.

2. 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.