When working with large datasets, retrieving and displaying all records at once can lead to slow performance, high memory usage and poor user experience.

Executing large queries can overload the database, slow down response times and make the UI difficult to navigate.

To address these challenges, pagination provides a structured approach to managing data retrieval efficiently while keeping the system fast and responsive.

Pagination

Pagination is a technique used to divide large datasets into smaller, manageable chunks.

Instead of retrieving all records at once, it fetches a subset of data based on a predefined limit and an identifier. This improves performance, reduces load times and enhances user experience.

There are several types of pagination, including:

• Offset Pagination
• Keyset(Cursor) Pagination
• Seek Pagination
• Hybrid Pagination

In today’s blog post, I’ll focus on Offset Pagination, the most common technique I’ve worked with.

Offset Pagination

Offset pagination retrieves data by skipping a specified number of records (OFFSET) and then fetching the next batch (LIMIT).

SELECT p."Id", p."Name", p."Description", p."Price"
FROM "Products" AS p
ORDER BY p."CreatedAt"
LIMIT 10 OFFSET 20;

In this example, the query skips the first 20 records and retrieves the next 10.

Offset pagination is ideal for a simple implementation that supports sorted data and random access to different pages.

NOTE: Offset pagination is not the most efficient option for large datasets, as performance decreases with higher offsets. It excels with small to medium-sized datasets.

Offset Pagination With Ef Core

You can implement offset pagination in EF Core easily using the Skip and Take methods.

var query = dbContext.Products
    .OrderBy(x=>x.CreatedAt)
    .Select(x => new ProductResponse(
        x.Id,
        x.Name,
        x.Description,
        x.Price));

var items = await query
    .Skip((page - 1) * itemsPerPage)
    .Take(itemsPerPage)
    .ToListAsync();

Skip((pageNumber - 1) * pageSize) – Skips records based on the page number.

Take(pageSize) – Retrieves the required number of records.

I prefer to create a generic PagedResponse class that includes additional metadata, which can be helpful:

public class PagedList<T>
{
    public PagedList(IEnumerable<T> items, int count, int pageNumber, int pageSize)
    {
        Items.AddRange(items);
        TotalCount = count;
        PageSize = pageSize;
        CurrentPage = pageNumber;
        TotalPages = (int)Math.Ceiling(count/(double)pageSize);
    }

    public List<T> Items { get; set; } = [];

    public int CurrentPage { get; private set; }

    public int TotalPages { get; private set; }

    public int PageSize { get; private set; }

    public int TotalCount { get; private set; }

    public bool HasPreviousPage => CurrentPage > 1;

    public bool HasNextPage => CurrentPage < TotalPages;
}

This PagedList class serves as a generic response that encapsulates paginated data along with metadata such as the current page, total pages and total item count.

Conclusion

Offset pagination, while simple and ideal for sorted data and random access, is best suited for small to medium-sized datasets.

With EF Core, you can easily implement offset pagination using Skip and Take methods.

By creating a PagedList class, you can return paginated data along with helpful metadata, ensuring both efficient data retrieval and a better user experience.

If you want to check out examples I created, you can find the source code here:

I hope you enjoyed it, subscribe and get a notification when a new blog is up!