Linq to Entity Framework: Select with only subset of child elements

Question

The following setup is true:

• A is parent class
• A has one to many of B

• A has one to many of C

• B has one to many of X

• C has one to many of X

• X has a property called EndDate: this is the filter criteria

I want to retrieve a set that has A, B, C and X (A.B.X and A.C.X) I want to filter the set to that only elements are returned that have X.EndDate = Date.

I don't want elements of X that don't pass this criteria. Can this be done in a single Select?

I'm currently trying following code:

var set = _context.Set<A>()
    .Include("B.X")
    .Include("C.X")
    .Where(a => a.B.Any(b => b.X.Any(x => x.EndDate == date)) ||
                a.C.Any(c => c.X.Any(x => x.EndDate == date)))
    .ToList();

However, when B.X is within filter criteria, it will also include C.X And when one of B.X (X can be many) is true to the criteria, it will return all of B.X entities

I tried my best to give an example:

A           B           X       X.EndDate
A1          B1          BX1     2015-01-01
A1          B1          BX2     2015-01-02
A1          B2          BX3     2015-01-09

A           C           X       X.EndDate
A1          C1          CX1     2015-01-03
A1          C1          CX2     2015-01-03
A1          C2          CX3     2015-01-02

When date == 2015-01-02

Results are:
A1 B1 BX1
      BX2
   B2 BX3
   C1 CX1
      CX2
   C2 CX3 

Desired results are:
A1 B1 BX2
   C2 CX2

Note: I can only use this notation and not the SQL-like notation. There are more entities that need to be included and this can (apparently) only be done by .Include("") with quotes

Answer 1

What you want to do is filter the included tables and that is not something that Entity Framework supports right now: http://entityframework.codeplex.com/workitem/47. If you are dealing with simple classes then you could instantiate new A's in your select statement like this:

var set = _context.Set<A>()
    .Include("B.X")
    .Include("C.X")
    .Where(a => a.B.Any(b => b.X.Any(x => x.EndDate == date)) ||
                a.C.Any(c => c.X.Any(x => x.EndDate == date)))
    .Select(a => new A() 
    {
        B = a.B
            .Where(b => b.X.Any(x => x.EndDate == DatePicker))
            .Select(b => new B()
                {
                    X = b.X.Where(x => x.EndDate == DatePicker) 
                }),
        C = a.C
            .Where(c => c.X.Any(x => x.EndDate == DatePicker))
            .Select(c => new C()
                {
                    X = c.X.Where(x => x.EndDate == DatePicker) 
                })
    });

If your classes are not so simple, i.e. you have many properties that you would have to map in your select statement or you have complex business validation rules, then your best bet may be to continue with the query you have and simply remove the B's, C's and X's you don't want from your resulting set before using it.