EF Working with transactions

Original: Https://msdn.microsoft.com/en-us/data/dn456843.aspx

Prior to EF6 Entity Framework insisted on opening the database connection itself (it threw an exception if it is passed a Connection that is already open). Since a transaction can only is started on an open connection, this meant that's the only to a user could wrap several oper Ations into one transaction is either to use a TransactionScope or with the objectcontext.connection property and Start calling Open () and BeginTransaction () directly on the returned entityconnection object. In addition, API calls which contacted the database would fail if you had started a transaction on the underlying Databas e connection on your own.

Note: The limitation of only accepting closed connections is removed in Entity Framework 6. For details, see Connection Management (EF6 onwards).

Starting with EF6 The framework now provides:

1. database.begintransaction () : An easier method for a user-to-start and complete transactions themselves within an Existing dbcontext–allowing several operations to being combined within the same transaction and hence either all committe D or all rolled back as one. It also allows the user to more easily specify the isolation level for the transaction.
2. database.usetransaction () : which allows the DbContext to use a transaction which was started outside of the Enti Ty Framework.

Combining several operations into one transaction within the same context

database.begintransaction () has a overrides–one which takes an explicit isolationlevel and one which takes no Arguments and uses the default isolationlevel from the underlying database provider. Both overrides return a dbcontexttransaction object which provides Commit () and Rollback () met Hods which perform commit and rollback on the underlying store transaction.

The dbcontexttransaction is meant to be disposed once it had been committed or rolled back. One easy-to-accomplish this is the using (...) {...} Syntax which would automatically call Dispose () when the using block completes:

Static voidStartowntransactionwithincontext () {using(varContext =NewBloggingcontext ()) {                 using(varDbcontexttransaction =context. Database.begintransaction ()) {Try{context. Database.executesqlcommand (@"UPDATE Blogs SET Rating = 5"+"WHERE Name like '%entity framework% '"                             ); varQuery = context. Posts.where (p = p.blog.rating >=5); foreach(varPostinchquery) {Post. Title+="[Cool Blog]"; } context.                          SaveChanges ();                     Dbcontexttransaction.commit (); }                     Catch(Exception) {dbcontexttransaction.rollback (); }                 }             }         } 

Note: Beginning a transaction requires the underlying store connection is open. So calling Database.begintransaction () would open the connection if it is not already opened. If Dbcontexttransaction opened the connection then it would close it when Dispose () is called.

Sometimes would like a transaction which are even broader in scope and which includes operations on the same database B UT outside of EF completely. To accomplish this must open the connection and start the transaction yourself and then tell EF a) to use the already- Opened database connection, and B) to use the existing transaction on that connection.

Must define and use a constructor on your context class which inherits from one of the DbContext Constructo RS which take I) an existing connection parameter and II) the Contextownsconnection Boolean.

Note: The contextownsconnection flag must is set to False when called in this scenario. This was important as it informs Entity Framework that it should not close the connection when it was done with it (e.g. see Line 4 below):

using (var conn = new SqlConnection("...")) 
{ 
    conn.Open(); 
    using (var context = new BloggingContext(conn, contextOwnsConnection: false)) 
    { 
    } 
}

Furthermore, must start the transaction yourself (including the IsolationLevel if you want to avoid the default Settin g) and let the Entity Framework know this there is a existing transaction already started on the connection (see line 33 below).

Then you is free to execute database operations either directly on the SqlConnection itself, or on the DbContext. All such operations is executed within one transaction. You take responsibility for committing or rolling back the transaction and for calling Dispose () on it, as well as for CLO Sing and disposing the database connection. e.g.:

Static voidusingexternaltransaction () {using(varconn =NewSqlConnection ("...") {Conn.                 Open (); using(varSQLTXN =Conn. BeginTransaction (System.Data.IsolationLevel.Snapshot)) {Try                    {                        varSqlCommand =NewSqlCommand (); Sqlcommand.connection=Conn; Sqlcommand.transaction=Sqltxn; Sqlcommand.commandtext=@"UPDATE Blogs SET Rating = 5"+"WHERE Name like '%entity framework% '";                         SqlCommand.ExecuteNonQuery (); using(varContext =NewBloggingcontext (Conn, contextownsconnection:false) {context.                              Database.usetransaction (SQLTXN); varQuery = context. Posts.where (p = p.blog.rating >=5); foreach(varPostinchquery) {Post. Title+="[Cool Blog]"; } context.                         SaveChanges ();                     } sqltxn.commit (); }                     Catch(Exception) {sqltxn.rollback (); }                 }             }         } 

EF Working with transactions