Transactions and Entity Framework

I recently came across a situation where I needed to delete all the data from a table and reinsert it (don’t ask why, it was quite sensible actually). I wanted to avoid anyone reading from the table while it was in an intermediate state, ie with part of the data in and part not.

I did a quick search on using transactions in EF, and came across an article (not linked for reasons that should become apparent) that strongly recommended not using them at all. This all seemed sensible advice, until I read the comments. A lot of people criticised the article as giving very bad advice. Amongst the links was one to an MSDN page that explained how to use transactions with EF6 onwards. This was much cleaner than the advice in the previous article, and being from Microsoft, one hopes it is more authoritative.

It’s worth reading the whole article (it’s not long, and it’s quite understandable for an MSDN article!). However, for for those with an extremely short attention span, here is a fairly crass example that illustrates what to do…

string sql = "UPDATE Blogs SET Rating = 5 WHERE Name LIKE '%Windows%'";
using (var dbContextTransaction = context.Database.BeginTransaction()) {
  try {
    context.Database.ExecuteSqlCommand(sql);
    var query = context.Posts.Where(p => p.Blog.Rating >= 5);
    foreach (var post in query) {
      post.Title += "[Cool Blog]";
    }
    context.SaveChanges();
    dbContextTransaction.Commit();
  }
  catch (Exception) {
    dbContextTransaction.Rollback();
  }
}

I haven’t checked if calling context.SaveChanges() more than once makes a difference, so I’m not sure if you have to do everything in the context without saving.

The MSDN article also includes information on how to use TransactionScope with earlier versions of EF, and presumably is reliable enough to follow.

The moral of the story is, don’t believe everything that you read! But then you knew that eh?

Be First to Comment

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.