LINQ to SQL handles a lot of functionality out of the box for you, including the ability to perform updates under transaction contexts for you transparently. If you modify, add or delete instances of entities the changes are updated in the context of a transaction when SubmitChanges() is called. This covers a wide range of operations and this is as it should be for an ORM: Transactions should be handled mostly transparently.

Every once in a while though you may need to perform some update tasks manually rather than through the LINQ model and you may end up combining operations in multiple data contexts that logically need to operate as a single transaction. I’m making up a slightly contrived example here as this can and probably should be handled as part of your model, but bear with me for demonstration purposes. Assume you have a parent record and you need to also delete all children and there’s no explicit relation defined in the model, so that you have to manually delete the items. As weird as that sounds I actually have several apps where this situation exists because the relationship is a rarely used one and managed manually rather than being enforced through the model.

So, inside of a business object that holds code snippets and comments associated with a snippet handles child deletion involving two explicit data operations like this:

public override bool Delete(CodeSnippet entity)
{
    if (entity == null)            
        entity = this.Entity;            

    using (TransactionScope trans = new TransactionScope())
    {
        // base business layer delete behavior through DataContext
        if (!base.Delete(entity))
            return false;

        // plain Ado.Net operation using Context and Context’s connection
        // no retrieval first to delete
        int result = this.Context.ExecuteCommand("delete from comments where snippetId = {0}",
                                                 entity.Id));
        if (result < 0)
            return false;

        trans.Complete();
        return true;
    }
}

In the business base layer the Delete operation in this case submits the active entity for deletion which internally looks something like this (simplified):

public virtual bool Delete(TEntity entity)
{        
     Table<TEntity> table = this.Context.GetTable(typeof(TEntity)) as Table<TEntity>;
     table.DeleteOnSubmit(entity);                    
     return this.SubmitChanges();
}

The idea is that there are two data operations involved – one to delete the parent entity and one to delete the children. Two separate operations, one using straight LINQ to SQL and SubmitChanges() and one using a plain old ADO.NET operation (internally wrapped in the base business layer). Both are using the same DataContext instance and therefore also the same connection object.

If your operation requires multiple data operations you’ll want to wrap the whole process into a transaction. LINQ to SQL will provide automatic transactions for any single SubmitChanges() updates performed, but it won’t help if you need to run multiple separate commands commands that need to be committed as one. So explicit transactions are required.

Another example of multiple data operations that require transaction wrapping might be Stored Procedure calls – either manual as I did above or for imported LINQ to SQL Stored Proc wrappers.

TransactionScope to the Rescue

The easiest way to do manual transactions with LINQ to SQL is by using TransactionScope. As you can see TransactionScope is very easy to use by wrapping a using{} statement around the transactable data access code. Calling Complete() on the TransactionScope commits the transaction, exiting the using block without calling Complete() causes the transaction to roll back.

TransactionScope is a high level Transaction wrapper that makes it real easy to wrap any code into a transaction without having to track transactions manually. Traditionally TransactionScope was a .NET wrapper around the Distributed Transaction Coordinator (DTC) but it’s functionality has expanded somewhat. One concern is that the DTC is rather expensive in terms of resource usage and it requires that the DTC service is actually running on the machine (yet another service which is especially bothersome on a client installation).

However, recent updates to TransactionScope and the SQL Server Client drivers make it possible to use TransactionScope class and the ease of use it provides without requiring DTC as long as you are running against a single database and with a single consistent connection string. In the example above, since the transaction works with a single instance of a DataContext, the transaction actually works without involving DTC. This is in SQL Server 2008.

To check this out, shut down the DTC service and run multiple operation wrapped in a transaction context against a single DataContext and notice that both committing and rolling back does all the right things even though DTC is not available.

However, if your code is a little more isolated and requires multiple connections or DataContexts things get a little more tricky. For example take this code that offloads the detail deletion to a separate business object which in my case uses a separate connection/data context:

public override bool Delete(CodeSnippet entity)
{
    if (entity == null)            
        entity = this.Entity;            

    using (TransactionScope trans = new TransactionScope())
    {
        // base business layer delete behavior through DataContext
        if (!base.Delete(entity))
            return false;

        // Use a new bus object and separate DataContext to delete
        busComment comment = new busComment();
        if (!comment.DeleteCommentsForSnippet(entity.Id))
        {
            this.SetError(comment.ErrorMessage);
            return false;
        }

        trans.Complete();
        return true;
    }
}

// in busComment.cs  - uses separate DataContext instance/connection

/// <summary>
/// Deletes all comments for a specific user
/// </summary>
/// <param name="snippetId"></param>
/// <returns></returns>
public bool DeleteCommentsForSnippet(string snippetId)
{
    int result =  this.Context.ExecuteNonQuery("delete from Comments where snippetId = @snippetId",
         this.Context.CreateParameter("@snippetId", snippetId));
    
   return result > -1;                
}

In this scenario two separate connections are involved and this transaction by default tries to escalate to a DTC transaction. If the DTC Service isn’t running the first LINQ to SQL or low level ADO.NET operation will fail with an error to the effect that DTC transaction context isn’t available.

TransactionScope DTC Requirements

The good news is that as of .NET 3.5 and SQL 2008/2005 local transactions no longer have to escalate to distributed transactions, when single connections are involved. According to that article with SQL 2008 multiple connections should also work as long as the same connection string is used but I was unable to get that behavior to work. When the DTC  service is not running and when using multiple data context I always get an error on the first data update operation.

You can control whether the connection participates in DTC transaction via the Enlist attribute on the connection string:

    <add name="CodePasteBusiness.Properties.Settings.CodePasteConnectionString"
         connectionString="Data Source=.;Initial Catalog=CodePaste;Integrated Security=True;Enlist=True;"
         providerName="System.Data.SqlClient"/>

The default for Enlist=True which means that transactions can escalate to DTC transactions. False treats all transactions as local ADO.NET transactions. Using False is useful only if you know for sure that you are not using distributed transactions anywhere in your application – in which case it provides a very slight performance improvement (no checks for distributed transactions pending).

When set to the default of true, and when escalating a transaction to the DTC when the service is not running, any update command produces an error:

MSDTC on server 'xxx' is unavailable.

If you want to figure out which transactions are escalating and which stay local you can turn off DTC and catch SqlExceptions  Number 8501 – those will be distributed transactions that fail.

To sum up – the DTC should be required only if you run commands across multiple connections/DataContexts. For single connections/Contexts TransactionScope stays with local and efficient Sql Transactions.

Transactions without TransactionScope

LINQ to SQL also supports transactions without TransactionScope although given how easy TransactionScope is and that it doesn’t have to escalate to DTC, I see little reason to do so. If you prefer this more traditional approach though it’s available. Using manual transactions also works against a single DataContext only as you can’t transfer an active transaction to another DataContext.

The following demonstrates manually using transactions with explicit commands which works just as you would expect it to:

public override bool Delete(CodeSnippet entity)
 {
     if (entity == null)            
         entity = this.Entity;

     this.Context.Connection.Open();
     DbTransaction trans = this.Context.Connection.BeginTransaction();
     this.Context.Transaction = trans;
     
     int result = this.Context.ExecuteCommand("delete from CodeSnippets where Id = {0}",
                                      entity.Id);

     if (result < 0) // !base.Delete(entity))
     {
         this.Context.Transaction.Rollback();
         return false;
     }

     result = this.Context.ExecuteCommand("delete from comments where snippetId = {0}",
                                          entity.Id);
     if (result < 0)
     {
         this.Context.Transaction.Rollback();
         return false;
     }

     this.Context.Transaction.Commit();
     return true;

 }

This even works if you call SubmitChanges() inside of the transaction – LINQ to SQL will respect the open transaction and keep it open until you Commit or Rollback explicitly or the connection is explicitly closed.

This works across a single DataContext, but not across multiple DataContexts. Trying to assign the transaction to another context throws an exception “Transaction does not match connection”, which is not surprising.

Summary

• SubmitChanges() implicitly uses transactions for all batch updates
• Multiple connections/DataContext update coordination requires manual transactions

If you use LINQ to SQL explicit transactions against a single DataContext:

• You can use TransactionScope – it’s easiest and won’t escalate to DTC within a single DataContext
• You can also use explicit DataContext.Transaction to manually manage an ADO.NET transaction

If you need to jump transactions across DataContexts:

• You have to use TransactionScope and DTC has to be available to use the DTC externally