Entity Framework 6 including only child records that have a specific value?

Question

I'm trying to return a record set that includes child records that meet a specific data requirements. This is my base query, and in this case I am only trying to bring back children where the IsActive field is true.

var result = db.Projects
         .Include(p => p.Department)
         .Include(p => p.ProjectPhases.Where(pp =>pp.IsActive ))
         .Include(p => p.Notes)
         .AsQueryable<Project>();

This returns an error:

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties.

Is there simple way to do this?

Filtered includes - one of the most requested EF features (with a lot of questions on SO). Not implemented even in the latest EF Core. — Ivan Stoev, Dec 20 '17 at 19:44
EF Code has [Global Query Filters](https://learn.microsoft.com/en-us/ef/core/querying/filters). — Olivier Jacot-Descombes, Dec 20 '17 at 19:50

score 0 · Answer 1 · answered Dec 20 '17 at 19:46

0

You can't perform where clause inside of Include. Why don't you just try like this;

var result = db.Projects
         .Include(p => p.Department)
         .Include(p => p.ProjectPhases)
         .Include(p => p.Notes)
         .Where(x => x.ProjectPhases.Where(pp =>pp.IsActive))
         .AsQueryable<Project>();

answered Dec 20 '17 at 19:46

lucky

12,734
4
24
46

I just found that the where clause `.Where(p =>p.ProjectPhases.All(a=>a.IsActive) )` does exactly what I need. – John S Dec 20 '17 at 19:50
3

This limits the `Projects`, not the `ProjectPhases`. It translates into some thing like `SELECT * FROM Projects WHERE ProjectID IN (SELECT ProjectID FROM ProjectPhases WHERE IsActive <>0)` – Olivier Jacot-Descombes Dec 20 '17 at 19:52

score 0 · Answer 2 · answered Dec 20 '17 at 19:52

Those Includes generate the joins, to filter out use the Where after the Include chain:

var result = db.Projects
         .Include(p => p.Department)
         .Include(p => p.ProjectPhases)
         .Include(p => p.Notes)
         .Where(it => it.ProjectPhases.All(a=>a.IsActive))
         .AsQueryable<Project>();

Entity Framework 6 including only child records that have a specific value?

2 Answers2