Entity Framework: Remove and Add entities with same key in a single request

Question

I need to Remove and Add entities with the same primary key value in a single request, can anybody suggest me the solution?

Below is my sample code that gives the error: Violation of PRIMARY KEY constraint 'PK_Table'. Cannot insert duplicate key in object 'dbo.Table'.

context.Set<Entity>().Attach(existingEntityObj);
Entry(existingEntityObj).State = EntityState.Deleted;

context.Set<Entity>().Add(newEntityObj);
context.Entry<Entity>(newEntityObj).State = EntityState.Added;

context.SaveChanges();

Assume both the objects (existingEntityObj and newEntityObj) have the same value in the primary key property.

thanks in advance!!

Glasgow

You would have to drop the primary key constraint, remove your entity, add your entity, and add the primary key constraint back. However, you can't drop primary key constraints when FKs reference it. What you're doing sounds like a bad idea. Can you not just modify the existing object with the new object's data? – Aplenty

Answer 1

You'll need to do two SaveChanges() calls in order to make this work. The problem here is that, while it appears you are first deleting the record and then adding a new one, the framework is actually doing the insert first.

The reason is because Entity Framework doesn't give you granular control over what orders the operations happen in. So your best bet is going to be to wrap the two in separate TransactionScope's which will let you control the individual transactions that are occurring.

You can read more here: https://blogs.msdn.microsoft.com/alexj/2009/01/11/savechangesfalse/

Cheryllches

Similar answer with transaction example #2601188 – Veinule

Answer 2

A single update statement can work for you so the solution is to update the old entity with the new entity values, if you want to keep the same primary key value. As primary key identifies an entity, deleting old one and adding a new one in place of it will have the same effect as update.

Correct me if you feel I am wrong, by giving me a perfect example.

Commend

Answer 3

You can also try using explicit DbContextTransaction, like so:

using (DbContextTransaction transaction = context.Database.BeginTransaction())
{
  context.DoSomething();
  context.SaveChanges();

  context.DoSomethingElse();
  context.SaveChanges();

  transaction.Commit();

}

Don't forget to also catch exceptions and then do transaction.Rollback();

Chlor

It only works in EF 6.x – Aerostat