SaveChanges has custom atomic UPDATE instructions ()

c# dapper entity-framework savechanges transactions


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:

EF transactions — Gist

The error here is (on the ExecuteSqlCommand call):

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:

  .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.

So apparently 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.

11/3/2012 12:02:26 AM

Accepted Answer

Wrap a 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.

11/2/2012 11:37:23 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow