dotConnect for MySQL Documentation
In This Topic
    Working with Transactions in Entity Framework
    In This Topic

    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:


    	var connection = context.Connection;
    
    
    	Dim connection = context.Connection
    
    

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


    	var connection = ((IObjectContextAdapter)dbContext).ObjectContext.Connection;
    
    
    	Dim connection = DirectCast(dbContext, IObjectContextAdapter).ObjectContext.Connection
    
    

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


           //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(); 
               }
            }
    
    
            '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.


           
            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(); 
            }
    
    
            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.