We are using SqlKata/Dapper(2.1.35) to implement a Web-API for a Postgres database with
around 20 tables. The API contains methods to query and manipulate data. It does not use
stored procedures. We test the repository methods of the API with around
200 unit tests, which run transactions against a test database rolling them back instead of committing.

Recently one of our tests started failing with the following error message:
Npgsql.PostgresException : 23503: insert or update on table "___" violates foreign key constraint "___"

We then annotated the offending test case with

[TestFixture, RequiresThread(ApartmentState.STA)]

and the problem vanished. All our repository methods rely on functions like

    protected async Task<IEnumerable<TEntity>> QueryAsync(Query query, IDbTransaction transaction, CancellationToken token)
    {
        var command = CompileQuery(query, transaction, token);
        return await transaction.Connection!.QueryAsync<TEntity>(command).ConfigureAwait(false);
    }

    private async Task<int> ExecuteAsync(Query query, IDbTransaction transaction, CancellationToken token)
    {
        var command = CompileQuery(query, transaction, token);
        return await transaction.Connection!.ExecuteAsync(command).ConfigureAwait(false);        
    }

We first checked that the queries use different connections of the connection pool and experimented
with the command flag NoCacheto no avail.
We finally decided to serialize the access using a semaphore.
This together with a call of SqlMapper.PurgeQueryCache(); made the failing test working again in MT.

The current implementation looks as follows

    protected async Task<IEnumerable<TEntity>> QueryAsync(Query query, IDbTransaction transaction, CancellationToken token)
    {
        await _semaphore.WaitAsync().ConfigureAwait(false);
        try
        {
            var command = CompileQuery(query, transaction, token);
            return await transaction.Connection!.QueryAsync<TEntity>(command).ConfigureAwait(false);
        }
        finally
        {
            SqlMapper.PurgeQueryCache();
            _semaphore.Release();
        }
    }

We suspect that we somehow manage to use different statements which map
to the same cache Identity so that concurrently running queries may use
a wrong cache entry. Leaving us with 2 unanswered questions:

• Did anyone run into the same issue?
• Is there a way to completely avoid caching?