Building a .NET Core 7 Web API with Pagination, Sorting, and Filtering

In modern web applications, it's common to deal with large datasets. Returning all the data in a single response can be inefficient and slow. To address this, developers often implement pagination, sorting, and filtering in their APIs. In this blog post, we'll walk through how to build a .NET Core 7 Web API that supports these features.

Prerequisites

Before we begin, ensure you have the following installed:

• .NET 7 SDK

• Visual Studio 2022 or Visual Studio Code

• A database (we'll use SQL Server for this example)

Step 1: Create a New .NET Core 7 Web API Project

First, let's create a new .NET Core 7 Web API project.

dotnet new webapi -n PaginationSortingFilteringApi
cd PaginationSortingFilteringApi

Step 2: Set Up the Database

For this example, we'll use Entity Framework Core to interact with the database. Let's create a simple Product entity.

1. Install the necessary NuGet packages:

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design

2. Create the Product entity:

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

3. Create the ApplicationDbContext:

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

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

4. Register the ApplicationDbContext in Program.cs:

var builder = WebApplication.CreateBuilder(args);

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

var app = builder.Build();

5. Add the connection string to appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=PaginationSortingFilteringDb;Trusted_Connection=True;"
  }
}

6. Run the migrations to create the database:

dotnet ef migrations add InitialCreate
dotnet ef database update

Step 3: Implement Pagination, Sorting, and Filtering

Now that we have our database set up, let's implement pagination, sorting, and filtering in our API.

1. Create a ProductService to handle the business logic:

public class ProductService
{
    private readonly ApplicationDbContext _context;

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

    public async Task<(IEnumerable<Product>, int)> GetProductsAsync(
        int pageNumber = 1,
        int pageSize = 10,
        string sortBy = "Name",
        string sortOrder = "asc",
        string filterByName = null)
    {
        var query = _context.Products.AsQueryable();

        // Filtering
        if (!string.IsNullOrEmpty(filterByName))
        {
            query = query.Where(p => p.Name.Contains(filterByName));
        }

        // Sorting
        if (sortOrder.ToLower() == "desc")
        {
            query = query.OrderByDescending(p => EF.Property<object>(p, sortBy));
        }
        else
        {
            query = query.OrderBy(p => EF.Property<object>(p, sortBy));
        }

        // Pagination
        var totalCount = await query.CountAsync();
        var products = await query
            .Skip((pageNumber - 1) * pageSize)
            .Take(pageSize)
            .ToListAsync();

        return (products, totalCount);
    }
}

2. Register the ProductService in Program.cs:

builder.Services.AddScoped<ProductService>();

3. Create a ProductsController to expose the API:

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

    public ProductsController(ProductService productService)
    {
        _productService = productService;
    }

    [HttpGet]
    public async Task<IActionResult> GetProducts(
        [FromQuery] int pageNumber = 1,
        [FromQuery] int pageSize = 10,
        [FromQuery] string sortBy = "Name",
        [FromQuery] string sortOrder = "asc",
        [FromQuery] string filterByName = null)
    {
        var (products, totalCount) = await _productService.GetProductsAsync(
            pageNumber, pageSize, sortBy, sortOrder, filterByName);

        var response = new
        {
            TotalCount = totalCount,
            PageNumber = pageNumber,
            PageSize = pageSize,
            Products = products
        };

        return Ok(response);
    }
}

Step 4: Test the API

Now that our API is set up, let's test it using a tool like Postman or Swagger.

1. Run the application:

dotnet run

2. Open Swagger UI by navigating to https://localhost:5001/swagger (or the appropriate URL for your setup).

3. Test the following endpoints:

• Get Products with Pagination: /api/products?pageNumber=1&pageSize=10

• Sort Products: /api/products?sortBy=Price&sortOrder=desc

• Filter Products by Name: /api/products?filterByName=Laptop

Conclusion

In this blog post, we've built a .NET Core 7 Web API that supports pagination, sorting, and filtering. These features are essential for handling large datasets efficiently and providing a better user experience. You can extend this example by adding more advanced filtering options, caching, or integrating with a front-end framework.