title: Entity framework - populating child objects, filtering on a child of child
body: <p>I'm trying to optimize some code, but I'm having a bit of a problem with it. I have a database, and the tables in question look like this in the EDMX...
  <a href="https://i.stack.imgur.com/njGua.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/njGua.jpg" alt="enter image description here"></a></p>
  
  <p>Each EmailQueue item can have multiple EmailContact and EmailEntity records, and each EmailContact could have multiple EmailSendFailures, though most will have none. </p>
  
  <p>I want to get each EmailQueue where there is at least one EmailEntity record, and at least one EmailContact record that does not have an EmailSendFailure record. *EDIT: Also, I do not want to include those EmailContact records in this query. *</p>
  
  <p>After some StackOverflowing and some trial and error, I was basically able to achieve something along these lines with this code:</p>
  
  <pre><code>var emails2 =
      (from eqs in
           this.context.EmailQueues
           .Include(q =&gt; q.EmailContacts)
           .Include(e =&gt; e.EmailEntities)
       where eqs.EmailContacts.Count &gt; 0
       &amp;&amp; eqs.EmailEntities.Count &gt; 0
       &amp;&amp; eqs.SentFlag == false
       select new
       {
           EmailQueue = eqs,
           EmailContact = eqs.EmailContacts.Where(c =&gt; !c.EmailSendFailures.Any()),
           EmailEntity = eqs.EmailEntities
       }).ToList();
  </code></pre>
  
  <p>The problem with that is it then returns a System.Collections.Generic.List&lt;{EmailQueue:Tesa.DataModels.EmailQueue, EmailContact:System.Collections.Generic.IEnumerable, EmailEntity:System.Collections.Generic.IEnumerable}>. I really don't want that. I want a List. </p>
  
  <p>I tried this, which seems like it should work, but I get this error:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <pre><code>List&lt;EmailQueue&gt; emails1 =
      this.context.EmailQueues.Include(q =&gt; q.EmailContacts.Where(c =&gt; !c.EmailSendFailures.Any()))
          .Include(e =&gt; e.EmailEntities)
          .Where(eqs =&gt; eqs.EmailContacts.Count &gt; 0 &amp;&amp; eqs.EmailEntities.Count &gt; 0 &amp;&amp; eqs.SentFlag == false)
          .ToList();
  </code></pre>
  
  <p>Anyone have any idea what I might be doing wrong here? My suspicion is it's somewhere in the navigation properties, due to some of the problems I had during my "trial and error" (more error than trial) phase, prior to entering the "I give up, I'm posting to StackOverflow" phase.</p>