Add/Remove many to many associations in Entity Framework

Question

I have three tables in my sample database:

Users

• ID
• Username
• Password

Roles

• ID
• Name
• Description

UserRoles

• UserID
• RoleID

UserRoles is a lookup table to simulate a many to many relationship. Adding records to this table allows one to associate records in Users and Roles. My problem is that in Entity Framework it correctly interprets this as a many to many relationship and abstracts away that lookup table.

This works great most of the time, but I'm not sure what to do when I want to add/delete entries from that lookup table. I can delete roles or users but that actually deletes the objects not just their association with each other.

I do know of one option to add a dummy column to the UserRoles lookup table. That will force Entity Framework to turn the lookup table into a full-blown entity, allowing me to add and remove them as lone objects. But I have no need for a dummy column and this seems like a hack. I am looking for better suggestions.

Answer 1

It should look something like this:

To Remove Relationship

user.Roles.Remove(existingRoleEntity);

To Add Relationship

user.Roles.Add(existingRoleEntity);

Answer 2

You can use the navigation properties on the entities:

(assuming u is a User object):

using (var db = new UserEntities())
{
    Role roleToRemove = db.Roles.Single(SelectRoleHere);
    User user = db.Users.Single(SelectUserHere);
    user.Roles.Remove(roleToRemove);
    db.SaveChanges();
}

EDIT - Added SaveChanges based on Slauma's comment.

Answer 3

I have solved this problem before by simply adding an Private Key Identifier Auto-Increment column to the lookup table as Entity Framework will always hide lookup tables that only contain 2 columns with foreign keys to the end tables. Sometimes you need to add a lookup entry directly yourself via Entity Framework and this will help you achieve that.

Update From Question Author

I just wanted to provide an update on my own implementation of this answer. I added an identity column to the lookup table and created a unique key over the two foreign key columns to prevent duplicate relationship entries in the table. My model now looks like this:

http://www.codetunnel.com/content/images/ManyToManyDynamic.jpg

The only thing that sucks is to get a collection of all associated Roles I would have to do this:

List<Role> roles = new List<Role>();
foreach (UserRole userRole in myUser.UserRoles)
    roles.Add(userRole.Role);

It's a little more work but unless there is an equivalent to user.Roles.Remove(role) (something like user.Roles.Associate(existingRoleEntity)) then this is my only option.

Update:

List<Role> roles = new List<Role>();
foreach (UserRole userRole in myUser.UserRoles)
    roles.Add(userRole.Role);

Can be achieved via:

IEnumerable<int> roleIDs = myUser.UserRoles.Select(r => r.RoleID);
IEnumerable<Role> roles = Entityies.Roles.Where(r => roleIDs.Contains(r.roleID);

You can always use a public partial class to extend User to have a property to return all roles using the above. Click the link for details of the public partial class stuff I gave on another question.