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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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

crokusek

Related Questions

Why is Entity Framework calling my stored procedure but returning an incorrect value?

Did you tried that? ...http://www.devtoolshed.com/using-stored-procedures-entity-framework-scalar-return-values...I think your procedure will look like this:...CREATE PROCEDURE [dbo].[GetAuditRecordPageCount] @Count INTEGER AS declare @retVal int set @retVal = ((SELECT COUNT(Id) FROM AuditRecords) / @Count) + 1 select @retVal ...And in the c# code:...
asp.net c# entity-framework sql-server
asked by wkudaka

Call stored procedure inside transaction using Entity Framework

I solved my problem....In the stored procedure there is a ...ROLLBACK... and ...COMMIT... keywords. But there is no ...BEGIN TRANSACTION... anywhere in the procedure. From the beginning, I thought it is strange....As you know ...COMMIT... decrements ...@@TRANCOUNT... by 1. Or to be more precise:...If @@TRANCOUNT is 1, COMMIT TRANSACTION makes all d...
c# entity-framework stored-procedures transactionscope
asked by algreat

Stored procedure working when calling exec sp and throws with EF6 on SQL Azure

Finally here is the fix:...I was suspecting a transaction issue, and after a few more investigation here is the problem: If you check here ...http://msdn.microsoft.com/en-us/data/dn456843...Starting with EF6 Database.ExecuteSqlCommand() by default will wrap the command in a transaction if one was not already present. There are overloads of this met...
azure-sql-database c# entity-framework entity-framework-6 stored-procedures
asked by JuChom

WCF OData Service stored procedure call generates "Operation could destabilize the runtime" error with $select option

As per what Layla Liu MSFT commented, $select is still not supported though I couldn't find any definitive documentation about it. ...From what I gather and observed, $select breaks the stored procedure call because it tries to alter the data shape already gotten from the database and attempts to return a dynamic entity instead. Something about the...
entity-framework-5 entity-framework-6 odata select wcf-data-services
asked by iamnobody

Calling an Oracle stored procedure with Entity Framework code-first and DevArt Drivers

We have reproduced the issue and are investigating it. We will notify you about the result. If you have any questions about the Devart products, please contact us via ...http://www.devart.com/company/contactform.html....
devart entity-framework entity-framework-6 oracle stored-procedures
asked by Devart

Call stored procedure using ExecuteSqlCommand (expects parameters which was not supplied)

This works:...var bookIdParameter = new SqlParameter(); bookIdParameter.ParameterName = "@BookId"; bookIdParameter.Direction = ParameterDirection.Output; bookIdParameter.SqlDbType = SqlDbType.Int; var authors = context.Database.ExecuteSqlCommand("usp_CreateBook @BookName, @ISBN, @BookId OUT", new SqlParameter("@BookName", "Book"), new SqlPa...
c# entity-framework sql-server stored-procedures
asked by Mill

What's the difference between Entity Framework (6) transactions with single and multiple SaveChanges() calls

Your question doesn't really seem to be about entity framework at all, and is more regarding sql transactions. A sql transaction is a single 'atomic' change. That is to say that either all the changes are committed, or none are committed....You don't really have an example which covers the scenario, but if you added another example like:...using (T...
c# entity-framework-6 transactions
asked by PaulG

how to call stored procedure in EF6 with output parameters

You can implement the following to retrieve the data for a stored procedure call ...using(var db = new iConext()) { var details = db.Database.SqlQuery<iType>("exec iProc @param", new SqlParameter("@param", iValue)); } ...iType... is int or string or long or can be a ComplexType...@param... is one or more required parameters...iContext... is fo...
asp.net-mvc entity-framework-6 output-parameter stored-procedures string
asked by Pradip

Running stored procedure in a SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Try using ...TransactionScope... instead as follows:...using (var txn = new TransactionScope( TransactionScopeOption.RequiresNew, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }) { return base.Database.ExecuteSqlCommand("EXEC mystoredprocedure"); } ...Or using ...Database.BeginTransact...
c# entity-framework-6 read-uncommitted sql-server stored-procedures
asked by wdosanjos

How to best avoid checking user data (payments, license status) on every action every single time?

You can use Cache for this kind of info. You retrieve it from database, and you invalidate that cache once you update any of their fields....And the logic to retrive that will see if the cache variable is null, you recalculate it....Here you can see several approaches, depending in what robust or extensible you want it to be: ...How to cache data i...
asp.net-mvc entity-framework-6
asked by Romias

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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