Sounds like most of questions on the internet, but here is the thing - when multiple connections are made during single ambient TransactionScope it will be promoted to DTC. So the question is, how to avoid this and share single connection among all the instance? There is a connection pooling mechanism (at least in Npgsql ) but I'm not sure it works as I expected it would be.

This is a repro: https://github.com/uhfath/TestTransactionDbContext

The project contains testing methods for Npgsql and Sql Server . They behave differently:

• On Sql Server I simply get The operation is not valid for the state of the transaction .
• On Npgsql the queries simply don't work.

What the project does is:

• starts an ambient TransactionScope :

private static TransactionScope CreateTransaction() =>
    new TransactionScope(
        TransactionScopeOption.Required,
        new TransactionOptions
        {
            IsolationLevel = IsolationLevel.ReadCommitted
        },
        TransactionScopeAsyncFlowOption.Enabled);

• creates a test client:

private static async Task<Client> Create(IServiceProvider serviceProvider)
{
    var client = new Client { Name = "client_1" };

    await using var scope = serviceProvider.CreateAsyncScope();
    var dbContext = scope.ServiceProvider.GetRequiredService<AsyncDbContext>();
    dbContext.Clients.Add(client);
    await dbContext.SaveChangesAsync();

    await Console.Out.WriteLineAsync($"Client: {client.Id}");
    return client;
}

• starts 100 tasks to read this created client (using returned Id):

private static Random _randomer = new();
private static async Task Read(IServiceProvider serviceProvider, DependentTransaction dependentTransaction, int clientId, int index)
{
    using (dependentTransaction)
    {
        //using (var transaction = CreateTransaction(dependentTransaction))
        {
            await using (var scope = serviceProvider.CreateAsyncScope())
            {
                var dbContext = scope.ServiceProvider.GetRequiredService<AsyncDbContext>();
                await Task.Delay(_randomer.Next(100, 250));
                var client = await dbContext.Clients.AsNoTracking().Where(cl => cl.Id == clientId).SingleOrDefaultAsync();
                await Console.Out.WriteLineAsync($"Read: {index} -> {client?.Id.ToString() ?? "!! NOT FOUND !!"}");
                //transaction.Complete();
            }
        }

        dependentTransaction.Complete();
    }
}

The main issue is that I need to read data in parallel and this is just a simple example of the main, bigger project where the goal is the same (it just uses plugins that are executed in parallel and each instantiates DbContext through DI).

As I mentioned, when using Sql Server I get an exception, but when using Npgsql the query sometimes returned correct result and sometimes !! NOT FOUND !! . The latter makes be believe that a transaction was somehow 'rolledback' by the time the query was run.

So how do we actually share a connection among all these DbContext instances so transaction wouldn't escalate to DTC?