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?
There is an overload of the ExecuteSqlCommand
method that prevents this behavior:
db.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, sql, parameters);
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.