With the release of EF6 we added two new APIs to DbContext.Database which make working with transactions easier: UseTransaction() and BeginTransaction().

UseTransaction

UseTransaction() is used to pass in an underlying store transaction which was begun elsewhere but in which you want the SQL commands executed by Entity Framework to participate. In order for this to work the Entity Framework must use the same connection on which the transaction was started – so you need the constructor which passes in an open connection. See the example below: 

    using (var conn = new SqlConnection(connString))
     {
         conn.Open(); // must open connection in order to begin a transaction
         using (var sqlTxn = conn.BeginTransaction()) { try
            {
                 // execute some commands on the SqlConnection
                var sqlCommand = new SqlCommand();
                sqlCommand.Transaction = sqlTxn;
                sqlCommand.CommandText = @"UPDATE MyEntity SET Processed = ‘Done’ " +
                                                                 "WHERE LastUpdated < ‘2013-03-05T16:43:00’";
                sqlCommand.ExecuteNonQuery();
 
                using (var ctx = new MyDbContext(conn, contextOwnsConnection = false))
                 {
                     // calling UseTransaction() tells the DbContext to participate
                     // in the underling transaction
                    ctx.Database.UseTransaction(sqlTxn);
 
                    // then use DbContext as normal - query, update, SaveChanges() etc. E.g.:
                    MyEntity myEntity = new MyEntity(){ Text = @"My New Entity" };
                    ctx.MyEntities.Add(myEntity);
                    ctx.SaveChanges();
                 }
 
               sqlTxn.Commit();
            }
            catch (Exception e)
            {
                sqlTxn.Rollback();
            }
         }
     }

 

Note: because the transaction was started outside of the Entity Framework, the caller is responsible for disposing the transaction.

BeginTransaction

BeginTransaction() has two overrides – one which takes an explicit IsolationLevel and one which takes no arguments and uses the default IsolationLevel from the underlying provider.

Both overrides return a DbContextTransaction object which is a wrapper around an underlying store transaction.

It is meant to be used via the using(…) {…} syntax and using it this way is recommended as then Dispose() will automatically be called on it when the using() finishes.

DbContextTransaction provides Commit() and Rollback() methods which perform Rollback and Commit on the underlying store transaction:

    using (var ctx = new MyDbContext())
    {
        // begin a transaction in EF – note: this returns a DbContextTransaction object
        // and will open the underlying database connection if necessary
        using (var dbCtxTxn = ctx.Database.BeginTransaction())
        {
           try
           {
                // use DbContext as normal - query, update, call SaveChanges() etc. E.g.:
               ctx.Database.ExecuteSqlCommand(
                   @"UPDATE MyEntity SET Processed = ‘Done’ "
                   + "WHERE LastUpdated < ‘2013-03-05T16:43:00’");

               var myNewEntity = new MyEntity() { Text = @"My New Entity" };
               ctx.MyEntities.Add(myNewEntity);
               ctx.SaveChanges();

               dbCtxTxn.Commit();
           }
           catch (Exception e)
           {
               dbCtxTxn.Rollback();
           }
        } // if DbContextTransaction opened the connection then it will close it here
    }

Note: beginning a transaction requires that the underlying store connection is open. So calling BeginTransaction() will open the connection if necessary. If DbContextTransaction opened the connection then it will close it when Dispose() is called. 

Last edited Mar 6, 2013 at 10:15 PM by lajones, version 9