title: Linq to Entity Framework: Select with only subset of child elements
body: <p>The following setup is true:</p>
  
  <blockquote>
    <ul>
    <li>A is parent class</li>
    <li>A has one to many of B</li>
    <li><p>A has one to many of C</p></li>
    <li><p>B has one to many of X</p></li>
    <li><p>C has one to many of X</p></li>
    <li><p>X has a property called EndDate: this is the filter criteria</p></li>
    </ul>
  </blockquote>
  
  <p>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 <code>X.EndDate = Date</code>.</p>
  
  <p>I <strong>don't</strong> want elements of X that don't pass this criteria.
  Can this be done in a single Select?</p>
  
  <p>I'm currently trying following code:</p>
  
  <pre><code>var set = _context.Set&lt;A&gt;()
      .Include("B.X")
      .Include("C.X")
      .Where(a =&gt; a.B.Any(b =&gt; b.X.Any(x =&gt; x.EndDate == date)) ||
                  a.C.Any(c =&gt; c.X.Any(x =&gt; x.EndDate == date)))
      .ToList();
  </code></pre>
  
  <p>However, when <code>B.X</code> is within filter criteria, it will also include C.X
  And when one of <code>B.X</code> (X can be many) is true to the criteria, it will return all of <code>B.X</code> entities</p>
  
  <p><strong>I tried my best to give an example:</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>Note:</strong> 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</p>