Entity Framework – transactions, multiple contexts and mixing with ADO.NET

As .NET developer I’m about to face the problem of using it in a quite complex system. I took some up front effort to prepare some concept of handling nested DB operations and transactions management. I’m not an Entity Framework specialist (nor enthusiast)  but the potential of this technology (the result of factors like MS marketing or community activity) shouldn’t be overlooked. Though I have faced a problem of EF which won’t appear in HelloWorld scenarios but will get very hard to overcome in enterprise-scale solutions – hence I write this post.

Imagine that you want to perform some DB operations using both Entity Framework and ADO.NET (for performance reasons) and you want these two operations to be performed in one transaction. For example in your DDD application you have some business code which does something with business entities and then notifies other parts of system of this fact by using domain events. It’s quite obvious that business operations and persisting domain events must be performed in a transactional manner. If a business code fails than the event must not get persisted (why to nitify other the parts of the system about something that did not happen). It’s equally incorrect if business operation is successful but the event does not get persisted – imagine accepting a customer’s order without modifying the warehouse state of ordered the product.

In Spring.NET all you need to do is to add [Transaction] attribute to your method. The proxy generated by transaction management aspect will take care of enlisting your operations in current transaction. To achieve this kind of behavior with EF and ADO you have to use TransactionScope (which will probably promote your transaction to DTM because two or more DB connections will be used) or find a way to reuse your EF context and connection (i.e. by passing it as a method parameter). I don’t like either of these two solutions.

What I would like to do is to use some kind of scope for DB operations which would allow me to nest them and automatically enlist nested operations in parent operations transactions. There should be also an option to start nested scope with its own transaction so when it fails, parent transaction gets committed but the nested one don’t.

Here are some unit tests which show what I mean:

namespace MKarczewski.Data.Tests
{
    [TestClass]
    public class ContextTests
    {
        private static string connString = @"data source=MY_DATABASE;initial catalog=DBOperationContextTest;integrated security=True;multipleactiveresultsets=True;App=EntityFramework";

        [TestInitialize]
        public void Initialize()
        {
            DBContextScope.Initialize(connString, new Assembly[] { Assembly.GetExecutingAssembly() });

            using (var conn = new SqlConnection(connString))
            using (var cmd = new SqlCommand("DELETE FROM Invoices", conn))
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

        [TestMethod]
        public void Test_ADO_single_scope_commit()
        {
            using (var uow = new DBContextScope(DBContextScopeMode.Required))
            {
                Assert.ReferenceEquals(uow.Current, DBContext.Current);

                AddInvoiceADO(uow.Current.DbConnection, 1, true);
                AddInvoiceADO(uow.Current.DbConnection, 2, false);

                uow.Complete();
            }
            Assert.IsTrue(ExistsInvoice(1, true));
            Assert.IsTrue(ExistsInvoice(2, false));
            Assert.IsTrue(CountInvoices() == 2);
        }

        [TestMethod]
        public void Test_ADO_single_scope_rollback()
        {
            using (var uow = new DBContextScope(DBContextScopeMode.Required))
            {
                AddInvoiceADO(uow.Current.DbConnection, 1, true);
                AddInvoiceADO(uow.Current.DbConnection, 2, false);
            }
            Assert.IsTrue(CountInvoices() == 0);
        }

        [TestMethod]
        public void Test_ADO_nested_scope_commit()
        {
            using (var uow = new DBContextScope(DBContextScopeMode.Required))
            {
                AddInvoiceADO(uow.Current.DbConnection, 1, true);
                AddInvoiceADO(uow.Current.DbConnection, 2, false);

                using (var uow2 = new DBContextScope(DBContextScopeMode.Required))
                {
                    AddInvoiceADO(uow2.Current.DbConnection, 3, true);
                    AddInvoiceADO(uow2.Current.DbConnection, 4, false);

                    uow2.Complete();
                }

                using (var uow3nested = new DBContextScope(DBContextScopeMode.RequiredNew))
                {
                    AddInvoiceADO(uow3nested.Current.DbConnection, 5, true);
                    AddInvoiceADO(uow3nested.Current.DbConnection, 6, false);

                    uow3nested.Complete();
                }

                uow.Complete();
            }

            Assert.IsTrue(ExistsInvoice(1));
            Assert.IsTrue(ExistsInvoice(2));
            Assert.IsTrue(ExistsInvoice(3));
            Assert.IsTrue(ExistsInvoice(4));
            Assert.IsTrue(ExistsInvoice(5));
            Assert.IsTrue(ExistsInvoice(6));
        }

        [TestMethod]
        public void Test_ADO_nested_scope_rollback_nested()
        {
            using (var uow = new DBContextScope(DBContextScopeMode.Required))
            {
                AddInvoiceADO(uow.Current.DbConnection, 1, true);
                AddInvoiceADO(uow.Current.DbConnection, 2, false);

                using (var uow2 = new DBContextScope(DBContextScopeMode.Required))
                {
                    AddInvoiceADO(uow2.Current.DbConnection, 3, true);
                    AddInvoiceADO(uow2.Current.DbConnection, 4, false);

                    uow2.Complete();
                }

                using (var uow3nested = new DBContextScope(DBContextScopeMode.RequiredNew))
                {
                    AddInvoiceADO(uow3nested.Current.DbConnection, 5, true);
                    AddInvoiceADO(uow3nested.Current.DbConnection, 6, false);

                    //rollback
                    //uow3nested.Complete();
                }

                uow.Complete();
            }

            Assert.IsTrue(ExistsInvoice(1));
            Assert.IsTrue(ExistsInvoice(2));
            Assert.IsTrue(ExistsInvoice(3));
            Assert.IsTrue(ExistsInvoice(4));
            Assert.IsFalse(ExistsInvoice(5));
            Assert.IsFalse(ExistsInvoice(6));
        }

        [TestMethod]
        public void Test_ADO_nested_scope_rollback_outer()
        {
            using (var uow = new DBContextScope(DBContextScopeMode.Required))
            {
                AddInvoiceADO(uow.Current.DbConnection, 1, true);
                AddInvoiceADO(uow.Current.DbConnection, 2, false);

                using (var uow2 = new DBContextScope(DBContextScopeMode.Required))
                {
                    AddInvoiceADO(uow2.Current.DbConnection, 3, true);
                    AddInvoiceADO(uow2.Current.DbConnection, 4, false);

                    uow2.Complete();
                }

                using (var uow3nested = new DBContextScope(DBContextScopeMode.RequiredNew))
                {
                    AddInvoiceADO(uow3nested.Current.DbConnection, 5, true);
                    AddInvoiceADO(uow3nested.Current.DbConnection, 6, false);

                    uow3nested.Complete();
                }

                //rollback
                //uow.Complete();
            }

            Assert.IsFalse(ExistsInvoice(1));
            Assert.IsFalse(ExistsInvoice(2));
            Assert.IsFalse(ExistsInvoice(3));
            Assert.IsFalse(ExistsInvoice(4));
            Assert.IsTrue(ExistsInvoice(5));
            Assert.IsTrue(ExistsInvoice(6));
        }

        [TestMethod]
        public void Exception_after_new_scope_when_previous_not_completed()
        {
            bool exception = false;

            using (var uow = new DBContextScope(DBContextScopeMode.Required))
            {
                AddInvoiceADO(uow.Current.DbConnection, 1, true);

                using (var uow1 = new DBContextScope(DBContextScopeMode.Required))
                {
                    AddInvoiceADO(uow1.Current.DbConnection, 2, false);
                }

                using (var uownested = new DBContextScope(DBContextScopeMode.RequiredNew))
                {
                    AddInvoiceADO(uownested.Current.DbConnection, 3, false);
                    uownested.Complete();
                }

                try
                {
                    using (var uow2 = new DBContextScope(DBContextScopeMode.Required))
                    {
                        AddInvoiceADO(uow2.Current.DbConnection, 4, false);
                        uow2.Complete();
                    }
                }
                catch (DBContextNotClosedException ex)
                {
                    exception = true;
                }

                uow.Complete();
            }

            Assert.IsTrue(exception);
            Assert.IsTrue(ExistsInvoice(3));
            Assert.IsTrue(CountInvoices() == 1);
        }

        [TestMethod]
        public void Mixed_ADO_and_EF_nested_commit()
        {
            using (var uow1 = new DBContextScope(DBContextScopeMode.Required))
            using (var ctx1 = new DBOperationContextTestEntities(uow1.Current.EFConnection))
            {
                AddInvoiceADO(uow1.Current.DbConnection, 1, true);
                ctx1.Invoices.AddObject(new Invoices() { OrderId = 2, Paid = false });

                using (var uow2 = new DBContextScope(DBContextScopeMode.Required))
                using (var ctx2 = new DBOperationContextTestEntities(uow2.Current.EFConnection))
                {
                    AddInvoiceADO(uow2.Current.DbConnection, 3, true);
                    ctx2.Invoices.AddObject(new Invoices() { OrderId = 4, Paid = false });

                    ctx2.SaveChanges();
                    uow2.Complete();
                }

                using (var uow3n = new DBContextScope(DBContextScopeMode.RequiredNew))
                using (var ctx3n = new DBOperationContextTestEntities(uow3n.Current.EFConnection))
                {
                    AddInvoiceADO(uow3n.Current.DbConnection, 5, true);
                    ctx3n.Invoices.AddObject(new Invoices() { OrderId = 6, Paid = false });

                    ctx3n.SaveChanges();
                    uow3n.Complete();
                }

                ctx1.SaveChanges();
                uow1.Complete();
            }

            Assert.IsTrue(ExistsInvoice(1));
            Assert.IsTrue(ExistsInvoice(2));
            Assert.IsTrue(ExistsInvoice(3));
            Assert.IsTrue(ExistsInvoice(4));
            Assert.IsTrue(ExistsInvoice(5));
            Assert.IsTrue(ExistsInvoice(6));
        }

        [TestMethod]
        public void Mixed_ADO_and_EF_nested_rollback_nested()
        {
            using (var uow1 = new DBContextScope(DBContextScopeMode.Required))
            using (var ctx1 = new DBOperationContextTestEntities(uow1.Current.EFConnection))
            {
                AddInvoiceADO(uow1.Current.DbConnection, 1, true);
                ctx1.Invoices.AddObject(new Invoices() { OrderId = 2, Paid = false });

                using (var uow2 = new DBContextScope(DBContextScopeMode.Required))
                using (var ctx2 = new DBOperationContextTestEntities(uow2.Current.EFConnection))
                {
                    AddInvoiceADO(uow2.Current.DbConnection, 3, true);
                    ctx2.Invoices.AddObject(new Invoices() { OrderId = 4, Paid = false });

                    ctx2.SaveChanges();
                    uow2.Complete();
                }

                using (var uow3n = new DBContextScope(DBContextScopeMode.RequiredNew))
                using (var ctx3n = new DBOperationContextTestEntities(uow3n.Current.EFConnection))
                {
                    AddInvoiceADO(uow3n.Current.DbConnection, 5, true);
                    ctx3n.Invoices.AddObject(new Invoices() { OrderId = 6, Paid = false });

                    ctx3n.SaveChanges();

                    //rollback
                    //uow3n.Complete();
                }

                ctx1.SaveChanges();
                uow1.Complete();
            }

            Assert.IsTrue(ExistsInvoice(1));
            Assert.IsTrue(ExistsInvoice(2));
            Assert.IsTrue(ExistsInvoice(3));
            Assert.IsTrue(ExistsInvoice(4));
            Assert.IsFalse(ExistsInvoice(5));
            Assert.IsFalse(ExistsInvoice(6));
        }

        [TestMethod]
        public void Mixed_ADO_and_EF_nested_rollback_outer()
        {
            using (var uow1 = new DBContextScope(DBContextScopeMode.Required))
            using (var ctx1 = new DBOperationContextTestEntities(uow1.Current.EFConnection))
            {
                AddInvoiceADO(uow1.Current.DbConnection, 1, true);
                ctx1.Invoices.AddObject(new Invoices() { OrderId = 2, Paid = false });

                using (var uow2 = new DBContextScope(DBContextScopeMode.Required))
                using (var ctx2 = new DBOperationContextTestEntities(uow2.Current.EFConnection))
                {
                    AddInvoiceADO(uow2.Current.DbConnection, 3, true);
                    ctx2.Invoices.AddObject(new Invoices() { OrderId = 4, Paid = false });

                    ctx2.SaveChanges();
                    uow2.Complete();
                }

                using (var uow3n = new DBContextScope(DBContextScopeMode.RequiredNew))
                using (var ctx3n = new DBOperationContextTestEntities(uow3n.Current.EFConnection))
                {
                    AddInvoiceADO(uow3n.Current.DbConnection, 5, true);
                    ctx3n.Invoices.AddObject(new Invoices() { OrderId = 6, Paid = false });

                    ctx3n.SaveChanges();
                    uow3n.Complete();
                }

                ctx1.SaveChanges();

                //rollback
                //uow1.Complete();
            }

            Assert.IsFalse(ExistsInvoice(1));
            Assert.IsFalse(ExistsInvoice(2));
            Assert.IsFalse(ExistsInvoice(3));
            Assert.IsFalse(ExistsInvoice(4));
            Assert.IsTrue(ExistsInvoice(5));
            Assert.IsTrue(ExistsInvoice(6));
        }

        [TestMethod]
        public void Commit_two_EF_contexts()
        {
            using (var uow1 = new DBContextScope(DBContextScopeMode.Required))
            using (var ctx1 = new DBOperationContextTestEntities(uow1.Current.EFConnection))
            using (var ctx2 = new DBOperationContextTestEntities(uow1.Current.EFConnection))
            {
                ctx1.Invoices.AddObject(new Invoices() { OrderId = 1, Paid = false });
                ctx2.Invoices.AddObject(new Invoices() { OrderId = 2, Paid = false });

                ctx1.SaveChanges();
                ctx2.SaveChanges();

                uow1.Complete();
            }

            Assert.IsTrue(ExistsInvoice(1));
            Assert.IsTrue(ExistsInvoice(2));
        }

        [TestMethod]
        public void Rollback_two_EF_contexts()
        {
            using (var uow1 = new DBContextScope(DBContextScopeMode.Required))
            using (var ctx1 = new DBOperationContextTestEntities(uow1.Current.EFConnection))
            using (var ctx2 = new DBOperationContextTestEntities(uow1.Current.EFConnection))
            {
                ctx1.Invoices.AddObject(new Invoices() { OrderId = 1, Paid = false });
                ctx2.Invoices.AddObject(new Invoices() { OrderId = 2, Paid = false });

                ctx1.SaveChanges();
                ctx2.SaveChanges();
                //Rollback
                //uow1.Complete();
            }

            Assert.IsFalse(ExistsInvoice(1));
            Assert.IsFalse(ExistsInvoice(2));
        }

        #region Data checks

        private void AddInvoiceADO(SqlConnection conn, int orderId, bool isPaid)
        {
            using (var cmd = new SqlCommand("insert into invoices(OrderId, Paid) values (" + orderId + ", " + (isPaid ? 1 : 0) + ")", conn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        private int CountInvoices()
        {
            using (var conn = new SqlConnection(connString))
            using (var cmd = new SqlCommand("select count(*) from Invoices", conn))
            {
                conn.Open();
                return Convert.ToInt32(cmd.ExecuteScalar());
            }
        }

        private bool ExistsInvoice(int orderId)
        {
            using (var conn = new SqlConnection(connString))
            using (var cmd = new SqlCommand("select count(*) from Invoices where OrderId = " + orderId, conn))
            {
                conn.Open();
                return Convert.ToInt32(cmd.ExecuteScalar()) > 0;
            }
        }

        private bool ExistsInvoice(int orderId, bool paid)
        {
            using (var conn = new SqlConnection(connString))
            using (var cmd = new SqlCommand("select count(*) from Invoices where OrderId = " + orderId + " AND Paid = " + (paid ? 1 : 0), conn))
            {
                conn.Open();
                return Convert.ToInt32(cmd.ExecuteScalar()) > 0;
            }
        }
        #endregion
    }
}

The idea of scope itself is also presented in THIS post.

Here is the portion of the code which will make these tests pass:

DBContext:

namespace MKarczewski.Data
{
    public enum DBContextScopeMode
    {
        Required,
        RequiredNew
    }

    public class DBContextNotClosedException : Exception
    {
        public DBContextNotClosedException(string message)
            : base(message)
        {
        }

        public DBContextNotClosedException(string message, Exception inner)
            : base(message, inner)
        {
        }
    }

    public class DBContext
    {
        [ThreadStatic]
        private static DBContext _current;
        public static DBContext Current
        {
            get
            {
                return _current;
            }
            internal set
            {
                _current = value;
            }
        }

        private bool _disposed;
        private TransactionScope _transactionScope;
        private SqlConnection _dbConnection;
        private EntityConnection _efConnection;

        public SqlConnection DbConnection { get { return _dbConnection; } }
        public EntityConnection EFConnection { get { return _efConnection; } }

        internal DBContext(string connectionString, Assembly[] efAssemblies)
        {
            var metadata = new MetadataWorkspace(new string[] { "res://*/" }, efAssemblies);

            _disposed = false;
            _dbConnection = new SqlConnection(connectionString);
            _efConnection = new EntityConnection(metadata, DbConnection);
            _transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew);

            _efConnection.Open();
        }

        internal void Commit()
        {
            _transactionScope.Complete();
        }

        public void Dispose()
        {
            if (!_disposed)
            {
                _efConnection.Dispose();
                _dbConnection.Dispose();
                _transactionScope.Dispose();

                _disposed = true;
            }
        }
    }
}

DBContextScope:

namespace MKarczewski.Data
{
    public class DBContextScope : IDisposable
    {
        private class DBContextScopeStatus
        {
            public DBContext Context { get; set; }
            public int DisposeCounter { get; set; }
            public bool IsIncomplete { get; set; }

            public DBContextScopeStatus()
            {
                IsIncomplete = false;
            }
        }

        #region Static instances

        private static bool _initialized = false;
        private static object _syncLock = new object();
        private static string _connectionString;
        private static Assembly[] _efAssemblies;

        [ThreadStatic]
        private static Stack<DBContextScopeStatus> _instances;
        private static Stack<DBContextScopeStatus> Instances
        {
            get
            {
                if (_instances == null) _instances = new Stack<DBContextScopeStatus>();
                return _instances;
            }
        }

        private static DBContextScopeStatus CurrentStatus
        {
            get
            {
                if (Instances.Count == 0) return null;
                return Instances.Peek();
            }
        }
        #endregion

        private bool _disposed;
        private bool _completed;

        public DBContext Current { get; private set; }

        public DBContextScope()
            : this(DBContextScopeMode.Required)
        {
        }

        public DBContextScope(DBContextScopeMode mode)
        {
            EnsureInitialized();

            _disposed = false;
            _completed = false;

            if (CurrentStatus == null || mode == DBContextScopeMode.RequiredNew)
            {
                var status = new DBContextScopeStatus()
                {
                    Context = new DBContext(_connectionString, _efAssemblies),
                    DisposeCounter = 1
                };
                Instances.Push(status);
            }
            else
            {
                if (CurrentStatus.IsIncomplete)
                    throw new DBContextNotClosedException("Current database operation context was not completed. Transaction will be rolled back.");

                CurrentStatus.DisposeCounter++;
            }

            Current = CurrentStatus.Context;
        }

        public static void Initialize(string connectionString, Assembly[] efAssemblies)
        {
            lock (_syncLock)
            {
                _connectionString = connectionString;
                _efAssemblies = efAssemblies;

                _initialized = true;
            }
        }

        public void Complete()
        {
            _completed = true;
        }

        public void Dispose()
        {
            if (!_disposed)
            {
                CurrentStatus.DisposeCounter--;

                if (!_completed)
                    CurrentStatus.IsIncomplete = true;

                if (CurrentStatus.DisposeCounter == 0)
                {
                    try
                    {
                        if (!CurrentStatus.IsIncomplete)
                            CurrentStatus.Context.Commit();
                    }
                    finally
                    {
                        CurrentStatus.Context.Dispose();
                        _instances.Pop();
                    }
                }

                _disposed = true;
            }
        }

        private void EnsureInitialized()
        {
            lock (_syncLock)
            {
                if (!_initialized)
                    throw new Exception("DBContextScope not initialized");
            }
        }
    }
}

Keep in mind that scopes are marked as ‘ThreadStatic’.

This approach has not been tested heavily yet but it seems to be good foundation for further development. Plus it lacks the option of modifying transaction isolation level and timeout but it’s easy to correct by overloading scopes constructors and handling cases when inner scope parameters don’t  match parent scope ones.