Mapping Many to Many table as 2 Many to Many Associations Associations

2. Modeling Entities

2.2 Many to Many Mapping

2.2.5 Mapping Many to Many table as 2 Many to Many Associations Associations

Console.WriteLine(club.ClubName);

}

Exposing Many To Many Relationship as EntityReference

2.2.5 Mapping Many to Many table as 2 Many to Many Associations

Problem: You have 3 tables in the database called Actors, Movies and

ActorMovie link table. An actor can be part of many movies and a movie can have many actors. The link table also contains an additional column

IsLeadingRole which determines if an actor is playing a leading role in the movie or not. You want to represent the Many To Many table as two types of associations in EDM. An Actor should have two types of Navigation to Movie entity; MoviesWithLeadingRole and MoviesWithSupportingArtist.

MoviesWithLeadingRole will return Movies where the actor has a leading role. MoviesWithSupportingArtist will return Movies where the actor is a supporting actor.

Solution: To map the link table as two different Entityset, we need to create two views, one view will return ActorMovie table where IsLeadingRole is 1 and other view will return ActorMovie table where IsLeadingRole is 0. Next step is to import the views, Actor and Movie table into entity data model and then create two many to many association between actor and Movies. One association would map to a view where IsLeadingRole equal to 1 and other association would map to view where IsLeadingRole is 1. Since views does not support inserts and updates, we will have to create separate stored procedure which will be responsible for inserting into our link table.

Discussion: First step is creating the 3 tables Actor, Movies and ActorMovie link table. For Actor table define ActorId as the primary key. For Movies table, define MovieId as the primary key and ActorMovie link table will have ActorId and MovieId as the primary key. In addition ActorMovie link table will also contain IsLeadingRole bit field that tells if the actor has a leading role in the movie or not. Database diagram below shows how the relationship between tables looks like.

Since we want to maintain two different types of movie collection for an actor; one with leading role and other with supporting roles, we need to create two views that return appropriate data from Actor_Movies table. Views

shown below achieve our requirement.

create view dbo.MoviesWithLeadingActor as

select ActorId,MovieId from Actors_Movies where IsLeadingRole = 1 create view dbo.MoviesWithSupportingActor

select ActorId,MovieId from Actors_Movies where IsLeadingRole = 0

Later we will create two many to many associations on the EDM and map them to our view. Since views do not support insert and deletes, we need to create two stored procedures that will be responsible for inserting and deleting into the Actor_Movies link table. Stored procedures below serve our need.

create proc dbo.InsertActor_Movies

(@ActorId int,@MovieId int,@IsLeadingRole bit) as

begin

insert into Actors_Movies values (@ActorId,@MovieId,@IsLeadingRole) end

create proc dbo.deleteActor_Movies (@ActorId int,@MovieId int)

as begin

delete Actors_Movies where ActorId =@ActorId and MovieId =@MovieId end

Since Many To Many table does not have a concept of Update, we only need stored procedure for Insert and Update cases. The insert stored procedure takes ActorId, MovieId and IsLeadingRole to insert into Actor_Movies table.

To delete an entry from Actor_Movies table we are passing ActorId and MovieId since they are primary key columns in the link table.

The next step is to import Actor and Movies table, our two views, and two stored procedure we created for inserts and delete to link table. Screen shots below shows how EDM model and model browser window looks like after importing objects from the database.

First step is to delete the two entities MoviesWithLeadingActors and

MoviesWithSupportingActor which are based on views. Next step is to create Many to Many association between Actor and Movies. This association will represent a collection containing only movies where the actor has leading role. To perform this operation, right click on Actors and Add Association.

You will get an association dialog. Screen shot below shows the values we filled for the association.

On the Add Association dialog above, I have indicated that both ends of the association will have a multiplicity of Many. To access Movies collection from actor we will use MoviesWithLeadingActor navigation property. This is because we will map this association to MoviesWithLeadingActor view.

After clicking okay on the dialog, you model should look like this.

To map the association we created, right clicking on the association line and select table mapping. On the table mapping select MoviesWithLeadingActor view and the designer would map the columns to properties on the association automatically.

Similarly to create our second association right click on actors, select add association and make both ends of the association as Many To Many and name the navigation property that goes from Actor to Movies as

MoviesWithSupportingActor. Screen shot below shows the values we filled for the association.

To map the association, right click the association line and select table

mapping. Map the association to MoviesWithSupportingActor view as shown below.

If you try to validate the model, you will not get any errors. However when we try to add items to the two collections created earlier, we will get an exception because the associations are mapped to views and views are not updatable. We need to map the associations to Insert and Delete stored procedures imported earlier. Since mapping associations to stored procedure

is not supported by the designer, we have to manually edit the msdl and specify insert and delete stored procedure. If we look at the insert stored procedure created earlier, it has an additional parameter called IsLeadingRole for which we cannot specify any mapping as it is not returned from our view definition. To be able to use the same stored procedure for inserting either ActorsWithLeadingRoles and ActorsWithSupportingRoles associations, we need to leverage the CommandText property of the function definition on the sddl and specify default values for IsLeadingRole depending on the

association being inserted. Example below shows the updated version of the function that inserts LeadingActors and SupportingActors relationship.

<Function Name="InsertLeadingActor_Movies" Aggregate="false"

BuiltIn="false" NiladicFunction="false" IsComposable="false"

</Function>

<Function Name="InsertSupporingActor_Movies" Aggregate="false"

BuiltIn="false" NiladicFunction="false" IsComposable="false"

</Function>

On the above function, we got rid of the additional parameter because we do not have any mapping for IsLeadingRole. Instead we created two functions that basically call the same stored procedure in the database with different default values for IsLeadingRole.

We then need to map the stored procedures to insert and delete function of associationSetMapping for both associations.

We need to map our association to Inserts and Delete stored procedure modified above. Mapping stored procedure to associations is currently not supported by the designer, so we will have to go into msdl and manually

specify the Insert and Delete functions for both the associations. Msdl below shows the ModificationFunctionMapping section for both associations.

Notice for LeadingActor association, we are calling

InsertLeadingActor_Movies stored procedure and for SupportingActor association, we are calling InsertSupporingActor_Movies stored procedure.

<AssociationSetMapping Name="ActorsMovies"

<AssociationSetMapping Name="ActorsMovies1"

TypeName="MultipleAssociationsModel.ActorsMovies1"

</InsertFunction>

<DeleteFunction

FunctionName="MultipleAssociationsModel.Store.deleteActor_Movies">

</EndProperty>

</EndProperty>

</DeleteFunction>

</ModificationFunctionMapping>

This completes our modeling of Many to much relationship as two Many To Many associations. The final entity entity data model looks like this

Now we can program against the model by adding the movie to right collection depending on if the actor participated in a leading role or

supporting role. When we retrieve the movies, both collections would have their correct types of movies filled. Code below creates three movies and adds two movies to MoviesWithLeadingActor collection and one movie to

MoviesWithSupportingActor. Then using a different datacontext, I am retrieving the collection count to ensure that I have two movies with

LeadingActor and 1 one movie with SupportingActor. When I am retrieving

the actor, I am using Include operator to load both collections ahead of time rather then call load separately on each collection to do lazy loading.

var db = new MultipleAssocationsEntities();

var george = new Actors { Name = "George Clooney" };

var Syriana = new Movies { Title = "Syriana" };

var BabyTalk = new Movies { Title = "Baby Talk" };

var Roseanne = new Movies { Title = "Roseanne" };

db.AddToMovies(Syriana);

db.AddToMovies(BabyTalk);

db.AddToMovies(Roseanne);

george.MoviesWithLeadingActor.Add(BabyTalk);

george.MoviesWithLeadingActor.Add(Syriana);

george.MoviesWithSupportingRole.Add(Roseanne);

db.SaveChanges();

//query using a different datacontext.

var db1 = new MultipleAssocationsEntities();

var george1 =

db1.Actors.Include("MoviesWithLeadingRole").Include("MoviesWithSupportingArti st").First(a => a.Name == "George Clooney");

Console.WriteLine("Total Movies with Leading Role " + george1.MoviesWithLeadingActor.Count());

Console.WriteLine("Total Movies with Supporing Role " + george.MoviesWithSupportingRole.Count());

Output from the code executed is shown below.

In document Entity Framework Learning Guide (Page 76-86)