NovaDev NovaDev -4 years ago 145
C# Question

Update a Many to Many relationship using Entity Framework?

I have a many to many join table, with a dual key (PersonId, RoleId). And for for simplicity, I've just got one PersonId in my Person table. Also, using EF7, which doesn't yet support a lot of the goodness that EF6 does (such as implicitness in joining the tables via navigation properties).

Whereas I can run this query in SQLite and it works with no problem:

update PersonRole set RoleId = 2 where PersonId = 1
, I cannot do the same thing in EF:

var du = context.PersonsRoles.Where(p => p.PersonId == 1).First();
du.RoleId = 2;
context.SaveChanges(); //get an error here

The error being this: "An unhandled exception has occurred: The property 'RoleId' on entity type 'PersonRole' is part of a key and so cannot be modified or marked as modified."

(ETA per comment below) - my model is:

public class PersonRole
public virtual int PersonId { get; set; }

public virtual int RoleId { get; set; }

I found an answer that included an option to delete the original row (1, 1) then reinsert (1, 2), but that seems inefficient to me. Is that really the only way to modify the relationship?

Answer Source

You are attempting to modify the key of a many-to-many relationship on the one side. Many-to-many relationships are represented in the database with a table holding the foreign keys of both sides in the relationship.

What you attempt to do, is attempting to change the key of an object, but a reference is still held in the foreign key table, leading to a constraint violation - as the value in the N-N table has not been updated.

This change is not allowed in EF7. You should use an SQL command to do it instead of taking into consideration the many-to-many table update.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download