Entity Framework Forcing Distributed Transaction

Question

I am unable to get the following code that only touches a single database using a single context to run without escalating to MSDTC, and is throwing an exception on context.SaveChanges():

public void DeleteGroupDetails(int groupId)
{
    // Note there is no ambient tx
    var thisIsNull = Transaction.Current;

    using (var scope = new TransactionScope())
    {
        var thisIsNotNull = Transaction.Current;

        using (var context = new MyDbEntities())
        {
            var deleted = context.tblGroups.Where(x => x.GroupID == groupId);

            context.tblGroups.RemoveRange(deleted);

            try
            {
                context.SaveChanges();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
        }

        //scope.Complete();
    }
}

The exception is "The underlying provider failed on Open. --> The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)"

Note:

• This is using EF 6 and SQL Server 2005
• Due to a locked down db server I am unable to use MSDTC
• I want to use TransactionScope in the POC because this will be running in WCF, which has TransactionScope built in and I do not want to litter my code with transaction management.
• Similar projects use NHibernate and do not have this problem

Connection string is:

connectionString="metadata=res:///ResourceAccess.MyDb.csdl|res:///ResourceAccess.MyDb.ssdl|res://*/ResourceAccess.MyDb.msl;provider=System.Data.SqlClient;provider connection string="data source=wil-gvpsqldev01;initial catalog=MyDb;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />

System.Transactions diagnostics are:

<E2ETraceEvent>
    <System>
        <EventID>0</EventID>
        <Type>3</Type>
        <SubType Name="Information">0</SubType>
        <Level>8</Level>
        <TimeCreated SystemTime="2014-10-31T14:39:43.0061489Z" />
        <Source Name="System.Transactions" />
        <Correlation ActivityID="{00000000-0000-0000-0000-000000000000}" />
        <Execution ProcessName="CCS.Host.Console.vshost" ProcessID="64568" ThreadID="13"/>
        <Channel/>
        <Computer>xxx</Computer>
    </System>
        <ApplicationData>
            <TraceData>
                <DataItem>
                    <TraceRecord Severity="Information">
                        <TraceIdentifier>http://msdn.microsoft.com/2004/06/System/Transactions/TransactionCreate</TraceIdentifier>
                        <Description>Transaction Created</Description>
                        <AppDomain>CCS.Host.Console.vshost.exe</AppDomain>
                        <ExtendedData>
                            <TraceSource>[Lightweight]</TraceSource>
                            <TransactionTraceIdentifier>
                            <TransactionIdentifier>2e0814b3-7dd2-4c05-ad69-c3787d95c208:1</TransactionIdentifier>
                            <CloneIdentifier>1</CloneIdentifier>
                            </TransactionTraceIdentifier>
                        </ExtendedData>
                    </TraceRecord>
                </DataItem>
            </TraceData>
        </ApplicationData>
</E2ETraceEvent>
<E2ETraceEvent>
    <System>
        <EventID>0</EventID>
        <Type>3</Type>
        <SubType Name="Information">0</SubType>
        <Level>8</Level>
        <TimeCreated SystemTime="2014-10-31T14:39:43.0181489Z" />
        <Source Name="System.Transactions" />
        <Correlation ActivityID="{00000000-0000-0000-0000-000000000000}" />
        <Execution ProcessName="CCS.Host.Console.vshost" ProcessID="64568" ThreadID="13"/>
        <Channel/>
        <Computer>xxx</Computer>
    </System>
    <ApplicationData>
        <TraceData>
            <DataItem>
                <TraceRecord Severity="Information">
                <TraceIdentifier>http://msdn.microsoft.com/2004/06/System/Transactions/TransactionScopeCreated</TraceIdentifier>
                <Description>TransactionScope Created</Description>
                <AppDomain>CCS.Host.Console.vshost.exe</AppDomain>
                <ExtendedData>
                    <TraceSource>[Base]</TraceSource>
                    <TransactionTraceIdentifier>
                    <TransactionIdentifier>2e0814b3-7dd2-4c05-ad69-c3787d95c208:1</TransactionIdentifier>
                    <CloneIdentifier>2</CloneIdentifier>
                    </TransactionTraceIdentifier>
                    <TransactionScopeResult>CreatedTransaction</TransactionScopeResult>
                </ExtendedData>
                </TraceRecord>
            </DataItem>
        </TraceData>
    </ApplicationData>
</E2ETraceEvent>
<E2ETraceEvent>
    <System>
        <EventID>0</EventID>
        <Type>3</Type>
        <SubType Name="Information">0</SubType>
        <Level>8</Level>
        <TimeCreated SystemTime="2014-10-31T14:39:49.1921489Z"/>
        <Source Name="System.Transactions"/>
        <Correlation ActivityID="{00000000-0000-0000-0000-000000000000}"/>
        <Execution ProcessName="CCS.Host.Console.vshost" ProcessID="64568" ThreadID="13"/>
        <Channel/>
        <Computer>ccc</Computer>
    </System>
    <ApplicationData>
        <TraceData>
            <DataItem>
                <TraceRecord Severity="Information">
                    <TraceIdentifier>http://msdn.microsoft.com/2004/06/System/Transactions/Enlistment</TraceIdentifier>
                    <Description>Enlistment Created</Description>
                    <AppDomain>CCS.Host.Console.vshost.exe</AppDomain>
                    <ExtendedData>
                        <TraceSource>[Lightweight]</TraceSource>
                        <EnlistmentTraceIdentifier>
                            <ResourceManagerId>00000000-0000-0000-0000-000000000000</ResourceManagerId>
                            <TransactionTraceIdentifier>
                                <TransactionIdentifier>2e0814b3-7dd2-4c05-ad69-c3787d95c208:1</TransactionIdentifier>
                                <CloneIdentifier>2</CloneIdentifier>
                            </TransactionTraceIdentifier>
                            <EnlistmentIdentifier>0</EnlistmentIdentifier>
                        </EnlistmentTraceIdentifier>
                        <EnlistmentType>PromotableSinglePhase</EnlistmentType>
                        <EnlistmentOptions>None</EnlistmentOptions>
                    </ExtendedData>
                </TraceRecord>
            </DataItem>
        </TraceData>
    </ApplicationData>
</E2ETraceEvent>
<E2ETraceEvent >
    <System >
        <EventID>0</EventID>
        <Type>3</Type>
        <SubType Name="Error">0</SubType>
        <Level>2</Level>
        <TimeCreated SystemTime="2014-10-31T14:39:50.8941489Z" />
        <Source Name="System.Transactions" />
        <Correlation ActivityID="{00000000-0000-0000-0000-000000000000}" />
        <Execution ProcessName="CCS.Host.Console.vshost" ProcessID="64568" ThreadID="13" />
        <Channel/>
        <Computer>ccc</Computer>
    </System>
    <ApplicationData>
        <TraceData>
            <DataItem>
                <TraceRecord  Severity="Error">
                    <TraceIdentifier>http://msdn.microsoft.com/2004/06/System/Transactions/TransactionException</TraceIdentifier>
                    <Description>TransactionException Thrown</Description>
                    <AppDomain>CCS.Host.Console.vshost.exe</AppDomain>
                    <ExtendedData xmlns="http://schemas.microsoft.com/2004/03/Transactions/TransactionExceptionTraceRecord">
                        <TraceSource>[Distributed]</TraceSource>
                        <ExceptionMessage>The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)</ExceptionMessage>
                    </ExtendedData>
                </TraceRecord>
            </DataItem>
        </TraceData>
    </ApplicationData>
</E2ETraceEvent>
<E2ETraceEvent >
    <System >
        <EventID>0</EventID>
        <Type>3</Type>
        <SubType Name="Warning">0</SubType>
        <Level>4</Level>
        <TimeCreated SystemTime="2014-10-31T14:39:50.9591489Z" />
        <Source Name="System.Transactions" />
        <Correlation ActivityID="{00000000-0000-0000-0000-000000000000}" />
        <Execution ProcessName="CCS.Host.Console.vshost" ProcessID="64568" ThreadID="13" />
        <Channel/>
        <Computer>ccc</Computer>
    </System>
    <ApplicationData>
        <TraceData>
            <DataItem>
                <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Warning">
                    <TraceIdentifier>http://msdn.microsoft.com/2004/06/System/Transactions/EnlistmentCallbackNegative</TraceIdentifier>
                    <Description>Enlistment Callback Negative</Description>
                    <AppDomain>CCS.Host.Console.vshost.exe</AppDomain>
                    <ExtendedData >
                        <TraceSource>[Lightweight]</TraceSource>
                        <EnlistmentTraceIdentifier>
                        <ResourceManagerId>00000000-0000-0000-0000-000000000000</ResourceManagerId>
                        <TransactionTraceIdentifier>
                            <TransactionIdentifier>2e0814b3-7dd2-4c05-ad69-c3787d95c208:1</TransactionIdentifier>
                            <CloneIdentifier>2</CloneIdentifier>
                        </TransactionTraceIdentifier>
                        <EnlistmentIdentifier>0</EnlistmentIdentifier>
                        </EnlistmentTraceIdentifier><EnlistmentCallback>Aborted</EnlistmentCallback>
                    </ExtendedData>
                </TraceRecord>
            </DataItem>
        </TraceData>
    </ApplicationData>
</E2ETraceEvent>
<E2ETraceEvent >
    <System >
        <EventID>0</EventID>
        <Type>3</Type>
        <SubType Name="Warning">0</SubType>
        <Level>4</Level>
        <TimeCreated SystemTime="2014-10-31T14:39:50.9601489Z" />
        <Source Name="System.Transactions" />
        <Correlation ActivityID="{00000000-0000-0000-0000-000000000000}" />
        <Execution ProcessName="CCS.Host.Console.vshost" ProcessID="64568" ThreadID="13" />
        <Channel/>
        <Computer>ccc</Computer>
    </System>
    <ApplicationData>
        <TraceData>
            <DataItem>
                <TraceRecord Severity="Warning">
                    <TraceIdentifier>http://msdn.microsoft.com/2004/06/System/Transactions/TransactionAborted</TraceIdentifier>
                    <Description>Transaction Aborted</Description>
                    <AppDomain>CCS.Host.Console.vshost.exe</AppDomain>
                    <ExtendedData >
                        <TraceSource>[Lightweight]</TraceSource>
                        <TransactionTraceIdentifier>
                        <TransactionIdentifier>2e0814b3-7dd2-4c05-ad69-c3787d95c208:1</TransactionIdentifier>
                        </TransactionTraceIdentifier>
                    </ExtendedData>
                </TraceRecord>
            </DataIt

Answer 1

The issue was with EF closing the connection between getting the entities for groupId and then deleting the entities. This was causing the escalation. The work around is to control the opening and closing of the connection:

http://msdn.microsoft.com/en-us/data/dn456849.aspx

Behavior in EF6 and future versions

For EF6 and future versions we have taken the approach that if the calling code chooses to open the connection by calling context.Database.Connection.Open() then it has a good reason for doing so and the framework will assume that it wants control over opening and closing of the connection and will no longer close the connection automatically.

The only documentation of this behavior I could find is the table in this blog entry that implies that versions of SQL Server prior to 2008 will escalate if there are multiple connections:

https://petermeinl.wordpress.com/2011/03/13/avoiding-unwanted-escalation-to-distributed-transactions/

Here is where I landed:

using System;
using System.Data;

namespace Services.ResourceAccess
{
    public class ResourceAccess : IDisposable
    {
        private readonly Lazy<MyDbEntities> _context;

        public ResourceAccess()
        {
            _context = new Lazy<MyDbEntities>(() =>
            {
                var context = new MyDbEntities();

                context.Database.Connection.Open();

                return context;
            });
        }

        public void DeleteGroupDetails(int groupId)
        {
            var deleted = _context.Value.tblGroupDetails.Where(x => x.GroupID == groupId);

            _context.Value.tblGroupDetails.RemoveRange(deleted);

            _context.Value.SaveChanges();
        }

        public void Dispose()
        {
            if (_context.IsValueCreated)
            {
                if (_context.Value.Database.Connection.State == ConnectionState.Open)
                {
                    _context.Value.Database.Connection.Close();
                }
            }
        }
    }
}