Sharing a connection and a transaction in EF across multiple contexts (UnintentionalCodeFirstException)

Question

The previous version and question are provided as an added context below. The improved problem formulation and question could be as follows:

• How does one share a transaction between multiple contexts in EF 6.1.0 database first and .NET 4.5.2 without doing a distributed transaction?

For that it looks like I need to share a connection between the multiple contexts, but the code examples and tutorials I've been looking at thus far haven't been that fruitful. The problem looks like is hovering around on how to define a functioning combination of a connection object and transaction object types so that EF database first object metadata is also built and found when constructing the object contexts.

That is, I would like to do akin to what has been described in the EF 6.n tutorials here. Some example code could be

int count1;
int count2;
using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled)) 
{ 
    //How to define this connection so as not to run into UnintentionalCodeFirstException?
    //Creating a dummy context to obtain the connectiong string like so
    //dummyContext.Database.Connection.ConnectionString  and then using the connection will be greeted with the aforementioned exception.      
    using(var conn = new SqlConnection("...")) 
    {
        using(var c1 = new SomeEntities(conn, contextOwnsConnection: false))
        {
            //Use some stored procedures etc.
            count1 = await c1.SomeEntity1.CountAsync();
        }

        using(var c2 = new SomeEntities(conn, contextOwnsConnection: false))
        {
            //Use some stored procedures etc.
            count2 = await c2.SomeEntity21.CountAsync();
        }
    }
}   

int count = count1 + count2;

In the examples there are also other methods as to how to create a shared connection and a transaction, but as written, the culprit seem to be that if, say, I provide the connectiong string in (the "..." part) the previous snippet as dummyContext.Database.Connection.ConnectionString I'll get just an exception.

---

I'm not sure if I'm just reading the wrong sources or if there's something else that's wrong in my code when I try to share a transaction across multiple EF contexts. How could it be done?

I've read quite a few other SO posts regarding this (e.g. this) and some tutorials. They did not help.

I have a strange problem in that it looks I don't have the constructor overloads defined as in other tutorials and posts. That is, taking the linked tutorial link, I can't write new BloggingContext(conn, contextOwnsConnection: false)) and use a shared connection and an external transaction.

Then if I write

public partial class SomeEntities: DbContext
{
    public SomeEntities(DbConnection existingConnection, bool contextOwnsConnection): base(existingConnection, contextOwnsConnection) { }
}

and use it like in the tutorials, I get an exception from the following line from the following T4 template generated code

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    throw new UnintentionalCodeFirstException();
}

I'm using .NET 4.5.2 and EF 6.1.0. I'ved constructed the edmx from an existing database and generated the code from there. In this particular situation I'm using Task Parallel threads to load dozens of SQL Server Master Data Services staging tables (yes, a big model) and to call the associated procedures (provided by the MDS one per table). MDS has its own compensation logic in case staging to some of the tables fails, but rolling back a transaction should be doable too. It just looks like I have a (strange) problem with my EF.

<Addendum: Steve suggested using straight TransactionScope. Without a shared connection that would require a distributed transaction, which isn't an option I can choose. Then if I try to provide a shared connection for the contexts (some options shown in the tutorials, one here I have the problem of "missing constructors". When I define one, I get the exception I refer in the code. All in all, this feels quite strange. Maybe there's something wrong in how I go about generating the DbContext and related classes.

<Note 1: It looks like the root cause is as in this blog post by Arthur (of EF developer team) Don't use Code First by mistake. That is, in database first development the framework seeks for the class-relational mappings as defined in the connection string. Something fishy in my connection string that is..?

Answer 1

Have you tried wrapping the calls in a transaction scope?

using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted }))
{

    // Do context work here
    context1.Derp();
    context2.Derp();

    // complete the transaction
    scope.Complete();
}