Why does EF throw an error with LastOrDefault in Include operations but not with OrderByDescending?

Question

I need to get the last record from the Monitoring table

return await _context.Applications
                .Include(s => s.Elements)
                .ThenInclude(d => d.Monitoring.LastOrDefault())
                .ToListAsync();

But EF throw an error :

System.InvalidOperationException: The expression 's.Elements.AsQueryable().LastOrDefault()' is invalid inside an 'Include' operation, since it does not represent a property access: 't => t.MyProperty'. 
To target navigations declared on derived types, use casting ('t => ((Derived)t).MyProperty') or the 'as' operator ('t => (t as Derived).MyProperty'). 
Collection navigation access can be filtered by composing Where, OrderBy(Descending), ThenBy(Descending), Skip or Take operations.

But when I execute the same request with OrderByDescending it works fine.

return await _context.Applications
                .Include(s => s.Elements)
                .ThenInclude(d => d.Monitoring.OrderByDescending(c => c.Id))
                .ToListAsync();

Can anyone explain what is the difference between using OrderByDescending and LastOrDefault?

How would it be correct to get the last item in this case?

Duplicated question https://stackoverflow.com/questions/39692360/linq-to-entities-does-not-recognize-the-method-lastordefault — D A, May 25 '23 at 11:04
@DA this is not duplicate for the linked one. Also EF Core supports (at least latest version) `LastOrDefault`. — Guru Stron, May 25 '23 at 12:00

score 2 · Accepted Answer · answered May 25 '23 at 11:55

The difference is that support for one is implemented and for another - is not. This is documented in the EF Core docs for Filtered Include:

Supported operations are: Where, OrderBy, OrderByDescending, ThenBy, ThenByDescending, Skip, and Take.

So you can do:

return await _context.Applications
    .Include(s => s.Elements)
    .ThenInclude(d => d.Monitoring.OrderByDescending(c => c.Id).Take(1))
    .ToListAsync();

Which should result in Monitoring having at most one element with largest Id.

This solution seems to work well. Now I only get the last record from the Monitoring table. Thank you @Guru Stron — iVelesco, May 25 '23 at 12:56

score 0 · Answer 2 · answered May 25 '23 at 11:42

The exception describes your problem almost perfectly. Include or ThenInclude can't accept an object, which is a result of LastOrDefault().

When you you call OrderByDescending instead, it's still a property accessor which can be converted by EF to the SQL code - also, it's a pointer to the collection.

Try to convert your code directly to the SQL code, how can you perform "JOIN" with a single row (this is what LastOrDefault tries to perform) ? - you can't.

The difference between LastOrDefault and OrderByDescending is that first one, results in a single record, and another one results in a collection, ordered by your preference.

_"When you you call OrderByDescending instead, it's still a property accessor"_ - no, it is not, it is a method call expression invoked on property accessor. — Guru Stron, May 25 '23 at 12:05

Why does EF throw an error with LastOrDefault in Include operations but not with OrderByDescending?

2 Answers2