</>ehnCRAFT
ASP.NET Core

Cursor Pagination

Complete guide to implementing efficient cursor-based pagination in C# with practical examples, best practices, and real-world use cases.

https://learn.microsoft.com/en-us/ef/core/

What is Cursor Pagination?

Cursor pagination is a powerful technique that uses a pointer (cursor) to track position in a dataset, rather than using page numbers or offsets. Unlike traditional offset-based pagination, cursor pagination provides better performance, consistency, and scalability when dealing with frequently changing data.

When you paginate with cursors, the system:

  1. Uses a unique identifier (ID, timestamp, or composite key) as a reference point
  2. Fetches the next set of results starting from that cursor
  3. Returns a new cursor for the next batch of data

Critical advantage: Cursor pagination maintains stable references even as data changes, preventing duplicate or missing items between requests.

Why Use Cursor Pagination?

1. Superior Performance

Traditional offset-based pagination (using SKIP and TAKE) degrades significantly as you paginate deeper. The database must scan and skip all previous records, making page 1000 much slower than page 1.

2. Consistent Results

If data is added or removed between requests, offset pagination can cause users to see duplicate items or miss items entirely. Cursor pagination maintains position regardless of data changes.

3. Scalability

Deep pagination with offsets puts unnecessary load on database resources. Cursor pagination uses indexed lookups (WHERE id > cursor) which remain fast regardless of dataset size.

4. Better User Experience

Ideal for infinite scroll, real-time feeds, and mobile applications where users continuously load more content without explicit page navigation.

5. Efficient Resource Usage

Queries execute consistently fast whether fetching the first page or the millionth, reducing server load and improving response times.

6. Handles Dynamic Data

Perfect for scenarios where data is frequently added, updated, or deleted, such as social media feeds, live dashboards, or streaming applications.

How Cursor Pagination Works

Basic Concept

Imagine you have a products table with 1 million records:

Traditional Offset Pagination (Inefficient):

-- Page 1: Fast
SELECT * FROM Products ORDER BY Id OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

-- Page 100: Slow - must skip 1,980 rows
SELECT * FROM Products ORDER BY Id OFFSET 1980 ROWS FETCH NEXT 20 ROWS ONLY;

-- Page 10,000: Very slow - must skip 199,980 rows
SELECT * FROM Products ORDER BY Id OFFSET 199980 ROWS FETCH NEXT 20 ROWS ONLY;

Cursor Pagination (Efficient):

-- First page: Fast
SELECT TOP 20 * FROM Products ORDER BY Id;
-- Returns cursor: last item's Id = 20

-- Next page: Still fast - uses index
SELECT TOP 20 * FROM Products WHERE Id > 20 ORDER BY Id;
-- Returns cursor: last item's Id = 40

-- Any page: Always fast - direct index lookup
SELECT TOP 20 * FROM Products WHERE Id > 199980 ORDER BY Id;

The difference: Every cursor pagination query is as fast as the first page because it uses indexed WHERE clauses instead of expensive OFFSET operations.

Core Concepts

The Cursor

A cursor is a reference point that marks your position in a dataset. Common cursor types include:

  • Simple ID cursor - Single integer or GUID: "12345"
  • Timestamp cursor - ISO datetime: "2025-01-15T10:30:00Z"
  • Composite cursor - Multiple fields: { "timestamp": "2025-01-15T10:30:00Z", "id": 12345 }
  • Encoded cursor - Base64-encoded JSON: "eyJ0aW1lc3RhbXAiOiIyMDI1LTAxLTE1VDEwOjMwOjAwWiIsImlkIjoxMjM0NX0="

Best practice: Use composite cursors (timestamp + ID) for tie-breaking when multiple records share the same timestamp.

Forward and Backward Pagination

Cursor pagination supports bidirectional navigation:

Forward Pagination:

WHERE (CreatedAt > cursor.Timestamp) 
   OR (CreatedAt = cursor.Timestamp AND Id > cursor.Id)
ORDER BY CreatedAt ASC, Id ASC

Backward Pagination:

WHERE (CreatedAt < cursor.Timestamp) 
   OR (CreatedAt = cursor.Timestamp AND Id < cursor.Id)
ORDER BY CreatedAt DESC, Id DESC

Cursor Encoding

Always encode cursors to prevent tampering and simplify API design:

public class TimestampCursor
{
    public DateTime Timestamp { get; set; }
    public int Id { get; set; }

    public string Encode()
    {
        var json = JsonSerializer.Serialize(this);
        return Convert.ToBase64String(Encoding.UTF8.GetBytes(json));
    }

    public static TimestampCursor? Decode(string cursor)
    {
        try
        {
            var json = Encoding.UTF8.GetString(Convert.FromBase64String(cursor));
            return JsonSerializer.Deserialize<TimestampCursor>(json);
        }
        catch
        {
            return null;
        }
    }
}

Common Cursor Pagination Workflows

1. Simple ID-Based Pagination (Entity Framework Core)

The most straightforward approach using auto-incrementing IDs:

public class ProductService
{
    private readonly ApplicationDbContext _context;

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

    public async Task<CursorPageResponse<Product>> GetProductsAsync(
        CursorPageRequest request)
    {
        var query = _context.Products.AsQueryable();

        if (!string.IsNullOrEmpty(request.Cursor))
        {
            if (int.TryParse(request.Cursor, out int cursorId))
            {
                if (request.Forward)
                {
                    query = query.Where(p => p.Id > cursorId);
                }
                else
                {
                    query = query.Where(p => p.Id < cursorId);
                }
            }
        }

        // Order by ID
        query = request.Forward 
            ? query.OrderBy(p => p.Id) 
            : query.OrderByDescending(p => p.Id);

        // Fetch one extra item to check if there's a next page
        var items = await query
            .Take(request.PageSize + 1)
            .ToListAsync();

        var hasMore = items.Count > request.PageSize;
        if (hasMore)
        {
            items = items.Take(request.PageSize).ToList();
        }

        // If we were going backward, reverse the items
        if (!request.Forward)
        {
            items.Reverse();
        }

        return new CursorPageResponse<Product>
        {
            Items = items,
            NextCursor = items.Any() ? items.Last().Id.ToString() : null,
            PreviousCursor = items.Any() ? items.First().Id.ToString() : null,
            HasNextPage = request.Forward && hasMore,
            HasPreviousPage = !request.Forward && hasMore,
            TotalRetrieved = items.Count
        };
    }
}

What happens:

  • Fetches PageSize + 1 items to detect if more pages exist
  • Uses indexed WHERE clause (Id > cursor) for fast lookups
  • Reverses results when paginating backward
  • Returns cursors for both directions

2. Timestamp-Based Pagination

For scenarios where ordering by creation time is more appropriate:

public async Task<CursorPageResponse<Product>> GetProductsByTimeAsync(
    CursorPageRequest request)
{
    var query = _context.Products.AsQueryable();

    if (!string.IsNullOrEmpty(request.Cursor))
    {
        var cursor = TimestampCursor.Decode(request.Cursor);
        if (cursor != null)
        {
            if (request.Forward)
            {
                query = query.Where(p => 
                    p.CreatedAt > cursor.Timestamp ||
                    (p.CreatedAt == cursor.Timestamp && p.Id > cursor.Id));
            }
            else
            {
                query = query.Where(p => 
                    p.CreatedAt < cursor.Timestamp ||
                    (p.CreatedAt == cursor.Timestamp && p.Id < cursor.Id));
            }
        }
    }

    query = request.Forward
        ? query.OrderBy(p => p.CreatedAt).ThenBy(p => p.Id)
        : query.OrderByDescending(p => p.CreatedAt).ThenByDescending(p => p.Id);

    var items = await query
        .Take(request.PageSize + 1)
        .ToListAsync();

    var hasMore = items.Count > request.PageSize;
    if (hasMore)
    {
        items = items.Take(request.PageSize).ToList();
    }

    if (!request.Forward)
    {
        items.Reverse();
    }

    string? CreateCursor(Product product) =>
        new TimestampCursor 
        { 
            Timestamp = product.CreatedAt, 
            Id = product.Id 
        }.Encode();

    return new CursorPageResponse<Product>
    {
        Items = items,
        NextCursor = items.Any() ? CreateCursor(items.Last()) : null,
        PreviousCursor = items.Any() ? CreateCursor(items.First()) : null,
        HasNextPage = request.Forward && hasMore,
        HasPreviousPage = !request.Forward && hasMore,
        TotalRetrieved = items.Count
    };
}

Key difference: Uses composite cursor (timestamp + ID) to handle records with identical timestamps.

3. High-Performance Pagination with Dapper

For maximum performance and control, Dapper provides direct SQL access:

public class ProductRepository
{
    private readonly IDbConnection _connection;

    public ProductRepository(IDbConnection connection)
    {
        _connection = connection;
    }

    public async Task<CursorPageResponse<Product>> GetProductsWithDapperAsync(
        CursorPageRequest request)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@PageSize", request.PageSize + 1);

        string cursorCondition = string.Empty;
        string orderBy = request.Forward ? "ASC" : "DESC";

        if (!string.IsNullOrEmpty(request.Cursor))
        {
            if (int.TryParse(request.Cursor, out int cursorId))
            {
                cursorCondition = request.Forward 
                    ? "WHERE Id > @CursorId" 
                    : "WHERE Id < @CursorId";
                parameters.Add("@CursorId", cursorId);
            }
        }

        var sql = $@"
            SELECT TOP (@PageSize)
                Id,
                Name,
                Price,
                CreatedAt,
                UpdatedAt
            FROM Products
            {cursorCondition}
            ORDER BY Id {orderBy}";

        var items = (await _connection.QueryAsync<Product>(sql, parameters))
            .ToList();

        var hasMore = items.Count > request.PageSize;
        if (hasMore)
        {
            items = items.Take(request.PageSize).ToList();
        }

        if (!request.Forward)
        {
            items.Reverse();
        }

        return new CursorPageResponse<Product>
        {
            Items = items,
            NextCursor = items.Any() ? items.Last().Id.ToString() : null,
            PreviousCursor = items.Any() ? items.First().Id.ToString() : null,
            HasNextPage = request.Forward && hasMore,
            HasPreviousPage = !request.Forward && hasMore,
            TotalRetrieved = items.Count
        };
    }

    public async Task<CursorPageResponse<Product>> GetProductsByTimestampDapperAsync(
        CursorPageRequest request)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@PageSize", request.PageSize + 1);

        string cursorCondition = string.Empty;
        string orderBy = request.Forward 
            ? "ORDER BY CreatedAt ASC, Id ASC" 
            : "ORDER BY CreatedAt DESC, Id DESC";

        if (!string.IsNullOrEmpty(request.Cursor))
        {
            var cursor = TimestampCursor.Decode(request.Cursor);
            if (cursor != null)
            {
                if (request.Forward)
                {
                    cursorCondition = @"
                        WHERE (CreatedAt > @CursorTimestamp) 
                           OR (CreatedAt = @CursorTimestamp AND Id > @CursorId)";
                }
                else
                {
                    cursorCondition = @"
                        WHERE (CreatedAt < @CursorTimestamp) 
                           OR (CreatedAt = @CursorTimestamp AND Id < @CursorId)";
                }
                
                parameters.Add("@CursorTimestamp", cursor.Timestamp);
                parameters.Add("@CursorId", cursor.Id);
            }
        }

        var sql = $@"
            SELECT TOP (@PageSize)
                Id,
                Name,
                Price,
                CreatedAt,
                UpdatedAt
            FROM Products
            {cursorCondition}
            {orderBy}";

        var items = (await _connection.QueryAsync<Product>(sql, parameters))
            .ToList();

        var hasMore = items.Count > request.PageSize;
        if (hasMore)
        {
            items = items.Take(request.PageSize).ToList();
        }

        if (!request.Forward)
        {
            items.Reverse();
        }

        string? CreateCursor(Product product) =>
            new TimestampCursor 
            { 
                Timestamp = product.CreatedAt, 
                Id = product.Id 
            }.Encode();

        return new CursorPageResponse<Product>
        {
            Items = items,
            NextCursor = items.Any() ? CreateCursor(items.Last()) : null,
            PreviousCursor = items.Any() ? CreateCursor(items.First()) : null,
            HasNextPage = request.Forward && hasMore,
            HasPreviousPage = !request.Forward && hasMore,
            TotalRetrieved = items.Count
        };
    }
}

Performance benefit: Dapper executes raw SQL with minimal overhead, typically 30-50% faster than EF Core for read operations.

4. Complex Filtering with Dapper

Build dynamic queries with multiple filters:

public async Task<CursorPageResponse<Product>> GetProductsWithFiltersDapperAsync(
    CursorPageRequest request,
    string? searchTerm = null,
    decimal? minPrice = null,
    decimal? maxPrice = null,
    int? categoryId = null)
{
    var parameters = new DynamicParameters();
    parameters.Add("@PageSize", request.PageSize + 1);

    var conditions = new List<string>();

    if (!string.IsNullOrEmpty(searchTerm))
    {
        conditions.Add("Name LIKE @SearchTerm");
        parameters.Add("@SearchTerm", $"%{searchTerm}%");
    }

    if (minPrice.HasValue)
    {
        conditions.Add("Price >= @MinPrice");
        parameters.Add("@MinPrice", minPrice.Value);
    }

    if (maxPrice.HasValue)
    {
        conditions.Add("Price <= @MaxPrice");
        parameters.Add("@MaxPrice", maxPrice.Value);
    }

    if (categoryId.HasValue)
    {
        conditions.Add("CategoryId = @CategoryId");
        parameters.Add("@CategoryId", categoryId.Value);
    }

    if (!string.IsNullOrEmpty(request.Cursor))
    {
        var cursor = TimestampCursor.Decode(request.Cursor);
        if (cursor != null)
        {
            if (request.Forward)
            {
                conditions.Add(
                    "(CreatedAt > @CursorTimestamp OR " +
                    "(CreatedAt = @CursorTimestamp AND Id > @CursorId))");
            }
            else
            {
                conditions.Add(
                    "(CreatedAt < @CursorTimestamp OR " +
                    "(CreatedAt = @CursorTimestamp AND Id < @CursorId))");
            }
            
            parameters.Add("@CursorTimestamp", cursor.Timestamp);
            parameters.Add("@CursorId", cursor.Id);
        }
    }

    var whereClause = conditions.Any() 
        ? "WHERE " + string.Join(" AND ", conditions) 
        : string.Empty;

    var orderBy = request.Forward 
        ? "ORDER BY CreatedAt ASC, Id ASC" 
        : "ORDER BY CreatedAt DESC, Id DESC";

    var sql = $@"
        SELECT TOP (@PageSize)
            Id,
            Name,
            Price,
            CategoryId,
            CreatedAt,
            UpdatedAt
        FROM Products
        {whereClause}
        {orderBy}";

    var items = (await _connection.QueryAsync<Product>(sql, parameters))
        .ToList();

    return BuildPageResponse(items, request);
}

Dapper vs Entity Framework Core

Performance Comparison

public class PerformanceBenchmark
{
    private readonly IDbConnection _connection;
    private readonly ApplicationDbContext _context;

    [Benchmark]
    public async Task<List<Product>> EFCore_CursorPagination()
    {
        var result = await _context.Products
            .Where(p => p.Id > 1000)
            .OrderBy(p => p.Id)
            .Take(20)
            .ToListAsync();
        
        return result;
    }

    [Benchmark]
    public async Task<List<Product>> Dapper_CursorPagination()
    {
        var sql = @"
            SELECT TOP 20
                Id, Name, Price, CreatedAt, UpdatedAt
            FROM Products
            WHERE Id > @CursorId
            ORDER BY Id ASC";

        var result = await _connection.QueryAsync<Product>(
            sql, 
            new { CursorId = 1000 });
        
        return result.ToList();
    }
}

When to Choose Dapper

Use Dapper when:

  • Maximum performance is critical (high-throughput APIs)
  • You need fine-grained control over SQL queries
  • Working with legacy databases or complex queries
  • Dealing with stored procedures
  • Database-specific optimizations are required
  • Read-heavy workloads with minimal updates

Use Entity Framework Core when:

  • Rapid development is prioritized
  • You want strong typing and IntelliSense
  • Change tracking is beneficial
  • Working with complex domain models
  • Team is more familiar with LINQ
  • Write-heavy workloads with complex relationships

Important Rules and Best Practices

The Golden Rule of Cursor Pagination

Always use indexed columns as cursor fields to maintain performance.

Without proper indexes, cursor pagination loses its performance advantage and may even be slower than offset pagination.

Required Database Indexes

CREATE INDEX IX_Products_Id ON Products(Id);

CREATE INDEX IX_Products_CreatedAt_Id ON Products(CreatedAt, Id);

CREATE INDEX IX_Products_Category_CreatedAt_Id 
ON Products(CategoryId, CreatedAt, Id);

CREATE INDEX IX_Products_Name_CreatedAt_Id 
ON Products(Name, CreatedAt, Id);

When to Use Cursor Pagination

✅ Infinite scroll interfaces
✅ Real-time feeds and timelines ✅ Mobile applications
✅ Large datasets ✅ Frequently changing data
✅ Performance-critical APIs
✅ Streaming data applications
✅ Activity logs and audit trails

When to Use Offset Pagination

✅ Small, static datasets ✅ Traditional page-based navigation ✅ Need to show total page count
✅ Users need to jump to arbitrary pages
✅ Administrative interfaces
✅ Reports with known data boundaries
✅ Data exports or batch processing

Advanced Techniques

Multi-Mapping with Dapper

Join related tables while maintaining cursor pagination:

public class ProductWithCategory
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public DateTime CreatedAt { get; set; }
    public Category Category { get; set; }
}

public async Task<CursorPageResponse<ProductWithCategory>> 
    GetProductsWithCategoryAsync(CursorPageRequest request)
{
    var parameters = new DynamicParameters();
    parameters.Add("@PageSize", request.PageSize + 1);

    string cursorCondition = string.Empty;
    if (!string.IsNullOrEmpty(request.Cursor))
    {
        var cursor = TimestampCursor.Decode(request.Cursor);
        if (cursor != null)
        {
            cursorCondition = request.Forward
                ? "WHERE (p.CreatedAt > @CursorTimestamp OR " +
                  "(p.CreatedAt = @CursorTimestamp AND p.Id > @CursorId))"
                : "WHERE (p.CreatedAt < @CursorTimestamp OR " +
                  "(p.CreatedAt = @CursorTimestamp AND p.Id < @CursorId))";
            
            parameters.Add("@CursorTimestamp", cursor.Timestamp);
            parameters.Add("@CursorId", cursor.Id);
        }
    }

    var orderBy = request.Forward 
        ? "ORDER BY p.CreatedAt ASC, p.Id ASC" 
        : "ORDER BY p.CreatedAt DESC, p.Id DESC";

    var sql = $@"
        SELECT TOP (@PageSize)
            p.Id,
            p.Name,
            p.Price,
            p.CreatedAt,
            c.Id,
            c.Name
        FROM Products p
        INNER JOIN Categories c ON p.CategoryId = c.Id
        {cursorCondition}
        {orderBy}";

    var items = (await _connection.QueryAsync<ProductWithCategory, Category, ProductWithCategory>(
        sql,
        (product, category) =>
        {
            product.Category = category;
            return product;
        },
        parameters,
        splitOn: "Id"))
        .ToList();

    return BuildPageResponse(items, request);
}

Compiled Queries for EF Core

Optimize frequently-executed queries:

private static readonly Func<ApplicationDbContext, int, int, Task<List<Product>>> 
    _compiledQuery = EF.CompileAsyncQuery(
        (ApplicationDbContext context, int cursorId, int pageSize) =>
            context.Products
                .Where(p => p.Id > cursorId)
                .OrderBy(p => p.Id)
                .Take(pageSize)
                .ToList()
    );

public async Task<List<Product>> GetProductsOptimizedAsync(int cursorId, int pageSize)
{
    return await _compiledQuery(_context, cursorId, pageSize);
}

Cursor Validation

Always validate cursor input to prevent errors:

public class CursorValidator
{
    public static bool IsValidCursor(string cursor)
    {
        if (string.IsNullOrEmpty(cursor)) return true;
        
        try
        {
            var decoded = TimestampCursor.Decode(cursor);
            return decoded != null && 
                   decoded.Timestamp <= DateTime.UtcNow &&
                   decoded.Id > 0;
        }
        catch
        {
            return false;
        }
    }
}

Pagination Settings

Enforce reasonable limits:

public class PaginationSettings
{
    public int DefaultPageSize { get; set; } = 20;
    public int MinPageSize { get; set; } = 1;
    public int MaxPageSize { get; set; } = 100;

    public int NormalizePageSize(int requestedSize)
    {
        if (requestedSize < MinPageSize) return MinPageSize;
        if (requestedSize > MaxPageSize) return MaxPageSize;
        return requestedSize;
    }
}

Real-World Use Cases

1. Infinite Scroll Frontend

let currentCursor = null;
let isLoading = false;

async function loadMoreProducts() {
    if (isLoading) return;
    
    isLoading = true;
    const response = await fetch(
        `/api/products?cursor=${currentCursor || ''}&pageSize=20`
    );
    const data = await response.json();
    
    data.items.forEach(item => appendProductToList(item));
    
    currentCursor = data.nextCursor;
    
    if (!data.hasNextPage) {
        disableInfiniteScroll();
    }
    
    isLoading = false;
}

window.addEventListener('scroll', () => {
    if (isNearBottom() && currentCursor) {
        loadMoreProducts();
    }
});

2. ASP.NET Core API Endpoint

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

    [HttpGet]
    [ResponseCache(Duration = 60, VaryByQueryKeys = new[] { "cursor", "pageSize" })]
    public async Task<ActionResult<CursorPageResponse<Product>>> GetProducts(
        [FromQuery] string? cursor,
        [FromQuery] int pageSize = 20,
        [FromQuery] bool forward = true)
    {
        if (pageSize < 1 || pageSize > 100)
        {
            return BadRequest("Page size must be between 1 and 100");
        }

        var request = new CursorPageRequest
        {
            Cursor = cursor,
            PageSize = pageSize,
            Forward = forward
        };

        var result = await _repository.GetProductsByTimestampDapperAsync(request);
        return Ok(result);
    }
}

3. GraphQL Integration

public class ProductQueries
{
    [UseDbContext(typeof(ApplicationDbContext))]
    [UsePaging]
    [UseFiltering]
    [UseSorting]
    public IQueryable<Product> GetProducts(
        [ScopedService] ApplicationDbContext context)
    {
        return context.Products;
    }
}

Testing Cursor Pagination

public class CursorPaginationTests
{
    [Fact]
    public async Task GetProducts_WithCursor_ReturnsCorrectPage()
    {
        var context = GetInMemoryContext();
        await SeedProducts(context, 50);
        var service = new ProductService(context);
        
        var firstPage = await service.GetProductsAsync(new CursorPageRequest 
        { 
            PageSize = 10 
        });
        
        Assert.Equal(10, firstPage.Items.Count);
        Assert.True(firstPage.HasNextPage);
        Assert.NotNull(firstPage.NextCursor);
        
        var secondPage = await service.GetProductsAsync(new CursorPageRequest 
        { 
            Cursor = firstPage.NextCursor,
            PageSize = 10 
        });
        
        Assert.Equal(10, secondPage.Items.Count);
        Assert.True(secondPage.HasNextPage);
        
        var firstPageIds = firstPage.Items.Select(p => p.Id).ToHashSet();
        var secondPageIds = secondPage.Items.Select(p => p.Id).ToHashSet();
        Assert.Empty(firstPageIds.Intersect(secondPageIds));
    }

    [Fact]
    public async Task GetProducts_WithDataChanges_MaintainsConsistency()
    {
        var context = GetInMemoryContext();
        await SeedProducts(context, 30);
        var service = new ProductService(context);
        
        var firstPage = await service.GetProductsAsync(new CursorPageRequest 
        { 
            PageSize = 10 
        });
        
        await AddNewProducts(context, 5);
        
        var secondPage = await service.GetProductsAsync(new CursorPageRequest 
        { 
            Cursor = firstPage.NextCursor,
            PageSize = 10 
        });
        
        var firstPageIds = firstPage.Items.Select(p => p.Id).ToHashSet();
        var secondPageIds = secondPage.Items.Select(p => p.Id).ToHashSet();
        Assert.Empty(firstPageIds.Intersect(secondPageIds));
    }
}

Dependency Injection

Dapper Configuration

public void ConfigureServices(IServiceCollection services)
{
    services.AddScoped<IDbConnection>(sp =>
    {
        var configuration = sp.GetRequiredService<IConfiguration>();
        var connectionString = configuration.GetConnectionString("DefaultConnection");
        return new SqlConnection(connectionString);
    });

    services.AddScoped<IDbConnectionFactory, SqlConnectionFactory>();
    services.AddScoped<ProductRepository>();
}

public interface IDbConnectionFactory
{
    IDbConnection CreateConnection();
}

public class SqlConnectionFactory : IDbConnectionFactory
{
    private readonly string _connectionString;

    public SqlConnectionFactory(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    public IDbConnection CreateConnection()
    {
        return new SqlConnection(_connectionString);
    }
}

Common Pitfalls and Solutions

1. Missing Indexes

Problem: Cursor pagination is slow despite correct implementation.

Solution: Ensure composite indexes exist on cursor fields:

-- Wrong: Single column index
CREATE INDEX IX_Products_CreatedAt ON Products(CreatedAt);

-- Correct: Composite index with tie-breaker
CREATE INDEX IX_Products_CreatedAt_Id ON Products(CreatedAt, Id);

2. Incorrect Sorting Direction

Problem: Items appear in wrong order when paginating backward.

Solution: Remember to reverse items when going backward:

if (!request.Forward)
{
    items.Reverse();
}

3. Not Handling Tie-Breakers

Problem: Duplicate or missing items when multiple records share the same timestamp.

Solution: Always use composite cursors:

// Include ID as tie-breaker
WHERE (CreatedAt > cursor.Timestamp) 
   OR (CreatedAt = cursor.Timestamp AND Id > cursor.Id)

4. Exposing Internal IDs

Problem: Raw IDs in cursors leak database structure.

Solution: Always encode cursors:

// Encode cursor before returning
var encoded = Convert.ToBase64String(
    Encoding.UTF8.GetBytes(JsonSerializer.Serialize(cursor))
);

Quick Reference

// Basic Entity Framework Core pattern
var query = _context.Products
    .Where(p => p.Id > cursorId)
    .OrderBy(p => p.Id)
    .Take(pageSize + 1);

// Basic Dapper pattern
var sql = @"
    SELECT TOP (@PageSize)
        Id, Name, Price
    FROM Products
    WHERE Id > @CursorId
    ORDER BY Id ASC";

// Composite cursor pattern
WHERE (CreatedAt > @Timestamp) 
   OR (CreatedAt = @Timestamp AND Id > @Id)
ORDER BY CreatedAt ASC, Id ASC

// Required index pattern
CREATE INDEX IX_Table_SortColumn_TieBreaker 
ON TableName(SortColumn, TieBreakerColumn);

Conclusion

Cursor pagination is an essential technique for building scalable, performant applications in C#. By using cursors instead of offsets, you provide a better user experience with faster queries, consistent results, and efficient resource usage.

Key takeaways:

  • Cursor pagination scales infinitely better than offset-based approaches
  • Proper composite indexes are crucial for maintaining performance
  • Encode cursors to prevent tampering and simplify API design
  • Dapper provides 30-50% performance improvement over EF Core for read-heavy workloads
  • Always validate cursor input and handle edge cases
  • Choose the right pagination strategy based on your specific use case

Additional Resources

Installation

Get started with Nuxt UI documentation template.