DEV Community

[HELP] Best practices for pagination using EF Core 8

Atmosphere on January 23, 2024

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

Read full post

Atmosphere • Jan 23

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

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

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 • Jan 25

Did this fit your exact use case?

Alexis Morisón • Jan 23

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