dotConnect for MySQL Documentation
Working with Transactions in Entity Framework
support@devart.com

In this article we consider a short description of the Entity Framework 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 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 a single ObjectContext or DbContext.

The possible solution is to use the database connection. The connection is accessed differently for ObjectContext and DbContext. An example for ObjectContext:


C#csharpCopy Code
	var connection = context.Connection;
Visual BasicCopy Code
	Dim connection = context.Connection

For DbContext you should open the connection of internal ObjectContext which is within DbContext.


C#csharpCopy Code
	var connection = ((IObjectContextAdapter)dbContext).ObjectContext.Connection;
Visual BasicCopy Code
	Dim connection = DirectCast(dbContext, IObjectContextAdapter).ObjectContext.Connection

The rest of the sample is the same for ObjectContext and DbContext:


C#csharpCopy Code
       //Opening connection
        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(); 
           }
        }
Visual BasicCopy Code
        'Opening connection
        connection.Open()
	Dim department As Dept = context.Depts.Where(Function(ByVal d As Dept) d.Deptno = 10).First()  
	department.LOC = "TEST"
        'Opening transaction
        Using transaction As System.Data.Common.DbTransaction = 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()
           }
        }

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.


C#csharpCopy Code
       
        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(); 
        }
Visual BasicCopy Code
        Dim department As Dept = Context.Depts.Where(Function(ByVal d As Dept) d.DEPTNO = 25).First()
	department.Dname = "xxx"
	Using TScope As New TransactionScope(TransactionScopeOption.RequiresNew)
	   'a new transaction will be created for this call  			
	    Context.SaveChanges()
            TScope.Complete()
	End Using


You can find more information about Transactions in MSDN.