Unable to begin a distributed transaction using Entiy Framework

Question

I'm having the following error executing this piece of code:

private bool _updateList(SysInfo _sysInfo, List<pList> _pList)
{
    try
    {

        foreach (var p in _pList)
        {
            _context.spUpdatePListApprovalFlow(p.countryID, _sysInfo.User.JobRoleID, p.src, p.id, p.status, _sysInfo.User.Username);
        }

        return true;
    }
    catch (Exception ex) //debug only
    {
        throw; //throws error to the main try catch
    }
}

ERROR

The operation could not be performed because OLE DB provider "MSDASQL" for linked server "AS400_LINKEDSRV" was unable to begin a distributed transaction.

However, everything works fine when I run the Stored Procedure in SQL Management Studio:

exec [dbo].[spUpdatePListApprovalFlow]
        @CountryID  = 123456,
        @UserTypeID = 23456,
        @Src = 1,
        @Id = '123456789',
        @Status = 30,
        @Username  = 'username'

I'm tired of digging for an answer nothing works... Few things I've tried:

• SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
• insert multiple transactions in the stored procedure

This sp has 2 sub stored procedures on it. One that writes into a table of the application's database, and another that updates a table in as400.

Answer 1

In EF6 stored procedures are called in an explicit transaction, by default. You can turn this off for a particular DbContext instance by changing its configuration after creating it, or for all instances of a DbContext type by changing it in the constructor. EG

    using (var db = new Db())
    {
        db.Configuration.EnsureTransactionsForFunctionsAndCommands = false;
        //. . .
    }

See: https://msdn.microsoft.com/en-us/library/system.data.entity.infrastructure.dbcontextconfiguration.ensuretransactionsforfunctionsandcommands

Answer 2

Ok, after half a day trying to solve this I've finally solved it.

Resolution

Downgraded from Entity Framework 6 to Entity Framework 5 and the distribuited transactions error has gone.

Just pay attention, if you're going to do this, you have to change some usings in your code. ( in auto generated code in the Data Model as well)

EF 6 uses

using System.Data.Entity.Core.Objects

EF 5 uses

using System.Data.Objects;

Answer 3

If you don't need distributed transactions you can try to disable them in the settings of the linked server:

EXEC master.dbo.sp_serveroption 
    @server=N'AS400_LINKEDSRV', 
    @optname=N'remote proc transaction promotion', 
    @optvalue=N'false'

Answer 4

Refer to this Microsoft page on Linked Servers.

Your System Administrator and/or DBA will probably need to make changes to address the missing linked server definition to your AS/400 server.

Another possible issue is that the AS/400 server (from IBM) lacks software support for the OLE DB data sources. This too would be something that the System Administration staff may need to address.