Efcore: EF Core 3 string compare doesnt work

[0Lucifer0]

Since i moved to ef core 3. string comparison doesnt work anymore i get this.
The LINQ expression 'Where(
source: DbSet,
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

[ErikEJ]

It probably worked because you did everything in memory in your client.

[smitpatel]

Duplicate of #1222

It "worked" because it did everything on client. Now we don't do client eval for where predicate.

[generik0]

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

?

[0Lucifer0]

It actually run on the clientside which is not something wanted in netcore 3. Running anything clientside is actually bad as it mean you get all the data.
Depending on the db you are targetting there is way to specify case insensitive.
In my specific case I just to lower which is server side and use it as comparison.

[generik0]

hi @0Lucifer0 so s.Name.ToLower() == username.ToLower() gets passed by the ORM as LOWER([Name]) = LOWER(@username)

?thx

[0Lucifer0]

Yes like this https://github.com/NosCoreIO/NosCore/blob/b48e3c1089c398419a7c59f2bbc4faa665ac186b/src/NosCore.GameObject/Networking/LoginService/LoginService.cs#L87

[generik0]

@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 :-)

[kinetiq]

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.

[brgrz]

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

[igoboj]

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

ToUpperInvariant() also throws the same LINQ exception, it seems only ToLower is properly translated.

[brgrz]

@igoboj Yes, I noticed but ToUpper() also works

[pkanavos]

@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 ToUpper or ToLower.

In fact, having to use ToUpper() or ToLower() is a strong smell. Indexes are built using the column's collation and trying to force a different one with COLLATE means no indexes can be used, resulting in a full scan. It's even worse if TOUPPER() or TOLOWER() 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 :

s => s.Name== username,

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]

As far as I know case insensitive collation doesn’t exist in postgres

[Lecarvalho]

You can use EF.Functions.ILike("A", "a") if you're using postgres