Many To Many Mapping Walkthrough

Problem: You have defined 3 tables in the database called MusicalShow, Sponsor and Show_Sponsor. A sponsor can contribute to many shows and a show can have many sponsors. You want to use the entity framework

designer to map link table as a many to many relationship. You also want to know how to insert and delete entities relationship between two entities having many to many relationships.

Solution: To map a link table to a many to many relationship in entity

framework, the link table cannot have payloads. If there is additional info that you are tracking about the relationship in the link table, entity framework has to create a third entity so you can access additional properties and also be able to insert and update those values for the link table. In our example,

Show_Sponsor table does not contain additional properties other then primary keys from Sponsor and MusicalShow table. Therefore when we import the model using the update wizard, entity framework recognizes that link table does not contain any payloads and automatically removes the link table by representing relationship between MusicalShow and Sponsor as Many To Many.

Discussion: To map a link table as many to many relationships, we have to create 3 tables in the database MusicalShow, Sponsor and Show_Sponsor.

The link table Show_Sponsor cannot have any additional properties apart from the primary keys from Sponsor and MusicalShow table. If there are additional fields found in the link table, then entity framework designer will import the tables as 3 entities instead of two entities with many to many relationships. Screen shot below database diagram that shows the relationship between the tables.

On the above database diagram, Show_Sponsor acts as link table between MusicalShow and Sponsor table and to make sure that a show cannot have the same sponsor repeated twice, I have defined ShowId and SponsorId to be the primary key of the link table. When we import the tables using Update Model from database wizard, entity framework gets rid of the link table and shows MusicalShow and Sponsor to have many to many relationships. Screen shot below shows the many to many relationships between Show and Sponsor.

When we import the model, we get two entities MusicalShow and Sponsors.

MusicalShow has a navigation property Sponsors that lets you access all the sponsors for a given MusicalShow. Similarly if you have sponsor entity, you can access all the musicalshows the sponsor has contributed to by using the MusicalShows navigation property. Both navigation properties are exposed as an EntityCollection. The association line between MusicalShow and Sponsor is a many to many which means both ends of the association have a

multiplicity of Many. To see that clearly we can select the association line and look in the property window to see the End role for each side of the association set. Screen shot below shows the properties windows for many to many association set.

Now that we have modeled our entities, we can program against these entities to retrieve many side of relationship using eager loading or lazy loading. In the code below I am using Include and Load operator to load many side of relationship for sponsors.

var db = new ManyToManyEntities();

var miller = db.Sponsors.First(s => s.Name ==

"Miller Lite");

//lazy load the miller

if (!miller.MusicalShows.IsLoaded) {

miller.MusicalShows.Load();

}

Console.WriteLine("Lazy Loading");

Console.WriteLine("Shows for miller lite " + miller.MusicalShows.Count());

//eager loading

var db2 = new ManyToManyEntities();

var miller2 =

db2.Sponsors.Include("MusicalShows").First(s => s.Name ==

"Miller Lite");

Console.WriteLine("Eager Loading");

Console.WriteLine("Shows for miller lite " + miller2.MusicalShows.Count());

On the above code, to load the MusicalShows entity collection, I am calling Load on MusicalShows if it is not already loaded. This process is called lazy loading of entities. Similarly to eager load MusicalShows, I can use the include operator with Sponsor to indicate that when I bring sponsor, also retrieve MusicalShows for the sponsor. It is important to mention at this point that EF does not support relationship span for many to many relationship.

Relationship span is a feature which allows EF framework to fix relationships of objects that are loaded separately. For instance if I load customers and orders separately and if the customers in the object context have an

association to any of the orders tracked in ObjectContext, Ef framework will fix the relationship automatically and associate those orders to customer. With Many to Many relationships, retrieving the many side of the relationship can be an expensive operation as it requires an extra join to link table and is not take care by the framework. In the code below, I have retrieved the shows and the sponsor separately and when I access the shows for the sponsor, I get a return value of 0 which indicates that EF did not fix the navigation

relationship for me although both sides of relationship were loaded in the objectcontext.

var db = new ManyToManyEntities();

var miller = db.Sponsors.First(s => s.Name ==

To delete and add relationship between show and a sponsor we can make use of Add Delete method exposed on entity collection for both sides of the

navigation. Example below shows how to add and delete MusicalShows from a sponsor.

var db = new ManyToManyEntities();

var show1 = new MusicalShow { ShowName = "Johnny and the Sprites" };

var show2 = new MusicalShow { ShowName = "Sesame

On the above code, I am creating two shows and adding it to MusicalShows navigation property of miller sponsor followed by SaveChanges. To remove a show from a sponsor, I am using Remove method on MusicalShow

navigation property. When I call Remove I am simply removing the

relationship between the show and sponsor, it does not actually remove the show from the database. Another way to remove a relationship is by deleting the entity itself. Like in the above example, I am deleting show2 and since show2 has relationship with Miller sponsor, EF framework understands that it does not makes sense to have a relation with an entity that is marked for deletion. Therefore when SaveChanges is called, EF also issues a delete command to delete the relationship. EF will only issue the command to delete a relationship when the relationship is loaded and tracked by the

ObjectContext. If the relationship between sponsor and show is not tracked by Object Context, deleting show would cause database to throw foreign key violation constraints because it will be still tied to a sponsor.

As discussed earlier, if the link table carries the responsibility of capturing data specific to the relationship, entity framework wouldn’t be able to transform relationship into many to many associations. This is because associations currently do not support properties other than having roles on

both ends of an association. This is something which EF team is considering in the next release of Entity framework.