DEV Community

Ilotus
Ilotus

Posted on

4

[HELP] Best practices for pagination using EF Core 8

#help #discuss #aspnet #csharp

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?

Warp
 Promoted

Warp.dev image

Warp is the highest-rated coding agent—proven by benchmarks.

Warp outperforms every other coding agent on the market, and gives you full control over which model you use. Get started now for free, or upgrade and unlock 2.5x AI credits on Warp's paid plans.

Download Warp

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