TransactionScope always tries to promote to MSDTC

Question

I am trying to use a transaction scope inside a loop. The entire loop takes place using a single connection to the database. I am using entity framework 4 for database access. During the second iteration of the loop, when the LINQ to Entites query executes, an exception is thrown stating that the MSDTC on the server is unavailable.

I've read that explicitly opening the connection and then enlisting the transaction is supposed to solve this problem, but it has not. Below is sample code that mirrors the basic operation that is taking place.

Any ideas on how to prevent an escalation to MSDTC?

Using context = New MyEntities()
    Dim connection = context.Connection

    connection.Open()

    For index = 0 to (Me.files.Count - 1)
        Dim query = From d In context.Documents
                    Where (d.DocumentID = documentID)
                    Select d.Status

        Dim status = query.FirstOrDefault()

        Using trans = New TransactionScope()
            connection.EnlistTransaction(Transaction.Current)

            Dim result = context.UpdateStatus(True)

            If (result = 1) Then
                WriteToFile()
                trans.Complete()
            End If
        End Using
    Next
End Using

Edit: Instead of TransactionScope, if I use connection.BeginTransaction(), transaction.Commit(), and transaction.Rollback(), it works fine. However, I would still like to find a way to make TransactionScope work.

Answer 1

TransactionScope originally had an issue where it would promote a transaction to a distributed transaction when it met another connection, even if all connections were to the same database. This was a known issue in the framework.

I believe they addressed this in .NET 4, what version are you using?

A workaround has been provided in this answer:

Why is TransactionScope using a distributed transaction when I am only using LinqToSql and Ado.Net

Basically the same as the comment to your question suggesting to actually only use one physical connection from the pool - so only one connection gets enlisted.

Reviewing your question again I can see the above won't likely make a different, as you only use one connection anyway. Perhaps try closing and re-opening the connection on each iteration explicitly, and use the benefits of connection pooling.

Or more ideally, drop the use of TransactionScope as IDbTransaction has enough scope here to cover your code.

Answer 2

Did you check description for EnlistTransaction in MSDN? It says:

New in ADO.NET 2.0 is support for using the EnlistTransaction method to enlist in a distributed transaction. Because it enlists a connection in a Transaction instance, EnlistTransaction takes advantage of functionality available in the System.Transactions namespace for managing distributed transactions. Once a connection is explicitly enlisted in a transaction, it cannot be unenlisted or enlisted in another transaction until the first transaction finishes.

It only mentions distributed transaction. You can try to use connection.BeginTransaction instead. It will return instance of EntityTransaction and you will call Commit to complete transaction.