Entity Framework Saving many to many changes

Question

I have a Users 1-* UserGroupLinks *-1 UserGroups Table structure and have created the following method:

public static string SaveUser(User user, List<UserGroup> newUserGroups)
{
    using (var dbContext = new DCSEntities())
    {       
        var existingUserGroups = user.UserGroups.ToList<UserGroup>();
        existingUserGroups.ForEach(d => user.UserGroups.Remove(d));                
        newUserGroups.ForEach(a => user.UserGroups.Add(a));
        dbContext.ObjectStateManager.ChangeObjectState(user, EntityState.Modified);
        dbContext.SaveChanges();
        return user.UserCode;
    }
}

I would like to remove all the usergrouplinks for that user, and then add the new list of usergroups. When I run this method I get a violation of primary key on the UserGroups object/UsergroupLink table, indicating that my attempt at removing the existing usergrouplinks has failed. How can I resolve this error?

So I've changed the original code to confirm a suspicion.

public static string SaveUser(User user, List<UserGroup> newUserGroups)
{
    using (var dbContext = new DCSEntities())
    {
        User dbUser = dbContext.Users.Where(u => u.UserCode == user.UserCode).Include(ug => ug.UserGroups).Include(s => s.Status).FirstOrDefault();
        var existingUserGroups = dbUser.UserGroups.ToList<UserGroup>();
        existingUserGroups.ForEach(d => dbUserUserGroups.Remove(d));   
        newUserGroups.ForEach(a => dbContext.UserGroups.Attach(a));
        newUserGroups.ForEach(a => dbUser.UserGroups.Add(a));
        dbContext.ObjectStateManager.ChangeObjectState(dbUser, EntityState.Modified);
        dbContext.SaveChanges();
        return dbUser.UserCode;
    }
}

What I can confirm is that this code works when and only when adding new groups for that user. As soon as you try and add an existing group, it gives the primary key violation. It is almost as if the line

existingUserGroups.ForEach(d => dbUserUserGroups.Remove(d));

Is not taking effect. My solution below is not elegant and therefore I have not marked it as an answer.

{"Violation of PRIMARY KEY constraint 'PK_Groups'. Cannot insert duplicate key in object 'dbo.UserGroups'. The duplicate key value is (QA).\r\nThe statement has been terminated."} — breakerdotnet, Apr 09 '14 at 13:54
Does `existingUserGroups` have items in it after the first line in the `using` block? — Gert Arnold, Apr 09 '14 at 20:05

score 0 · Answer 1 · edited May 23 '17 at 11:49

The idea to use 2 different dbcontexts actually worked, but as per my comment above, I don't think it is an elegant or the correct solution:

public static string SaveUser(User user, List<UserGroup> newUserGroups)
{
    using (var dbContext = new DCSEntities())
    {
        User dbUser = dbContext.Users.Where(u => u.UserCode == user.UserCode).Include(ug => ug.UserGroups).Include(s => s.Status).FirstOrDefault();
        var existingUserGroups = dbUser.UserGroups.ToList<UserGroup>();
        existingUserGroups.ForEach(d => dbContext.UserGroups.Detach(d));
        existingUserGroups.ForEach(d => dbUser.UserGroups.Remove(d));
        dbContext.ObjectStateManager.ChangeObjectState(dbUser, EntityState.Modified);
        dbContext.SaveChanges();
    }

    using (var dbContext = new DCSEntities())
    {
        User dbUser = dbContext.Users.Where(u => u.UserCode == user.UserCode).Include(ug => ug.UserGroups).Include(s => s.Status).FirstOrDefault();
        newUserGroups.ForEach(a => dbContext.UserGroups.Attach(a));
        newUserGroups.ForEach(a => dbUser.UserGroups.Add(a));
        dbContext.ObjectStateManager.ChangeObjectState(dbUser, EntityState.Modified);
        dbContext.SaveChanges();
        return dbUser.UserCode;
    }
}

I even went as far as to copy this solution verbatim and still got the primary key violation issue entity framework update many to many relationship: virtual or not

If anyone can explain why I need to do this with 2 different contexts I would greatly appreciate it. Thanx

Entity Framework Saving many to many changes

1 Answers1