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

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?

Answer 1

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

db.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, sql, parameters); 

Answer 2

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