When Skip() Lies: The Hidden Pagination Bug Killing Production APIs

The Bug Report That Didn't Make Sense

A few weeks ago, I got paged at 3 AM. Our main customer listing endpoint — used by dozens of frontend apps — was returning HTTP 500 errors under real client load. The logs showed "Request Entity Too Large" coming from SQL Server.

The weird part? It worked perfectly in local development. Our integration tests passed. Everything looked fine.

The Investigation

The endpoint used a typical EF Core pagination pattern:

[HttpGet]
public async Task<ActionResult<PagedResult<CustomerDto>>> GetCustomers(
    int page = 0, 
    int pageSize = 50)
{
    var customers = await _context.Customers
        .OrderBy(c => c.Id)
        .Skip(page * pageSize)
        .Take(pageSize)
        .Select(c => new CustomerDto 
        { 
            Id = c.Id, 
            Name = c.Name, 
            Email = c.Email 
        })
        .ToListAsync();

    return Ok(new PagedResult<CustomerDto>(customers, totalCount));
}

Local testing with 10,000 records? Flawless. Load testing with simulated clients? Smooth. Production with real traffic? Catastrophic failure.

I pulled the actual SQL being generated by enabling query logging in production:

SELECT [t].[Id], [t].[Name], [t].[Email]
FROM (
    SELECT 
        [c].[Id], 
        [c].[Name], 
        [c].[Email"],
        ROW_NUMBER() OVER(Order by [c].[Id]) AS [row]
    FROM [Customers] AS [c]
) AS [t]
WHERE [t].[row] BETWEEN @___page_0 * @__pageSize_1 + 1 AND (
    @__page_0 * @__pageSize_1) + @__pageSize_1
ORDER BY [t].[row]

There it was. EF Core was wrapping the entire table scan in a nested subquery with ROW_NUMBER(), then slicing with BETWEEN. As traffic increased, these queries stacked up, consuming tempdb memory for the window function operations until SQL Server threw the "Request Entity Too Large" error.

Why Offset-Based Pagination Fails at Scale

The fundamental problem isn't EF Core or SQL Server — it's the OFFSET/FETCH model itself.

Every time you request page 50, SQL Server has to:

1. Scan all records from the beginning
2. Sort them (even if already ordered, the window function requires it)
3. Apply the ROW_NUMBER() to every single row
4. Filter down to your requested page

This is O(n) work that gets repeated on every page request. Your database doesn't remember "I already computed the first 2450 rows last time."

The Fix: Keyset Pagination

Keyset pagination (also called "seek method") eliminates the OFFSET entirely by remembering where you left off:

[HttpGet]
public async Task<ActionResult<PagedResult<CustomerDto>>> GetCustomers(
    int? lastId = null,
    int pageSize = 50)
{
    var query = _context.Customers
        .OrderBy(c => c.Id)
        .Take(pageSize + 1);  // Fetch one extra to know if there's more

    if (lastId.HasValue)
    {
        // Instead of Skip(), use WHERE clause
        query = query.Where(c => c.Id > lastId.Value);
    }

    var customers = await query
        .Select(c => new CustomerDto 
        { 
            Id = c.Id, 
            Name = c.Name, 
            Email = c.Email 
        })
        .ToListAsync();

    var hasMore = customers.Count > pageSize;
    var result = customers.Take(pageSize);

    return Ok(new PagedResult<CustomerDto>(
        result, 
        hasMore, 
        customers.LastOrDefault()?.Id));
}

The generated SQL becomes beautifully simple:

SELECT [c].[Id], [c].[Name], [c].[Email"]
FROM [Customers] AS [c]
WHERE [c].[Id] > @__lastId_0
ORDER BY [c].[Id]
LIMIT @__pageSize_1

No subqueries. No window functions. Just a direct index seek.

Additional Optimization: Project Before You Paginate

Another crucial optimization from this incident: always project to DTOs before paging operations:

// ❌ Wrong - selects all columns then projects
var customers = await _context.Customers
    .OrderBy(c => c.Id)
    .Skip(page * pageSize)
    .Take(pageSize)
    .Select(c => new CustomerDto { ... })
    .ToListAsync();

// ✅ Right - projects first, pages second  
var customers = await _context.Customers
    .Where(c => /* conditions */)
    .Select(c => new CustomerDto { ... })  // Project first
    .OrderBy(dto => dto.Id)
    .Skip(page * pageSize)
    .Take(pageSize)
    .ToListAsync();

When you project first, EF Core generates cleaner SQL that works with smaller result sets through the pagination logic.

The Real Lesson

Pagination seems like a solved problem. But when you mix Entity Framework Core's LINQ translation with SQL Server's tempdb limitations and real-world traffic patterns, things go sideways fast.

The practical takeaways:

1. Always read your generated SQL — What looks like efficient LINQ can become a monster query
2. Never trust local performance — Your dev machine can't simulate production load
3. Use keyset pagination for large datasets — Offset/FETCH doesn't scale
4. Project DTOs early — Reduce what flows through your pagination logic
5. Monitor tempdb usage — It's often the silent killer in SQL Server

Pagination bugs are insidious because they don't show up in development. They lurk until traffic hits critical mass, then surface as mysterious timeouts and memory errors. The next time you write .Skip().Take(), remember — your database is lying to you.

---