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?

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?

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

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

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