EF Core - With Generic Host (Pagination)

EF Core 에서 Table 의 data 들을 paging 하고 싶을 수 있다. 

각각의 모든 Table 마다  paging 코드를 작성하는 것 보다는 아래와 같은 형태로 작성하는 것이 좋다. 

 

참조 : https://www.codingame.com/playgrounds/5363/paging-with-entity-framework-core

public static PagedResult<T> GetPaged<T>(this IQueryable<T> query, 
                                         int page, int pageSize) where T : class
{
     var result = new PagedResult<T>();
     result.CurrentPage = page;
     result.PageSize = pageSize;
     result.RowCount = query.Count();


     var pageCount = (double)result.RowCount / pageSize;
     result.PageCount = (int)Math.Ceiling(pageCount);
 
     var skip = (page - 1) * pageSize;     
     result.Results = query.Skip(skip).Take(pageSize).ToList();
 
     return result;
}

//
public abstract class PagedResultBase
{
    public int CurrentPage { get; set; }
    public int PageCount { get; set; }
    public int PageSize { get; set; }
    public int RowCount { get; set; }

    public int FirstRowOnPage
    {

        get { return (CurrentPage - 1) * PageSize + 1; }
    }

    public int LastRowOnPage
    {
        get { return Math.Min(CurrentPage * PageSize, RowCount); }
    }
}

public class PagedResult<T> : PagedResultBase where T : class
{
    public IList<T> Results { get; set; }

    public PagedResult()
    {
        Results = new List<T>();
    }
}

 

EF Core 용 Generic Host 프로젝트 생성

Test 를 위해 Console 프로젝트를 생성하자

생성한 project 에 logging 및 dependency injection 등 aspnet core 에서 사용하던 방식을 적용해 보자

https://yogingang.tistory.com/350

DotNET Console Generic Host - Dependency Injection with Scrutor

dotnet generic host 라는 것을 이용하여 Console app 을 구성하면 된다. 

dotnet add package Microsoft.Extensions.Hosting
...
var builder = Host.CreateApplicationBuilder(args);
...

그리고 sqlserver dbprovider 를 이용하여 ef core 에 연결작업을 하고

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
...
builder.Services.AddDbContext<SqlServerContext>
    (options => options.UseSqlServer
                        (connectionString,
                        x => x.EnableRetryOnFailure(maxRetryCount: maxRetryCount)));

Production 및 User 라는 모델을 만들어 migration 작업을 하자

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
...
public class User
{
    [Key]
    public int Id { get;set;}
    public string UserId { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

public class Production
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public string Type { get; set; }
    public int Count { get; set; }
}
...
//SqlServerContext.cs
public class SqlServerContext : DbContext
{
    public SqlServerContext(DbContextOptions options) : base(options)
    {
        
    }
    public DbSet<Production> Productions { get; set; }
    public DbSet<User> Users { get; set; }
}

...
//db connection string set
//appsettings.json
{

  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },

  "ConnectionStrings": {
    "Server": "Data Source=(localdb)\\MSSQLLocalDB;Database=EFTest;Trusted_Connection=True;"
  }
}
//migration and db update 
dotnet ef migrations add InitialCreate --project EF_01 --startup-project EF_01 --context SqlServerContext
dotnet ef database update --project EF_01 --startup-project EF_01 --context SqlServerContext

 

기본적인 뼈대는 아래와 같다.

주요 작업들만 구현해 보았다.

 

Program.cs

using EF_01;
using EF_01.HostedService;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;

var builder = Host.CreateApplicationBuilder(args);
builder.Services.AddHostedService<Worker>();
var connectionString = builder.Configuration.GetConnectionString("Server") ?? string.Empty;
var maxRetryCount = 3;
builder.Services.AddDbContext<SqlServerContext>
    (options => options.UseSqlServer
                        (connectionString,
                        x => x.EnableRetryOnFailure(maxRetryCount: maxRetryCount)));

IHost host = builder.Build();
host.Run();

HostedService/Worker.cs

public sealed class Worker : IHostedService
{
    private readonly ILogger _logger;

    public Worker(
        ILogger<Worker> logger,
        IHostApplicationLifetime appLifetime)
    {
        _logger = logger;

        appLifetime.ApplicationStarted.Register(OnStarted);
        appLifetime.ApplicationStopping.Register(OnStopping);
        appLifetime.ApplicationStopped.Register(OnStopped);
    }

    public Task StartAsync(CancellationToken cancellationToken)
    {
        _logger.LogInformation("1. StartAsync has been called.");

        return Task.CompletedTask;
    }

    public Task StopAsync(CancellationToken cancellationToken)
    {
        _logger.LogInformation("4. StopAsync has been called.");

        return Task.CompletedTask;
    }

    private void OnStarted()
    {
        _logger.LogInformation("2. OnStarted has been called.");
    }

    private void OnStopping()
    {
        _logger.LogInformation("3. OnStopping has been called.");
    }

    private void OnStopped()
    {
        _logger.LogInformation("5. OnStopped has been called.");
    }
}

그리고 paging 에 대한 쿼리는 아래와 같이 사용한다. 

...
_context.Users.OrderBy(u=>u.Id).GetPaged(request.PageNo, request.PageSize);  
...

 

 

 

관련영상

https://youtu.be/pke5OC6b2B8