Transactional operation with SaveChanges and ExecuteStoreCommand

Question

I have a problem that I would like to share. The context is a bit messy, so I will try to do my best in the explanation.

I need to create a transactional operation over a number of entities. I'm working with EF CodeFirst but with a legacy database that I can't change. In order to create a more consistent model than the database provides I'm projecting the database information into a more refined entities I created on my own.

As I need to use different contexts, my initial idea was to use TransactionScope which gave me good results in the past. Why do I need different contexts? Due to diverse problems with db, I can't make the updates only in one operation (UnitOfWork). I need to retrieve different IDs which only appears after SaveChanges().

using (var scope = new TransactionScope())
{
    Operation1();
    Operation2();
    Operation3(uses ExecuteStoreCommand)
    SaveChanges();

    Operation4();
    SaveChanges();
}

I know that, in order to use TransactionScope, I need to share the same connection among all the operations (And I'm doing it, passing the context to the objects). However, when I execute one of the operations (which uses ExecuteStoreCommand) or I try to do some update after the first SaveChanges I always receive the MSDTC error (the support for distributed transactions is disabled) or even more rare, as unloaded domains.

I don't know if someone can help me, at least to know which is the best direction for this scenario.

Looks like the entityconnection opens new connections below the hood. Maybe you can hook on [StateChange](http://msdn.microsoft.com/en-us/library/system.data.common.dbconnection.statechange.aspx) to get more detail. — Gert Arnold, Feb 23 '12 at 13:29
If you are connecting to different databases (or you are using Sql Server 2005) your transaction is being promoted to a distributed transaction. Note that if you are using Sql Server 2005 the transaction is automatically promoted to a distributed transaction since EF will open connection more than once. If you want to share the same connection among multiple contexts you should open the connection inside a transaction (this should automatically enlist the connection in the transaction) and pass the connection to the context ctor. EF will not close connection it did not open. — Pawel, Feb 26 '12 at 06:21
Note that opening connection on your own is also a way to prevent from promoting the transaction to a distributed transaction on Sql Server 2005 since EF will not close and reopen the connection. — Pawel, Feb 26 '12 at 06:22

score 7 · Accepted Answer · edited May 23 '17 at 10:29

Have a look at this answer:
Entity Framework - Using Transactions or SaveChanges(false) and AcceptAllChanges()?
The answer does exactly what you require having a transaction, over multiple data contexts.

This post on Transactions and Connections in Entity Framework 4.0 I found really helpful too.

For people who may need a simpler solution, here's what I use when I need to mix ExecuteStoreCommand and SaveChanges in a transaction.

using (var dataContext = new ContextEntities())
{
   dataContext.Connection.Open();
   var trx = dataContext.Connection.BeginTransaction();

   var sql = "DELETE TestTable WHERE SomeCondition";
   dataContext.ExecuteStoreCommand(sql);

   var list = CreateMyListOfObjects(); // this could throw an exception
   foreach (var obj in list)
      dataContext.TestTable.AddObject(obj);
   dataContext.SaveChanges(); // this could throw an exception

   trx.Commit();
}

The connection is automagically closed when the context falls out of scope, i.e. at the end of the using statement. — Paul Zahra, Oct 04 '12 at 13:49

Transactional operation with SaveChanges and ExecuteStoreCommand

1 Answers1