DEV Community

Ilotus
Ilotus

Posted on

[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?

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

Read next

dot_nettips_a4e90828daa4 profile image

🚀 Unlock the Power of .NET Aspire! 🚀

Dot Net Tips -

juarezasjunior profile image

C# Tip: Avoid Unused Variables

Juarez Júnior -

karenpayneoregon profile image

Storing passwords safely (C#)

Karen Payne -

ramoneeza profile image

Icon Banks for Winforms and .NET9

Ramon Ordiales Plaza -