One transaction with multiple dbcontexts

Question

I am using transactions in my unit tests to roll back changes. The unit test uses a dbcontext, and the service i'm testing uses his own. Both of them are wrapped in one transaction, and one dbcontext is in the block of the other. The thing is, when the inner dbcontext saves his changes, it's not visible to the outer dbcontext (and i don't think it's because the other dbcontext might already have the object loaded). Here is the example:

[TestMethod]
public void EditDepartmentTest()
{
    using (TransactionScope transaction = new TransactionScope())
    {
        using (MyDbContext db = new MyDbContext())
        {
            //Arrange
            int departmentId = (from d in db.Departments
                                   where d.Name == "Dep1"
                                   select d.Id).Single();
            string newName = "newName",
                   newCode = "newCode";

            //Act
            IDepartmentService service = new DepartmentService();
            service.EditDepartment(departmentId, newName, newCode);

            //Assert
            Department department = db.Departments.Find(departmentId);
            Assert.AreEqual(newName, department.Name,"Unexpected department name!");
            //Exception is thrown because department.Name is "Dep1" instead of "newName"
            Assert.AreEqual(newCode, department.Code, "Unexpected department code!");
        }
    }
}

The service:

public class DepartmentService : IDepartmentService
{
    public void EditDepartment(int DepartmentId, string Name, string Code)
    {
        using (MyDbContext db = new MyDbContext ())
        {
            Department department = db.Departments.Find(DepartmentId);

            department.Name = Name;
            department.Code = Code;

            db.SaveChanges();

        }
    }
}

However, if I close the outer dbcontext before calling the service and open a new dbcontext for the assert, everything works fine:

[TestMethod]
public void EditDepartmentTest()
{
    using (TransactionScope transaction = new TransactionScope())
    {
        int departmentId=0;
        string newName = "newName",
               newCode = "newCode";

        using (MyDbContext db = new MyDbContext())
        {
            //Arrange
            departmentId = (from d in db.Departments
                                   where d.Name == "Dep1"
                                   select d.Id).Single();
        }

        //Act
        IDepartmentService service = new DepartmentService();
        service.EditDepartment(departmentId, newName, newCode);

        using (MyDbContext db = new MyDbContext())
        {
            //Assert
            Department department = db.Departments.Find(departmentId);
            Assert.AreEqual(newName, department.Name,"Unexpected department name!");
            Assert.AreEqual(newCode, department.Code, "Unexpected department code!");
        }
    }
}

So basically i have a solution for this problem (thought of it during the writing of this question) but i still wonder why it isn't possible accessing uncommitted data in the transaction when the dbcontexts are nested. Could it be becuase using(dbcontext) is like a transaction itself? If so, i still don't understand the issue since i'm calling .SaveChanges() on the inner dbcontext.

Answer 1

In the first scenario, you are nesting DbContexts. A connection to the database is opened for each on of them. When you call your service method within the using block, a new connection is opened within the TransactionScope while there is another one already open. This causes your transaction to be promoted to a distributed transaction, and partially committed data (the result of the DbContext.SaveChanges call in the service) not being available from your outer connection. Also note that distributed transactions are far slower and thus, this has the side effect of degrading performance.

In the second scenario, while you open and close three connections, only one connection is open at the same time within your transaction. Since these connections share the same connection string, the transaction won't be automatically promoted to a distributed connection and thus, each subsequent connection within the transaction has access to the changes performed by the previous connection.

You may try adding the Enlist=false parameter to your connection string. This would disable automatic enlisting in a distributed transaction, causing an exception to be raised in your first scenario. The second scenario would keep working flawlessly if you are using SQL Server 2008 and beyond, since the transaction won't get promoted. (Prior versions of SQL Server will still promote the transaction in this scenario.)

You may also find helpful this great answer to a quite similar question.