Net core EF 3.1 LINQ string comparison no longer working

c# ef-core-3.1 entity-framework-core linq

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?

1
1
1/18/2020 9:34:44 AM

Popular Answer

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?

1
1/17/2020 11:46:48 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow