MSDTC + Linq to Sql Update

When I was writing my previous article, I was keenly aware that it had it's limitations. One in particular was using an already open DataContext.

Take the following code:

// Create outside TransactionScope
var c1 = new UserDataContext();

using (var scope = new TransactionScope())
{
    c1.Users.ToList();

    // This will promote to MSDTC
    using (var c2 = new UserDataContext())
        c2.Users.ToList();

    scope.Complete();
}

Since it was created outside the TransactionScope I have no way of automatically sharing these connections.

To help solve this problem (cleanly) I added a SharedConnectionScope class and a UseExistingConnection call (shown below) - both take an existing DataContext.

// Create outside TransactionScope
var c1 = new UserDataContext();

using (var scope = new TransactionScope())
using (SharedDataContext.UseExistingConnection(c1))
{
    c1.Users.ToList();

    // This will share existing connection
    using (var c2 = new UserDataContext())
        c2.Users.ToList();

    scope.Complete();
}

Caveat: It only works with one 'external' connection, but that's still better than none.

You can grab the updated code here.

Quick Application - Tickle Me Pink

I do all my WPF coding in straight XAML, and for my prototypes I like to use the standard .Net colors.

So instead of playing a fun colorname -> color guessing game, I thought I'd have some fun and whip up this tiny color app (it's ridiculously easy in WPF). 

The following code is the entire application, it combines WPF, reflection, Linq and Anonymous Types.

 

UPDATE: Adjusted layout to look better in google reader.

<Window x:Class="Colors.ColorWindow" Title="Colors" 
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:my="clr-namespace:Colors" WindowStyle="ToolWindow"
    Height="500" Width="200">
  <Window.Resources>
    <DataTemplate x:Key="ColorTemplate">
      <StackPanel Orientation="Horizontal">
        <Border BorderBrush="Black" BorderThickness="1" Width="30" Height="30" Margin="10"
                Background="{Binding Brush}" ToolTip="{Binding Brush.Color}" />
        
        <TextBlock Text="{Binding Name}" VerticalAlignment="Center" />
        </StackPanel>
    </DataTemplate>
  </Window.Resources>

  <ListBox x:Name="colors" ItemTemplate="{StaticResource ColorTemplate}" />
</Window>

public partial class ColorWindow : Window
{
    public ColorWindow()
    {
        InitializeComponent();

        colors.ItemsSource = typeof(Brushes)
            .GetProperties(BindingFlags.Static | BindingFlags.Public)
            .Select(x => new { 
                                Name = x.Name, 
                                Brush = x.GetValue(null, null)
                             });
    }
}

DataModel-View-ViewModel

WPF changed the rules for UI best practice, but it didn't update the book.

With all the new and improved DataBinding and Command features - a new set of patterns have emerged, including a recommended pattern called DataModel-View-ViewModel (DM-V-VM).

At a conceptual level this pattern looks fantastic – but there are issues... namely it doesn’t work for non-trivial scenarios.

Rather than a boring high level rant, I'll use boring examples to explain why:

Cannot support events

The recommended approach is to use DataTemplates and Commands - but DataTempates cannot handle events, and the command system is not extensive enough (eg. doesn't support mouse clicks).

Supporting events requires using code behind; which means using at least a UserControl base class.

No Keyboard support for Standard Commands

Dan Crevier and John Gossman don't mention the standard Commands, instead opting to create their own – not because it's better practice, but it's easier to demonstrate.

Their suggested approach uses CommandParameter bindings on the buttons, which only works with OnClick (i.e when the shortcut Key is pressed "null" is passed through).

Since the InputBindings.KeyBinding.CommandParameter does not support DataBinding - supporting the keyboard (and still using commands) means we can't use CommandParameters.

UI support breaks abstraction

Say that we want to delete all the selected items from a list; this means storing a list of items in the ViewModel (since we not using CommandParameters), which we then bind from our View.

But this is purely for UI logic rather than business logic!

Complicated scenarios might need multiple properties - and since the binding is not clearly defined, it will introduce bugs.

The abstraction is now broken, so let's just move our ViewModel into the code behind.

Unit testing the UI logic now not easy

No kidding, that's why we initially moved the code into the ViewModel!

All of the real logic should be implemented in the data bound objects or by a class separate to the UI. These classes are unit testable, but the UI integration is not.

Aren't we back to where we started?

Not quite, WPF's new style allows us to develop a cleaner system than the days of WinForms and MFC - and while there's not a published best practice for it, it doesn't mean that it doesn't exist.


UI programming is hard, especially since it looks so easy.

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.