EF6 wraps every single stored procedure call in its own transaction. How to prevent this?

entity-framework sql-server transactions

Question

Profiled with SQL Server Profiler: EF 6 wraps every single stored procedure call with BEGIN TRAN and COMMIT TRAN.

Is not it a breaking change?

Maybe it is not only a breaking change, but makes any transactional logic impossible in SPs as we never can rollback our transaction in the stored procedure using ROLLBACK TRAN (note: there are no nested transactions in SQL Server), so one rollback rollbacks to @@TRANCOUNT zero. As we were in a transaction because EF 6 we got "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0." standard SQL Server error.

Please do not ask me why I want to call stored procedures. I have hundreds, and all of them are using TRY ... COMMIT ... CATCH ROLLBACK logic.

Any ideas how can I prevent EF 6 to do this?

1
31
11/15/2013 6:00:56 AM

Accepted Answer

There is an overload of the ExecuteSqlCommand method that prevents this behavior:

db.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, sql, parameters);
35
1/27/2014 3:10:56 PM

Popular Answer

In EF 6.1.2, a flag controls the behavior. Setting EnsureTransactionsForFunctionsAndCommands to false will affect SPs that have been imported into an entity (these call ExecuteFunction() internally).

 using (SomeEf6Context ctx = NewContext())
 {
     ctx.Configuration.EnsureTransactionsForFunctionsAndCommands = false;
     // Call an imported SP
 }      

The setting will not affect any SaveChanges() calls.

MSDN Link



Related Questions





Related

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