StringComparison.InvariantCultureIgnoreCase cannot be translated while used in a LINQ query

Question

I am facing a problem executing the below query in .NET 6.

query = context.Where(user =>                       
                      user.Email.Contains(model.Email,
                      StringComparison.InvariantCultureIgnoreCase));

After searching the web I understood that EF Core does translate Contains for server-side evaluation - but not the overload that accepts StringComparison.InvariantCultureIgnoreCase or any other StringComparison. But never found the correct way to solve this issue

So I changed the query to something as follows to make it work:

query = context.Where(user =>
                      user.Email.ToLower().Contains(model.Email.ToLower());

Even though it is working I am not entirely happy with this solution and still wondering which solution solves my problem best. Would using ToLowerInvariant() be a better solution? Any better approach to solve this?

UPDATE

ToLowerInvariant() does not work and causes the same error caused by StringComparison.InvariantCultureIgnoreCase

Answer 1

It seems like your are writing your LINQ query on a DbSet. This is not possible as it cannot be translated to SQL statements.

You could however use the EF.Functions.Like function. This gets translated to the SQL provider and is by default case insensitive.

query = context.Where(user =>                       
                  EF.Functions.Like(user.Email, model.Email));