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.

Answer 1

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