Why doesn't TransactionScope work with Entity Framework?

Question

See the code below. If I initialize more than one entity context, then I get the following exception on the 2nd set of code only. If I comment out the second set it works.

{"The underlying provider failed on Open."}

Inner: {"Communication with the underlying transaction manager has failed."}

Inner: {"Error HRESULT E_FAIL has been returned from a call to a COM component."}

Note that this is a sample app and I know it doesn't make sense to create 2 contexts in a row. However, the production code does have reason to create multiple contexts in the same TransactionScope, and this cannot be changed.

Edit

Here is a previous question of me trying to set up MS-DTC. It seems to be enabled on both the server and the client. I'm not sure if it is set up correctly. Also note that one of the reasons I am trying to do this, is that existing code within the TransactionScope uses ADO.NET and Linq 2 Sql... I would like those to use the same transaction also. (That probably sounds crazy, but I need to make it work if possible).

How do I use TransactionScope in C#?

Solution

Windows Firewall was blocking the connections to MS-DTC.

using(TransactionScope ts = new System.Transactions.TransactionScope())
        {
                using (DatabaseEntityModel o = new DatabaseEntityModel())
                {
                    var v = (from s in o.Advertiser select s).First();
                    v.AcceptableLength = 1;
                    o.SaveChanges();
                }

                //-> By commenting out this section, it works
                using (DatabaseEntityModel o = new DatabaseEntityModel())
                {
                    //Exception on this next line
                    var v = (from s1 in o.Advertiser select s1).First();                         v.AcceptableLength = 1;
                    o.SaveChanges();
                }
                //->

                ts.Complete();
        }

Solution 1

Your MS-DTC (Distributed transaction co-ordinator) is not working properly for some reason. MS-DTC is used to co-ordinate the results of transactions across multiple heterogeneous resources, including multiple sql connections.

Take a look at this link for more info on what is happening.

Basically if you make sure your MS-DTC is running and working properly you should have no problems with using 2 ADO.NET connections - whether they are entity framework connections or any other type.

Solution 2

You can avoid using a distributed transaction by managing your own EntityConnection and passing this EntityConnection to your ObjectContext. Otherwise check out these.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=580828&SiteID=1&mode=1 http://forums.microsoft.com/msdn/showpost.aspx?postid=113669&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

EntityConnection conn = new EntityConnection(ConnectionString);

using (TransactionScope ts = new TransactionScope())
{
    using (DatabaseEntityModel o = new DatabaseEntityModel(conn))
    {
            var v = (from s in o.Advertiser select s).First();
            v.AcceptableLength = 1;
    }

    //-> By commenting out this section, it works
    using (DatabaseEntityModel o = new DatabaseEntityModel(conn))
    {
        //Exception on this next line
        var v = (from s1 in o.Advertiser select s1).First();
                v.AcceptableLength = 1;
    }
    //->

    ts.Complete();
}

Solution 3

Add C:\Windows\msdtc.exe to the firewall exceptions on both the firewall and server. I spent ages monkeying around opening specific port numbers and ranges to no avail before I did this.

Solution 4

I'm going to stick this here because I spent 3 hours with a colleague yesterday debugging this issue. Every single answer surrounding this says that this is always a firewall issue; however in our case it wasn't. Hopefully this will spare someone else the pain.

The situation that we have is that we are currently in the process of migrating to the Entity Framework. That means that we have parts of the code where inside a single transaction connections are opened both directly using a new SqlConnection(connectionString).Open() and indirectly by using an EF data context.

This has been working fine in our application for a while, but when we started to retrospectively go and put tests around the code that worked in production, the code executed from the test runner kept throwing this error the first time the EF object tried to connect to the database after a direct connection had been made in the same transaction.

The cause of the bug eventually turned out to be that if you do not supply an Application Name= argument to your connection string, the Entity Framework adds one by default (something like EntityFrameworkMUF). This means that you have two distinct connections in your connection pool:

1. The one that you open manually with no Application Name= argument
2. An automatically generated one suffixed Application Name=EntityFrameworkMUF

and it is not possible to open two distinct connections inside a single transaction. The production code specified an application name; hence it worked; the test code did not. Specifying the Application Name= argument fixed the bug for us.

Solution 5

BTW you should consider using SaveChanges(false) in combination with AcceptChanges() when you using Explicit transactions like this.

That way if something fails in SaveChanges(false), the ObjectContext hasn't discarded your changes so you can re-apply later or do some error logging etc.

See this post for more information: http://blogs.msdn.com/alexj/archive/2009/01/11/savechanges-false.aspx

Cheers

Alex

View more solutions

Comments

[NotDan]

I'm not just using Entity Framework, so reusing the EntityConnection isn't an easy solution either (see edit above)

[NotDan]

See my edit above. I think you are right and I am trying to get MS-DTC setup... I'm not sure why it isn't working.

[NotDan]

This works now. Windows Firewall was blocking the connections to MS-DTC.

[Pontus Gagge]

+1 for avoiding DTC. Not that it's bad, it's just that distributed transactions isn't something to choose lightly. It's a close linking of application and resources, which risks low availability by design.

[Sander Rijken]

You can also call context.Connection.Open() to manually manage it. You don't need to create the EntityConnection manually

[greenoldman]

I don't get it -- you showed example with one EntityConnection, so it should work, but it fails. So how is this a solution?

[Alexander Derck]

My problem was even more specific, one connectionstring was using App=EntityFramework the other was using Application Name = EntityFramework. Best thing to do test if this is your issue, is to copy connectionstring from one context and pass it as a parameter when you create the second one.