Adding Many to Many Relationship between Entities

March 7, 2009

Adding Many to Many Relationship between Entities


A friend of mine asked meAdding Many to Many Relationship between Entities
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:
Example Database


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


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.
Add New Association
In the Add Association form create the many to many relation between
the two entities.
Add Association Form
Then in the Mapping Details View map the new association to the
new EntitySet that we created earlier.
Mapping Details


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


 


 


Entity Designer Diagram After Adding Many to Many Relationship
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.


DotNetKicks Image

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

2 comments

  1. LukeMarch 9, 2009 ב 13:47

    I’ve hit a similar problem with a different ORM mappers like this before – Id be very interested to know if dropping the CountryToPersonToCompany.ID column and setting a Composite Key across the remaining three Foreign Keys would give EF the ability to mould this together properly?

    Reply
  2. Gil FinkMarch 9, 2009 ב 20:30

    @Luke,
    Making a Composite Key across the remaining three Foreign Keys and droping the ID field will still generate an entity instead of a many to many relation. About the example, its a real life database which have a many to many relation between a lot of tables so my friend can’t drop the ID field.

    Reply