Add / remove many of the many associations in the Entity Framework

Question

I have three tables in my database:

of users

• I WOULD
• Username
• password

Roles

• I WOULD
• title
• Description

UserRoles

• User ID
• Role id

UserRoles is a correspondence table that mimics a many-to-many relationship. Adding entries to this table allows you to link entries in users and roles. My problem is that in the Entity Framework it correctly interprets this as a many-to-many relationship and abstracts this lookup table.

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

I know one possibility to add a dummy column to the UserRoles lookup table. This will force the Entity Framework to turn the lookup table into a full entity, which will allow me to add and remove them as separate objects. But I do not need a dummy column, and it looks like a hack. I am looking for the best deals.

Answer 1

It should look something like this:

Deleting Relationships

user.Roles.Remove(existingRoleEntity); 

Add relationship

 user.Roles.Add(existingRoleEntity); 

Answer 2

You can use navigation properties for objects:

(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 comment.

Answer 3

I solved this problem earlier by simply adding an Auto-Increment Private Key Identifier column to the lookup table, since the Entity Framework will always hide lookup tables that contain only 2 columns with foreign keys in the end tables. Sometimes you need to add a record directly directly through the Entity Framework , and this will help you with this.

Update from the author of the question

I just wanted to provide an update of my own implementation of this answer. I added an identifier column to the lookup table and created a unique key on two columns of the foreign key to prevent duplicate relationship entries in the table. Now my model is as follows:

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

The only thing that sucks is to get a collection of all the related roles that I would have to do:

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

This is a bit more, but if there is no 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 with:

 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 the property of returning all roles using the above. Click the link for details on the public partial class things I gave on another question.