A command is already in progress exception using transient service

[kobruleht]

Using Npgsql EF data provider as scoped service throws exception "A command is already in progress"

To reproduce:

1. Configure ASP.NET 5 MVC Core application to use NpgSql EF provider as scoped service in StartUp.cs :

     public void ConfigureServices(IServiceCollection services)
         {
         services.AddHttpContextAccessor();
         services.AddScoped<MyDbContext>();
         ...

3. Use following method to get dynamic data as described in

#1862 (comment)

and in

https://stackoverflow.com/questions/55267883/efcore-fromsql-async

    partial class MyDbContext
    {
        async public Task<IEnumerable<T>> ExecQuery<T>(string sql, params object[] parameters) where T : class
        {
            using var db2 = new ContextForQueryType<T>(Database.GetDbConnection());
            var res = await db2.Set<T>().FromSqlRaw(sql, parameters).ToListAsync();
            return res;
        }
    
        class ContextForQueryType<T> : DbContext where T : class
        {
            readonly DbConnection connection;
            public ContextForQueryType(DbConnection connection)
            {
                this.connection = connection;
            }
    
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder.Entity<T>().HasNoKey();
                base.OnModelCreating(modelBuilder);
            }
    
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseNpgsql(connection);
                base.OnConfiguring(optionsBuilder);
            }
        }
    }

Observed. Exception at line

var res = await db2.Set<T>().FromSqlRaw(sql, parameters).ToListAsync();

Npgsql.NpgsqlOperationInProgressException (0x80004005): A command is
already in progress: select ...

at
Npgsql.NpgsqlConnector.g__DoStartUserAction|233_0(<>c__DisplayClass233_0&
) at Npgsql.NpgsqlConnector.StartUserAction(ConnectorState
newState, NpgsqlCommand command, CancellationToken cancellationToken,
Boolean attemptPgCancellation) at
Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean
async, CancellationToken cancellationToken) at
Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean
async, CancellationToken cancellationToken) at
Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior
behavior, CancellationToken cancellationToken) at
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject
parameterObject, CancellationToken cancellationToken)

...

How to fix this so that scoped service can used ? Or is it reasonable to use Npgsql only as transient service in MVC Core application ?

New DbContext which re-uses existing DbContext connection is created. Maybe this causes the issue since MARS is not supported by Npgsql.

Should keyless result types pre-added to existing DbContext by using

public DbSet<KeyLess1> KeyLesses1 { get; set; } 
public DbSet<KeyLess2> KeyLesses2 { get; set; }
... 
public DbSet<KeyLessN> KeyLessesN { get; set; }

and new DbContext creation removed.