Handling Large Result Sets with Paging

In this lesson, you will learn how to optimize database performance by fetching data in smaller, manageable chunks using server-side pagination.

When dealing with thousands of records, loading everything into memory at once is a recipe for performance bottlenecks and high memory usage. Instead of grabbing the entire table, we use the Skip and Take methods to instruct Entity Framework Core to generate SQL that only retrieves the specific subset of rows we need.

How it works

The Skip(n) method tells the database to ignore the first n records, while Take(m) tells it to return only the next m records. Because these methods operate on IQueryable, the translation happens inside the database engine, meaning the unused data never actually travels across the network or enters your application's RAM.

Here is a simple pattern to implement this:

public async Task<List<Product>> GetProductsPagedAsync(int pageNumber, int pageSize)
{
    // Ensure we skip correctly (e.g., page 1 skips 0, page 2 skips pageSize)
    int itemsToSkip = (pageNumber - 1) * pageSize;

    return await _context.Products
        .OrderBy(p => p.Id) // Pagination requires a consistent sort order
        .Skip(itemsToSkip)
        .Take(pageSize)
        .ToListAsync();
}

Critical Implementation Details

1. Always Order: You must use .OrderBy() before applying pagination. Databases do not guarantee row order; without an explicit order, Skip and Take can return unpredictable results or duplicates across different pages.
2. Server-Side Execution: Because we are building an IQueryable chain, EF Core translates this into a LIMIT and OFFSET clause (or equivalent depending on the provider). This ensures the "heavy lifting" is done by the database server, leaving your application lightweight.
3. The "Total Count" Problem: If you need to show the user total page numbers (e.g., "Page 1 of 50"), you will need to perform a separate CountAsync() query to determine the total number of records available.

Key Takeaway: By combining .OrderBy(), .Skip(), and .Take(), you shift the burden of large data management to the database, ensuring only the necessary records are processed by your application.