How do I make contains case-insensitive in ef core

Question

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

• IQueryable gives you the database provider implementation of Contains.
• IEnumarable 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 which I don't really understand why since I'm using IQueryable so it should use the database provider implementation which is case-insensitive by default? I'm using EF Core 2 and currently just running a local MSSQLLocalDB

Answer 1

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

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

Answer 2

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.