Adding Many to Many Relationship between Entities

Adding Many to Many Relationship between Entities

A friend of mine asked me
this week a question regarding
link tables in Entity Framework.
In their system they have a
link table of many to many
relationship between more then
two tables.

The Problem

We have a link table with more then two reference keys but
Entity Framework map this table into an entity instead of an
entity association of many to many. For example I have the following
hypothetic database which has a relation of many to many between
persons, companies and countries:

The result of the mapping after using the Entity Framework Wizard will
look like:

The problem with this mapping is when we want to query the entities,
we use extra joins with CountriesToPersonsToCompanies table which we
would like to spare. For the propose of inserts and updates it will give a
better performance using the two one to many relationship way but for
queries we will pay in performance.

Modeling two one to many relationship as Many to Many relationship

We would like to keep the two one to many relationship for the CUD
operations but for queries we would like to build a many to many
relationship.
How to do that?
We will build a new EntityType for every new association that we need that
will hold the primary keys of the table we want to use. Also we will need a
new EntitySet for the created EntityType which will use a DefiningQuery to
retrieve the data. Then in the designer we will build a new association of
many to many and map it to the new created EntitySet.

Step 1 – Create the EntityType 

In order to create a new EntityType we will need to edit the SSDL manually.
Open the SSDL in Xml editor mode and create a new EntityType for each
many to many relation that you need. The following Xml fragment is
an example of an EntityType for the given problem:

<EntityType Name="CompaniesToCountries">

  <Key>

    <PropertyRef Name="CompanyID" />

    <PropertyRef Name="CountryID" />

  </Key>

  <Property Name="CompanyID" Type="int" Nullable="false" />

  <Property Name="CountryID" Type="int" Nullable="false" />

</EntityType>

Step 2 – Create the EntitySet

After we defined the EntityType we will create a new EntitySet for that
type. The EntitySet will be created using a DefiningQuery element which
is a read only view definition for that set. The following Xml fragment is
an example of an EntitySet for the given problem:

<EntitySet Name="CompaniesToCountriesSet" EntityType="Self.CompaniesToCountries">

  <DefiningQuery>

    SELECT CompanyID, CountryID FROM CountriesToPersonsToCompanies

  </DefiningQuery>

</EntitySet>

Step 3 – Adding Many to Many Association in the Designer

When we have the EntityType and EntitySet we can go to the designer
and create a new many to many association to map to the created
EntitySet.
From the designer surface press right mouse key and choose
Add –> Association.

In the Add Association form create the many to many relation between
the two entities.

Then in the Mapping Details View map the new association to the
new EntitySet that we created earlier.

After that we will get the following model which has a new many to
many relationship between Country entity and Company entity.

Step 4 – Check the relation

After building the new relationship we need to check it.
The following code will use the new many to many relationship
that we created:

using (EntityFrameworkExampleEntities context = new EntityFrameworkExampleEntities())

{

    var company = context.Companies.Include("Country").First();

    foreach (var country in company.Country)

    {

        Console.WriteLine(country.CountryName);

    }

    Console.ReadLine();

}

Step 5 – Do steps 1-4 for each many to many relationship that you want to add

Summary

Lets sum up, I showed how to add a new many to many relationship
in order to retrieve data when Entity Framework didn’t mapped my
relationship to a many to many relationship. One drawback of this
solution that using the Update Model from Database feature will
erase all the changes that we did because we edited the schema manually.