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

by

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.

More Related Contents:

  1. Create code first, many to many, with additional fields in association table
  2. Entity Framework 5 Updating a Record
  3. Entity Framework (EF) Code First Cascade Delete for One-to-Zero-or-One relationship
  4. Entity Framework Join 3 Tables
  5. Insert/Update Many to Many Entity Framework . How do I do it?
  6. How to create a many-to-many mapping in Entity Framework?
  7. What are Independent Associations and Foreign Key Associations? [duplicate]
  8. Get return value from stored procedure
  9. Many-to-many mapping table
  10. An object with the same key already exists in the ObjectStateManager. The ObjectStateManager cannot track multiple objects with the same key
  11. Non-static method requires a target
  12. Creating Composite Key Entity Framework
  13. Entity Framework (.NET Full Framework) Ordering Includes [duplicate]
  14. Calculated column in EF Code First
  15. Mapping Database Views to EF 5.0 Code First w/Migrations
  16. EF migration for changing data type of columns
  17. how to create an audit trail with Entity framework 5 and MVC 4
  18. Mocking EF DbContext with Moq
  19. EF5 Code First – Changing A Column Type With Migrations
  20. Can EF automatically delete data that is orphaned, where the parent is not deleted?
  21. get date part only from datetime value using entity framework
  22. NHibernate – Many to Many Query using Junction/Joiner Table
  23. Self-referencing many-to-many recursive relationship code first Entity Framework
  24. EF5 Getting this error message: Model compatibility cannot be checked because the database does not contain model metadata
  25. How to define Many-to-Many relationship through Fluent API Entity Framework?
  26. MVC 4 – Many-to-Many relation and checkboxes
  27. Modelling polymorphic associations database-first vs code-first
  28. RichTextBox syntax highlighting in real time–Disabling the repaint
  29. Embedding assemblies inside another assembly
  30. Handler “ExtensionlessUrlHandler-Integrated-4.0” has a bad module “ManagedPipelineHandler” in its module list

Leave a Comment