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.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.
Screen shot 1
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.
Screen Shot 2
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.
Screen Shot 3
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.
Related links--
http://msdn.microsoft.com/en-us/magazine/cc163286.aspx
http://www.youtube.com/watch?v=nkrYxGUZmvg
http://msdn.microsoft.com/en-us/data/ee712907
