Practical .NET

Managing Transactions in Entity Framework 6

Here's an article about managing transactions that you don't need to read because, with one exception, Entity Framework will do the right thing by default. But, in the .NET Framework 4 and later, you can do more (if you ever need to).

• By Peter Vogel
• 08/25/2014

If you used NuGet to add the latest version of Entity Framework (EF) to your Microsoft .NET Framework 4 (or later) application, then you can take advantage of the new transaction management features that come with EF 6. Of the two features that I'm going to talk about, one might matter more to you: better handling for intermittent connections.

The other feature I'm going to discuss -- the ability to combine multiple updates into a single transaction -- is less interesting. By default, EF wraps your SaveChanges method calls into a transaction, which is probably what you want: All your updates succeed or all your updates fail as a group. If you do need something different (to combine several SaveChanges calls into a single transaction or to combine EF code with ADO.NET code in a transaction), then your first choice should be the TransactionScope object (as long as you're not using ADO.NET async methods … and even that's possible in the .NET Framework 4.5.1).

Handling Transactions with Intermittent Connection
The new EF support for handling transactions with intermittent connections might be useful to you, though. The issue this feature addresses is that it's possible for EF to raise a CommitFailedException that says your changes might not have been committed. EF can raise this exception even though all your changes were, in fact, saved (remember: the message says your changes might not have been committed). EF could misreport your changes being saved because, due to momentary loss of network connectivity, EF didn't receive the message from the database that reports success.

If this is a concern to you then, in EF 6.1, you should add this code to the constructor method of a class that inherits from DbConfiguration:

Public Class MyConfiguration 
      Inherits DbConfiguration  

      Public Sub New
            Me.SetTransactionHandler(SqlProviderServices.ProviderInvariantName, 
                   Function() New CommitFailureHandler())     
            Me.SetExecutionStrategy(SqlProviderServices.ProviderInvariantName, 
                   Function() New SqlAzureExecutionStrategy())   
       End Sub
End Class

Just put this class in the same project with your DbContext object and your DbContext object will find it and use it.

This change ensures that CommitFailedException will be raised only if your changes really haven't been committed. The change isn't free, however: It causes EF to add a table called _Transactions to your database, and to read and write to that table to check for incomplete transactions. This raises two issues.

First, it's possible that EF might not delete all the entries it makes to the _Transactions table. To prevent the table from growing without bounds, you should add code like the following to your application to delete any leftover rows in the table. Just make sure you call this code before your application first uses EF (in this example, I've called my DbContext object AWEntitiesTransacted):

Dim db As New AWEntitiesTransacted
Dim cfh As CommitFailureHandler
cfh = CommitFailureHandler.FromContext(db)
If cfh IsNot Nothing Then
  cfh.ClearTransactionHistory()
End If

Because this change adds more I/O to your application, you'll need to consider whether this potential problem worries you enough to incur the extra overhead. You might want to consider just keeping track of one change in every update and, if EF raises a CommitFailedException error, checking the database to see if that change was made (it probably won't happen very often). If the change isn't there then your changes really weren't saved.

A Feature You'll Never Need (Probably)
The other feature new to EF is the ability to create a single transaction that combines any combination of ADO.NET code, DbContext objects and calls to SaveChanges (provided that all of these entities work with the same Connection object).

You can only use this technique if you're doing code-first EF development because you must create a custom DbContext class with a constructor that accepts a Connection object. Within that class you must call the constructor for the DbContext class you've inherited from, passing a Connection object and False (the False that tells DbContext that it doesn't own the connection).

In Visual Basic, that code looks like this:

Public Class AWEntitiesTransacted
       Inherits DbContext

  Public Sub New(conn As SqlConnection)
    MyBase.New(conn, False)

In C#, you'd do the same thing with code like this:

public class AWEntitiesTransacted: DbContext
{
  public AWEntitiesTransacted(SqlConnection conn): base(conn, False)
  {

To bind two SaveChanges into a single transaction you first create a Connection object and open it. With the connection open you can call its BeginTransaction method to retrieve a Transaction object:

Dim conn As SqlConnection
Dim tran As SqlTransaction
conn = New SqlConnection("…connection string…")

conn.Open()
tran = conn.BeginTransaction

Now, you create your DbContext object, passing it your Connection object through your new constructor (I've put this inside a Try…Catch block to simplify some code later in this column):

Dim db As AWEntitiesTransacted
Try
  db = New AdventureWorksLTEntitiesRevised(conn, False)

Finally, you tell the DbContext object to use your Transaction by passing your Transaction object to the UseTransaction method, which you can find off the DbContext Database property:

db.Database.UseTransaction(tran)

Now, when you call SaveChanges, none of your changes will take effect until you call the Transaction object's Commit method. You can also use this Transaction object with ADO.NET Command objects to include them in the transaction. If something does go wrong, you call the Transaction's Rollback method to discard your changes (or just skip calling the Transaction's Commit method).

No matter what you do with the Transaction object, you should close your Connection when you're done. Using the Try…Catch block I created earlier, that code would look like this:

  tran.Commit()
Catch
  tran.Rollback()
Finally
  conn.Close()
End Try

One last note: You could do what I've suggested here with less work by wrapping all of the code in a Using block that creates a TransactionScope object. However, just because I can't imagine why you would need this feature doesn't mean that you won't need it someday. So, if you ever do, here it is.