Avoiding MSDTC with Linq to Sql

Now MSDTC is not evil; it's just overkill when it isn't needed. It slows performance, increases complexity and requires client side configuration... and it can activate when you least expect it.

Take the following code:

void CreateUsers()
{
    using (var scope = new TransactionScope(TransactionScopeOption.Required))
    {
        CreateUser("Fred");
        CreateUser("Joe");

        scope.Complete();
    }
}

void CreateUser(string name)
{
    using (var context = new UserDataContext())
    {
        context.Users.InsertOnSubmit(new User { name = name });
        context.SubmitChanges();
    }
}

This code uses the recommended (and very helpful) TransactionScope class - and unfortunately invokes MSDTC.

Since we're using separate instances of UserDataContext this creates separate database connections - and causes TransactionScope to "promote" the transaction to require MSDTC.

Avoiding this is as simple as sharing the database connection - after all, it is the same database - but passing database connections to sub methods gets ugly really quick.


My Solution

To solve this problem in a cleaner manner, I wrote a Transaction Resource Manager - which works behind the scenes and makes the above code work without change.

It simply shares database connections across the instances (only within a transaction). It's thread safe, and easy to integrate - simply change the DataContext base class in the dbml designer.



Grab the code and check it out. There's an example program and a whole slew of unit tests (around 275 - testing the various permutations of nested TransactionScopes).

Kudos to Nick Guerrea's Weak Dictionary - which I use to weakly track the transactions and share the connections.


Same Solution for other Scenarios

There are other scenarios where this problem arises:

Say you've got a database with many tables (or a plug-in system that shares a database) - a modular approach uses multiple DataContext classes.

Our problem occurs when you need to modify tables from different DataContexts within a single transaction.

Thankfully, the root cause is still the same and our solution solves these cases as well.


This has been a great learning experience for me, and I must say that the Transaction Resource Managers are very interesting (might even make a good alternate ScopeGuard implementation?)

EDIT: See updated article for extra goodness.