Update a Many to Many relationship using Entity Framework?

c# entity-framework entity-framework-core

Question

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.PersonsRoles.Update(du);
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?

Accepted Answer

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.


Popular Answer

You can use this extension i made to remove the unselected and add the newly selected to the list

    public static void TryUpdateManyToMany<T, TKey>(this DbContext db, IEnumerable<T> currentItems, IEnumerable<T> newItems, Func<T, TKey> getKey) where T : class
    {
        db.Set<T>().RemoveRange(currentItems.Except(newItems, getKey));
        db.Set<T>().AddRange(newItems.Except(currentItems, getKey));
    }

    public static IEnumerable<T> Except<T, TKey>(this IEnumerable<T> items, IEnumerable<T> other, Func<T, TKey> getKeyFunc)
    {
        return items
            .GroupJoin(other, getKeyFunc, getKeyFunc, (item, tempItems) => new { item, tempItems })
            .SelectMany(t => t.tempItems.DefaultIfEmpty(), (t, temp) => new { t, temp })
            .Where(t => ReferenceEquals(null, t.temp) || t.temp.Equals(default(T)))
            .Select(t => t.t.item);
    }

using it looks like this

 var model = db.Persons
             .Include(x => x.PersonsRoles)
             .FirstOrDefault(x => x.PersonId == person.PersonId );

 db.TryUpdateManyToMany(model.PersonsRoles, listOfNewRoleIds
 .Select(x => new PersonRole
 {
     RoleId = x,
     PersonId = person.PersonId
 }), x => x.PersonId);


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why