Violation of PRIMARY KEY constraint in Entity Framework code first link table

Question

I have a User table and a Roles table. There is a automatically generated UsersRoles link table which contains the Id from the User and Roles tables. This is generated using the following code:

modelBuilder.Entity<User>()
                .HasMany(u => u.Roles)
                .WithMany(r => r.Users)
                .Map(c => { 
                    c.MapLeftKey("UserId");
                    c.MapRightKey("RoleId");
                    c.ToTable("UsersRoles");
                });

When I am trying to add an unrelated Entity and call Context.SaveChanges() I receive the following error:

Violation of PRIMARY KEY constraint 'PK_UsersRoles'. Cannot insert duplicate key in object 'dbo.UsersRoles'. The duplicate key value is (2beaf837-9034-4376-9510-b1609c54efbe, dcd16d00-d46e-4d48-8328-3e7b35b11ccf). The statement has been terminated.

I have checked the Conext.ChangeTracker.Entries() for the items mentioned in the error and the Entity State is marked as Unchanged.

The only Entity that is marked as Added is the new record I am trying to add, everything else is marked as Unchanged.

Code for adding Entity:

 RoleGroup group = Context.RoleGroups.Create();
 group.Title = roleGroupName;
 Context.Set<RoleGroup>().Add(group);
 Context.SaveChanges();

Does anyone know why this is happening?

Answer 1

To add to Slauma's answer, it looks like if you set the ICollection property on one of these auto many-to-many things, EF gets confused and doesn't realize that you are clearing the collections elements by doing this.

So instead of doing this:

user.Roles = new List<Role>();
user.Roles.Add(role);

You have to do this:

user.Roles.Clear();
user.Roles.Add(role);

This worked for me.

You shouldn't have to do this though. This should be a bug in EF.

Answer 2

The fact that the User with UserId = 2beaf837-9034-4376-9510-b1609c54efbe and the Role with RoleId = dcd16d00-d46e-4d48-8328-3e7b35b11ccf are in state Unchanged does not mean that nothing gets written to the database.

Especially for many-to-many relationships (generally for independent associations) EF maintains a state for the relationship itself which is different from the entity state. If an entry gets inserted into the link table it means that the relationship entry for the two entities in question is in state Added although the entity state for those entities is Unchanged. You cannot see the relationship entry when enumerating the DbContexts ChangeTracker. It will only return entity states. You have to go down to the underlying ObjectContext to query for the relationship state.

Example:

using (var ctx = new MyContext())
{
    var user = ctx.Users.Find(1);
    var role = ctx.Roles.Find(5);

    user.Roles = new List<Role>();
    user.Roles.Add(role);

    ctx.SaveChanges();
}

Here user and role will be both in state Unchanged, but still a record is inserted into the link table. And this code will throw your exception if user 1 and role 5 are already linked in the database.

Adding the group has nothing to do with the problem. Only the call to SaveChanges is causing the exception because you most likely have created a relationship between the two entities somewhere before the code snippet in your question.

Answer 3

You are trying to insert the same combination of user / role into the UserRoles table:

Violation of PRIMARY KEY constraint 'PK_UsersRoles'. Cannot insert duplicate key in object 'dbo.UsersRoles'. The duplicate key value is (2beaf837-9034-4376-9510-b1609c54efbe, dcd16d00-d46e-4d48-8328-3e7b35b11ccf). The statement has been terminated.

Check to see which user has the ID of 2beaf837-9034-4376-9510-b1609c54efbe, and which role has the ID of dcd16d00-d46e-4d48-8328-3e7b35b11ccf. If you are sure you've only entered that user once, is the method that inserts the data being called more than once without you realising?