Linq to Entities, select parent entities with child entities, where child entities match condtion

Question

This seems like it should be a simple thing, but I just can't seem to find an example that does what I need.

I have entities "Person", and "SavedSearch". These are joined correctly with foreign keys, so navigation properties work. A "Person" can include multiple "SavedSearch" entities.

What I want to do, is select a list of all of the "Person" entities, each with a collection of "SavedSearch" entities, where these SavedSearch entities meet a particular condition.

This is the closest I've been able to get...

Dim person_query = From p In db.Person
                   Where p.SavedSearch.Any(Function(s) s.SendEmails = True)
                   Select New SavedSearchDetails With {
                     .PersonID = p.PersonID,
                     .SavedSearchList = p.SavedSearch.Where(Function(s) s.SendEmails = True)
                   }

This gives me the correct results, but it seems incorrect having to specify the where condition twice. Checking the SQL generated, I can see that it's using left outer joins, which I don't think should be necessary.

So essentially what I need is a list of "Person" entities, with a list of "SavedSearch" entities, where "SendEmail" is true.

Also I should add. I'm only wanting the "Person" entities if they include the matching "SavedSearch" child entities.

Answer 1

You use the Include method to include child entities in the query result:

Dim peopleWithDavedSearches = From p In db.Person.Include("SavedSearch")
                              Where p.SavedSearch.Any(Function(s) s.SendEmails)
                              Select p

Each Person object in peopleWithDavedSearches will then have its SavedSearch property populated if there are any entities to populate it with.

You may also be able todo this:

Dim peopleWithDavedSearches = From p In db.Person.Include(Function(person) person.SavedSearch)
                              Where p.SavedSearch.Any(Function(s) s.SendEmails)
                              Select p

I'm not 100% sure whether standard LINQ to Entities offers that functionality these days or you still need an additional reference but you can try it and see.

Note that, if you want to include children of children then you just use dot notation, e.g.

From item In list.Include("Child.GrandChild.GreatGrandChild")

If you want to include descendents on multiple branches then you just call Include more than once, e.g.

From item In list.Include("Child1").Include("Child2")