c# - 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!

Answer 1

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.