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