11
Dec
Off

Transaction issue after upgrading from EF5 to EF6

I had an issue after upgrading a Project to entity Framework 6: The procedure behind a linked Server wouldn’t work with a distributed transaction.

Reason:

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 method that allow you to override this behavior if you wish. Also in EF6 execution of stored procedures included in the model through APIs such as ObjectContext.ExecuteFunction() does the same (except that the default behavior cannot at the moment be overridden).

 

Solution:

for the whole context:

context.Configuration.EnsureTransactionsForFunctionsAndCommands = False;

or by command Statement:

context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, “[statement]”);

 

Alternative:

Disable the remote transaction promotion of the linked server alltogether (not elegant)

EXEC master.dbo.sp_serveroption @server=N’LINKED SERVER NAME’, @optname=N’remote proc transaction promotion’, @optvalue=N’false’

Tags: