I was forced to explore something interesting when i was playing around with the Entity Framework and executing the below LINQ query.
EmployeeEntities entityContext = new EmployeeEntities(); var Records = (from m in entityContext.Employees Select m).LastOrDefault();
My assumption was that the result would be the last record in the table .. But to my surprise , it threw an Exception
LINQ to Entities does not recognize the method ‘Employees’ Last(System.Linq.IQueryable`1[Employees])’ method, and this method cannot be translated into a store expression.
Instead , the below query worked..
EmployeeEntities entityContext = new EmployeeEntities(); var Records = (from m in entityContext.Employees Select m).List<Employees>().LastOrDefault();
Interestingly , here’s the reason why i got the error .
The above said functions and there are still quite a few LINQ operators that are not supported in the LINQ to Entities and the reason why other query worked with List is simply because the data from the SQL is loaded to the Generic List first and the LastOrDefault is applied directly to the List and not to the SQL .
You can find the list of functions that are not supported in .NET Framework in the MSDN site
Supported and Unsupported LINQ Methods (LINQ to Entities)
Moral of the story is “Get to know what is supported and not supported , before we start”.:)
3 Comments
A couple things you might want to consider are:
1) If you want this basic approach, you might want to use AsEnumerable() rather than ToList() since that way you won’t have the overhead of creating a list but rather you will just enumerate the query and then return the last result. It will use linq to objects for the last operator–AsEnumerable signals to the EF to switch from a remote query executed on the database to linq to objects executed locally.
2) Even better performance would be to order your query by some property, set your order to be descending rather than ascending and then use FirstOrDefault(). That way the database will only return the one entity rather than all the entities and then throwing all but the last one away.
– Danny
It’s not doable to support LastOrDefault in SQL because SQL isn’t sequence based but set based. This means that by default the results have no ordering, unless you specify one, and even then, it’s not said the ordering gives a satisfying result: if two or more elements are equal, it’s by definition undefined in which order they’re returned by the database.
So a naive approach for LastOrDefault would be:
var q = (from c in ctxt.Customers
orderby c.Country descending
select c).FirstOrDefault();
however, that’s misleading: you can’t deterministically determine which customer is returned here, because it’s likely more than 1 customer will have the same country as another customer.
If you store it in a List first, and then call LastOrDefault(), it works, because List is a sequence, it’s stored in an array, so every time you call LastOrDefault() on that list gives the same value.
The same goes for .Reverse(), which also doesn’t work for the same reason.
Linq to Sql doesnt support .ElementAt(n) for the same reason, but I think they missed a spot there: .ElementAt(n) is equal to .Skip(n-1).Take(1);