Using asynchronous save changes on Entity Framework with multiple contexts

Question

I am using EF 6 with a UoW pattern. I have multiple contexts defined in my UoW since I'm using data from multiple databases. Everything seems to be working correctly except the CommitAsync function I have defined. Here's the code I have:

    public async Task CommitAsync()
    {
        try
        {
            using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
            {
                if (_context1 != null)
                    await _context1.SaveChangesAsync();
                if (_context2 != null)
                    await _context2.SaveChangesAsync();

                scope.Complete();
            }
        }
        catch (DbEntityValidationException ex)
        {
            //..
        }
    }

When I run this code saving changes in both contexts I get:

The transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D024)

The await _context2.SaveChangesAsync(); is where the error happens. If I remove the TransactionScope from this function, the code seems to work without error. I am hesitant to remove the scope for multiple contexts.

Just in case it'll help, here's the code I use to call this function:

        state.Name = "Texas";
        _uow.StateRepository.Update(state);

        user.FirstName = "John";
        _uow.UserRepository.Update(user);

        await _uow.CommitAsync();

Thanks!

Answer 1

Using two connections in the same scope requires MSDTC.

You can enable MSDTC to solve this problem. But it does not work with many HA solutions and is slow.

The only way to avoid using MSDTC is to use the same connection for everything that must be transacted. Since you are using multiple databases that is harder than usual. You need to use SqlConnection.ChangeDatabase or issue the respective SQL to switch between databases on the same connection. The connection must be kept open.

Alternatively, you can use three part names for your object references (e.g. DB1.dbo.MyTable).

There is no other way. Either MSDTC or sharing of the same connection.