Create a pagination API with .NET
Tags: pagination, net
Published: 2024-07-12 02:34:12

---
title: Create a pagination API with .NET
published: true
date: 2024-07-08 08:11:00 UTC
tags: Pagination,NET
canonical_url: https://blog.stackpuz.com/create-a-pagination-api-with-dotnet/
---

![Pagination API with .NET](https://blog.stackpuz.com/media/posts/8/cover.jpg)

Splitting larger content into distinct pages is known as pagination. This approach significantly enhances the user experience and speeds up the loading of web pages. This example will demonstrate how to create a pagination API using .NET and use MySQL as a database.

## Prerequisites 

- .NET 8
- MySQL

## Setup project 

```
dotnet new webapi -o dotnet_api -n App
```

Create a testing database named "example" and run the [database.sql](https://github.com/StackPuz/Example-Pagination-dotnet-8/blob/main/database.sql) file to import the table and data.

## Project structure 

```
├─ Controllers
│  └─ ProductController.cs
├─ Models
│  ├─ DataContext.cs
│  └─ Product.cs
├─ wwwroot
│  └─ index.html
├─ Util.cs
├─ Program.cs
├─ App.csproj
└─ appsettings.json


```

## Project files 

### App.csproj 

This file is the .NET project configuration file. We added the `MySql.EntityFrameworkCore` package here.

```xml
<Project Sdk="Microsoft.NET.Sdk.Web">
    <PropertyGroup>
        <TargetFramework>net8.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
    </PropertyGroup>
    <ItemGroup>
        <PackageReference Include="MySql.EntityFrameworkCore" Version="8.0.0" />
    </ItemGroup>
</Project>
```

### appsettings.json 

This is the .NET application configuration file that contains the database connection information.

```json
{
    "Logging": {
        "LogLevel": {
            "Default": "Warning"
        }
    },
    "AllowedHosts": "*",
    "ConnectionStrings": {
        "Database": "server=localhost;port=3306;database=example;user id=root;password=;"
    }
}
```

### Program.cs 

This file is the main entry point for a .NET API application.

```c#
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();
builder.Services.AddDbContext<App.Models.DataContext>(options => options.UseMySQL(builder.Configuration.GetConnectionString("Database")));
var app = builder.Build();
app.UseDefaultFiles();
app.UseStaticFiles();
app.UseRouting();
app.MapControllers();
app.Run();
```

- `app.UseDefaultFiles()` uses index.html as the default HTML file.
- `app.UseStaticFiles()` serves the static files in the folder wwwroot.

## Util.cs 

This file defines the extension method `OrderBy()` of the `IQueryable` class to implement the dynamic column sorting for the Entity Framework.

```c#
using System.Linq.Expressions;
using System.Reflection;

namespace App
{
    public static class Util
    {
        public static IQueryable<TEntity> OrderBy<TEntity>(this IQueryable<TEntity> query, string column, string direction)
        {
            var type = typeof(TEntity);
            var parameter = Expression.Parameter(type);
            var property = type.GetProperty(column, BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase);
            var member = Expression.MakeMemberAccess(parameter, property);
            var lamda = Expression.Lambda(member, parameter);
            var method = direction == "desc" ? "OrderByDescending" : "OrderBy";
            var expression = Expression.Call(typeof(Queryable), method, new Type[] { type, property.PropertyType }, query.Expression, Expression.Quote(lamda));
            return query.Provider.CreateQuery<TEntity>(expression);
        }
    }
}
```

### DataContext.cs 

This is the required file when working with Entity Framework (EF) in a .NET application. It's used to map the tables and columns information from the database to the entities.

```c#
using Microsoft.EntityFrameworkCore;

namespace App.Models
{
    public partial class DataContext : DbContext
    {
        public virtual DbSet<Product> Product { get; set; }

        public DataContext()
        {
        }

        public DataContext(DbContextOptions<DataContext> options) : base(options)
        {
        }
        
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Product>(entity =>
            {
                entity.ToTable("Product");
                entity.HasKey(e => e.Id);
                entity.Property(e => e.Id).HasColumnName("id");
                entity.Property(e => e.Name).HasColumnName("name").HasMaxLength(50).IsUnicode(false);
                entity.Property(e => e.Price).HasColumnName("price").HasColumnType("decimal(12,2)");
            });
        }
    }
}
```

### Product.cs 

This file defines the model information that maps to our database table named "Product".

```c#
using System.ComponentModel.DataAnnotations;

namespace App.Models
{
    public partial class Product
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
    }
}
```

### ProductController.cs 

This file is used to handle incoming requests and produce the paginated data for the client.

```c#
using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Mvc;
using App.Models;

namespace App.Controllers
{
    public class ProductController : Controller
    {
        private readonly DataContext _context;

        public ProductController(DataContext context)
        {
            _context = context;
        }

        [HttpGet("api/products")]
        public async Task<IActionResult> Index()
        {
            int page = Request.Query["page"].Any() ? Convert.ToInt32(Request.Query["page"]) : 1;
            int size = Request.Query["size"].Any() ? Convert.ToInt32(Request.Query["size"]) : 10;
            string order = Request.Query["order"].Any() ? Request.Query["order"].First() : "Id";
            string direction = Request.Query["direction"].Any() ? Request.Query["direction"].First() : "asc";
            var query = _context.Product.Select(e => new {
                Id = e.Id,
                Name = e.Name,
                Price = e.Price
            });
            query = query.OrderBy(order, direction);
            var products = await query.Skip((page - 1) * size).Take(size).ToListAsync();
            return Ok(products);
        }
    }
}

```

- We utilize the query string to get `page, size, order, direction` and create the paginated data by using the `query.Skip((page - 1) * size).Take(size)` method.
- We use the extension method `OrderBy()` that is defined in the Util.cs file to perform sorting on the `IQueryable` object.

### index.html 

Instead of entering the URL manually to test our API, we used this file to create links for easier testing.

```html
<!DOCTYPE html>
<head>
</head>
<body>
    <ul>
        <li><a target="_blank" href="/api/products">Default</a></li>
        <li><a target="_blank" href="/api/products?page=2">Page 2</a></li>
        <li><a target="_blank" href="/api/products?page=2&size=25">Page 2 and Size 25</a></li>
        <li><a target="_blank" href="/api/products?page=2&size=25&order=name">Page 2 and Size 25 and Order by name</a></li>
        <li><a target="_blank" href="/api/products?page=2&size=25&order=name&direction=desc">Page 2 and Size 25 and Order by name descending</a></li>
    </ul>
</body>
</html>
```

## Run project 

```
dotnet run
```

Open the web browser and goto http://localhost:5122  
You will find this test page.

![test page](https://blog.stackpuz.com/media/posts/8/index.PNG)

## Testing 

### Testing without any parameters 

Click the "Default" link, and it will open the URL `http://localhost:5122/api/products`

![default test](https://blog.stackpuz.com/media/posts/8/default.PNG)

The API will return paginated data with default parameters (page = 1 and size = 10).

### Page index test 

Click the "Page 2" link, and it will open the URL `http://localhost:5122/api/products?page=2`

![page index test](https://blog.stackpuz.com/media/posts/8/page-test.PNG)

The API will return paginated data on the second page, starting with product id 11

### Page size test 

Click the "Page 2 and Size 25" link, and it will open the URL `http://localhost:5122/api/products?page=2&size=25`

![page size test](https://blog.stackpuz.com/media/posts/8/page-size-test.PNG)

The API will return paginated data on the second page by starting with product id 26 because the page size is 25.

### Order test 

Click the "Page 2 and Size 25 and Order by name" link, and it will open the URL `http://localhost:5122/api/products?page=2&size=25&order=name`

![order test](https://blog.stackpuz.com/media/posts/8/order-test.PNG)

The API will return paginated data on the second page, but the product order is based on the product name.

### Descending order test 

Click the "Page 2 and Size 25 and Order by name descending" link, and it will open the URL `http://localhost:5122/api/products?page=2&size=25&order=name&direction=desc`

![descending order test](https://blog.stackpuz.com/media/posts/8/order-desc-test.PNG)

The API will return paginated data on the second page, but the product order is based on the product name in descending order.

## Conclusion 

In this article, you have learned how to utilize the Entity Framework to implement the pagination API for a .NET application with less effort. The pagination approach will enhance the user experience and speed up your .NET API. Thanks for reading.

Source code: [https://github.com/stackpuz/Example-Pagination-dotnet-8](https://github.com/stackpuz/Example-Pagination-dotnet-8)

Create a CRUD Web App in Minutes: [https://stackpuz.com](https://stackpuz.com)