Pagination in .Net Api

Pagination in ASP.NET Core Web API with Entity Framework Core

Pagination is a technique to divide a large list of items into multiple pages. It is used to improve the performance of the application by reducing the amount of data sent to the client. In this article, we will learn how to implement pagination in an ASP.NET Core Web API application with Entity Framework Core and SQL Server.

We will use the repository pattern to access the database. We will use the Newtonsoft.Json library to ignore reference loops in the JSON response.

Prerequisites

• .NET
• Visual Studio or any code editor
• SQL Server
• Postman or any API testing tool

Create a new .NET Web API application

dotnet new webapi  --use-controllers 

Install packages

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.AspNetCore.Mvc.NewtonsoftJson

create a Player model

public class Player
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Club { get; set; }
    public DateTime Created { get; set; }
}

Create the PaginatedList class in the Models folder

public class PaginatedList<T>
{
    public List<T> Items { get; }
    public int PageIndex { get; }
    public int TotalPages { get; }
    public bool HasPreviousPage => PageIndex > 1;
    public bool HasNextPage => PageIndex < TotalPages;

    public PaginatedList(List<T> items, int pageIndex, int totalPages)
    {
        Items = items;
        PageIndex = pageIndex;
        TotalPages = totalPages;
    }
}

Create the ApiResponse class in the Models folder

public class ApiResponse
{
    public bool Success { get; set; }
    public string Message { get; set; } = null;
    public object Data { get; set; }

    public ApiResponse(bool success, string message, object data)
    {
        Success = success;
        Message = message;
        Data = data;
    }
}

AppDbContext class in the Data folder

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }

    public DbSet<Player> Players { get; set; }
}

IPlayerRepository

public interface IPlayerRepository
{
    Task<PaginatedList<Player>> GetPlayers(int pageIndex, int pageSize);
    Task<Player> AddPlayer(Player player);
}

PlayerRepository

public class PlayerRepository : IPlayerRepository
{
    private readonly AppDbContext _context;

    public PlayerRepository(AppDbContext context)
    {
        _context = context;
    }

    public async Task<PaginatedList<Player>> GetPlayers(int pageIndex, int pageSize)
    {
        var players = await _context.Players
            .OrderBy(b => b.Id)
            .Skip((pageIndex - 1) * pageSize)
            .Take(pageSize)
            .ToListAsync();

        var count = await _context.Players.CountAsync();
        var totalPages = (int)Math.Ceiling(count / (double)pageSize);

        return new PaginatedList<Player>(players, pageIndex, totalPages);
    }

    public async Task<Player> AddPlayer(Player player)
    {
        _context.Players.Add(player);
        await _context.SaveChangesAsync();
        return player;
    }
}

PlayerController class

[ApiController]
[Route("api/[controller]")]
public class PlayerController : ControllerBase
{
    private readonly IPlayerRepository _playerRepository;

    public PlayerController(IPlayerRepository playerRepository)
    {
        _playerRepository = playerRepository;
    }

    [HttpGet]
    public async Task<ActionResult<ApiResponse>> GetPlayers(int pageIndex = 1, int pageSize = 10)
    {
        var players = await _playerRepository.GetPlayers(pageIndex, pageSize);
        return new ApiResponse(true, null, players);
    }

    [HttpPost]
    public async Task<ActionResult<ApiResponse>> AddPlayer(Player player)
    {
        var newPlayer = await _playerRepository.AddPlayer(player);
        return new ApiResponse(true, null, newPlayer);
    }
}

Program.cs

using Microsoft.EntityFrameworkCore;
using pagina.Data;
using pagina.Repository;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddDbContext<AppDbContext>(options =>
   {
       options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"));
   });

builder.Services.AddControllers().AddNewtonsoftJson(options =>
{
    options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
});

builder.Services.AddScoped<IPlayerRepository, PlayerRepository>();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=myapp;User Id=sa;Password=********(!)Password;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

Migrations

dotnet ef migrations add InitialCreate
dotnet ef database update

Run the application

dotnet run

Comments

[Karen Payne]

Well written but since there is a database used you should consider providing a GitHub repository for code and an SQL script to create the database. I never write with a database involved without providing a source code.