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?