Why does a dapper query fail and a ado call not?

Question

In netcore 5 C# I check at the start of the application if the database is on the latest version if not, depending on the version installed at the specific customer, it updates automagically (and does specific things per customer).

I was refactoring and tried to see if I could replace the current sql execution call with a Dapper call but failed:

a. I have for example this piece of sql in a string:

SET NUMERIC_ROUNDABORT OFF
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON SET DATEFORMAT YMD SET XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

DECLARE @items AS CURSOR
DECLARE @item AS nvarchar(250)

SET @items = CURSOR FOR
    SELECT
        N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.parent_object_id)) +  N'.' + QUOTENAME(OBJECT_NAME(t.parent_object_id)) + '  DROP CONSTRAINT ' +  QUOTENAME(t.name) +  N';'

    FROM
        [sys].[check_constraints] AS t
    WHERE
        t.is_ms_shipped = 0

    OPEN @items FETCH NEXT FROM @items INTO @item WHILE @@FETCH_STATUS = 0

BEGIN
    EXEC (@item)

    FETCH NEXT FROM @items INTO @item

END

CLOSE @items

DEALLOCATE @items 

COMMIT TRANSACTION

b. I run this (inside a transaction) with Dapper:

public IDbConnection DbConnection
    {
        get
        {
            return new SqlConnection(_connectionstring);
        }
    }

using IDbConnection db = DbConnection;
await db.ExecuteAsync(statement)

And it fails with "System.PlatformNotSupportedException: This platform does not support distributed transactions."

c. When I replace the dapper call with:

string connectionString = _context.Database.GetDbConnection().ConnectionString;
SqlConnection _connection = new SqlConnection(connectionString);
await _connection.ExecuteCommandAsync(statement).ConfigureAwait(false);

It runs fine (and all the hundreds of other sql statements after that also)

The transaction surrounding it is :

using (TransactionScope scope
                        = new TransactionScope(TransactionScopeOption.Required, System.TimeSpan.FromMinutes(10),
                                       TransactionScopeAsyncFlowOption.Enabled))
                    {
  // a loop with hundreds of sql files and corresponding calls
  // to execute
}

( i am aware of https://github.com/dotnet/runtime/issues/19318 )

full stacktrace on request

System.PlatformNotSupportedException: 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.DbConnectionInternal.ActivateConnection(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.WaitForPendingOpen()
--- End of stack trace from previous location ---
   at Dapper.SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param) in /_/Dapper/SqlMapper.Async.cs:line 645

Answer 1

Additional speculation (comes "after" the below historically, but "before" in terms.of things to try) based on my thoughts in a comment: without changing the property at all, you could try adding

db.Open();

or

await db.OpenAsync().ConfigureAwait(false);

into your existing code, on the hypothesis that the difference is the number of times the connection gets opened.

---

Speculation, but I wonder if the exception is caused purely by the property getter returning a new connection each time. This means you're likely to be involving multiple connections, which (when combined with TransactionScope) could cause bad things. Maybe simply:

private IDbConnection _db;
public IDbConnection DbConnection
   => _db ?? CreateOpenConnection();
private IDbConnection CreateOpenConnection()
{
    if (_db is null)
    {
        _db = new SqlConnection(_connectionstring);
        _db.Open();
    }
    return _db;
}