Transactionscope throwing exception this platform does not support distributed transactions while opening connection object

Question

TransactionScope is throwing a exception in .net core 2.2

In this example I created a scope of TransactioScop. Opening SQL transaction for one database which is working fine.
After the first transaction I´m calling commit which will commit the SQL transaction. I try to open call transaction for another database while creating a transaction and the system is throwing an exception

This platform does not support distributed transactions.

tried to remove SQL transaction

c#

using (TransactionScope scop =new TransactionScope(TransactionScopeOption.Required, TransactionScopeAsyncFlowOption.Enabled))
{       
    _db1UOW.Begin(); //creating sql transaction
    await _db1UOW.IDenialDetailsRepositorydb1.InsertDenialDetails(denialsDetails);
    await _db1UOW.IRuleDetailsRepositorydb1.InsertRulesDetails(rulesDetails);
    _db1UOW.Commit(); //commitng sql transaction

    _db2UOW.Begin(); //creating sql transaction (but while opening connection object its throwing exception as This platform does not support distributed transactions)
    await _db2UOW.IRuleDetailsRepository.GetRulesDetails();
    await _db2UOW.IDenialDetailsRepository.InsertDenialDetails(denialsDetails);
    var data = await _db2UOW.IRuleDetailsRepository.InsertRulesDetails(rulesDetails);
    _db2UOW.Commit(); //commitng sql transaction
    scop.Complete();
}

Message

"This platform does not support distributed transactions."  at System.Transactions.Distributed.DistributedTransactionManager.GetDistributedTransactionFromTransmitterPropagationToken(Byte[] propagationToken)
   at System.Transactions.TransactionInterop.GetDistributedTransactionFromTransmitterPropagationToken(Byte[] propagationToken)
   at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
   at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
   at System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
   at System.Transactions.Transaction.Promote()
   at System.Transactions.TransactionInterop.ConvertToDistributedTransaction(Transaction transaction)
   at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
   at System.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte[] whereAbouts)
   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()

Answer 1

I managed to resolve by suppressing if there is existing transaction and still setting TransactionScopeAsyncFlowOption.Enabled and it works perfectly

public class TransactionMiddleware : IMiddleware
{
    public async Task InvokeAsync(HttpContext context, RequestDelegate next)
    {
        // Check if there is an existing TransactionScope
        bool hasExistingTransaction = Transaction.Current != null;

        // If there is no existing transaction, create a new TransactionScope
        using (var scope = hasExistingTransaction
            ? new TransactionScope(TransactionScopeOption.Suppress, TransactionScopeAsyncFlowOption.Enabled)
            : new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
        {
            try
            {
                await next(context);
                // If there was no existing transaction, complete the new TransactionScope
                if (!hasExistingTransaction)
                {
                    scope.Complete();
                }
            }
            finally
            {
                // Dispose of the TransactionScope (automatically calls Complete if no exception occurred)
                scope.Dispose();
            }
        }
    }
}

Answer 2

.NET Core doesn't support Distributed Transactions because it would require a different transaction manager on each platform. It may appear in the future (here's the issue in-progress), but for now any Transaction that would require two different resource managers will throw this exception.

Instead you can coordinate separate transactions. Have two separate transactions complete their work, and then commit them both. There is a possibility that the first commit succeeds and the second one fails, but for SQL Server (with one exception), that would be a very rare occurance. Something like:

_db1UOW.Begin(); //creating sql transaction
await _db1UOW.IDenialDetailsRepositorydb1.InsertDenialDetails(denialsDetails);
await _db1UOW.IRuleDetailsRepositorydb1.InsertRulesDetails(rulesDetails);

_db2UOW.Begin(); //creating sql transaction 
await _db2UOW.IRuleDetailsRepository.GetRulesDetails();
await _db2UOW.IDenialDetailsRepository.InsertDenialDetails(denialsDetails);
var data = await _db2UOW.IRuleDetailsRepository.InsertRulesDetails(rulesDetails);
 
_db1UOW.Commit(); //commitng sql transaction
try
{
    _db2UOW.Commit(); //commitng sql transaction
}
catch (Exception ex)
{
     LogError("Second transaction failed to commit after first one committed.  Administrators may need to fix stuff");
     throw;
}

Or if the two databases are on the same server you can use cross-database queries with a single SqlConnection to enlist the changes in a single SQL Server transaction.

Answer 3

You are doing a distributed transaction across multiple databases. I used to encounter this problem, and have already written a distributed transaction framework which also support dotnet.

Transaction coordinator: https://github.com/dtm-labs/dtm

Dotnet SDK: https://github.com/dtm-labs/dtmcli-csharp

A blog about how to design a distributed transaction: https://www.c-sharpcorner.com/article/distributed-transaction-in-c-sharp-microservices-using-saga-pattern/