Violation of Primary Key error when trying to insert record into SQL Server using Entity Framework

Question

Violation of PRIMARY KEY constraint 'PrimaryKeyId'. Cannot insert duplicate key in object 'dbo.Table'. The duplicate key value is (xxx).\r\nThe statement has been terminated.

This has been occurring off and on for several weeks now, and every time I think I have it fixed, and it pops up a couple days later. I am using:

dbContext.Table.Add(myObject);
dbContext.SaveChanges();

This is in a using statement, trying to add an object with a current id of 0. The PrimaryKeyId is the identity in the table, and is set to auto-increment by 1. However, Entity Framework seems to be taking a random Id and trying to assign it to my object and then add said object to the database.

This only happens on this one table and this same process is used on many other tables without any problems. The table being acted on is set up identical to other tables where this process is being used without error. Any ideas as to what could be causing this? To clarify, Entity Framework appears to be attempting to assign an already existing Primary Key to a new object.

Solution to my specific problem: Tar and feather a DBA

Explanation: While running a lengthy/complex import script, our DBA set it up to reseed the table at x, which is way below the current value in the identity column. So there technically hasn't been a problem these past several weeks, it was just human error. This question could/should/maybe ought to be dragged out behind a woodshed and put out of its misery. The tarring/feathering is his suggestion by the way (I do not endorse abuse of coworkers without their consent).

In the event that this question isn't deleted, the recommended fix is to check the current identity value on the table by using

 select ident_current('tableName') 

and comparing it to the highest value in the table. Especially if there are manual imports/modifications being done through a script where the seed might be manually reset.

Answer 1

This should prove helpful (Particularly the paragraph I have made bold): Taken from Working with Entity Keys

Entity Keys and Added Objects

When a new entity is created, the Entity Framework defines temporary key and sets the IsTemporary property to true. When you call the SaveChanges method, the Entity Framework assigns a permanent key and sets the IsTemporary property to false.

If the corresponding column value is an identity that is generated in the database, set the StoreGeneratedPattern attribute of the property element of an entity in the storage model to Identity. When the Entity Data Model tools generate a data model from an existing data source, the StoreGeneratedPattern attribute is added to each property element (CSDL) element that represents an identity or a computed column in the data source. The Entity Framework replaces the value of the property in a temporary key with the identity value that is generated by the data source after SaveChanges is called.

The following details the internal process that replaces the temporary key with a permanent key that contains the server-generated values:

• The entity object is constructed.
• At this point the key properties all have default values, either null or 0.
• The new object is added to the ObjectContext either by calling the AddObject method on ObjectContext or ObjectSet or by adding an object to the collection of objects on the "many" end of the relationship.
• At this point, the Entity Framework generates a temporary key, which is used to store the objects in the ObjectStateManager.
• SaveChanges is called on the ObjectContext.
• An INSERT statement is generated by the Entity Framework and executed on the data source.
• If the INSERT operation succeeds, server-generated values are written back to the ObjectStateEntry.
• The ObjectStateEntry updates the object with the server-generated value.
• When AcceptChanges is called on the ObjectStateEntry, a permanent EntityKey is computed by using the new server-generated values.
• AcceptChanges is called automatically at the end of the SaveChanges execution, or when the SaveChanges method is called with the AcceptAllChangesAfterSave flag.
• The ObjectStateManager replaces all instances of the temporary key with the new permanent key.