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

You _should not_ return `new` objects from property getters, especially not _expensive_ objects like `SqlConnection` - you'll leak native resources that way. Property getters should be free from side-effects. — Dai, Sep 05 '21 at 00:34
"And it fails with "System.PlatformNotSupportedException: This platform does not support distributed transactions." - please post the stack-trace of that exception. — Dai, Sep 05 '21 at 00:35
.NET Core does not support Distributed Transactions (see https://stackoverflow.com/questions/56328832/transactionscope-throwing-exception-this-platform-does-not-support-distributed-t ) - I think your ADO code works because the transaction completes within the SQL code - though I'll admit I'm unsure (distributed transactions are a can 'o' worms, imo) — Dai, Sep 05 '21 at 00:36
The SQL content is out of my scope, Im not the owner of these (large set of legacy etc) | the new sql connection is only run once during a db update but im curious how to else retrieve it since im not hardcoded reading appsettings.json values (or via injected Configuration) but only have EF dbcontext | there is not much else stacktrace | the key thing is that probably dapper likes something else — edelwater, Sep 05 '21 at 00:42
My remark about your `DbConnection` property is just me _strongly urging_ you to change it to a method, not a property. e.g. `public SqlConnection CreateConnection() => new SqlConnection( _connectionString );` - that's all. — Dai, Sep 05 '21 at 00:44
i simplified the code. in reality this is a IHostedService where in the constructor the _connection is set via provider.GetRequiredService — edelwater, Sep 05 '21 at 00:47
The ExecuteCommandAsync that is apparently working: where is that defined? That doesn't sound like vanilla ADO.NET, so: where did that come from? Also note: Dapper *also* just uses ADO.NET, so if Dapper is failing in this way, it is coming *from* ADO.NET; I'm curious what this other method is doing, hence the question — Marc Gravell, Sep 05 '21 at 08:12
Additional note: I wonder if your ExecuteCommandAsync opens *but does not close* the connection. Dapper tries to be tidy: if you give it a closed connection, it closes it when it has finished; this means Dapper might be opening the connection multiple times, vs once with the other method. In a DTC context, that is very different. It could be that simply adding an explicit Open / OpenAsync fixes things. — Marc Gravell, Sep 05 '21 at 08:30
Ah! you are right, it was coming from an extension .dll that was in the solution: "public static async Task ExecuteCommandAsync(this DbConnection connection, string query) { try { using DbCommand cmd = connection.CreateCommand(query); cmd.CommandTimeout = 0; await cmd.NonQueryAsync().ConfigureAwait(false); return true; } catch { throw; } }" also extensions " DbCommand command = connection.CreateCommand();" — edelwater, Sep 05 '21 at 12:13
And that exection is the extension method public static async Task NonQueryAsync(this DbCommand command) { bool wasOpen = command.Connection.State == ConnectionState.Open; if (!wasOpen) command.Connection.Open(); try { await command.ExecuteNonQueryAsync().ConfigureAwait(false); } catch { throw; } finally { if (!wasOpen) command.Connection.Close(); } return 1; } — edelwater, Sep 05 '21 at 12:14

Marc Gravell · Accepted Answer · 2021-09-05T08:33:30.630

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;
}

Yes, I think it is something like this, especially now that i found out that it is an extension method that has open logic encapsulated. will try. — edelwater, Sep 05 '21 at 12:19
I had to fiddle a little more to make sure that all services called in the transaction used the same classes. then it worked. — edelwater, Sep 20 '21 at 03:20

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

1 Answers1