3

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

Guevara Brik
  • 39
  • 4
  • Did you try ToLowerInvariant? I have a hunch it won't be supported either. – Lasse V. Karlsen Jan 04 '22 at 15:45
  • If you can write a comparison that includes all you want, but also a few extras, I would run the final comparison in memory, after EF has done its thing. – Lasse V. Karlsen Jan 04 '22 at 15:47
  • 2
    what is your db provider? – Daniel A. White Jan 04 '22 at 15:51
  • 1
    If you are using SQL, it's going to ignore case by default already – Jonesopolis Jan 04 '22 at 16:01
  • 1
    If you are using SQL Server then you can force a specific collation, see https://learn.microsoft.com/en-us/ef/core/miscellaneous/collations-and-case-sensitivity. I have not tried this with `.Contains()` but it's worth a try. Not sure if this is supported by other DB providers. – Peter B Jan 04 '22 at 16:03
  • Case-sensitivity when translating to SQL is a database issue, not a C# issue. What database provider? How is your `Email` column's collation set? – NetMage Jan 04 '22 at 20:01
  • @LasseV.Karlsen I did try ToLowerInvariant(), which lead to the same error as using the string comparisons – Guevara Brik Jan 05 '22 at 06:30
  • @DanielA.White MySql – Guevara Brik Jan 05 '22 at 06:31
  • @PeterB checking this out – Guevara Brik Jan 05 '22 at 06:42
  • 1
    @NetMage it is not only about case sensitivity, its about InvariantCulture. While this worked fine with .NET 2.2, it is not being translated anymore into SQL since .NET 3.1. Here it is described why it is no longer supported – Guevara Brik Jan 05 '22 at 06:48
  • https://github.com/dotnet/efcore/issues/1222#issuecomment-611113142 – Guevara Brik Jan 05 '22 at 07:02
  • 1
    Since you never had this run server-side it could be an option to force client-side evaluation yourself if you really want .Net semantics for comparison (and if the amount of data you pull is limited). If you don't want that it will always be a question of choosing a collation. No other option. Since it's about email addresses, that only allow a restricted range of characters, almost any case-insensitive collation will do. – Gert Arnold Jan 05 '22 at 08:48
  • Does this answer your question? [Net core EF 3.1 LINQ string comparison no longer working](https://stackoverflow.com/questions/59795841/net-core-ef-3-1-linq-string-comparison-no-longer-working) – sommmen Jan 05 '22 at 08:52
  • You didn't mention you were comparing to EF Core 2.2. In 2.2, you were not translating to SQL - EF Core 2.x automatically switched from translation to client-side evaluation when it ran into untranslatable methods. When migrating to EF Core 3 or above, you can add a `AsEnumerable()` just before the untranslatable method to get the same result. – NetMage Jan 05 '22 at 19:55
  • @sommmen thx for the link, but no this doesn't answer my question – Guevara Brik Jan 06 '22 at 11:30
  • @NetMage i will check this out, thx for the response – Guevara Brik Jan 06 '22 at 11:30
  • @NetMage `AsEnumerable()` actually doesn't work in my case, since I am expecting an IQueryable – Guevara Brik Jan 06 '22 at 11:35
  • You can put `AsQueryable()` at the end even though it is really an `IEnumerable`. – NetMage Jan 06 '22 at 22:38
  • @GuevaraBrik did you find a solution? I'm having to enumerate before running the invariant culture comparison, potentially very costly :( – Jimmy Apr 07 '22 at 05:38

3 Answers3

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));
klekmek
  • 533
  • 3
  • 11
  • Is it really? Have you read this: [Is the LIKE operator case-sensitive with SQL Server?](https://stackoverflow.com/q/14962419/1220550) – Peter B Jan 04 '22 at 16:04
  • [According to the docs](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.dbfunctionsextensions.like?view=efcore-5.0#Microsoft_EntityFrameworkCore_DbFunctionsExtensions_Like_Microsoft_EntityFrameworkCore_DbFunctions_System_String_System_String_) it is case insensitive when evaluated on the client. – klekmek Jan 04 '22 at 16:05
  • 1
    To @klekmek: OP question is about an issue in translating an expression to SQL, which tells me that client side evaluation is not the case here. Also both this answer and my comment are about translation to SQL. – Peter B Jan 04 '22 at 16:11
  • 1
    @klekmek I have tested your solution and the Like function is not case-insensitive, ToLower or ToUpper are definitely needed on both sides of the comparison in order for this to work. – Guevara Brik Jan 05 '22 at 06:40
0

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

NotFound
  • 5,005
  • 2
  • 13
  • 33
0

Try this:

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