How to avoid System.Data.Entity.Infrastructure.DbUpdateException

Question

I have this DbContext object which consists of -

- Employee
- CompanyAddress  (PK: AddressFirstLine, City)
Note: one Employee can have many CompanyAddress

Records are added to CompanyAddress table only if some address doesn't exists in CompanyAddress table.
If I have two DBContext objects from database say Snapshot1, Snapshot2. Say when both these snapshots were taken, there were no records in CompanyAddress table. When changes were made to Snapshot1 and saved - records are written to CompanyAddress table.
When changes were made to Snapshot2 and saved using

mydataContext.SaveChanges();

exception occurs:

System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries
System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_CompanyAddress'. Cannot insert duplicate key in object 'dbo.CompanyAddress'

It seems saving of Snapshot1 made Snapshot2 dirty because when they are saved back to database, both had same CompanyAddress records.

What other call/settings I can make on dbContext object to avoid this error?

Thank you!

trailmax · Accepted Answer · 2013-12-24 18:01:07Z

13

Your error has got nothing to do with the DbContext objects. Your problem is that you are trying to insert a record with duplicating primary key. That is what your exception message says.

Look at how you create your CompanyAddress objects and what are the keys when you save them - this will give you the clues.

Edit: And it is a bad idea to have primary key to be a natural key, i.e. you should not assign city and address as primary keys. You should have either Guid or Integer to be primary key that is not dependent on the information stored in DB.

And to enforce uniqueness, before you save to DB, you check if that record exists, and can add a unique index to database table based on the unique constraints.

answered Dec 24, 2013 at 18:01

trailmax

35.2k25 gold badges149 silver badges238 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1552869 Over a year ago

Thanks for replying! Before trying to add, I am actually checking whether that record exists or not. However, I am not sure how to tell my dbContext of snapshot2 to update itself when snapshot1 was written.

trailmax Over a year ago

Is there a reason to have 2 dbContexts? these are supposed to be used like singletons

user1552869 Over a year ago

The error was happening due to concurrency issue. For now, we just placed a try-catch around this piece of code and logged the error.

carloswm85 Over a year ago

"And to enforce uniqueness, before you save to DB" Is there a way to check this while saving to the DB? I'm trying to save up to 5,000 records not at once, but in 500 chunks, but this exception is popping up. I guess that at some point, MAYBE, EF is messing it up somehow with the int PK it is setting, but it is just a guess.

Collectives™ on Stack Overflow

How to avoid System.Data.Entity.Infrastructure.DbUpdateException

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related