LINQ Transactions using a Custom Base Datacontext

• Download source - 16.53 KB

Introduction

LINQ to SQL allows developers to rapidly develop data driven applications by bridging the gap between business logic and the database.

One of the central pieces of the LINQ to SQL system is the DataContext.

The DataContext is the source of all entities mapped over a database connection. It tracks changes that you made to all retrieved entities and maintains an "identity cache" that guarantees that entities retrieved more than one time are represented by using the same object instance. In general, a DataContext instance is designed to last for one "unit of work" however your application defines that term. A DataContext is lightweight and is not expensive to create. A typical LINQ to SQL application creates DataContext instances at method scope or as a member of short-lived classes that represent a logical set of related database operations.

LINQ to SQL supports three distinct transaction models. The following lists these models in the order of checks performed.

• Explicit Local Transaction

  When SubmitChanges is called, if the Transaction property is set to a (IDbTransaction) transaction, the SubmitChanges call is executed in the context of the same transaction.

  It is your responsibility to commit or rollback the transaction after successful execution of the transaction. The connection corresponding to the transaction must match the connection used for constructing the DataContext. An exception is thrown if a different connection is used.

• Explicit Distributable Transaction

  You can call LINQ to SQL APIs (including, but not limited to SubmitChanges) in the scope of an active Transaction. LINQ to SQL detects that the call is in the scope of a transaction and does not create a new transaction. LINQ to SQL also avoids closing the connection in this case. You can perform query and SubmitChanges executions in the context of such a transaction.

• Implicit Transaction

  When you call SubmitChanges, LINQ to SQL checks to see whether the call is in the scope of a Transaction or if the Transaction property (IDbTransaction) is set to a user-started local transaction. If it finds neither transaction, LINQ to SQL starts a local transaction (IDbTransaction) and uses it to execute the generated SQL commands. When all SQL commands have been successfully completed, LINQ to SQL commits the local transaction and returns.

In summary:

1. LINQ creates a Transaction for you when calling SubmitChanges. What if you need to change the transaction isolation level? What if you need to execute a query with a (NOLOCK) clause? (ReadUncommitted) How can you accomplish this task using the default DataContext? It can be done, but we want a reusable solution.
2. Using System.Transactions is an elegant approach and the ONLY option for distributed transactions. If you opt to wrap all transactions in a System.Transactions block, then you might not need a solution for DataContext Transactions.
3. If you manually start a Transaction, YOU must close it. This holds true for connections as well. This is an important point to remember when creating this class.

Background

I needed an easy way to ensure the proper Isolation Level for executing my LINQ to SQL queries and this class was the result. There are times when performing a 'Dirty Read' IS the desired effect and the BaseDataContext allows me to accomplish this.

Using the Code

The BaseDataContext can be used in three ways:

1. When using a *.dbml file, you can change the base class setting for the DataContext from DataContext to BaseDataContext. If you ONLY need 'Dirty Reads' (Uncommitted) for a Context, this is the easiest method of implementation.
2. If you need more control over your DataContext, you can extend the partial class created by the *.dbml file.
   
   
   C#

   public partial class NorthwindDataContext : BaseDataContext
   {
       public NorthwindDataContext(string connectionString, IsolationLevel level) :
           base(connectionString, level) { }
   } 

3. If you prefer NOT to use the default Contexts created by the *.dbml file, then you can directly inherit from the BaseDataContext.

I have uploaded a new version which will ONLY open the connection/transaction when needed.

This ensures the connections/transactions are closed in a timely manner.

• You should always wrap the BaseDataContext inside a using statement:
  
  
  C#

  using (var Ctx = new NorthwindDataContext
      (ConnectionString, IsolationLevel.ReadCommitted) 
                  { LoadOptions = NorthwindLoadOptions.LoadCustomerWithOrders  })
              {

• Connections are closed during Dispose. Pending Transactions are also committed during this time.

Ensuring Proper Transaction Levels

How can we be CERTAIN that our base class is setting the transaction levels properly?
We can do so by using SQL Profiler and making sure that the calls are wrapped inside the proper Transaction.

Let's take a look at some code:

using (var Ctx = new NorthwindDataContext
        (ConnectionString, IsolationLevel.ReadUncommitted) 
   { LoadOptions = NorthwindLoadOptions.LoadCustomerWithOrders  })
{
    Item = Ctx.Customers.First();

    Item.ContactName = "Rocks";
    Ctx.SubmitChanges();
}

Simple enough. It reads a record, changes the contactname, then submits the change to the context. In the using statement, we specify a Transaction level of ReadUncommitted. This means we WANT dirty reads.

In the connection string, I specify the name of the application by setting the App='DTX' property. This will allow me to filter out any extraneous data in the profiler and see what my base class is doing.

Success! We see the SET TRANSACTION LEVEL READ UNCOMMITTED line before any of our reads/write are performed.

What about read committed?

            using (var Ctx = new NorthwindDataContext(ConnectionString,
IsolationLevel.ReadCommitted) 
                { LoadOptions = NorthwindLoadOptions.LoadCustomerWithOrders })
            { 
                Item = Ctx.Customers.Where(p => p.ContactName == "Rocks").First();
                Item.ContactName = string.Format("LinqToSql - {0}", 
DateTime.Now.Second.ToString());
                Ctx.SubmitChanges();       
            }

Again, simple LINQ syntax.

Let's check the Profiler.

Read committed! Perfect.

Great success!

Points of Interest

I plan on keeping this updated through this site and on my blog.

Use the System.Transactions objects to handle Distributed Transactions. This class is designed for projects that do not require distributed transactions.

History

• Release 1.0. - Transactional support for LINQ using a custom DataContext
• Release 1.1. Less Eager = Better. Changed how/when connections/transactions are opened/closed