Entity framework - populating child objects, filtering on a child of child

Question

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...

Each EmailQueue item can have multiple EmailContact and EmailEntity records, and each EmailContact could have multiple EmailSendFailures, though most will have none.

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. *

After some StackOverflowing and some trial and error, I was basically able to achieve something along these lines with this code:

var emails2 =
    (from eqs in
         this.context.EmailQueues
         .Include(q => q.EmailContacts)
         .Include(e => e.EmailEntities)
     where eqs.EmailContacts.Count > 0
     && eqs.EmailEntities.Count > 0
     && eqs.SentFlag == false
     select new
     {
         EmailQueue = eqs,
         EmailContact = eqs.EmailContacts.Where(c => !c.EmailSendFailures.Any()),
         EmailEntity = eqs.EmailEntities
     }).ToList();

The problem with that is it then returns a System.Collections.Generic.List<{EmailQueue:Tesa.DataModels.EmailQueue, EmailContact:System.Collections.Generic.IEnumerable, EmailEntity:System.Collections.Generic.IEnumerable}>. I really don't want that. I want a List.

I tried this, which seems like it should work, but I get this 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.

List<EmailQueue> emails1 =
    this.context.EmailQueues.Include(q => q.EmailContacts.Where(c => !c.EmailSendFailures.Any()))
        .Include(e => e.EmailEntities)
        .Where(eqs => eqs.EmailContacts.Count > 0 && eqs.EmailEntities.Count > 0 && eqs.SentFlag == false)
        .ToList();

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.

Answer 1

Try doing all the .includes first and then the where clause like:

context.Subjects.Include(r => r.RelatedSubjects).Include(t => t.Organisations).Include(y => y.Organisations.Select(i => i.Directories))
            .Where(y => y.Organisations.Any(e => e.Status == "Live")); }