EnterpriseLibrary, EF, and Transaction Scope: why am I seeing different behavior?

Question

I have one app that uses EnterpriseLibrary and Unity, and uses TransactionScope in just one place. This works nicely, despite the fact that it runs against SQL Server 2005:

// Execute a stored proc using a DbDatabase object inserted by Unity

using(TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
{
    // Update something using the same DbDatabase object
    // Run the stored proc above, again
    // Assert that the results are different than from the previous call.
}

Yes, this deliberately ends without a scope.Complete(): the example is from a test.

I also have another application just beginning. It uses Entity Framework 4.1. It accesses the same database on the same server. I attempted to use TransactionScope, with the same "make change, verify change, roll back change" idea in mind.

using(TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
{
    using(ProjectEntities db = new ProjectEntities())
    {
         Assert.IsFalse(db.tblEntities.Any(e=>e.X == desired_value));

         db.tblEntities.Add(new tblEntity() { X = desired_value });
         db.SaveChanges();

         Assert.IsTrue(db.tblEntities.Any(e=> e.X == desired_value));

    }
}

This fails with the very familiar error about MSDTC not being enabled for network access.

Right now, this minute, the first test in the first project succeeds, the second test fails.

So I have two questions:

Is there a way to rejigger my second test that would keep the transaction from escalating to MSDTC?
Anybody know why I'm getting different results from the two frameworks? Does EntLib keep a single connection allocated and open during the whole time it's used? Does EF do the opposite?

Piotr Perak · Answer 1 · 2012-12-07T23:09:15.870

1

I don't know about the EnterpriseLibrary but EF does create and open new connection for every query and I think this is why you see those different results.

You can verify this by opening two DbConnections by hand.

edited Dec 07 '12 at 23:09

answered Dec 07 '12 at 23:02

Piotr Perak

10,718
9
49
86

score 1 · Accepted Answer · answered Apr 29 '13 at 08:42

I have made many test regarding EF, EntLib DAAB, and TransactionScope.

There are several points you must take into account.

SQL Server Version
Connection String
EF and EntLib version

I don't remember the other combinations, but with SQL Server 2008 or later, EF5, and Entlib 5, you can enroll several DbContexts and DAAB operations in the same TransactionScope without scalating to MSDTC. But there's a very tricky part:

the connection string must include: MultipleActiveResultSets=true;
the connection string must have the exact format used by EF

The second part is the most confusing: when you use a connection string to EF, it will chaneg its format, but EntLib uses it as is in the connection string of the config file. So, what you have to do is debug the code, and note down the modified version of the connection string used by EF. You can find it in ctx.Database.Connection.ConnectionString, where ctx is the DbContext you're using. Once you've done so, just copy and paste the modified version of your connection string to your config file, and both EF and EntLib will use the same connection string, thus not escalating to MSDTC.

For previous versions of SQL Server (and sometimes depending on EF version) you can find different problems, but this guidelines can help you test your exact setup.

EnterpriseLibrary, EF, and Transaction Scope: why am I seeing different behavior?

2 Answers2