efcore: EF Core 3 string compare doesnt work
Since i moved to ef core 3. string comparison doesnt work anymore i get this. The LINQ expression ‘Where<Account>( source: DbSet<Account>, predicate: (a) => Equals( a: a.Name, b: (Unhandled parameter: __username_0), comparisonType: OrdinalIgnoreCase))’ could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
it used to work in ef core 2 with this where expression
s => string.Equals(s.Name, username, StringComparison.OrdinalIgnoreCase)
Steps to reproduce
use a string equals in a where clause
s => string.Equals(s.Name, username, StringComparison.OrdinalIgnoreCase)
Further technical details
EF Core version: 3.0 Database provider: NPGSQL Target framework: .NET Core 3.0 Operating system: Windows 10 IDE: Visual Studio 2019 16.3
About this issue
- Original URL
- State: closed
- Created 5 years ago
- Reactions: 15
- Comments: 15 (2 by maintainers)
@brgrz not really, and isn’t needed anyway - in SQL, comparisons are affected by the collation. If the table uses a case-insensitive collation, there’s no reason to use
ToUpperorToLower.In fact, having to use
ToUpper()orToLower()is a strong smell. Indexes are built using the column’s collation and trying to force a different one withCOLLATEmeans no indexes can be used, resulting in a full scan. It’s even worse ifTOUPPER()orTOLOWER()is used. Not only does the server have to scan everything, it’s asked to calculate some dummy data too.Long story short, this just works and uses any appropriate indexes if a case-insensitive collation is used :
If it doesn’t, one needs to ask why a case-sensitive collation was used in the first place, and whether it’s appropriate for the client to use a case insensitive search instead
@0Lucifer0! 👍 cheers. I still think that the ORM should translate the string.Equals(s.Name, username, StringComparison.OrdinalIgnoreCase to the best server-side behavior. But I guess that’s life 😃
Duplicate of #1222
It “worked” because it did everything on client. Now we don’t do client eval for where predicate.
It probably worked because you did everything in memory in your client.
We have used ToLower() with LINQ to SQL for years and I too think this should be solved in a better way by LINQ doing the translation. It is also number one issue new coders run into with LINQ.
Also, it is better to do ToUpperInvariant() instead of ToLower() or ToLowerInvariant() https://docs.microsoft.com/en-us/dotnet/standard/base-types/best-practices-strings
It is good that I’m no longer apparently pulling the entire table to the client (how did this never break my project??) to do this comparison, however it seems like this should really be translated. Having to know to do .ToLower() or whatever feels a little hackish, the type of thing you encounter a lot in low-level languages… We are usually more elegant than that in .NET land.
This also breaks a ton of code. For instance, resharper pushes me towards doing explicit string comparisons with Equals here since that’s typically best practice.
hi @0Lucifer0 so s.Name.ToLower() == username.ToLower() gets passed by the ORM as LOWER([Name]) = LOWER(@username)
?thx
@ajcvickers So what is the correct way to make the server do it? I mean why does the linq not translate to sql. e.g.:
WHERE convert(varbinary, myField) = convert(varbinary, value)
?