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

Question

"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?

Solution 1

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

Solution 2

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.

Solution 3

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

Solution 4

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

Solution 5

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();

View more solutions

Comments

[Gert Arnold]

Not true. It depends solely on the database collation. EF doesn't have any influence here, let alone EF version.

[DarkUrse]

Collation has influence on this and I am not saying otherwise. However if you don't want to use collation, you have an option which is the one I'm pointing out here. However, let me edit my reply and hopefully I won't be too wrong.

[Gert Arnold]

OK, go ahead, but there's really no way whatsoever to direct this from the client, else than applying ToLower, but that's "cheating".

[Jeremy Holovacs]

EF.Functions.ILike... what a PITA it was to find out about that. Thanks.

[Hakan Fıstık]

The performance hell exactly what brought me to here, I was using the second method, and I was not able to understand why my query is taking 5 minutes to execute, please be very careful with the second method, the change in the performance is scary.

[Hakan Fıstık]

This could cause a very big performance issue, especially with PostgreSQL, see the @Stas answer above to avoid the performance disaster that you may face

[Rohin Tak]

Behaviour of IQueriable would depend on the database. For example, in SQL server it would be case insensitive but for postgres it's case sensitive

[aleksander_si]

What about security; it seems quite possible this is not secure: EF.Functions.Like(x.Name, $"%{keyword}%")

[Observer]

It is possible to set both sides to upper or lowercase: context.Counties.Where(x => x.Name.ToUpper().Contains(keyword.ToUpper())).ToList();

[John Weisz]

How's the performance on this one? Is it translated to an efficient query with MySQL? Or does it have to load entries one-by-one to do a comparison on the server?

[Darkseal]

It gets translated like this: WHERE (LOCATE(CONVERT(LCASE(@__value_0) USING utf8mb4) COLLATE utf8mb4_bin, LCASE(b.Name)) > 0)

[aochagavia]

I am a bit late to the party here, but I was wondering about the performance impact, since the second method seems to work perfectly for me. I inspected the query and I saw it is using strpos(lower(...)) for the string comparison, which in this post (dba.stackexchange.com/questions/89901/…) is mentioned as the faster alternative. Am I missing something here?

[Dharmeshsharma]

@HakanFıstık ToLower in postgres will use scan for column that's why its very poor performance.

[Mahmood Garibov]

And also there are invariant culture problem you can't pass invariant culture