Filtering multi-level includes in EF

Question

I have the following relationship between 3 tables

grandparent: Environment

parent: Organization

child: Dataset

I would like to select all Datasets from the organization where OrganizationName == org and the environment where EnvironmentName == env

I try with Entity Framework this

   var result = myContext.Environment
                                .Where(t => t.EnvironmentName == env)
                                .Include(s => s.Organizations)
                                .Where(t => t.OrganizationName == org)
                                .Include(t => t.Datasets);

but it does not work - the error message says that t.OrganizationName is not a member of Environment - and this is true

OrganizationName and EnvironmentName are unique

How can this be done?

[`Include`s can't be filtered.](https://stackoverflow.com/a/16801205/861716) — Gert Arnold, Oct 21 '17 at 22:33
what is the relationships between the entities ? is it one to one ??? if you need Datasets then dont you think you need to queryover Datasets entity ??? — RJ-, Oct 22 '17 at 16:40

user2250152 · Accepted Answer · 2017-10-24T06:23:06.113

0

You can select the dataset with this query:

var dataSets = from ds in myContext.Dataset
               join o in myContext.Organization on ds.OrganizationId equals o.OrganizationId
               join e in myContext.Environment on o.EnvironmentId equals e.EnvironmentId
               where e.EnvironmentName == "TEST"
               where o.OrganizationName == "TSO"
               select ds;

edited Oct 24 '17 at 06:23

answered Oct 22 '17 at 16:42

user2250152

14,658
4
33
57

In SQL I would like to do this SELECT d.* FROM dbo.Environment AS e INNER JOIN dbo.Organization AS o ON e.EnvironmentId = o.EnvironmentId INNER JOIN dbo.Dataset AS d ON o.OrganizationId = d.OrganizationId WHERE e.EnvironmentName = 'TEST' and o.OrganizationName = 'TSO' – Paul Sorensen Oct 23 '17 at 06:58
@PaulSorensen I edited my anwser according to your requirements. – user2250152 Oct 24 '17 at 06:25

score 0 · Answer 2 · answered Oct 22 '17 at 17:41

Disclaimer: I'm the owner of the project Entity Framework Plus

EF+ Query IncludeFilter feature allows filtering related entities.

var result = myContext.Environment
            .Where(t => t.EnvironmentName == env)
            .IncludeFilter(s => s.Organizations.Where(o => OrganizationName == org))
            .IncludeFilter(t => t.Datasets);

Note: You cannot mixte Include && IncludeFilter.

Filtering multi-level includes in EF

2 Answers2