Modeling two 1 to many relationship as Many to Many relationship relationship

Problem: You have defined 3 tables in the database; Club, Members and Membership. A club can have many members and a member can be part of many clubs. This relationship is stored in Membership table. Membership table carries a payload column MemberDues that stores how much Dues a member has towards a club. Because Membership table contains additional columns apart from the primary keys from Club and Members table, entity framework models the relationship as two 1 to Many associations. This is good for inserts and updates but for querying purpose to know what clubs a member is associated with, extra joins with MemberShip table, makes the query harder to read and adds noise to the code. You want your entity data model to support Many to Many relationship in addition to two 1 to many relationship. For querying purpose you want to leverage the Many to Many relationships but Inserts and Updates can still be processed using a separate Membership link table.

Solution: When we import Club and Members table, entity framework does not get rid of the link table because it has additional columns that are required for the relationship and cannot be specified on insert and update if link entity

did not exists. For querying purpose, we can also create an additional

association that has Many To Many relationship between Club and Members that is void of additional columns. To accomplish that we have to manually modify the SSDL file as this is not supported in the designer. In the ssdl model, we have to create an EntityType that contains only the primary keys from Club and Members entity. In addition we have to define an entityset that will use a DefiningQuery to only retrieve the columns we mentioned in the entity type earlier and also map the results of the query to the EntityType.

After creating EntityType and EntitySet, we can go into the designer and create a Many To Many association between Clubs and Members and map the association to the entityset we created in the ssdl.

Discussion: In this walk through we will define a Many to Many association between Club and Member in addition to two 1 to many associations created by the designer when we import the tables into our model. To get started we will create 3 tables Clubs, Members and Membership table. Screen shot below shows the database diagram after creating the tables and relationship between them.

After creating the tables, we will import the model into EDM by using Update Model from the database. Screen shot below shows the entity data model created by EF when we import the tables.

The next step is to create a new entity type called ClubMember with only primary keys from Club and MemberShip table which is ClubId and

MemberShipId. The entity would be part of an entityset which we will call ClubMembers and data for the entityset would come from a defining query.

Now you must be wondering why we cannot simply create an association between club and members and map the association to existing Membership table we imported earlier. The reason is because you cannot map two entities to a single row in a table. Since we already have mapping for a record in membership table that was created when the model got imported as two 1 to many relationship, we cannot reuse that. When you try to map the Many to Many associations to the same Membership table, edmx validation would raise the following error.

Error 3034: Problem in Mapping Fragments. Two entities with possibly different keys are mapped to the same row.

SSDL below shows the EntityType and EntitySet created that will later be used in mapping Many To Many associations between club and Members.

<EntitySet Name="ClubMember" EntityType="Self.ClubMember">

On ssdl above, ClubMember only contains entity keys from Club and

Members table. The results of ClubMember entity set comes from a defining query which only retrieves ClubId and MembershipId columns and is mapped to ClubMember entity type. Since we are editing the SSDL model manually, it is important to know that if you try to update the model again, you will lose all your changes as the ssdl model gets overwritten. In the next release of EF, the update model wizard would preserve changes made to ssdl model even after updating the model from the database. After creating entity type and entityset we can go back to the designer and add Many to Many association between Club and Member by right clicking Club entity, selecting Add and choosing association. Screen shot shown below captures the association we have created.

The above assocication has Club and Members as both sides of the

association with a multiplicity of Many. To access the Members for Club from Club entity, we will use Members navigation property. Similarly to access Clubs collection from member, we will use Clubs navigation property.

Next step is to map the association to the virtual table we created earlier on the stored. To do that, right click the Many to Many associations we created earlier and select table mapping. When you select ClubMember table, the designer auto populates the fields with entity keys defined on both ends of the association.

Screen shot below shows how we mapped many to many associations.

Screen shot below shows the completed model with 2 to 1 many associations and 1 read only Many to Many association between club and member.

To confirm the mapping is configured correctly we can write a simple query that accesses clubs a member is associated with without using the

Membership intermediary table. Codes below uses include to eagerly load

Clubs for a member. Using the Clubs property, I loop through the club collection and print the club name to the output window. Screen shot below shows the output to console window.

var db = new ManyToManyEntities();

var scott = db.Members.Include("Clubs").First(m =>

m.Name == "Scott");

foreach (var club in scott.Clubs) {

Console.WriteLine(club.ClubName);

}

Exposing Many To Many Relationship as EntityReference

2.2.5 Mapping Many to Many table as 2 Many to Many