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?
Accepted Answer
There is an overload of the ExecuteSqlCommand method that prevents this behavior:
db.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, sql, parameters);
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.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
