4

I have 3 tables - 

User (Id, Name)
Roles (Id, Name)
UserRoles (UserId, RoleId)

I think they are self explanatory. How do I update an entry (UserId and RoleId) in UserRoles?

context.User.Roles gives me the list of roles but how do I update them?

Thank you.

tempid
  • 7,838
  • 28
  • 71
  • 101
  • There has been a similar question here: http://stackoverflow.com/questions/1732609/how-do-i-create-and-update-a-many-to-many-relationship-with-ef – Yakimych Aug 31 '10 at 19:31
  • That link says "load the desired object, set the changed properties and call SaveChanges on the context." context.User.Roles gives me the list of roles. I can do a for-each and update the Id, but how do I update the corresponding UserId foreach RoleId in that table? – tempid Aug 31 '10 at 19:48
  • Anyone? This is driving me crazy. – tempid Aug 31 '10 at 21:18

1 Answers1

6

From your comment:

context.User.Roles gives me the list of roles. I can do a for-each and update the Id, but how do I update the corresponding UserId foreach RoleId in that table?

First of all, you should NOT update the Id's.
Secondly, since you are using EF, you should try to think in terms of objects (or entities), rather than "DB-many-to-many-mapping-tables". Every User entity has a collection of Roles. If you remove a Role from the User.Roles collection and call context.SaveChanges(), the corresponding entry will be deleted from the UserRoles tabele. Similarly, when you add a Role object to the User.Roles collection, and save changes, a new entry will be created in the UserRoles table.
The following sample might be useful for clarity:

var user = context.Users.Include("Roles").Where(u => u.Name == "User1").FirstOrDefault();
user.Roles.Remove(user.Roles.Where(r => r.Name == "Admin").FirstOrDefault());
context.SaveChanges();

(null-reference checks omitted for simplicity).

Yakimych
  • 17,612
  • 7
  • 52
  • 69
  • Thank you for your answer. I learnt that I have to delete the record completely from the join table and add a new one instead of trying to update the existing record. Also, I needed to have the UserId and RoleId as a composite key in the join table for this to work. – tempid Sep 02 '10 at 13:16