One transaction with multiple dbcontexts

In the first scenario, you are nesting DbContexts. A connection to the database is opened for each on of them. When you call your service method within the using block, a new connection is opened within the TransactionScope while there is another one already open. This causes your transaction to be promoted to a distributed transaction, and partially committed data (the result of the DbContext.SaveChanges call in the service) not being available from your outer connection. Also note that distributed transactions are far slower and thus, this has the side effect of degrading performance.

In the second scenario, while you open and close three connections, only one connection is open at the same time within your transaction. Since these connections share the same connection string, the transaction won't be automatically promoted to a distributed connection and thus, each subsequent connection within the transaction has access to the changes performed by the previous connection.

You may try adding the Enlist=false parameter to your connection string. This would disable automatic enlisting in a distributed transaction, causing an exception to be raised in your first scenario. The second scenario would keep working flawlessly if you are using SQL Server 2008 and beyond, since the transaction won't get promoted. (Prior versions of SQL Server will still promote the transaction in this scenario.)

You may also find helpful this great answer to a quite similar question.

This works:

public class Test1 { public int Id { get; set; } public string Name { get; set; } }

public class Test2
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class DC1 : DbContext
{
    public DbSet<Test1> Test1 { get; set; }

    public DC1(SqlConnection conn)
        : base(conn, contextOwnsConnection: false)
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.HasDefaultSchema("dc1");

        modelBuilder.Entity<Test1>().ToTable("Test1");
    }
}

public class DC2 : DbContext
{
    public DbSet<Test2> Test2 { get; set; }

    public DC2(SqlConnection conn)
        : base(conn, contextOwnsConnection: false)
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.HasDefaultSchema("dc2");

        modelBuilder.Entity<Test2>().ToTable("Test2");
    }
}

...

using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EntityConnectionString"].ConnectionString))
{
    conn.Open();

    using (var tr = conn.BeginTransaction())
    {
        try
        {
            using (var dc1 = new DC1(conn))
            {
                dc1.Database.UseTransaction(tr);
                var t = dc1.Test1.ToList();
                dc1.Test1.Add(new Test1
                {
                    Name = "77777",
                });
                dc1.SaveChanges();
            }
            //throw new Exception();
            using (var dc2 = new DC2(conn))
            {
                dc2.Database.UseTransaction(tr);
                var t = dc2.Test2.ToList();
                dc2.Test2.Add(new Test2
                {
                    Name = "777777",
                });
                dc2.SaveChanges();
            }
            tr.Commit();
        }
        catch
        {
            tr.Rollback();
            //throw;
        }
        App.Current.Shutdown();
    }
}

I guess it is better to fetch outside the transaction so no locking occurs, but I am not sure - need to investigate this myself

Update: The above code works with code-first approach The below code is for database-first

public MetadataWorkspace GetWorkspace(Assembly assembly)
{
    MetadataWorkspace result = null;
    //if (!mCache.TryGetValue(assembly, out result) || result == null)
    {
        result = new MetadataWorkspace(
            new string[] { "res://*/" },
            new Assembly[] { assembly });
        //mCache.TryAdd(assembly, result);
    }
    return result;
}

...

using(var conn = new SqlConnection("..."))
{
  conn.Open();
  using(var tr = conn.BeginTransaction())
  {
        using(var entityConnection1 = new EntityConnection(
            GetWorkspace(typeof(DbContext1).Assembly), conn))
      {
        using(var context1 = new ObjectContext(entityConnection1))
        {
          using(var dbc1 = new DbContext1(context1, false))
          {
            using(var entityConnection2 = new EntityConnection(
                GetWorkspace(typeof(DbContext2).Assembly), conn))
            {
                using(var context2 = new ObjectContext(entityConnection2))
                {
                  using(var dbc2 = new DbContext2(context2, false))
                  {
                    try
                    {
                        dbc1.UseTransaction(tr);
                        // fetch and modify data
                        dbc1.SaveChanges();

                        dbc2.UseTransaction(tr);
                        // fetch and modify data
                        dbc2.SaveChanges();

                        tr.Commit();
                    }
                    catch
                    {
                        tr.Rollback();
                    }
                  }
                }
              }
          }
        }
      }
  }
}

It is useful when using many DbContexts in you app. For example if you have thousands of tables - I just created so called "modules" with about hundred table per module. And each "module" has single context sometimes though I need to do cross-module data modifications in a single transaction