How to manually load related entities in a N:N relationship?

Question

I am using EF5 and when the the relationship is 1:N, if I want to load related entities I do the following:

• With T-SQL I load from database the main entities with a T-SQL like that:

  select * 
  from MainEntities 
  where ...
  

• with T-SQL I load the related entities

  select * 
  from RelatedEntities 
  where IDMainEntity IN (---)
  

At this point EF populate the property navigation of the main entities with the related entities. Also, in the local property of the type of each entity in the dbContext I have all the entities of each type.

However, if i do the same with a N:N relationship, I don't have the entity of the middle table of the relation, and when I execute the queries I have in the local of the dbContext the entities of each type, but the property navigation is not populated.

I would like to know why and if it exists some alternative.

I use this way because I want to use T-SQL for create dynamic queries. If I use eager loading I don't have the same flexibility to dynamic queries than when I use TSQL, and it is less efficient. If I use explicit loading I to do N additional queries, one of each record in the results of the main entity With my way, I only one additional query, because I get all the related entities at once. If I use lazy loading I have the same problem, N additional queries.

Why EF does not populate the related properties when the relation is N:N?

Thanks.

Answer 1

The feature you are talking about is called Relationship Span or Relationship Fixup and indeed - as you have noticed - it does not work for many-to-many relationships. It only works if at least one end of the association has multiplicity 1 (or 0..1), i.e. it works for one-to-many or one-to-one relationships.

Relationship Span relies on an entity having a foreign key. It doesn't matter if it has an explicit foreign key property (foreign key association) or only a foreign key column in the corresponding database table without a property in the model (independent association). In both cases the FK value will be loaded into the context when the entity gets loaded. Based on this foreign key value EF is able to figure out if a related entity that has the same primary key value as this FK value is attached to the context and if yes, it can "fixup the relationship", i.e. it can populate the navigation properties correctly.

Now, in a many-to-many relationship both related entities don't have a foreign key. The foreign keys are stored in the link table for this relationship and - as you know - the link table does not have a corresponding model entity. As a result the foreign keys will never be loaded and therefore the context is unable to determine which attached entities are related and cannot fixup the many-to-many relationship and populate the navigation collections.

The only LINQ queries where EF will support you to build the correct object graph with populated navigation collections in a many-to-many relationship are eager loading...

var user = context.Users.Include(u => u.Roles).First();

...or lazy loading...

var user = context.Users.First();
var rolesCount = user.Roles.Count();
// Calling Count() or any other method on the Roles collection will fill
// user.Roles via lazy loading (if lazy loading is enabled of course)

...or explicit loading with direct assignment of the result to the navigation collection:

var user = context.Users.First();
user.Roles = context.Entry(user).Collection(u => u.Roles).Query().ToList();

All other ways to load the related entities - like projections, direct SQL statements or even explicit loading without assignment to the navigation collection, i.e. using .Load() instead of .Query().ToList() in the last code snippet above - won't fixup the relationship and will leave the navigation collections empty.

If you intend to perform mainly SQL queries rather than LINQ queries the only option I can see is that you write your own relationship management. You would have to query the link table in addition to the tables for the two related entities. You'll probably need a helper type (that is not an entity) and collection that holds the two FK column values of the link table and probably a helper routine that fills the navigation collections by inspecting the primary key values of the entities you find as attached in the DbSet<T>.Local collections and the FK values in the helper collection.