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:
- 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. - 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 Transaction
s. - 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:
- 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. - If you need more control over your
DataContext
, you can extend the partial class created by the *.dbml file.
public partial class NorthwindDataContext : BaseDataContext
{
public NorthwindDataContext(string connectionString, IsolationLevel level) :
base(connectionString, level) { }
}
- If you prefer NOT to use the default
Context
s 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.
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