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

  • 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 ?
    Saturday, February 13, 2021 11:48 PM

Answers

  • 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 ?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, February 14, 2021 10:10 AM

All replies

  • 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?

    Sunday, February 14, 2021 3:11 AM
  • 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. 

    Sunday, February 14, 2021 7:29 AM
  • 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.

    Sunday, February 14, 2021 9:39 AM
  • 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 ?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, February 14, 2021 10:10 AM
  • 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

    Sunday, February 14, 2021 12:45 PM
  • 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.

    Sunday, February 14, 2021 4:40 PM