97ldave 97ldave - 2 months ago 22
C# Question

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

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

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.