How to run two Entity Framework Contexts inside TransactionScope without MSDTC?

Question

This problem is not readily reproducible in a simple example here but was wondering if anyone has any experience and tips, here is the issue:

• using Entity Framework
• have many points in application where (1) data is written to some entity table e.g. Customer, (2) data is written to history table
• both of these actions use Entity Framework, HOWEVER, they use different contexts
• these actions need to be both in one transaction: i.e. if one fails to write, the other should not write, etc.
• I can wrap them with a TransactionScope,

like this:

using (TransactionScope txScope = new TransactionScope()) {
    ...
}

but this gives me:

Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for network transactions.

Our database admin has told me that MSDTC is disabled by choice and can not be installed.

Hence I am making changes trying to create my own EntityConnection with a MetadataWorkspace with the idea that each context will use the same EntityConnection. However, this is proving near impossible trying to get it to work, e.g. currently I continue to get the above error even though theoretically both contexts are using EntityConnection. It's difficult to understand where/why Entity Framework is requiring the MSDTC for example.

Has anyone gone down this road before, have experience or code examples to share?

Demote

Answer 1

Well, the problem is quite easy.

If you are using sql server 2008 you should not have that problem because you have promotable transaction, and as .NET knows that you are using the same persistence store (the database) it wont promote it to DTC and commit it as local. look into promotable transaction with sql server 2008.

As far as I know Oracle is working in its driver to support promotable transactions, but I do not know the state, MS oracle driver does not support it. http://www.oracle.com/technology/tech/windows/odpnet/col/odp.net_11.1.0.7.20_twp.pdf

If you are using a driver that do not support promotable transactions it is impossible for .NET to use local transaction doing two connections. You should change your architecture or convince the database admin for installing MSDTC.

Loosetongued

hmmm, we actually are using SQL Server 2008, it seems to not actually know that we are using the same database in both connections: my hence my current approach is to build two EntityConnection objects, both from ONE SqlConnection object, and perhaps that these SQLConnection objects are teh same in both contexts EF will not promote it to DTC. – Demote

UPDATE: sorry, the database is actually still SQL Server 2005, which as you noted seems to be the problem, I double-checked the version after reading this most excellent post with lots of recorded data as he worked through exactly this problem: #1691392 – Demote

so at the end you have two options: changing the sqlserver to 2008 or changing the code. Good luck! – Loosetongued

Answer 2

I had a similar problem with SQL 2008, Entity Framework.

I had two frameworks defined (EF1, and EF2) but using identical connection strings to a sql 2008 database.

I got the MSDTC error above, when using nested "usings" across both. eg the code was like this:

using (TransactionScope dbContext = new TransactionScope())
{
     using (EF1 context = new EF1())
     {
         // do some EF1 db call
         using (EF2 context2 = new EF2())
         {
              // do some EF2 db call
          }
      }
      dbContext.Complete();
}

It wasnt as simple as this, because it was split across several methods, but this was the basic structure of "usings".

The fix was to only open one using at a time. No MTDSC error, No need to open distributed transactions on db.

using (TransactionScope dbContext = new TransactionScope())
{
     using (EF1 context = new EF1())
     {
         // do some EF1 db call

      }
     using (EF2 context2 = new EF2())
     {
              // do some EF2 db call
     }
     dbContext.Complete();
}

Mulloy

yeah this code doesn't work. it is not different than calling methods that each spawn a different context. – Jolda

TransactionScope is no longer the recommended way to manage transactions on EF 6. I know that you wrote your answer considering EF1 and EF2 but I just wanted to pointed out that now we shouldn't use transaction scope. See this link from Microsoft: msdn.microsoft.com/en-us/data/dn456843.aspx – Handbarrow

@FranciscoGoldenstein Fair, but dbContext.Database.BeginTransaction() only applies if you are using a single context and it's already open. That is the only case your linked article addresses. It doesn't work for more complex conditions. – Justinjustina

Answer 3

I think that what you need to do is to force your contexts to share single database connection. You will be able then to perform these two operations against two different contexts in single transaction. You can achieve this by passing one EntityConnection object to both of your context's constructors. Of course this approach will require you to pass this object to methods which update DB.

I have recently blogged about creating database context scope which will make using multiple EF contexts and transactions easier.

Lucchesi