Linq to Sql is a cruel mistress.
The groans about Linq to Sql and N-tier development are echoing all over the internet. Rather than recant what has already been stated (by some very knowledgeable people), I will take on another portion of the Linq to Sql framework, the DataContext.
Transactions & the DataContext often cause headaches, but they can be resolved with a simple wrapper class.
Before we begin, I will make a few points regarding Transactions & the DataContext:
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.
Now we need a class that handles all of opening/closing of transactions and facilitates setting transaction levels in your DataContext.
On To Part 2!
