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

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

Solution 2:

To add on @Slauma answer:

I faced the same problem recently, getting frustrated that the navigation property is not being set after calling Query().Where().Load(), although I can see that the objects are loaded into the DbContext.

I needed the collection to be part of my main object and use it as you would any other navigation property and not just manage a separate collection, so I did this:

project.Labels = this.Context
                    .Entry (project)
                    .Collection (p => p.Labels)
                    .Query ()
                    .Where (l => l.CreateUserName == this.UserId)
                    .ToList();

The problem with this is that EF thinks I added new relationships, which I can't blame it, but it is not what I wanted. As a result, when trying to save the Project object I got an exception when EF tried to insert the relationship into the link table because a row with the same key (projectId + labelId) already exists.

So, the final was to reset the state of the relationships between the project and the labels:

foreach (Label l in project.Labels)
                {
                    ((System.Data.Entity.Infrastructure.IObjectContextAdapter)this.Context.AsDbContext ()).ObjectContext.ObjectStateManager.ChangeRelationshipState<Project> (project, l, p => p.Labels, EntityState.Unchanged);
                }

After that I was able to use the Labels property just like any other navigation property, not caring that behind the scenes it's a many-to-many relationship.