3 way many to many in Entity Framework

Question

I have 3 tables of data and a fourth table that maps between them i.e. 3 id's with all three columns as primary key.

What I really want is object A to contain a List where B is an object that contains List. I'd settle for a 2 dimentional array of B and C in A.

Is there a way to do this? Am I going about this all wrong?

Answer 1

In a typical many-to-many case, where the join table only has the composite key (two primary key fields), Entity Framework will add the navigation properties directly to the related entities. However, when the join table has extra fields, Entity Framework creates an entity for the join table so you can "get at" the extra information.

In the case you described above, Entity Framework will generate an Entity for your "join" table that will have associations to all 3 of your objects.

I created an example using the entities Item1, Item2, Item3, and Link.

From a database perspective, if I have the ID of an Item1, I can query the table with something like:

select Item1ID, Item2ID, Item3ID from Link where Item1ID = 1

This will give me a list of Item2, Item3 "pairs" that are associated with Item1 #1. Using Linq to Entities, I can query the EF model similarly by doing:

var q =
    from link in context.Link
    where link.Item1ID == 1
    select new { link.Item1, link.Item2, link.Item3 };

Or if I already have an Item1 object that I want to get relationships for:

item1.Link.Select(l => new { l.Item1, l.Item2, l.Item3 });

These are just basic examples, but you can easily use the LINQ extension methods and IQueryable to transform/group the data to your liking.

If you can provide more details about your case, I could give you more specific examples. Hope that helps!

Tim

Answer 2

If I understood your question correctly you are talking about relationships between 3 types of entities. Let's assume that these entities are Book, Author and Publisher. In this case relationship are

Book <-- many-to-many --> Author <-- many-to-many --> Publisher <-- Book

The way you can map these relationships to your domain model depends on how are you going to navigate through object graph in your application.

For there 3 entities there 3 options:

a) Object Book contains list of Authors and list of Publishers b) Object Author contains list of Books and list of Publishers a) Object Publisher contains list of Books and list of Authors

Some of the relationships could be redundant from the point of view of your design. For example, for case b) would be enough to keep a list of Books in Author object, and list of Publishers in Book object. On the words,

Author[Books] --> Book[Publishers] --> Publisher

Other cases also possible

Publisher[Books] --> Book[Authors] --> Author Publisher[Author] --> Author[Books] --> Book

Hope this helps.