Linq to Sql and the Unit of Work pattern

January 29 2011

I come across an interesting problem a few weeks ago.  One that I should have been aware of didn't show it’s head during testing, thanks to me testing in short periods.  Before I launch into the problem and my solution, I’ll take a few minutes to describe the application.



For the last 6 years I’ve been developing billing systems for phone airtime retailers, in particular satellite  phones (although the particular type of phone is irrelevant to the software).  My main product is a web application and website, supported by Windows Services.  One Windows Service provides logging, exception handling, email, and other infrastructure, communicating with the other applications and service via WCF.  The other Windows Service executes scheduled tasks and monitoring functions such as importing call data from various airtime providers and performing health checks and monitoring important state changes of business entities (such as when Contracts expire or come off suspension).

The Website and Web Application architecture follows Domain Driven Design paradigm with POCO business entities and a Repository Pattern Data Abstraction Layer, coordinated by an Application Layer.  For data access I chose Linq 2 Sql, partly because at the time of initial development it was a new technology I wanted to explore.  The scheduled task runner is relatively simple and the Import service was implemented using SOA with it’s own set of layers.  It uses Linq to Sql for both data access and for its simple domain model. I use constructor injection with a service locator to set the repositories for the import service.  This means when the Scheduled Service loads of it’s various tasks and monitors, the repository is created and injected into the import service.  The Linq to Sql repository creates the DataContext in the repository constructor, one context per constructor (an obvious problem if you’ve just read the below).  In both applications, service and web, I use TransactionScope to manage what is effectively a unit of work.  Bad, I know, but I never got around to correctly implementing the unit of work pattern.


I have console applications for hosting the WCF services and the and scheduled services when I’m debugging.


The Problem

A few months ago a customer was testing on the staging/test environment, where the import service is executing in a Windows Service that never gets restarted.  The tester would occasionally get emailed a message notifying that a contract could not be found for a few calls, even though he could see the phone number associated with an active contract in the web application.  I tested on my machine and could not replicated the problem, even using the same test dataset.


How Linq to Sql Repositories should be implemented

(or where the ASP.NET MVC book by Guthrie et al threw me off course)

A couple of weeks ago, with my bug list down to a bare minimum I decided to tackle this problem, especially with go-live looming.  Anyone who has worked with Linq to SQL should know this, but for those that don’t, Linq to Sql uses the DataContext to communicate with the database.  A DataContext is also an implementation of a Unit of Work and meant to be short lived, for only the duration of the business transaction.


When I started working with Linq 2 Sql, the first thing I worked on was importing call data.   I used the series of blog posts by Scott Guthrie and the ASP.NET MVC Nerd Dinner application to implement my repositories.  This sample code created the DataContext in the constructor of each repository, assigning it to a member variable.  I did the same.  The Nerd Dinner application created the repository was created when the controller was created, so I used the same repository lifecycle and the repository lived for the live of the service class, which in turn lived for the life of the Windows Service application.

A DataContext instance does not recognise new database records created by other contexts, such as Linq to SQL in another application.  This was the root cause of my problem.  I’d create or modify an entity in the web application and the DataContext in the Windows Service hosted Import Service would not recognise the change.  I never saw (or never noticed due to not repeating) this problem in my short running tests because I’d spool up the command line host for only short periods of time, resulting in a new DataContext being created for the Import Service.  The Windows Service however never got a new DataContext and the problem was found.

On top of this pretty fundamental flaw, caused by me not properly recognising that, as the MSDN library said, the DataContext is intended to be short lived, I recognised that the DataContext was not acting like a true Unit of Work, in that it was not shared amongst repositories involved in the business transaction.

To fix this problem I needed a simple way of creating a DataContext in the application layer for the period I needed it.  I also needed a simple way to share this DataContext amongst repositories involved in the business transaction.  Skip forward a few days of trial and error and I came up with the follow:

IUnitOfWork Lightwight interface representing a UnitOfWork. As a DataContext already acts like a UnitOfWork, I decided it should implement this interface.
UnitOfWorkManager Static helper class to provide an ambient IUnitOfWork/DataContext to the repositories
RepositoryBase An abstract base class all repositories implement
UnitOfWorkScope Similar to a TransactionScope, the UnitOfWorkScope provides a one shared UnitOfWork for all repositories in that scope. The scope also calls the underlying UnitOfWork’s Commit method when all work is completed.


Public Interface IUnitOfWork
    Inherits IDisposable

    Sub Commit()

End Interface


Not much to this interface, as the DataContext handles most of the resposibility of change tracking.

Public Class UnitOfWorkManager

    Private Shared _current As IUnitOfWork

    Public Shared Property Current() As IUnitOfWork
            Return _current
        End Get
        Set(ByVal value As IUnitOfWork)
            _current = value
        End Set
    End Property

End Class

UnitOfWorkManager keeps track of the current unit of work.  This class could do with more work, such as if multiple units of work need to exist at the same time.  At the moment, it assumes all currently executing code shares the same unit of work.  A problem for multithreaded code.  Another easy change could be to return a new Unit of Work / DataContext if there is no current context, but not assign to be the current.

Public MustInherit Class RepositoryBase

    Private _db As MyDataContext

    Protected Sub New()

    End Sub

    Protected Sub New(ByVal db As MyDataContext)
        _db = db
    End Sub

    Protected ReadOnly Property Db() As MyDataContext
            Return If(_db, CType(UnitOfWorkManager.Current , MyDataContext))
        End Get
    End Property

End Class

All Linq to Sql repositories implement this abstract base class and make use of the ambient DataContext it provides.

Public Class UnitOfWorkScope
    Implements IDisposable

    Private _disposed As Boolean
    Private _unitOfWork As IUnitOfWork
    Private Shared _runningScopes As Stack(Of UnitOfWorkScope)

    Public Sub New()

        If UnitOfWorkManager.Current Is Nothing Then
            UnitOfWorkManager.Current = New MyDataContext(ConfigItems.ConnectionString)
        End If
        _unitOfWork = UnitOfWorkManager.Current


    End Sub

    Public Sub Commit()
    End Sub

    Public Sub Dispose() Implements IDisposable.Dispose
    End Sub

    Private Sub Dispose(ByVal disposing As Boolean)

        If Not disposing Then Return
        If _disposed Then Return

        _disposed = True

    End Sub

    Private ReadOnly Property UnitOfWork() As IUnitOfWork
            Return _unitOfWork
        End Get
    End Property

    Private Shared ReadOnly Property RunningScopes() As Stack(Of UnitOfWorkScope)
            If _runningScopes Is Nothing Then
                _runningScopes = New Stack(Of UnitOfWorkScope)
            End If
            Return _runningScopes
        End Get
    End Property

    Private Shared Sub RegisterScope(ByVal scope As UnitOfWorkScope)
        If scope Is Nothing Then Return

        UnitOfWorkManager.Current = scope.UnitOfWork

    End Sub

    Private Shared Sub UnregisterScope(ByVal scope As UnitOfWorkScope)


        If (RunningScopes.Count > 0) Then
            Dim currentScope As UnitOfWorkScope = RunningScopes.Peek()
            UnitOfWorkManager.Current = currentScope.UnitOfWork
            UnitOfWorkManager.Current = Nothing
        End If

    End Sub

End Class

The UnitOfWorkScope uses the current unit of work as supplied by the UnitOfWorkManager if it exists.  If not, it creates a concrete unit of work (we could use IoC here).  Scopes are pushed onto a stack and popped off the stack when the Dispose method is called.  When the stack is empty, the DataContext is destroyed.


Partial Class MyDataContext
    Implements IUnitOfWork

    Public Sub Commit() Implements IUnitOfWork.Commit
    End Sub

End Class


Finally the DataContext itself implements the IUnitOfWork interface.

This all ties together in an application layer coordinating service:

Public Class MyService
    Protected _repository1 as Repository1 = new Repository1()
    Protected _repository2 as Repository2 = new Repository2()

    Public Sub New()
    End Sub

    Public Sub DoSomeWork()

        Using uow = new UnitOfWorkScope()

            Dim thingOne = _repository1.GetBy(3) ' Get thing with Id = 3
            thingOne.Name = "Test"


            Dim thingTwo = new Thingo()
            With thingTwo
                .Name = "Jimmy"
                .Age = 12
                .Occupation = "Software Architect"
            End With 



        End Using 

    End Sub

End Class

Post a comment

comments powered by Disqus