.NET Web API Pagination with IQueryable – The Ultimate Guide

Learn how to implement efficient pagination in .NET Web API using IQueryable. Optimize performance, reduce response time, and handle large datasets seamlessly.

---

Introduction

Have you ever faced performance issues while fetching large datasets in your .NET Web API? Imagine you have thousands (or even millions) of records in your database, and your API returns all of them in one go—sounds inefficient, right?

This is where pagination comes to the rescue! By implementing pagination, you can limit the number of records per request, making your API more efficient and user-friendly.

In this guide, we’ll explore how to implement efficient pagination in .NET Web API using IQueryable, discuss best practices, and provide a complete hands-on example.

---

Why Use Pagination in Web API?

Pagination is crucial for API performance and better user experience. Here’s why:

✅ Improves Performance – Reduces the data load on the server.
✅ Optimizes Database Queries – Fetches only required records instead of entire tables.
✅ Enhances User Experience – Allows users to navigate through data efficiently.
✅ Saves Bandwidth – Reduces the size of API responses, improving speed.

Now, let’s dive into implementing pagination in .NET Web API with IQueryable.

---

Setting Up a .NET Web API for Pagination

Step 1: Create a .NET Web API Project

First, create a new .NET 7 Web API project (or use an existing one).

dotnet new webapi -n PaginationDemo
cd PaginationDemo

Step 2: Install Required Packages

If using Entity Framework Core, install the required package:

dotnet add package Microsoft.EntityFrameworkCore

For SQL Server, install:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

---

Using IQueryable for Efficient Pagination

What is IQueryable?

IQueryable allows LINQ queries to be executed on a database-level, rather than fetching all records and filtering in-memory. It enables lazy loading, improving performance.

Let’s see how to use IQueryable for pagination.

---

Implementing Pagination in .NET Web API

Step 1: Create a Sample Entity

Let’s create a Product entity:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

Step 2: Create a Database Context

Create a ApplicationDbContext to interact with the database:

using Microsoft.EntityFrameworkCore;

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

    public DbSet<Product> Products { get; set; }
}

Step 3: Add Pagination Parameters

To support pagination, create a PaginationParameters class:

public class PaginationParameters
{
    private const int maxPageSize = 50;
    public int PageNumber { get; set; } = 1;

    private int _pageSize = 10;
    public int PageSize
    {
        get { return _pageSize; }
        set { _pageSize = (value > maxPageSize) ? maxPageSize : value; }
    }
}

✅ This class ensures:

• Default page size is 10 records.
• Maximum page size is 50 to prevent excessive data fetching.

---

Step 4: Implement Pagination Using IQueryable

Now, let’s create a generic extension method for pagination:

using System.Linq;

public static class IQueryableExtensions
{
    public static IQueryable<T> Paginate<T>(this IQueryable<T> query, PaginationParameters pagination)
    {
        return query.Skip((pagination.PageNumber - 1) * pagination.PageSize)
                    .Take(pagination.PageSize);
    }
}

✅ What This Does:

• Skip – Skips records based on PageNumber.
• Take – Retrieves only PageSize records.

---

Step 5: Implement API Controller with Pagination

Now, let’s create a ProductsController:

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using System.Threading.Tasks;

[Route("api/[controller]")]
[ApiController]
public class ProductsController : ControllerBase
{
    private readonly ApplicationDbContext _context;

    public ProductsController(ApplicationDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    public async Task<IActionResult> GetProducts([FromQuery] PaginationParameters pagination)
    {
        var products = _context.Products.AsQueryable().Paginate(pagination);

        var totalRecords = await _context.Products.CountAsync();
        var totalPages = (int)Math.Ceiling((double)totalRecords / pagination.PageSize);

        var response = new
        {
            CurrentPage = pagination.PageNumber,
            TotalPages = totalPages,
            TotalRecords = totalRecords,
            PageSize = pagination.PageSize,
            Data = await products.ToListAsync()
        };

        return Ok(response);
    }
}

---

Testing the API

Test API with Pagination Query Parameters

Start the API and make a GET request:

GET /api/products?pageNumber=2&pageSize=10

Sample JSON Response

{
  "currentPage": 2,
  "totalPages": 5,
  "totalRecords": 50,
  "pageSize": 10,
  "data": [
    { "id": 11, "name": "Product 11", "price": 12.99 },
    { "id": 12, "name": "Product 12", "price": 15.99 }
  ]
}

✅ Key Features:

• Provides metadata (current page, total pages, etc.).
• Supports dynamic pagination using query parameters.
• Uses IQueryable for optimized database queries.

---

Best Practices for Pagination in .NET API

🚀 Use IQueryable – Enables lazy loading and prevents unnecessary memory consumption.
🚀 Limit Maximum Page Size – Avoids performance issues with excessively large queries.
🚀 Index Your Database Columns – Index the primary key and commonly queried fields.
🚀 Return Metadata – Provide TotalRecords and TotalPages for better client-side handling.
🚀 Use Cursor-Based Pagination – For high-performance, use cursor-based pagination with Id filtering instead of Skip/Take.

---

Conclusion

Pagination is essential for scaling APIs efficiently. By implementing IQueryable with Skip and Take, we ensure optimal performance, reduced server load, and better user experience.

👉 Next Steps?

• Implement sorting & filtering along with pagination.
• Explore cursor-based pagination for high-volume APIs.
• Optimize database queries using indexes and stored procedures.

💬 Have questions? Drop a comment below! 🚀

---

FAQs

1. What is the difference between IQueryable and IEnumerable?

IQueryable executes queries on the database, while IEnumerable loads all data in memory before filtering.

2. Why use pagination in Web API?

Pagination reduces response size, improves performance, and enhances user experience.

3. What is the best way to paginate large datasets?

For large datasets, use cursor-based pagination instead of Skip/Take to prevent performance bottlenecks.

4. Can I implement infinite scrolling using pagination?

Yes, you can implement infinite scrolling using pageSize and pageNumber in API requests.

---

🚀 Ready to build scalable APIs? Implement pagination in your .NET Web API today! 🚀