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

Fortalice

Are there any triggers on the table, perhaps? – Tomfool

Wow I didn't even think of that. Just checked and there are no triggers. – Fortalice

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();
                }
            }
        }
    }
}

Fortalice

I've never seen this behavior of EF cause MSDTC to kick in. It should be perfectly possible to open and close the context's connection multiple times within one TransactionScope. But another question: why use TransactionScope when there is only one SaveChanges call? – Socage

@GertArnold Good question. Regarding TS this code will ultimately live in WCF which uses TS if you use the built in Tx management. Once I got this working I moved the TS out of this method into a calling method and called another method with another SaveChanges. When I run into a problem I try to simplify it as much as possible to get to the root cause and the code above was the simplest I could get it while still failing. I will update the answer regarding your first question. – Fortalice