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).

http://stackoverflow.com/questions/794364/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();
     }

Answer 1

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

Answer 2

The problem is that 2 different DataContext effectively create two different connections.

In that case, the transaction HAS to be promoted to a distributed transaction. I assume your problem comes from the coniguration of MS DTC (Microsoft Distributed Transaction Coordinator) on the server and or the client. If the server is not configured to allow remote connections for MSDTC for example, you will encounter that kind of exception.

you can refer to this MS page for example for troubleshooting MSDTC problems, and google is filled to the brim with articles/forum questions about it.

Now, it might be something else, but it really sounds like it is an MSDTC problem.

Answer 3

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.

Answer 4

I've written a answer in another question about how to diagnose MSDTC transactions failing.

You might find that answer helpful.

http://stackoverflow.com/questions/7694/how-do-i-enable-mstdc-on-sqlserver/479149#479149

Answer 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

Answer 6

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.

Answer 7

I did have similar errors occurred when using DTC when reading messages from MQ queue, processing them and storing in SQL 2005 Express Edition database. I haven't enough time to investigate till end whether 2005 or excatly Express edition caused this problem, but switching to 2008 Standard faded that particular behavior out.