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?

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

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

Solution 3

You know sometimes I start getting lost in fancy LINQ extension methods and attempting to figure out how to eagerly load exactly what I want and resort to a very simple "join" concept.

var result = 
(from f in SubForums
from c in Classes
from t in Term
where t.TermId = 1
select new { SubForum = f, Class = c, Term = t }).ToList();

This is a simple join that uses predefined navigation properties (hence you don't have to specify the join condition). You return an anonymous type with everything that you need. The beauty of this is that Entity Framework will do the auto-fixup for you, therefor you're free to return the SubForum only from your method if you wish, it will automatically contain the Class and subsequent Term references.

Comments

[Slauma]

You still need the filter between GetSubForms and Select: Where(sf => sf.Classes.Any(c => c.TermId == termId)). Otherwise you would load all subforms from the DB, including subforms with classes with no matching termId.

[amiry jd]

@Slauma OK, really I don't understand the question completely; So I update my answer; Thank you :)

[Slauma]

True, the first Any in the question is confusing. I've just written a comment under the question because I believe he meant actually a Where.

[Shawn Mclean]

@Javad_Amiry I edited the question with the added complexity.

[Admin]

Minor nit: Include(Expression) does exist as the System.Data.Entity.DbExtensions.Include extension method, but has a totally different meaning. It's used as an alternative to string literals: repo.Terms.Include(term => term.Classes).

[amiry jd]

@Lolcoder so you can use the end-to-begin pattern; I haven't Win and VS just now to test the codes; so test them please and let me know if there is any problem.

[Wouter de Kort]

Thnx. I've modified my answer