LINQ To Entities does not recognize the method Last. Really?

Question

In this query:

public static IEnumerable<IServerOnlineCharacter> GetUpdated()
{
    var context = DataContext.GetDataContext();
    return context.ServerOnlineCharacters
        .OrderBy(p => p.ServerStatus.ServerDateTime)
        .GroupBy(p => p.RawName)
        .Select(p => p.Last());
}

I had to switch it to this for it to work

public static IEnumerable<IServerOnlineCharacter> GetUpdated()
{
    var context = DataContext.GetDataContext();
    return context.ServerOnlineCharacters
        .OrderByDescending(p => p.ServerStatus.ServerDateTime)
        .GroupBy(p => p.RawName)
        .Select(p => p.FirstOrDefault());
}

I couldn't even use p.First(), to mirror the first query.

Why are there such basic limitations in what's otherwise such a robust ORM system?

Answer 1

That limitation comes down to the fact that eventually it has to translate that query to SQL and SQL has a SELECT TOP (in T-SQL) but not a SELECT BOTTOM (no such thing).

There is an easy way around it though, just order descending and then do a First(), which is what you did.

EDIT: Other providers will possibly have different implementations of SELECT TOP 1, on Oracle it would probably be something more like WHERE ROWNUM = 1

EDIT:

Another less efficient alternative - I DO NOT recommend this! - is to call .ToList() on your data before .Last(), which will immediately execute the LINQ To Entities Expression that has been built up to that point, and then your .Last() will work, because at that point the .Last() is effectively executed in the context of a LINQ to Objects Expression instead. (And as you pointed out, it could bring back thousands of records and waste loads of CPU materialising objects that will never get used)

Again, I would not recommend doing this second, but it does help illustrate the difference between where and when the LINQ expression is executed.