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.