Working with transactions in Entity Framework and LINQ to SQL

Entity Framework

In this article we consider a short description of the Entity Framework and LINQ to SQL transactional models. There are also some simple code listings illustrating different aspects of the transactional model.

A transaction is a series of operations performed as a single unit of work. Entity Framework transactions are a part of its internal architecture. The SaveChanges method operates within a transaction and saves results of the work. It is designed to ensure data integrity. The common reason of integrity problems is a Concurrency violation, which can be raised during the saving process. Concurrency violation occurs an OptimisticConcurrencyException in this case. To resolve this conflict you have to call the Refresh method with the StoreWins or ClientWins value, and after that call SaveChanges again. But be aware, that the Refresh with the ClientWins option can be a source of problem too. It rewrites all changes made to the data after context query execution.

Imagine we want the SaveChanges to be only a part of more complicated update process. For example, we want to execute several SaveChanges as one operation in the single ObjectContext.
The possible solution is to use the database connection. Here is a sample code:

       //Opening connection
        context.Connection.Open(); 
        DEPT department = context.DEPT.Where(d => d.DEPTNO == 10).First();
        department.LOC = "TEST";
        //Opening transaction
        using (System.Data.Common.DbTransaction transaction 
        = context.Connection.BeginTransaction()) 
        {            
           //This call participates in the transaction
           context.SaveChanges(); 
           department.DNAME = "TEST";
           //This call also participates in the transaction
           context.SaveChanges(); 
           //Replace the flag condition with the one you need 
           //or remove it and leave only commit part
           if(flag) 
           {
              //transaction completed successfully, both calls succeeded
              transaction.Commit(); 
           }
           else 
           {
              //something is wrong, both calls are rolled back
              transaction.Rollback(); 
           }
        }

As we can easily ensure, the transaction is opened on database connection, and SaveChanges are added to this transaction.

You can use TransactionScope as another approach (our solutions have implementation of the TransactionScope for Oracle, MySQL and PostgreSQL database servers). It can be used both as a local transaction replacement and as a wrapping transaction for the local SaveChanges.

The TransactionScope ensures that changes to objects in the object context are coordinated with a message queue. Object Services use this transaction when it saves changes to the database. When an UpdateException occurs, the operation is retried up to two times. The changes in the object context are accepted when the operation succeeds. See Managing Transactions in Object Services for more information.

A TransactionScope object has three options:

1. Join the ambient transaction, or create a new one if one does not exist (Required).
2. Be a new root scope, that is, start a new transaction and have that transaction be the new ambient transaction inside its own scope (RequiresNew).
3. Not take part in a transaction at all. There is no ambient transaction as a result (Suppress).

The following code displays the RequiresNew usage.

        
        DEPT department = context.DEPT.Where(d => d.DEPTNO == 25).First();
        department.DNAME = "xxx";
        using (TransactionScope tscope 
        = new TransactionScope(TransactionScopeOption.RequiresNew)) 
        {
           //a new transaction will be created for this call
           context.SaveChanges(); 
           tscope.Complete(); 
        }

LINQ to SQL

LINQ to SQL also has transactions as a part of its architecture, just as Entity Framework.

Implicit Local Transaction. LINQ to SQL starts a local DbTransaction and uses it to execute generated SQL commands, if there is no open transaction into the LINQ to SQL connection instance. When all SQL commands have been successfully completed, LINQ to SQL commits the transaction and returns.

Explicit Local and Distributed Transaction. LINQ to SQL performs a check whether this call was executed in the scope of the Transaction started by user (local or distributed), when SubmitChanges is called. It is executed in the context of the same transaction, if the DataContext.Transaction property is set to a DbTransaction instance. Please note, that you should either commit or rollback the transaction (LINQ to SQL does not perform it for you).

        //Opening connection
        context.Connection.Open();  
        DEPT department = context.DEPT.Where(d => d.DEPTNO == 10).First();
        department.LOC = "TEST";
        //Opening transaction. 
        //Transaction property        
        context.Transaction = context.Connection.BeginTransaction();
        //This call participates in the transaction
        context.SubmitChanges(); 
        department.DNAME = "TEST";
        //This call also participates in the transaction 
        context.SubmitChanges(); 
        //Replace the flag condition with the one you need 
        //or remove it and leave only commit part
        if(flag)
        {
          //transaction completed successfully, both calls succeeded 
          context.Transaction.Commit(); 
        }
        else 
        {
          //something is wrong, both calls are rolled back 
          context.Transaction.Rollback();  
        }

Here is an example for DataContext.Transaction usage. Please note the difference – in LINQ we have a context-level Transaction property, unlike the Entity Framework case.

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. This is the default Microsoft LINQ to SQL behaviour.

Note: Devart LINQ to SQL implements this situation a bit different – we use a new connection for every querying operation for performance reasons. There is a way to obtain the planned behaviour, however. To achieve this, you can use Devart.Data.DataContext.MaxUsedConnections property:

Devart.Data.DataContext.MaxUsedConnections = 1

Then all queries will be executed using the single instance of the connection. In case of submitting the changes the DataContext.Connection persists.

Distributed Transactions and TransactionScope. All of the described above TransactionScope for Entity Framework features can be used for LINQ to SQL too. Here is a code sample about usage Suppress option of TransactionScope:

        DEPT department = context.DEPT.Where(d => d.DEPTNO == 25).First();
        department.DNAME = "xxx";
        //No transaction will be started for this call
        using (TransactionScope tscope = new TransactionScope(TransactionScopeOption.Suppress)) 
        {
            context.SubmitChanges();
            tscope.Complete();
         }

You can find more information about Transactions in MSDN.