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));

Answer 2

How your query reacts depends on the collation you set on the server side. After all your linq expressions will be translated into an SQL query and how that is interpreted will depend on your database and column settings.

What you could try is stating a collation in your query e.g.

var customers = context.Customers
    .Where(c => EF.Functions.Collate(c.Name, "latin1_general_ci collation") == "John")
    .ToList();
//SQL_Latin1_General_CP1_CI_AS for SQL Server
//latin1_general_ci collation for MySQL

as found in the Microsoft documentation. Where CI stands for case-insensitive (opposed to CS). Be aware that this query won't be able to leverage the index on the Name due to the custom collation. So it would be better to define it on the column (or table/database).

Answer 3

Try this:

query = context.Where(user => EF.Functions.Collate(user.email, 
            "SQL_Latin1_General_CP1_CI_AI").Contains(model.Email));