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.

Why do you want to use Transaction? It looks like just using db.SaveChanges() would be enough if you want to save data after successeful sending email and skip saving after fail. — Lana, Jan 10 '21 at 10:26
Updated my answer. There are changes to the entity before the email is sent, because I need those changes to create the PDF. FYI: my code is a lot more complex than the example I posted, but the details might make the question less clear. Let me know if you need more details. — Jon Koeter, Jan 10 '21 at 10:45
dbContextTransaction.Rollback() rollbacks only db changes, your changes in entities still exist, I am not so good in Entity Framework because I work with Entity Framework Core, but I think your changes in some entities exist and tracked so the next db.SaveChanges() and Commit just save it. For more accurate answer it would be better to see what you do in your invisible for us code. — Lana, Jan 10 '21 at 10:49
You really, really should **NOT** be doing extended processing within a transaction. — Charlieface, Jan 10 '21 at 11:36

score 2 · Accepted Answer · answered Jan 10 '21 at 13:34

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).

Thats great! Inspired by your answer I found this: https://stackoverflow.com/a/17967959/448355. I'm using that now (creating a lot of child-objects and changing relation-objects) and it works great. Thank you so much! — Jon Koeter, Jan 10 '21 at 17:20

Rollback or commit Transactions inside a foreach loop

1 Answers1