Aug 28, 2012 7:00 PM
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)
And here’s the TransactAbortContext class:
''' Put a "SET XACT_ABORT ON" command on the connection for a dbcontext
''' This is necessary when calling updates on a synonym which is actually
''' a reference to a linked table on another SQL Server instance.
Public Class TransactAbortContext
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.ExecuteStoreCommand("SET XACT_ABORT ON")
_dbContext = dbContext
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not _disposed Then
If disposing Then
Dim objectContext = DirectCast(_dbContext, IObjectContextAdapter).ObjectContext
_disposed = True
Public Sub Dispose() Implements IDisposable.Dispose
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.