A Command Is Already in Progress

I am attempting to run a background worker for a web app that I am developing. I am using Npgsql as my EF Core provider.

For clarification, I have injected my DbContext with a Transient lifetime, and have allowed Pooling in my connection string, however, whenever I try to test it I get the following error:

Npgsql.NpgsqlOperationInProgressException: A command is already in progress: [My Query Here]

I have my Program Class Set up as such:

public class Program
{
    public static void Main(string[] args)
    {
        CreateHostBuilder(args).Build().Run();
    }

    public static IHostBuilder CreateHostBuilder(string[] args) =>
        Host.CreateDefaultBuilder(args)
            .ConfigureServices((hostContext, services) =>
            {
                // Get the configuration
                IConfiguration config = hostContext.Configuration;

                // DbContext
                services.AddDbContext<MyDbContext>(options => options.UseNpgsql(config.GetConnectionString("PostgreSQLString")), ServiceLifetime.Transient);

                services.AddHostedService<Worker>();
                services.AddScoped<IDTOService, BackgroundDTOService>();
            });
}

Which then leads to my Worker class

public class Worker : BackgroundService
{
    private Logger logger;

    public Worker(IServiceProvider services, IConfiguration configuration)
    {
        this.Services = services;

        var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
        optionsBuilder.UseNpgsql(configuration.GetConnectionString("PostgreSQLString"));
        var context = new StatPeekContext(optionsBuilder.Options);

        this.logger = new Logger(new LogWriter(context));
    }

    public IServiceProvider Services { get; }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        this.logger.LogInformation("ExecuteAsync in Worker Service is running.");
        await this.DoWork(stoppingToken);
    }

    private async Task DoWork(CancellationToken stoppingToken)
    {
        using (var scope = Services.CreateScope())
        {
            var context = scope.ServiceProvider.GetRequiredService<MyDbContext>();
            var dtoService = scope.ServiceProvider.GetRequiredService<IDTOService>();
            await dtoService.ProcessJSON(stoppingToken);
        }
    }

    public override async Task StopAsync(CancellationToken cancellationToken)
    {
        this.logger.LogInformation("Worker service is stopping.");
        await Task.CompletedTask;
    }
}

which leads to my BackGroundDTOService

public class BackgroundDTOService : IDTOService
{
    private int executionCount = 0;
    private Logger logger;
    private MyDbContext context;
    private DbContextOptionsBuilder<MyDbContext> optionsBuilder;

    public BackgroundDTOService(IConfiguration configuration, MyDbContext context)
    {
        this.optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
        this.optionsBuilder.UseNpgsql(configuration.GetConnectionString("PostgreSQLString"));

        this.logger = new Logger(new LogWriter(new MyDbContext(this.optionsBuilder.Options)));

        this.context = context;
    }

    public async Task ProcessJSON(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            this.executionCount++;

            this.logger.LogInformation($"DTO Service is working. Count: {this.executionCount}");

            this.ProcessTeams();

            await Task.Delay(TimeSpan.FromSeconds(1), stoppingToken);
        }
    }

    public void ProcessTeams()
    {
        // Add Any Franchises that don't exist
        var franchiseDumps = this.context.RequestDumps.Where(rd => rd.Processed == false && rd.DumpType == "leagueteams");
        foreach (RequestDump teamDump in franchiseDumps)
        {
            var league = this.context.Leagues.Include(l => l.Franchises).FirstOrDefault(l => l.Id == teamDump.LeagueID);
            var teams = Jeeves.GetJSONFromKey<List<DTOTeam>>(teamDump.JsonDump, "leagueTeamInfoList");

            foreach (DTOTeam team in teams)
            {
                this.UpdateFranchise(team, league);
            }

            this.logger.LogInformation($"DTO Service Processed League Teams on count {this.executionCount}");
        }

        this.context.SaveChanges();
    }

The error appears to occur immediately after snagging franchiseDumps when it tries to get league

2 Answers

Could you try materialising the query:

 var franchiseDumps = this.context.RequestDumps.Where(rd => rd.Processed == false && rd.DumpType == "leagueteams").ToList();
8

I had the same issue and I realized that PostgreSQL doesn't support Multiple Active Result Sets (MARS) compare to MSSQL.

An alternative way that worked for me to fetch objects with foreign key:

IEnumerable<Expense> objList = _db.Expenses;
foreach (var obj in objList.ToList())
{
    obj.ExpenseCategory = _db.ExpenseCategories.FirstOrDefault(u => u.Id == obj.ExpenseCategoryId);
}
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Chloe Bennett

Chloe Bennett

Culture, Media & Entertainment Columnist

Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.

Share this article
Twitter Facebook Pinterest