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

Question

User-1262787652 posted

Using Npgsql EF data provider as scoped service throws exception.

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

https://github.com/dotnet/efcore/issues/1862#issuecomment-451671168

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.<StartUserAction>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 ?

Answer 1

User1120430333 posted

https://www.npgsql.org/efcore/

It doesn't seem to be any different  than using MS SQL Server and EF. So how did you go off course?

Answer 2

User-1262787652 posted

There is difference since Multiple Active Result Sets are not supported by Npgsql.

Code creates DbContext which re-uses existing DbContext connection.

Maybe this causes the issue.

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

etc. so that new DbContext is not created. 

Answer 3

User1120430333 posted

There is difference since Multiple Active Result Sets are not supported by Npgsql.

Of course, it has nothing to do with the DBcontext and how it used between EF and Npgsql.

Code creates DbContext which re-uses existing DbContext connection.

Maybe this causes the issue.

I would most likely say so. Once you DI the Dbcontext, you should not be trying to new-up a new Dbcontext. The IoC is probably going to complain about it.

Answer 4

User-1262787652 posted

I added keyless entity to existing DbContext

    public partial class MyDbContext : MyScaffoldContext
    {
        public DbSet<TextValuePair> TextValuePairs { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<TextValuePair>().HasNoKey();
            base.OnModelCreating(modelBuilder);
        }

    public class TextValuePair
    {
        public string Text { get; set; }
        public string Value { get; set; }
    }

and called FromSqlRaw directly:

            return await ctx.TextValuePairs.FromSqlRaw(string.Format(@"select '' as Text, '' as Value "
                ).ToListAsync();
        }

But A command is already in progress exception still occurs in this line. How to fix this ?

Answer 5

User1120430333 posted

I would say stop DI DBcontext and new-up Dbcontext with the 'new' keyword to instance DBcontext.

https://github.com/npgsql/efcore.pg/issues/1205

Answer 6

User-1262787652 posted

It looks like await keyword was missing in calling method.

Signature was   

async Task<string> Method()

Without await this method returns type name immediately to caller and seeims to continue in background.

It looks like  caller runs other database call and this caused concurrent data retrieval in single connection.