LINQ Interview Question #122

How should pagination LINQ queries be optimized for large data sets?

LINQ Performance, EF Core LINQ, Expressions & Advanced Queries Senior Advanced

Quick Interview Answer

Offset pagination with Skip and Take is simple, but very large offsets can become increasingly expensive because the database may still need to process skipped rows.

Detailed Explanation

Offset pagination with Skip and Take is simple, but very large offsets can become increasingly expensive because the database may still need to process skipped rows.

For large or frequently traversed data sets, keyset or seek pagination can be more efficient. It filters from the last seen ordered key instead of skipping an ever-growing number of rows.

Code Example

// Keyset-style example:
var page = await db.Products
    .Where(p => p.Id > lastSeenId)
    .OrderBy(p => p.Id)
    .Take(pageSize)
    .ToListAsync();