Entity Framework include Where

Question

If I have a query that looks like this:

var forms = repo.GetForms().Where(f => f.SubForms.Any(sf => sf.Classes.Any(c => c.TermId == termId)));

From this you can see my schema is as follows:

SubForm has many Class which has many Term.

What I want:

All SubForms with their Classes In a particular Term.

What is happening now is that I get all the SubForm that has any Class in a particular Term. That means that SubForm comes back with ALL child Class and not just the ones related to the Term.

For eg. I have 2 terms, a subform with 2 classes in each term. This query brings back 4 classes instead of the 2 in that particular term.

Is there any Include('Expression') that I can use to say that I only want to include all classes based on a condition? Or is my query wrong?

Answer 1

Use this:

var subForms = repo.GetSubForms.Select(sf = new {
        SubForm = sf,
        Classes = sf.Classes.Where(c => c.TermId == termId)
    }).ToList()
    .Select(t => t.SubForm)
    .ToList();

UPDATE: based on @Slauma's comment:

If you want to load SubForms that they have any Class that has a Term by termId, you can go from end to begin; like this:

var subForms = repo.Terms.Where(t => t.Id == termId).Select(t => new {
        Term = t,
        Class = t.Class,
        SubForm = t.Class.SubForm
    }).ToList()
    .Select(t => t.SubForm).ToList();

OR in a easiest way, you can use Include on your Term, see:

var subForms = repo.Terms.Include("Class.SubForm").Where(t => t.Id == termId)
                   .Select(t => t.Class.SubForm).ToList();

NOTE: As I can understand from your question, you have a relationship like this:

SubForm has_many Class has_many Term

But, your provided code is showing a relationship like this one:

SubForm has_many Class
Term has_many Class

If you can, put your entities in question, or explain their relationship more please. Thank you.

Answer 2

An Include(Where Expression) does not exist. If you use eager loading with Include you will always load all the elements.

There is a way around this by using projections. The basic idea is you will select a new anonymous type with the property you want and another property with the filtered navigational items. EF will link those together and as a result you will fake a Include(Where ... )

Check this for an example.