The LINQ expression could not be translated and will be evaluated locally

by

You have to be aware of the difference between IEnumerable and Iqueryable.

An 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.

An 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 Provider. The 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 GetEnumerator and 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, because the compiler does not know what functions are supported by your version of entity framework. Therefore you won’t see this error at compile time, you’ll see it at runtime.

The error 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)

More Related Contents:

  1. Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax
  2. Entity-framework code is slow when using Include() many times
  3. Dynamic query with OR conditions in Entity Framework
  4. Entity Framework | Sequence contains more than one matching element
  5. Should the order of LINQ query clauses affect Entity Framework performance?
  6. How to create LINQ Expression Tree to select an anonymous type
  7. Unable to create a constant value of type Only primitive types or enumeration types are supported in this context
  8. Many-to-many self referencing relationship
  9. “A lambda expression with a statement body cannot be converted to an expression tree”
  10. Why is .Contains slow? Most efficient way to get multiple entities by primary key?
  11. Entity framework very slow to load for first time after every compilation
  12. How can I lock a table on read, using Entity Framework?
  13. Establish a link between two lists in linq to entities where clause
  14. Entity Framework/Linq EXpression converting from string to int
  15. navigation property should be virtual – not required in ef core?
  16. A specified Include path is not valid. The EntityType does not declare a navigation property with the name *
  17. Error: “The specified LINQ expression contains references to queries that are associated with different contexts”
  18. LINQ to Entities does not recognize the method ‘Int32 Int32(System.String)’ method, and this method cannot be translated into a store expression
  19. How to add the same column to all entities in EF Core?
  20. How to group by on 2 child entities and get total of both this child entities?
  21. How to convert an expression tree to a partial SQL query?
  22. Generate EF orderby expression by string [duplicate]
  23. How can I call a SQL Stored Procedure using EntityFramework 7 and Asp.Net 5
  24. How does LINQ expression syntax work with Include() for eager loading
  25. Optimistic concurrency: IsConcurrencyToken and RowVersion
  26. Could not load file or assembly Microsoft.Extensions.DependencyInjection.Abstractions, Version=1.1.0.0
  27. Entity framework EF.Functions.Like vs string.Contains
  28. Update records using LINQ
  29. Unable to edit db entries using EFCore, EntityState.Modified: “Database operation expected to affect 1 row(s) but actually affected 0 row(s).”
  30. Calling a SQL User-defined function in a LINQ query

Leave a Comment