Rollback or commit Transactions inside a foreach loop

Question

I'm using Entity Framework 6 and .Net Framework 4.8 in a MVC application. I'm trying to do two things to a list of entities (invoices):

• Generate an email and send it.
• Update the entity and save it.

When the email fails to send, I want to roll back all changes I made to the entity.

This is my code:

foreach (var id in listOfIds)
{
  using (var dbContextTransaction = db.Database.BeginTransaction())
  {
    try 
    {
      var invoice = db.Invoices.Find(id);
      MakeChangesToInvoice(invoice);
      var pdf = GeneratePdf(invoice);
      SendEmail(pdf);
      db.SaveChanges();
      dbContextTransaction.Commit();
    }
    catch(SomeEmailException)
    {
      dbContextTransaction.Rollback();
    }
  }
}

The problem here is that when I have a succesfull iteration AFTER a faulty iteration, the changes of the faulty iteration (that called Rollback) still get saved.

Answer 1

The entity you've modified is still tracked by context, so changes will be propagated to the db on the next SaveChanges call (in the next iteration), so you need either recreate your dbcontext (which can possibly have a noticeable hit on performance, need to check that) for each iteration or detach the entity. Something like this:

Invoice invoice = null;
try 
{
  invoice = db.Invoices.Find(id);
  MakeChangesToInvoice(invoice);
  var pdf = GeneratePdf(invoice);
  SendEmail(pdf);
  db.SaveChanges();
  dbContextTransaction.Commit();
}
catch(SomeEmailException)
{
  dbContextTransaction.Rollback();
  // note that if you have complex object graph this possibly will not detach child objects 
  if(invoice != null) dbContext.Entry(invoice).State = EntityState.Detached;
}

Or use DetachAll from this answer if it is suitable. Also note that in case of bi number of objects recreating context can be a better choice (or you can recreate it only in case of an error in the previous run).