DEV Community

Ilotus
Ilotus

Posted on

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

Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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();
});
Enter fullscreen mode Exit fullscreen mode

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

Top comments (6)

Collapse
 
atmosphere profile image
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();
});
Enter fullscreen mode Exit fullscreen mode

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);
  }
}
Enter fullscreen mode Exit fullscreen mode
Collapse
 
bigboybamo profile image
Olabamiji Oyetubo

Did this fit your exact use case?

Collapse
 
alexismorison95 profile image
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);

Collapse
 
atmosphere profile image
Ilotus

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

Collapse
 
vahidn profile image
Vahid Nasiri

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

Collapse
 
jangelodev profile image
João Angelo

Hi, Atmosphere ,
Thanks for sharing

Some comments may only be visible to logged-in visitors. Sign in to view all comments. Some comments have been hidden by the post's author - find out more