Transaction with multiple DbContext and each one with its own connection on Entity Framework

Question

I know it looks like a duplicated since there are tons of questions regarding transactions on multiple contexts but none of them refer to this scenarios.

The setup

1. SQL Server 2016 (latest preview) on develop machine or SQL Azure v12 on production
2. Entity Framework 6.1.3
3. .Net 4.5 on a regular application
4. Application can run on an Azure Cloud Service or a VM, doesn't really matter

What we have in code

1. A single TransactionScope
2. Two DbContexts created outside the TransactionScope (it comes created by our dependency injection mechanics that are tied to ASP.Net MVC, so I left it out the scope for simplicity of the sample)
3. Each context create and maintain its own connection which points to different databases on different servers(it can also point to the same server in case of running on a development machine, but still different databases).
4. We use regular SQL Authentication - userId and password(just in case someone points to some post talking about problems with Integrated Security and MSDTC, we don't use it locally, since on Azure SQL it is not supported)

Our code sample

When we do something like:

var contextA = new ContextA();
var contextB = new ContextB();
using(var scope = new TransactionScope())
{
     var objA = new EntityA();
     objA.Name = "object a";
     contextA.EntitiesA.Add(objA);
     contextA.SaveChanges();

     var objB = new EntityB();
     objB.Name = "object B";
     contextB.EntitiesB.Add(objB);
     contextB.SaveChanges();
     scope.Complete();
}

What we receive

A System.Data.Entity.Core.EntityException is thrown on the call of contextA.SaveChanges() with the following messages:

Root Exception: The underlying provider failed on EnlistTransaction.

Inner Exception: Connection currently has transaction enlisted. Finish current transaction and retry.

So, anyone have a clue on what exactly is going wrong with this sample?

We are trying to have a single transaction using multiple contexts and each context with its own connection to the database. Obviously since the data of each context is on different database servers(in production) we can't use the DbContext ctor that receives a DbConnection and shares it with both contexts, so share a DbConnection is not an option.

Thank you very much, I really appreciate any help.

Answer 1

Distributed transactions with TransactionScope are now supported by Azure SQL Database. See TransactionScope() in Sql Azure.