LINQ filter on navigation property

Question

I have the following two entities:

public class Person 
{ 
    public Person() 
    {
        Items = new HashSet<Item>();
    }

    public string Id { get; set; }
    public string Name { get; set; }
    public ICollection<Item> Items { get; private set; }
}

public class Item
{
    public string Id { get; set; }
    public DateTime Date { get; set; }
    public string Status { get; set; }
    public Person Person { get; set; }
    public string PersonId { get; set; }
}

I would like to select all the persons, and only include their latest Item (ordered by date).

I figured something like this should work:

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

But apparently EF Core is unable to do this. The items collection will get very large (> 20000), so loading them all for every person is undesirable. What should I do instead?

You're right @PeterSchneider. I did not completely copy my entity class. I have edited this now. — laurensvm, Jun 17 '20 at 14:50
I do not completely understand @Sinatr. If I simply say `_context.Persons.Include(e => e.Items).ToList();`, it will load all the items in this list, right? — laurensvm, Jun 17 '20 at 14:51
Yes, unfortunately are missusing `Include`. For queries, use `Where` and `Select` `OrderBy` directly. Include is for entities that should be loaded in the result. You cannot do some "complex subqueries" in include. — Pac0, Jun 17 '20 at 14:53
See [here](https://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql)... — Peter Schneider, Jun 17 '20 at 14:53
general note, just noticed: it's a bit surprising to me to see "string" as the type of ID. I don't know your DB, but for most I would expect an `int`or a `Guid` (both in DB and in entity def). DBs like SQL Server perform poorly on text identifiers. — Pac0, Jun 17 '20 at 15:30

ocuenca · Accepted Answer · 2020-06-17T15:21:37.290

1

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))
                      .ToList();

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)
                                                   .Take(1)
                                       })
                      .ToList();

edited Jun 17 '20 at 15:21

answered Jun 17 '20 at 14:57

ocuenca

38,548
11
89
102

I'd rather not use more third party libraries for a single query. Regarding the third option, is there a straightforward way to retrieve a Person object with p.Items = persons.Items? – laurensvm Jun 17 '20 at 15:06
hi @laurensvm, sorry I don't understand well the question, but if you are asking me if you can project a new Person object with that result querying items, then no, EF is not going to allow you to do that – ocuenca Jun 17 '20 at 15:14
I think I will instead rewrite my code to be able to use your third option. Thank you for your help! – laurensvm Jun 17 '20 at 15:15

score 1 · Answer 2 · answered Jun 17 '20 at 14:57

1

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())

answered Jun 17 '20 at 14:57

Pac0

21,465
8
65
74

Thank you! I have thought about querying the items list, but it is possible that a Person does not have any items, in which I still want to return the person, but with an empty list of items. Can I combine this query with `_context.Persons.Include(e => e.Items).ToList();` in some way? Also, the index should be on the item.PersonId? – laurensvm Jun 17 '20 at 15:03
1

about index : Yes, index should be on PersonId and then on date (with the order you expect). – Pac0 Jun 17 '20 at 15:05
about persons without items, unfortunately this approach will only give persons that have at least one item linked. You could _also_ retrieve all persons that don't have any items linked, (select all unique personId from table items, and then select persons with id _not_ in this list) but I'm not sure about the performance of that, there might be an anothersimpler approach. – Pac0 Jun 17 '20 at 15:09
Ah, you could do a left join of persons with the result of the above query on items. – Pac0 Jun 17 '20 at 15:11
And I am missing the actual projection you need of course :) – Pac0 Jun 17 '20 at 15:28

LINQ filter on navigation property

2 Answers2