Pagination Patterns

Offset-Based (Skip/Take)

// Basit pagination
public async Task<PagedResult<Product>> GetProductsAsync(int page, int pageSize)
{
    var query = context.Products
        .Where(p => p.IsActive)
        .OrderBy(p => p.Name);

    var totalCount = await query.CountAsync();

    var items = await query
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();

    return new PagedResult<Product>
    {
        Items = items,
        TotalCount = totalCount,
        Page = page,
        PageSize = pageSize,
        TotalPages = (int)Math.Ceiling(totalCount / (double)pageSize)
    };
}

// Sonuç modeli
public class PagedResult<T>
{
    public List<T> Items { get; set; } = [];
    public int TotalCount { get; set; }
    public int Page { get; set; }
    public int PageSize { get; set; }
    public int TotalPages { get; set; }
    public bool HasPrevious => Page > 1;
    public bool HasNext => Page < TotalPages;
}

SQL Çıktısı (Offset)

-- Sayfa 5, 20 kayıt/sayfa
SELECT [p].[Id], [p].[Name], [p].[Price]
FROM [Products] AS [p]
WHERE [p].[IsActive] = 1
ORDER BY [p].[Name]
OFFSET 80 ROWS FETCH NEXT 20 ROWS ONLY;

-- Count sorgusu (ayrı çalışır)
SELECT COUNT(*)
FROM [Products] AS [p]
WHERE [p].[IsActive] = 1;

-- Sayfa 5, 20 kayıt/sayfa
SELECT p.id, p.name, p.price
FROM products AS p
WHERE p.is_active = TRUE
ORDER BY p.name
LIMIT 20 OFFSET 80;

-- Count sorgusu (ayrı çalışır)
SELECT COUNT(*)
FROM products AS p
WHERE p.is_active = TRUE;

Offset sorunu: OFFSET 1000000 → SQL Server 1M satır tarar ve atar! Büyük veri setlerinde yavaşlar.

Keyset (Cursor) Pagination — Performans İçin

// ✅ Büyük veri setlerinde çok daha hızlı
public async Task<List<Product>> GetProductsAfterAsync(int lastId, int pageSize)
{
    return await context.Products
        .Where(p => p.IsActive && p.Id > lastId)
        .OrderBy(p => p.Id)
        .Take(pageSize)
        .ToListAsync();
}

// Composite cursor (birden fazla sütun)
public async Task<List<Product>> GetProductsAfterAsync(
    DateTime lastDate, int lastId, int pageSize)
{
    return await context.Products
        .Where(p => p.CreatedAt > lastDate ||
                    (p.CreatedAt == lastDate && p.Id > lastId))
        .OrderBy(p => p.CreatedAt)
        .ThenBy(p => p.Id)
        .Take(pageSize)
        .ToListAsync();
}

SQL Çıktısı (Keyset)

-- lastId = 5000 sonrasını getir
SELECT TOP(20) [p].[Id], [p].[Name], [p].[Price]
FROM [Products] AS [p]
WHERE [p].[IsActive] = 1 AND [p].[Id] > 5000
ORDER BY [p].[Id];

-- lastId = 5000 sonrasını getir
SELECT p.id, p.name, p.price
FROM products AS p
WHERE p.is_active = TRUE AND p.id > 5000
ORDER BY p.id
LIMIT 20;

Karşılaştırma