Many To Many Mappings in Entity Framework

So far in my previous blog postings, I have discovered goodness with linq to SQL as I travel the path of migrating from linq to SQL to entity framework. This is not to deny that entity framework also has plus points which cannot be ignored. Among them is support for many to many relationship. Many to Many relationship is a concept that is very common in most OR mappers so one would wonder why it did not make it into the service pack release for sp1 for linq to SQL. I am sure Danny Simmons will have a better answer to this question than me.

Many to Many tables are used in the database when both sides of the tables has many relationship to the other side of the table. It is easier to explain the concept through an example. For instance if you have Employees and Projects. An employee can be part of many projects and in a single project there can be many employees participating. A scenario like this requires a third table which can include relationship from both tables. That table can be called ProjectAssignment. Below shows how the database diagram looks like.


In the above example, I have a table called Employees, which has all the employees in the database. Another table Projects which lists all the projects in the database. Than I have the connecting table called ProjectAssignment which defines how each employee is tied to a project. Notice in the ProjectAssignment, I have marked combination of EmployeeId and ProjectId to be the primary key. because you cant have the same employee be repeated for a given project. When I run the entity framework designer on the database, I end up with the following entity framework diagram.


When you look  at the above generated entity diagram, you must be wondering where did my third table ProjectAssignment go? Well you don't need it. ProjectAssignment was a table in the database only used for normalization because database tables do not directly support the concept of many to many tables and therefore the need to bring in the third table arose. However in the OO world, many to many relationships can be mapped easily between objects using navigation relations where you have reference to Employee object, you can use Projects property to get access to all the projects an employee is part of. Similarly Project entity exposes an entity set called Employees which you can use to traverse to get a collection of all the employees participating in the project. Following code shows how to achieve that in the code.


In the above example, I am accessing the projects collection of the employee to find out the total projects an employee belongs to. Same is the case with getting all the employees that are part of the project by accessing the employees entity set available on project entity.

So what are the use cases that actually do not turn into many to many relationship in the entity diagram? In cases where many to many table is not only used for relationship, but also carries the responsibility of capturing data specific to that relationship. For example, if we we decide that for each employee in a specific project, we need track how much percentage of the project has been completed and also how much money an employee has spent on that project. The best place to keep track of these additional attributes would be the ProjectAssignment table because it is a table that manages the relation of an employee to a project. The screen shot below shows how the database diagram looks like after adding additional attributes.


Above screen shot shows 2 more new columns, Amount Spent and PerComplete that we are tracking inside of ProjectAssignment table for each employee on a specific project. Since these attributes are an important attributes to a relationship, it is no longer converted to many to many tables relation. Converting this relation to simply many to many relationship would cause it to loose attributes that we are tracking for that relation. Hence entity framework keeps this table intact in the entity framework designer and if you have to access the projects for an employee, you have to travel your way through the ProjectAssignment table to reach either projects or employee table. Code below shows the how to get all projects for an employee and all employees for a project.



In the example above, to access all the projects for an employee, I have to navigate to all the ProjectAssignment and for each ProjectAssignment access its Project. I do the similar navigation when I have to get access to all the employees for a particular project.


  • NorthwindEntities entity = new NorthwindEntities();
    var product = entity.Products.First(p => p.ProductID == 1);
    var order = product.Order_Details.Select(o => o.Orders);
    lblCount.Text = order.Count().ToString();
    grdView.DataSource = order;

    Could you please correct me? It's returned 0 row. But When I execute with management tool, It return 38 records.


  • Welcome to the hard facts of entity framework.
    Try this
    NorthwindEntities entity = new NorthwindEntities();

    var product = entity.Products.First(p => p.ProductID == 1);
    product.Order_Details.Load();//must be done.
    var order = product.Order_Details.Select(o => o.Orders);

    lblCount.Text = order.Count().ToString();

    grdView.DataSource = order;


  • Hi. Very interesting post.
    Could you please help how to insert new rows when many-to-many is between two tables?
    Does is need to write manually function ro insert/update (where)???


  • Thanks.

    Your post helped me a lot.

  • Hei,

    This is a very good post. It really helps me .

  • How do you save changes after assigning new relationships between many-to-many entities.



  • Hello,

    Good explanation, but I'm also wondering how I'm gonna save changes or do inserts on the "link" / many-to-many table. E.g. Add an employer to the project.

  • Very useful thanks
    But im having a problem. My count is equal to 0.
    But on the database i inserted a project, an employee, and also the normalized table.
    So...why the count of navigation properties on the 2 entites is 0?

  • Pretty useful! Is there a way to have the objects loaded implicitly though?

  • Dies ist ein gro�er Ort. Ich m�chte hier noch einmal.

  • How do you remove a many to many relation??

  • How we can assign a project to an employee. I need code please.

  • How we can assign a project to an employee. Need my code examples

  • You may query first the project, create a employee entity witht the values and then say:

    project.employee = employeeNewEntitie;


  • i want know how to create observable collection in silverlight 3 application plz ans me

  • any idea how to select a list of projects that have not been assigned to a specific employee?

    There are Project 1, Project 2, Project 3

    IE: Employee A has Project 1 and Project 3 assigned in the Project Assignment Table

    I require the query to return only Project 2

    Employee B has project 2 Assigned, I require a query that will return Project 1, Project 3

  • Hi,

    I'm trying the code below but it doesn't work.
    I'm just wanna select a order and then show all the related products in na datagridview.
    The produtos.count is getting correct but the datagridview is empty.

    Using dboc As New NorthwindEntities
    Dim pedido = dboc.Orders.First(Function(p) p.OrderID = 10255)
    Dim produtos = pedido.Order_Details.Select(Function(prd) prd.Products)
    lblProdutos.Text = produtos.Count
    gdvProdutos.DataSource = produtos
    End Using

    What´s is missing ??


  • Hi,
    Problem solved !

    Using dboc As New NorthwindEntities
    Dim consulta = (From p In dboc.Orders.Include("Order_Details") _
    Select p _
    Where p.OrderID = 10255)

    For Each m In consulta
    For Each p In m.Order_Details
    End Using


  • Hi all!

    I've faced a problem running EF + WCF RIA Services + Silverlight4 using many-to-many relationships. The issue is:

    1-My EF classes are correctly generated (my Employee class has a field with a set of Projects).
    2-My RIA service metadata classes are correct too (the Employee metadata class contains a field with a set of Projects).


    3-The autogenerated code (from the RIA service), in the client side (SL4), doesn't have the set of Projects field, so I can't get access to the projects the Employee is involved in.

    The autogenerated code in the client side has the rest of the relationships (one-to-many), but not this one (many-to-many) and by getting an Employee I can't access to hes list of projects.

    Are you able to see the point??
    Any thought about this??

  • Thanks for the related entities Load(), didnt know you have to load them manually to see what is releated to the record.


  • Thanks a lot, I have been looking for this vital piece of background information for a few days now, very well explained.

  • Hey, i really had to clap for your blog. I'm lookign for some info on this matter in dealing with many-many relationships. I hope u can help me here.

    I have a many-many relationship in my model. let's say the projects person. My model does not generate the intermediate entity, which is fine. however, when I want to assign a person to an existing proyect i had lot of issues. First I try to retrieve the proyect in want the person to be assigned to, then I added this object to the proyects list the person entity has, but it said something like thos entities belong to diffente ObjectContext. I think we already solved that issue by dettaching and re-attaching the proyect object. Now, it seems to work just fine until the point I send my person object to update. I seem tobe update in memory but when we svaeChanges in the DataContext object, it return -1 and it does not affect the tables.

    Well I hope it's clear and you can give us a clue.

    thanks in advance

  • I am having trouble deleting elements from one of the two tables, please help!

  • How do you make a primary-foreign key, like in your ProjectAssigment table? I can only create a primary key or a foreign key

  • Excelent post. Was exactly what I was looking to answer. I am developing with the pattern Model-First and I generate the database from the model.


Comments have been disabled for this content.