Im getting this WARNING in EntityFramework Core what is wrong?
I already set MSSQL Datebase to Case Sensitive.
var test = await _context.Students .FirstOrDefaultAsync(m => m.LastName.Equals("ALEXANDER", StringComparison.InvariantCultureIgnoreCase));
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'where [m].LastName.Equals("ALEXANDER", InvariantCultureIgnoreCase)' could not be translated and will be evaluated locally.
You have to be aware of the difference between
IEnumerable object represents a sequence of objects. It holds everything to enumerate over this sequence: you can ask for the first element of the sequence, and once you've got an element you can ask for the next one, as long as there is a next one.
IQueryable object seems like an IEnumerable, however, it does not represent an enumerable sequence, it represents the potential to get an IEnumerable sequence.
The IQueryable object holds an
Expression and a
Expression is a generic description expressing what must be queried. The
Provider knows who will execute the query (usually a database management system) and what language is used to communicate with this DBMS (usually SQL).
If you start enumerating an IQueryable, either explicitly using
MoveNext, or implicitly by calling foreach, ToList, Max, FirstOrDefault, etc, which will deep inside call GetEnumerator and MoveNext, the Expression is sent to the Provider, who will translate it into SQL and fetch the data from the DBMS. The fetched data is returned as an IEnumerable, of which the GetEnumerator and MoveNext are called.
So the query is not executed before you call GetEnumerator and MoveNext.
What does this have to do with my question?
Entity framework can only convert classes and methods to SQL that it knows about. Entity Framework does not know your own functions. In fact, there are several LINQ function that are not supported by entity framework. See Supported and Unsupported LINQ methods
One of the unsupported methods is
String.Equals(string, StringComparison). If you use this function, the compiler can't complain, you'll see this error at runtime. It tells you that the data will first be fetched before the function is called.
This might lead to inefficient behaviour.
Your LINQ statement is equal to (leave out the async-await, not part of the problem)
var test = dbContext.Students .Where(student => student.LastName.Equals("ALEXANDER", StringComparison.InvariantCultureIgnoreCase)) .FirstOrDefault();
Since Equals can't be used, the warning says that the data is fetched locally before the Where is executed. So it might be that several items that will not pass the Where will be transferred from the DBMS to your local process.
If your database can ignore case sensitivity, consider changing your code to:
var test = dbContext.Students .Where(student => student.LastName == "ALEXANDER") .FirstOrDefault();
This will result in a SQL statement similar to:
SELECT TOP 1 * from myDatabase.Students where LastName = "ALEXANDER"
(not sure if this is correct SQL, since I use entity framework my SQL is a bit rusty. I guess you'll get the gist)