Dealing with many to many relationship in Entity Framework

Problem:

If table has many to many relationships and it doesn’t have any additional columns other than the referenced columns, entity framework will not generate the entity by default, which causes the issue to manage the relationships.

Solution:

In Entity Framework 4.0 this many to may relationship made easy which allows the automatic insertion of records in both the master and child table in a single transaction.

To describe the problem further, let us assume the below scenario.

Here the database contains Users and Roles tables. Both the tables are being referred by UserRoles. Our next step is to generate the Entities through Ado.Net Entity Data model template. After successful Entity creation, if we open the generated entities it will show only the two master tables and not the other UserRoles table. The same can be seen in model browser where all the tables are being listed in “Entities” section and only two tables in “Tables” section as below.

For a moment we might think that it’s a problem with model generator because of which the table got missed. But if we open the EDMX file in custom editor(right click on EDMX file->Open With->Automatic Editor Selector (XML)) all the properties will be listed as below.

So by this it is clear that entity generator has all the properties that are required to map the tables. It internally maintains the relationship without showing the actual table as there are no additional properties for “UserRoles” table except the primary key columns of the master tables.

Then how can we insert into this table without having entity being generated?

Entity framework will take care of that and we don’t need to write any additional queries to manage this table i.e. if we insert a user into Users table, the related record with the respective relations will be added to the UserRoles table. Here is the sample code to insert the user record and see how the records are added to both the Users and UserRoles table.

using (DemoEntities demoEnt = new DemoEntities())
{
User userInfo = new User()
{
UserId = 1,
UserName = “Super Admin”,
Password = “admin”
};
Role userRole = (from r in demoEnt.Roles where r.RoleId == 1 select r).Single();
userInfo.Roles.Add(userRole);
demoEnt.Users.AddObject(userInfo);
demoEnt.SaveChanges();
}

The resulted table results will be as below:

Note that both the DML statements will be wrapped in “Begin transaction” to avoid the redundant data on behalf of entity framework itself.
One another problem is selecting the rows from Users table associated with specific role. How can we relate the Users and Roles as there is no common column to join these?
It is pretty simple as we have relation between User and roles table. Below LINQ query would solve the issue.

public List<User> GetUsersByRole()
{
using (DemoEntities demoEnt = new DemoEntities())
{
var userList = (from u in demoEnt.Users

from r in u.Roles

where r.RoleId == 1

select u).ToList();
return userList;
}
}

Note that if UserRoles table has at least one additional column other than the existing two columns (UserId, RoleId), this table will be generated as Entity as shown below and in this scenario everything will be same as in the normal way. In the below model we have added the “notes” column to the UserRoles table.

Thanks for reading.

Happy Coding