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.

image

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.

image

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.

   image

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.

image

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.

image

image

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.

Published Thursday, August 21, 2008 12:26 AM by zhirani

Comments

# re: Many To Many Mappings in Entity Framework

Friday, August 22, 2008 10:08 AM by quachnguyen

       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;

       grdView.DataBind();

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

Thanks,

QuachNguyen

# re: Many To Many Mappings in Entity Framework

Friday, August 22, 2008 2:15 PM by zhirani

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;

      grdView.DataBind();

# re: Many To Many Mappings in Entity Framework

Saturday, August 23, 2008 2:14 AM by quachnguyen

Thank for you replying.

But I got run-time error with message

The data source for GridView with id 'grdView' did not have any properties or attributes from which to generate columns.  Ensure that your data source has content.

I also create column with databound filed.

Please show me how to fix problem,

Thanks

# re: Many To Many Mappings in Entity Framework

Thursday, September 18, 2008 9:14 AM by Mikhail Lunin

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!

# re: Many To Many Mappings in Entity Framework

Monday, September 22, 2008 8:36 AM by diver_182@yahoo.de

Thanks.

Your post helped me a lot.

# re: Many To Many Mappings in Entity Framework

Monday, September 29, 2008 4:26 AM by MisterFantastic

Hei,

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

Thanks

# re: Many To Many Mappings in Entity Framework

Friday, October 17, 2008 2:40 PM by Robert

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

User.Roles.Add(role1);

User.Roles.Add(role2);

etc..

SaveChanges...?

# re: Many To Many Mappings in Entity Framework

Thursday, November 20, 2008 8:55 AM by Jan-Erik

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.

# re: Many To Many Mappings in Entity Framework

Friday, November 21, 2008 7:00 AM by Marco

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?

# re: Many To Many Mappings in Entity Framework

Tuesday, November 25, 2008 9:05 AM by Steph

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

# re: Many To Many Mappings in Entity Framework

Sunday, March 8, 2009 4:35 AM by ...

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

# re: Many To Many Mappings in Entity Framework

Wednesday, March 11, 2009 9:54 AM by Christoffer

How do you remove a many to many relation??

# re: Many To Many Mappings in Entity Framework

Monday, May 11, 2009 10:12 AM by Qasim Ali

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

# re: Many To Many Mappings in Entity Framework

Monday, May 11, 2009 10:14 AM by qasimali84

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

# re: Many To Many Mappings in Entity Framework

Tuesday, June 9, 2009 2:35 PM by Escarcha

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

project.employee = employeeNewEntitie;

context.savechanges();

# re: Many To Many Mappings in Entity Framework

Tuesday, August 11, 2009 10:54 AM by reka

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

# re: Many To Many Mappings in Entity Framework

Friday, August 28, 2009 11:07 AM by ancalagon

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

# re: Many To Many Mappings in Entity Framework

Thursday, January 21, 2010 1:56 PM by david ZIng

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)

pedido.Order_Details.Load()

Dim produtos = pedido.Order_Details.Select(Function(prd) prd.Products)

lblProdutos.Text = produtos.Count

gdvProdutos.DataSource = produtos

End Using

 What´s is missing ??

Thanks

# re: Many To Many Mappings in Entity Framework

Thursday, January 21, 2010 7:51 PM by david ZIng

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

   p.ProductsReference.Load()

   lstProdutos.Items.Add(p.Products.ProductName)

Next

Next

End Using

Thanks

# re: Many To Many Mappings in Entity Framework

Wednesday, February 10, 2010 12:51 PM by Alberto

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).

but

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??

# re: Many To Many Mappings in Entity Framework

Thursday, March 18, 2010 3:05 PM by Atif

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

Cheers

# re: Many To Many Mappings in Entity Framework

Saturday, May 8, 2010 9:31 PM by Paul Connolly

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

Paul

# re: Many To Many Mappings in Entity Framework

Wednesday, November 10, 2010 10:00 PM by Ricker Silva

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

# re: Many To Many Mappings in Entity Framework

Thursday, February 10, 2011 12:47 PM by Albino

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

# re: Many To Many Mappings in Entity Framework

Friday, June 10, 2011 3:05 PM by lilk9

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

# re: Many To Many Mappings in Entity Framework

Sunday, October 30, 2011 10:50 AM by Mike Tirado

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.

Thanks