Transactional operation with SaveChanges and ExecuteStoreCommand

Question

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

I need to create a transactional operation on multiple objects. I work with EF CodeFirst, but with an outdated database that I cannot change. To create a more consistent model than the database, I project the database information into more advanced objects that I created myself.

Since 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 various problems with db, I cannot do updates in only one operation (UnitOfWork). I need to get different identifiers that appear only 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 between all operations (And I do this by passing context to objects). However, when I perform one of the operations (using ExecuteStoreCommand), or I try to perform some updating after the first SaveChanges, I always get an MSDTC error (distributed transaction support is disabled) or even more rarely, like uploaded domains .

I don't know if anyone can help me at least know that this is the best direction for this scenario.

Many thanks,

Answer 1

Take a look at this answer:
Entity Framework - Using transactions or SaveChanges (false) and AcceptAllChanges ()?
The answer does exactly what the transaction requires in several data contexts.

This post on Transactions and Connections in Entity Framework 4.0 also helped me a lot.

For people who might need a simpler solution, this is 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(); }