Accepted answer

Yes unfortunately you can't use Include extension method that way, but if you are open to use a third party library then I recommend you to use Entity Framework Plus, with that lib you could do this:

var persons = _context.Persons
                      .IncludeFilter(e => e.Items.OrderByDescending(i => i.Date).Take(1))

There is a second option could be using Global Filters but I think first solution is close to what you are looking for.

A third option would be to project the query with the result you are expecting:

var persons = _context.Persons
                      .Select(e=> new {Person=e,
                                       Item=e.Items.OrderByDescending(i => i.Date)


Yes, unfortunately are missusing Include.

For your queries, you could use a GroupBy on Items and a subquery, though.

Query on the items, grouped by PersonId, ordered by date inside the group, and take first of them.

Hopefully you'll have some relevant index in place to speed up the query on the db side.

That should be something like :

_context.Items.GroupBy(i => i.PersonId)
    .Select(g => g.OrderByDescending(p => p.Date).FirstOrDefault())

Related Query

More Query from same tag