DbContext with linked servers

Weird things happen when you try to use DbContext to update rows on a synonym which points to a linked server.

System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Unable to start a nested transaction for OLE DB provider "SQLNCLI10" for linked server "WebSQL". A nested transaction was required because the XACT_ABORT option was set to OFF.

If the changes can be rolled into a stored procedure, this is one solution; SET XACT_ABORT ON can be called at the beginning of the procedure. But this may not be simple, and kind of defeats the purpose of using Entity Framework.

Another solution is to set the XACT_ABORT option globally, for the server instance. This is a bit extreme. I actually found this little error when we turned that option OFF (the default) – it had been on, but that was breaking some Microsoft accounting software that lived on the server in question. So change this at the server level at your own risk…

I wrote a little class to set XACT_ABORT ON for my connection. It’s used like this:

Using New TransactAbortContext(MyDbContext)
    MyDbContext.SaveChanges()
End Using

And here’s the TransactAbortContext class:

Imports System.Data.Entity.Infrastructure
Imports System.Data.Entity

''' <summary> 
''' Put a "SET XACT_ABORT ON" command on the connection for a dbcontext 
''' </summary> 
''' <remarks> 
''' This is necessary when calling updates on a synonym which is actually
''' a reference to a linked table on another SQL Server instance. 
''' </remarks> 
Public Class TransactAbortContext
    Implements IDisposable

    Private _dbContext As DbContext
    Private _disposed As Boolean ' To detect redundant calls 

    Public Sub New(ByVal dbContext As DbContext)
        Dim objectContext = DirectCast(dbContext, IObjectContextAdapter).ObjectContext
        objectContext.Connection.Open()
        objectContext.ExecuteStoreCommand("SET XACT_ABORT ON")
        _dbContext = dbContext
    End Sub

    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not _disposed Then
            If disposing Then
                Dim objectContext = DirectCast(_dbContext, IObjectContextAdapter).ObjectContext
                objectContext.Connection.Close()
            End If
        End If
        _disposed = True
    End Sub

    Public Sub Dispose() Implements IDisposable.Dispose
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
End Class

A connection manually opened on the underlying ObjectContext is kept open and used for SaveChanges, until manually closed.

The TransactAbortContext class keeps track of this for the passed-in DbContext.