Does ADO Entity Framework support non DTC transactions? Multiple queries inside one EntityContext and one TransactionScope is causing DTC promotion

Question

I have a web application that uses the Entity Framework - we make use of the TransactionScope class to provide ambient transactions.

Is there any way to tell EF to use a standard T-SQL transaction in preference to DTC transaction? Quite often we make a number of queries to different tables inside one EntityContext and one TransactionScope instance, however this seems to always promote the transaction to DTC

I have put a short example together, see below.... The query to the individual table correctly starts a T-SQL transaction and was on connection SPID 54 Then the query to the ContactUs table is made and EF does this on a different connection (SPID 53) which has the knock on effect of promoting the transaction to a DTC transaction.

using (MyEntities DB = new MyEntities())
{
    using (TransactionScope t = new TransactionScope())
    {
        DB.Individual.First().EmailAddress = "bob" + DateTime.Now.Second.ToString() + "@bob.com"; // done on connection 54
        DB.ContactUs.First(); // done on connection 53 thus promoting the transaction!!
        DB.SaveChanges();
        t.Complete();
    }
}

Answer 1

It is not the Entity Framework which is causing use of distributed transactions; rather, it is TransactionScope. The EF will use a "normal" transaction to wrap up all SQL statements executed in the context of a single call to SaveChanges(). In the example you give, you have only one call to SaveChanges. Therefore, you would not need to use TransactionScope at all to have your changes applied atomically.

I do not know how to make TransactionScope use non-distributed transactions, but I'm reasonably confident that it is not the Entity Framework which is doing this.

Answer 2

You didn't mention, but it sounds like you are using SQL Server 2005. Under SQL Server 2005, if more than one connection is opened inside a TransactionScope then the transaction will escalate from a lightweight transaction to a distributed transaction. It doesn't matter if it is the same database and the same ConnectionString -- it still gets promoted.

This isn't an "issue" with Entity Framework as much as it is an existing behavior with System.Transactions. The issue exists with regular ADO.NET as well. In the past I have used Enterprise Library which under the covers maintains a list of connections that are participating in transactions and if you attempt to retrieve a new connection while in a transaction, it will return you the connection that is already open and participating in your transaction.

If you use SQL Server 2008, then the behavior is improved so that transactions are not promoted when opening multiple connections to the same database.

Answer 3

1. Set Enlist=false on connection string to avoid auto enlistment on transaction.

2. Manually enlist connection as participants in transaction scope. (http://msdn.microsoft.com/en-us/library/ms172153%28v=VS.80%29.aspx)