[HELP] Best practices for pagination using EF Core 8

Hi everyone. I'm learning EF Core 8 and encountered some issues with pagination in the following code.

using Dashboard.Data;
using Dashboard.Data.Models;
using JetBrains.Annotations;
using MediatR;
using Microsoft.EntityFrameworkCore;
using Shared.Utils;

namespace Dashboard.Core.Advertising.Api;

[UsedImplicitly]
public class ListAdvertisersQuery : PaginationQuery, IRequest<PaginationResult<Advertiser>> {
  public bool WithCampaigns { get; set; }
}

[UsedImplicitly]
public class ListAdvertisersQueryHandler(DataContext dataContext) : IRequestHandler<ListAdvertisersQuery, PaginationResult<Advertiser>> {
  public async Task<PaginationResult<Advertiser>> Handle(ListAdvertisersQuery query, CancellationToken cancellationToken) {
    var queryable = dataContext.Advertisers.AsQueryable();

    if (query.WithCampaigns) {
      queryable = queryable.Include(x => x.Campaigns);
    }

    var totalItemsTask = queryable.CountAsync(cancellationToken);
    var resultTask = queryable.Take(query.Limit).ToListAsync(cancellationToken);

    await Task.WhenAll(resultTask, totalItemsTask);

    return PaginationResult<Advertiser>.From(resultTask.Result, totalItemsTask.Result, query);
  }
}

When I run this code multiple times, I encounter an error.

System.InvalidOperationException: A second operation was started on this context instance before a previous operation completed. This is usually caused by different threads concurrently using the same instance of DbContext. For more information on how to avoid threading issues with DbContext, see https://go.microsoft.com/fwlink/?linkid=2097913.

DataContext is registered in DI like this

builder.Services.AddDbContext<DataContext>(options => {
  var databaseConfiguration = builder.Configuration.GetSection(DatabaseConfiguration.SectionName).Get<DatabaseConfiguration>();

  options
    .UseNpgsql(databaseConfiguration!.ConnectionString)
    .UseSnakeCaseNamingConvention();
});

I don't want to await every query sequentially. Could you help me with it and explain how to run it in parallel?

Comments

[Ilotus]

Okay, I've found a solution. I can use IDbContextFactory.

learn.microsoft.com/en-us/ef/core/...

Register in DI

builder.Services.AddDbContextFactory<DataContext>(options => {
  var databaseConfiguration = builder.Configuration.GetSection(DatabaseConfiguration.SectionName).Get<DatabaseConfiguration>();

  options
    .UseNpgsql(databaseConfiguration!.ConnectionString)
    .UseSnakeCaseNamingConvention();
});

Using

[UsedImplicitly]
public class ListAdvertisersQueryHandler(IDbContextFactory<DataContext> dataContextFactory) : IRequestHandler<ListAdvertisersQuery, PaginationResult<Advertiser>> {
  public async Task<PaginationResult<Advertiser>> Handle(ListAdvertisersQuery query, CancellationToken cancellationToken) {
    await using var totalItemsContext = await dataContextFactory.CreateDbContextAsync(cancellationToken);
    await using var resultContext = await dataContextFactory.CreateDbContextAsync(cancellationToken);

    var random = new Random();

    var website = random.Next(1000, 10_000_000).ToString();

    var totalItemsTask = totalItemsContext.Advertisers
      .Where(x => x.Website == website)
      .CountAsync(cancellationToken);

    var queryable = resultContext.Advertisers.AsQueryable().AsNoTracking();

    if (query.WithCampaigns) {
      queryable = queryable.Include(x => x.Campaigns);
    }

    var resultTask = queryable
      // .Skip(query.CalculateOffset())
      .Skip(random.Next(1, 1000))
      .Take(query.Limit)
      .ToListAsync(cancellationToken);

    await Task.WhenAll(totalItemsTask, resultTask);

    var result = resultTask.Result;
    var totalItems = totalItemsTask.Result;

    return PaginationResult<Advertiser>.From(result, totalItems, query);
  }
}

[Olabamiji Oyetubo]

Did this fit your exact use case?

[Alexis Morisón]

You can't use await Task.WhenAll(), do this:
var totalItems = await queryable.CountAsync(cancellationToken);
var result = await queryable.Take(query.Limit).ToListAsync(cancellationToken);

return PaginationResult<Advertiser>.From(result, totalItems, query);

[Ilotus]

Got it. Is there a way to run it independently? My dataset is quite large, so running queries sequentially is slow.

[Vahid Nasiri]

If you have a high load on your server, start implementing caching: github.com/VahidN/EFCoreSecondLeve...

[João Angelo]

Hi, Atmosphere ,
Thanks for sharing