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.

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.

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}%"));
}