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.

Straw

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: codeproject.com/Articles/18714/… – Calycine

Possible duplicate of Case insensitive 'Contains(string)' – Drayman

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 – Straw

@JasonReddekopp Questions about string processing in .Net have little relevance to LINQ to SQL queries. – Denticulation

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? – Arbogast

Answer 1

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.

Vivianaviviane

Answer 2

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

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

Indigoid

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? – Indigoid

@Indigoid 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. – Clynes

@Indigoid 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. – Denticulation

Contains in ef core does not convert to a like statement, it converts to a CHARINDEX() expression – Diaghilev

Answer 3

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.

Freehand