search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.73k stars 3.17k forks source link

string.ToLowerInvariant() and .ToUpperInvariant() should have sql translations #18995

Closed shaulbehr closed 2 years ago

shaulbehr commented 4 years ago

I am upgrading from EF Core 2.2.6 to 3.1 (preview 2). I have several places in code where I'm doing thing like:

var result = await db.Things.Where(t => t.Name.ToLowerInvariant() == name.ToLowerInvariant()).ToListAsync();

Since upgrading, these queries are all failing because there's no Sql translation.

This fix is simple enough; just change to .ToLower() or .ToUpper(). But this seems like an unnecessarily breaking change.

Further technical details

EF Core version: 3.1 preview 2 Database provider: Npgsql Target framework: .NET Core 3.0 Operating system: Windows / Linux IDE: JetBrains Rider

smitpatel commented 4 years ago

Since upgrading, these queries are all failing because there's no Sql translation.

As you said, there is no SQL translation. Refer to breaking change https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client

For that particular query, you really don't want to get your whole table in the memory to evaluate the filter.

shaulbehr commented 4 years ago

@smitpatel I realize now that my code was evaluating locally, even though I thought it wasn't! So I'm actually quite happy about this particular breaking change. What I'm suggesting, though, is that EF should have a translation for .ToLowerInvariant().

smitpatel commented 4 years ago

What would be SQL translation for ToLowerInvarint()? We are happy to add translation if there exists one.

shaulbehr commented 4 years ago

Oh, um...I'm much more of an ideas guy. I don't really know that much about what's happening under the hood in those SQL translations, and I was just hoping there would be an obvious candidate. bitmoji

smitpatel commented 4 years ago

Due to server using collations etc, it is not straight forward to translate. If you can write a SQL, I can make it happen under the hood.