Multiple databases(datacontext) on same server without MS DTC

Question

I'm using EF5.0 with SQL server 2008. I have two databases on the same server instance. I need to update tables on both databases and want them to be same transaction. So I used the TransactionScope. Below is the code -

public void Save()
{
        var MSObjectContext = ((IObjectContextAdapter)MSDataContext).ObjectContext;
        var AWObjectContext = ((IObjectContextAdapter)AwContext).ObjectContext;

        using (var scope = new TransactionScope(TransactionScopeOption.Required,
                                             new TransactionOptions
                                                 {
                                                     IsolationLevel = IsolationLevel.ReadUncommitted
                                                 }))
        {               
            MSObjectContext.SaveChanges(SaveOptions.DetectChangesBeforeSave);
            AWObjectContext.SaveChanges(SaveOptions.DetectChangesBeforeSave);

            scope.Complete();
        }
    }

When I use the above code Transaction gets promoted to DTC. After searching on internet I found that this happens because of two different connectionstrings / connections. But what I dont understand is if I write a stored procedure on one database which updates table in a different database (on same server) no DTC is required. Then why EF or TransactionScope is promoting this to DTC? Is there any other work around for this?

Please advise

Thanks in advance

Sai

Answer 1

With plain DbConnections, you can prevent DTC escalation for multiple databases on the same server by using the same connection string (with any database you like) and manually change the database on the opened connection object like so:

using (var tx = new TransactionScope())
{
    using (var conn = new SqlConnection(connectStr))
    {
        conn.Open();
        new SqlCommand("INSERT INTO atest VALUES (1)", conn).ExecuteNonQuery();
    }
    using (var conn = new SqlConnection(connectStr))
    {
        conn.Open();
        conn.ChangeDatabase("OtherDB");
        new SqlCommand("INSERT INTO btest VALUES (2)", conn).ExecuteNonQuery();
    }
    tx.Complete();
}

This will not escalate to DTC, but it would, if you used different values for connectStr.

I'm not familiar with EF and how it manages connections and contexts, but using the above insight, you might be able to avoid DTC escalation by doing a conn.ChangeDatabase(..) and then creating your context like new DbContext(conn, ...).

But please note that even with a shared connect string, as soon as you have more than one connection open at the same time, the DTC will get involved, like in this modified example:

using (var tx = new TransactionScope())
{
    using (var conn = new SqlConnection(mssqldb))
    {
        conn.Open();
        new SqlCommand("INSERT INTO atest VALUES (1)", conn).ExecuteNonQuery();
        using (var conn2 = new SqlConnection(mssqldb))
        {
            conn2.Open();
            conn2.ChangeDatabase("otherdatabase");
            new SqlCommand("INSERT INTO btest VALUES (2)", conn2).ExecuteNonQuery();
        }
    }
    tx.Complete();
}