how to get eager loading in a many to many relatio

Question

I have a database with four tables. TableA and TableB are the main tables and the TableC is the table of the many to many relationships.

• TableA(IDTableA, Name...)
• TableB(IDTableB, Name...)
• TableC(IDTableA, IDTableB)

This create three entities, The EntityA has an ICollection of Entity C and Entity C has a Collection of EntitiesB, so when I try to get the related entities I do this:

myContext.EntityA.Include(a=>a.EntityB.Select(b=>b.EntityC));

But this throw and exception that says that the collection is null.

So I would like to know if it is possible to do an eager loading when there are a table for the many to many relationship.

Thanks.

Answer 1

I think you need this:

var q = myContext.EntityC.Include("EntityA").Include("EntityB").ToList();

If you want Bs of an A:

var aId; // = something...;

var bs = from c in q
                     where c.EntityAId == aId
                     select c.EntityBId;

And simply vice versa if you need As of a B:

var bId; // = something...;

var eas = from c in q
                     where c.EntityBId == bId
                     select c.EntityAId;

Answer 2

With many to many association in Entity Framework you can choose between two implementations:

• The junction table (C) is part of the conceptual model (class model) and the associations are A—C—B (1—n—1). A can't have a collection of Bs.
• The junction table is not part of the conceptual model, but Entity Framework uses it transparently to sustain the association A—B (n—m). A has a collection of Bs and B has a collection of As. This is only possible when table C only contains the two FK columns to A and B.

So you can't have both.

You (apparently) chose the first option, so you will always have to query the other entites through C, like

from a in context.As
select new { a, Bs = a.Cs.Select(c => c.B) }

or

from a in As.Include(a1 => a1.Cs.Select(c => c.B))