For various reasons I need to be able to do several custom SQL
UPDATE commands when I call
SaveChanges on a
DbContext. I want this to happen atomically so either both the regular
SaveChanges and the SQL succeeds, or neither does.
I don't know how to accomplish this though. What I've tried so far is this and various variations:
The error here is (on the
ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.
Okay, fair enough. The
ExecuteSqlCommand doesn't have an overload that accepts a transaction though. So, I installed the Dapper package and replaced the offending line with this Dapper call and I pass in the transaction:
this.Database.Connection .Execute("insert into Tests (Foo, Bar) values ('test', 2)", transaction: tran);
but now the error happens on
base.SaveChanges() and it gives me the following:
SqlConnection does not support parallel transactions.
SaveChanges always creates a new transaction even though I created one on the connection of the datacontext already?
Is there any way in which I can accomplish what I want?
I'm using Entity Framework 5.0 Code First.
TransactionScope around all operations. This class is the common abstraction for .NET transactions. EF and
SqlConnection's will enlist automatically. Be sure to look for best-practices though as there are some pitfalls like accidentally triggering distributed transactions.