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

.net, c#, ef-code-first, entity-framework

Solution

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 `DbContext`s `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.

Problem

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?

Original source