Net core EF 3.1 LINQ string comparison no longer working

Question

I have the following class:

public class Employee
{
    public string Name {get; set;}
    ...
}

and a LINQ query in EF Core 2.1

Employee GetEmployeeByName(string name) {
  return Context.Employee.Where ( w =>String.Compare(w.Name, name, true) == 0).FirstOrDefault();
}

After it is converted to Net Core EF 3.1, there is an error.

LINQ expression 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()

I have to change the query to

Employee GetEmployeeByName(string name) {
  return Context.Employee.Where ( w =>w.Name.ToLower() == name.ToLower()).FirstOrDefault();
}

Is there a better way to do this?

Carmarthenshire

What db are you using? – Aixenprovence

Microsoft SQL Server. – Carmarthenshire

I was heading towards the answer from barett- string ops would only be case send if they were done on the client side c#. For a simple query that is passed to the db, like .Where(x => x.Name == "Smith") the case sens comes from the db, and SQLS isn't normally case sens by default – Aixenprovence

Answer 1

If you're after a case-insensitive string comparisons, the recommendation (AFAIK) is to use the EF.Functions extensions, which translate into the correct SQL statements.

Your example becomes this (using Like):

using Microsoft.EntityFrameworkCore;

Employee GetEmployeeByName(string name) {
  return Context.Employee.Where(w => EF.Functions.Like(w.Name, name)).FirstOrDefault();
}

which translates into something similar (depending on the server version) to

SELECT TOP(1) <<list of fields here>> FROM Employee WHERE Name LIKE(@name)

The functions which are available are dependent on the version of EF Core and the underlying DBMS, but since you mentioned SQL Server, the above will work, assuming you used the "default" collation. Related: Is the LIKE operator case-sensitive with MSSQL Server?

Ney

is EF.Functions can work with another DB-Engine except for MsSql? – Athanor

Yes. Which functions are available, and what works, depends on the providers you have installed. That is noted in my answer, though. – Ney

No. The EF.Functions class is available for multiple vendors. You just won't see the same functions, depending on what you're using. – Ney

Hmm, I understand what you mean. I am sorry for my bad English, perhaps my previous comment is unclear for you. Thank you very much for your nice explanations. – Athanor

Answer 2

As @tieson-t mentioned you can use EF.Functions to do a Like compare, but maybe the more "proper" way of doing it (depending on use-case of course) is to specify the collation of the comparison.

Instead of defining the default collation for the column as @barrett777 suggested, there is a EF.Function called Collate where you can specify the collation to use for the current query.

For example for SQLite I used

.Where(t => EF.Functions.Collate(t.Name, "NOCASE").Equals(m))

Some more collation info...

• SQLite Collation info: https://www.sqlite.org/datatype3.html#collating_sequences
• MSSQL: https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15

Heliostat

Answer 3

I believe EF Core queries' case sensitivity comes from collation configured at the database

https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15

You could set the collation for the Employee.Name column to a case insensitive collation, and then your EF Core queries on that column should be case insensitive by default

Neibart

Answer 4

You can still do exactly the same... Doing this in.NET Core 2.2

Employee GetEmployeeByName(string name) {
    return Context.Employee.Where(w =>String.Compare(w.Name, name, true) == 0).FirstOrDefault();
}

Is really the same thing in .NET Core 3.1 doing what it is told to do :

Employee GetEmployeeByName(string name) {
    var entityList = Context.Employee.ToList();
    return entityList.Where(w =>String.Compare(w.Name, name, true) == 0).FirstOrDefault();
}

It's just a way for Microsoft to make you realize you'll have to retrieve all entries of the table to do the string comparison... It was happening like that in .NET Core 2.2.

Source: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client

Mortician

This is an inefficient way, It will load all your data on DB-Server into an App-Server and then filtering the result by specific criteria (and remove the non-matching results). You must keep the row in IQueryable<T> to make sure the EF load (generating query) only filtered data. – Athanor

Of course it is, and that's why Microsoft decided to make it an error, unless you're doing it on purpose. There is no other way to do exactly what is asked because "String.Compare" doesn't translate to SQL query. – Mortician

In that case, I agreed. But for LinqToSql using ToLower is the best way (I think). – Athanor

Absolutely, if case isn't sensitive. – Mortician