How do I make contains case-insensitive in ef core 2?

Question

I am trying to filter a list by a search string. It says in the doc on the blue note that:

IQueryable gives you the database provider implementation of Contains.
IEnumerable gives you the .NET Framework implementation of Contains
The default setting of SQL Server instances is case-insensitive.
Using ToUpper to make an explicit case-insensitive call should be avoided because it has a performance penalty.

My filtering is as follows:

IQueryable<ApplicationUser> customers = 
    from u in _context.Users
    where (u.Customer != null && u.IsActive)
    select u;

if (!string.IsNullOrEmpty(searchString))
{
    customers = customers.Where(s => s.Email.Contains(searchString));
}

This solution however is case-sensitive, and I don't really understand why: since I'm using IQueryable, it should use the database provider implementation, that is case-insensitive by default, right?

I'm using EF Core 2 and currently just running a local MSSQLLocalDB.

You could eqialize them using String.ToLower() and then compare those temporary strings. You may also want to run String.Normalize() on it, just to avoid Unicode inconsistencies. Note that there can be significant processing, memory, and GC load with creating that many Strings in a loop. Strings are the wierdest class out there See this article on comparing equality in general: https://www.codeproject.com/Articles/18714/Comparing-Values-for-Equality-in-NET-Identity-and — Christopher, Jan 11 '18 at 23:09
Possible duplicate of [Case insensitive 'Contains(string)'](https://stackoverflow.com/questions/444798/case-insensitive-containsstring) — Jason Reddekopp, Jan 11 '18 at 23:18
Yes calling ToLower() or Normalize() would have the exact same performance overhead as ToUpper() as in my 4th bullet point. I am looking to avoid that and the best solution might just be to change my collation settings in my db — J.Kirk., Jan 11 '18 at 23:18
@JasonReddekopp Questions about `string` processing in .Net have little relevance to LINQ to SQL queries. — NetMage, Jan 11 '18 at 23:44
String.Contains is case-sensitive so that part is to be expected. If you skip that part doesn't the db give you back all the Users in the Context? — illug, Feb 19 '18 at 12:21

score 5 · Accepted Answer · answered Mar 14 '19 at 12:29

starting from version 2.1 of the EF Core, you can use HasConversion(). But the information in the database will be stored in lowercase:

builder.Property(it => it.Email).HasConversion(v => v.ToLowerInvariant(), v => v);

I solved a similar problem. This change solved all my problems.

score 4 · Answer 2 · edited Jun 23 '20 at 19:15

4

You would be better off using LIKE operator, e.g.

if (!String.IsNullOrEmpty(searchString))
{
    customers = customers.Where(x => EF.Functions.Like(x.Email, $"%{searchString}%"));
}

edited Jun 23 '20 at 19:15

Massimiliano Kraus

3,638
5
27
47

answered Jan 11 '18 at 22:51

zaitsman

8,984
6
47
79

Because whoever reads your code now has clear intent? `.Contains()` would (you would hope) translate to `LIKE` anyway, but it may not be obvious to someone who has just read the code. Re: case sensitivity -> that is definitely in the collation settings of your db? – zaitsman Jan 11 '18 at 22:55
2

@zaitsman The intent isn't to do a pattern match using `LIKE`. The intent is to check whether `Email` contains the specified value, and `s.Email.Contains(searchString)` documents that intent best. All you should care about is whether it's successfully translated to SQL. The best way to do it may or may not be using `LIKE`. – Jan 11 '18 at 23:00
2

@zaitsman Whoever reads the code is likely a C# programmer, who knows exactly what `Contains` does, and may not be familiar with SQL and it's strange wildcards. – NetMage Jan 11 '18 at 23:45
1

Contains in ef core does not convert to a like statement, it converts to a CHARINDEX() expression – Chazt3n Feb 22 '19 at 18:09

score 1 · Answer 3 · answered Dec 31 '19 at 20:01

StringComparison is answer for me.

customers = customers.Where(s => s.Email.Contains(searchString, StringComparison.CurrentCultureIgnoreCase));

OR

customers = customers.Where(s => s.Email.Contains(searchString, StringComparison.InvariantCultureIgnoreCase));

works for me.

How do I make contains case-insensitive in ef core 2?

3 Answers3

Linked