How can I get two units of work under a single transaction

Question

I have one MS Sql Server containing two databases. In a C# console application I have created two different entity data models (edmx) (EF6); one for DatabaseA and one for DatabaseB; applying the repository and unit of work patterns to both. Separately, they work well. No problems. What I am not able to figure out is how to bring both under a single 'transaction'.

Before EF, I would create SqlConnection and SqlTransaction, modify the relevant tables in either database within that transaction, then commit or rollback, as appropriate. But that doesn't seem to have an analog in EF.

UnitOfWorkForDatabaseA.Commit(); 
UnitOfWorkForDatabaseB.Commit(); //If this fails, both should rollback

But that doesn't seem possible with two separate units of work each with its own ObjectContext.

Do I need to surround them both in a TransactionScope? Or maybe design a SuperUnitOfWork?

Answer 1

Either use a TransactionScope and a Distributed Transaction (warning requires MSDTC), or use a single SqlConnection for both DbContext instances. You will have to manually switch the database context from the first database to the second by calling

USE OtherDatabaseName

To make this work the easiest way is to use TransactionScope (it will not be promoted to a DTC transaction since you're using a single SqlConnection).

eg

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration;
using System.Data.SqlClient;
using System.Linq;
using System.Transactions;

namespace ConsoleApp8
{

    public class A
    {
        public int AID { get; set; }
        public string Name { get; set; }
    }

    public class B
    {

        public int BId { get; set; }
        public string Name { get; set; }
    }
    class DbA : DbContext
    {
        public DbA(): base()
        {

        }
        public DbA(DbConnection con) : base(con,false)
        {

        }
        public DbSet<A> A { get; set; }


    }
    class DbB : DbContext
    {
        public DbB() : base()
        {

        }
        public DbB(DbConnection con) : base(con, false)
        {

        }
        public DbSet<B> B { get; set; }


    }



    class Program
    {      

        static void Main(string[] args)
        {

            Database.SetInitializer(new CreateDatabaseIfNotExists<DbA>());
            Database.SetInitializer(new CreateDatabaseIfNotExists<DbB>());
            string DatabaseNameA, DatabaseNameB;
            using (var db = new DbA())
            {
                db.Database.Initialize(false);
                DatabaseNameA = db.Database.Connection.Database;
            }

            using (var db = new DbB())
            {
                db.Database.Initialize(false);
                DatabaseNameB = db.Database.Connection.Database;
            }

            var opts = new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted };

            using (var dbA = new DbA())
            using (var tran = new TransactionScope(TransactionScopeOption.Required, opts))
            {


                var a = dbA.A.Create();
                a.Name = "someA";
                dbA.A.Add(a);
                dbA.SaveChanges();

                dbA.Database.ExecuteSqlCommand($"use [{DatabaseNameB}]");
                using (var dbB = new DbB(dbA.Database.Connection))
                {

                    var b = dbB.B.Create();
                    b.Name = "someB";
                    dbB.B.Add(b);
                    dbB.SaveChanges();

                }

                tran.Dispose();

            }



            using (var dbA = new DbA())
            {
                dbA.Database.Connection.Open(); //lock the connection open if not using a transaction
                Console.WriteLine($"Count of A: {dbA.A.Count()}");

                dbA.Database.ExecuteSqlCommand($"use [{DatabaseNameB}]");
                using (var dbB = new DbB(dbA.Database.Connection))
                {
                    Console.WriteLine($"Count of B: {dbB.B.Count()}");

                }



            }

            Console.WriteLine("Hit any key to exit");
            Console.ReadKey();






        }
    }
}

outputs

Count of A: 0
Count of B: 0
Hit any key to exit

The reason you have to use TransactionScope here is that SaveChanges will otherwise use SqlTransaction internally. Fun fact: SqlTransaction is pretty badly broken. It requires manual SqlCommand enlistment, and it doesn't support nested transactions (which it calls "parallel transactions"). Any way it dates from .NET 1.0, and can't really be changed. It was made to work properly with System.Transactions when it appeared in .NET 2.0, though.