When dealing with a large volume of data, pagination becomes essential—especially when this data needs to be presented to the user in an efficient and scalable manner. Without proper pagination implementation, systems can face serious performance issues and poor user experience, particularly when loading lists with thousands or even millions of records.
Importance of Pagination
Pagination is crucial to:
Optimize performance: Loading a large amount of data at once can slow down the application and overload the server. Pagination allows only a portion of the data to be loaded at a time, improving performance and reducing latency.
Improve user experience: Navigating through pages provides a smoother interaction, with no need to wait for a massive dataset to load. Users can access data more quickly without long wait times.
Reduce resource usage: By loading only part of the data at a time, memory and processing usage is significantly reduced, which can be critical in environments with many simultaneous users.
Pagination Approaches in .NET
In this article we’ll explore three main approaches to implement pagination in a .NET application: database pagination, Entity Framework pagination, and manual list pagination.
Database Pagination with SQL
The first pagination approach is done directly at the database level. Using SQL commands, it’s possible to load a portion of the data based on parameters like OFFSET and FETCH NEXT, which allow the division of data into pages and avoid memory overload.
public async Task GetPagedProductsAsync(int pageNumber, int pageSize)
{
var sql = @"
SELECT *
FROM Products
ORDER BY ProductID
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;
";
var offset = (pageNumber - 1) * pageSize;
var products = await _dbConnection.QueryAsync<Product>(sql,
new {
Offset = offset,
PageSize = pageSize
});
}
Here, the @PageNumber parameter represents the page number you want to retrieve, and @PageSize represents the number of records per page. The OFFSET clause is used to skip records before starting to return results based on the page number and size.
Using this with Dapper, if you pass pageNumber = 1 and pageSize = 20, the query will return the first 20 records. For pageNumber = 2, the query will skip the first 20 and return the next 20—i.e., from the 21st to the 40th record. The FETCH NEXT clause limits the number of records returned based on @PageSize.
Pagination with Entity Framework
When using Entity Framework (EF) for pagination, the approach is more abstract and object-oriented compared to raw SQL. Instead of SQL commands like OFFSET and FETCH NEXT, EF provides the Skip() and Take() methods for simpler and more intuitive pagination.
public async Task<Product> GetPagedProductsAsync()
{
var pageNumber = 1;
var pageSize = 15;
var query = _context.Products.AsQueryable();
var items = await query
.Skip((pageNumber - 1) * pageSize )
.Take(pageSize)
.ToListAsync();
return items;
}
The query begins by creating an IQueryable from the products collection (_context.Products), allowing you to build and manipulate the query efficiently without immediately executing it against the database.
The Skip() method ignores records before the desired page. The number of records to skip is calculated using (pageNumber - 1) * pageSize. The Take() method limits the number of records to the size of the current page.
Manual Pagination on Lists
When the data is already loaded in memory, you might need to paginate manually without querying a database. This is common when you already have a dataset available (like an in-memory list) and want to split it into pages for better display or navigation.
This method is useful when the data isn’t very large and can be handled in memory, avoiding extra database queries.
public Products GetPagedProducts(List<Product> products, int pageNumber, int pageSize)
{
var totalItems = products.Count;
var items = products
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToList();
var totalPages = (int)Math.Ceiling((double)totalItems / pageSize);
return new Products
{
Items = items,
CurrentPage = pageNumber
};
}
The GetPagedProducts method receives a list of products, the page number, and the page size. It calculates the total number of items using Count(), which is then used to determine the total number of pages.
The Skip() method skips records from previous pages based on (pageNumber - 1) * pageSize, and Take() fetches the current page’s records.
The total number of pages is calculated using Math.Ceiling() to ensure an extra page is returned if the total items aren’t perfectly divisible by page size.
Finally, the method returns a list of Product items for the current page, along with the current page number.
Creating a Paginated API in ASP.NET Core
Let’s now demonstrate how to build a paginated API using one of the discussed approaches.
Basic API Structure
First, create a simple controller that accepts query string parameters (pageNumber and pageSize). If no values are provided, default values will be used.
[Route("api/products")]
[ApiController]
public class ProductsController : ControllerBase
{
private readonly IProductRepository _productRepository;
public ProductsController(IProductRepository productRepository)
{
_productRepository = productRepository;
}
[HttpGet]
public async Task<IActionResult> GetPagedProducts(int pageNumber = 1, int pageSize = 10)
{
var result = await _productRepository.GetPagedProductsAsync(pageNumber, pageSize);
return Ok(result);
}
}
Now let’s create the ProductRepository that encapsulates the logic for retrieving paged data.
using Microsoft.EntityFrameworkCore;
public interface IProductRepository
{
Task<Products> GetPagedProductsAsync(int pageNumber, int pageSize);
}
public class ProductRepository : IProductRepository
{
private readonly ApplicationDbContext _context;
public ProductRepository(ApplicationDbContext context)
{
_context = context;
}
public async Task<Products> GetPagedProductsAsync(int pageNumber, int pageSize)
{
var query = _context.Products.AsQueryable();
var totalItems = await query.CountAsync();
var items = await query
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToListAsync();
var totalPages = (int)Math.Ceiling((double)totalItems / pageSize);
return new Products
{
Items = items,
CurrentPage = pageNumber + 1
};
}
}
To store the paginated results, you can use a response model like:
public class Products
{
public List<Product> Items { get; set; }
public int CurrentPage { get; set; }
}
Also, remember to register the service in Program.cs:
builder.Services.AddScoped<IProductRepository, ProductRepository>();
With that, our API pagination is ready.
Conclusion
There are several approaches to implementing pagination in .NET applications, and each has its pros and cons. Each technique has its ideal use case, and choosing the right one depends on your project’s specific needs, such as data volume, performance requirements, and complexity.