Begin transaction with 2 DBContexts

Question

The idea : I am trying to run an insertion to 2 databases using 2 different dbContext, the goal is to allow a role back on the insertion from both QBs in case of an exception from ether one of the insertions.

My code:

using (var db1 = new DbContext1())
{
    db1.Database.Connection.Open();
    using (var trans = db1.Database.Connection.BeginTransaction())
    {
        //do the insertion into db1
        db1.SaveChanges();

        using (var db2 = new DbContext2())
        {
            //do the insertions into db2
            db2.SaveChanges();
        }
        trans.Commit();
    }
}

On the first call to save changes: db1.SaveChanges(); I get an invalid operation exception : sqlconnection does not support parallel transactions

I tried figuring out what does it exactly mean, why does it happen and how to solve it but haven't been able to achieve that.

So my questions are: What does it exactly mean? and why do I get this exception? How can I solve it? Is there a way to use the begin transaction is a different way that won't cause this error?

Also, is this the proper way to use begin transaction or should I do something different?

***For clarification, I am using the db1.Database.Connection.Open(); because otherwise I get an "connection is close" error.

Answer 1

Instead of trying to strech your connection and transaction across two DbContext, you may go for handling your connection and transaction outside of your DbContext, something like this :

using (var conn = new System.Data.SqlClient.SqlConnection("yourConnectionString"))
{
    conn.Open();
    using (var trans = conn.BeginTransaction())
    {
        try
        {
            using (var dbc1 = new System.Data.Entity.DbContext(conn, contextOwnsConnection: false))
            {
                dbc1.Database.UseTransaction(trans);
                // do some work
                // ...
                dbc1.SaveChanges();
            }

            using (var dbc2 = new System.Data.Entity.DbContext(conn, contextOwnsConnection: false))
            {
                dbc2.Database.UseTransaction(trans);
                // do some work
                // ...
                dbc2.SaveChanges();
            }
            trans.Commit();
        }
        catch
        {
            trans.Rollback();
        }
    }
}