C# | Best Practices for Pagination using EF Core 8

Pagination is a crucial aspect of application development, especially when dealing with large datasets. Entity Framework (EF) Core 8 in C# provides powerful features for implementing efficient pagination. In this guide, we'll explore best practices for implementing pagination using EF Core 8, along with examples.

1. Use Skip and Take for Simple Pagination

EF Core provides the Skip and Take methods, which are essential for implementing pagination efficiently. Skip allows you to skip a specified number of rows, and Take limits the number of rows returned.

var pageNumber = 1;
var pageSize = 10;

var result = dbContext.YourEntity
    .OrderBy(e => e.SortingProperty)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToList();

In this example, pageNumber and pageSize determine the current page and the number of items per page, respectively.

2. Use AsNoTracking for Read-Only Operations

For read-only operations like fetching data for display purposes, consider using AsNoTracking to improve performance by avoiding the overhead of tracking changes.

var result = dbContext.YourEntity
    .AsNoTracking()
    .OrderBy(e => e.SortingProperty)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToList();

This is particularly useful when you don't intend to update or save changes to the entities retrieved.

3. Leverage Indexed Columns for Sorting

Ensure that the columns used for sorting are indexed. Indexed columns significantly improve the performance of sorting operations.

// Ensure SortingProperty is indexed
modelBuilder.Entity<YourEntity>()
    .HasIndex(e => e.SortingProperty);

Efficiently indexed columns will accelerate sorting and enhance overall pagination performance.

4. Use Count for Total Record Count

To determine the total number of records without fetching all data, use Count before applying pagination. This avoids loading unnecessary data.

var totalRecords = dbContext.YourEntity.Count();
var result = dbContext.YourEntity
    .OrderBy(e => e.SortingProperty)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToList();

5. Handle Concurrent Modifications with Take and Skip

Be cautious when using Skip and Take for pagination in scenarios where data can be concurrently modified. In such cases, consider using alternative methods like keyset pagination for better consistency.

What Next?

Implementing pagination efficiently is crucial for enhancing the performance of applications dealing with large datasets. By following these best practices, you can ensure that your pagination logic is optimized and scalable when using EF Core 8 in C#.

Comments

[John V]

One of the problems with the OrderBy discussion is that EF adds its own set of OrderBy on top of yours.

[Hassan BOLAJRAF]

You're right :)
Entity Framework can sometimes introduce additional OrderBy clauses, especially when dealing with complex queries or when eager loading related entities. This behavior occurs because EF tries to ensure consistency in the result set, particularly with pagination scenarios or when using certain LINQ methods like FirstOrDefault or Skip/Take.
To avoid unintended ordering, it's a good practice to explicitly define your OrderBy clauses at the right stage of the query and be aware of any implicit ordering EF might apply. If necessary, using .AsEnumerable() or .ToList() before applying your OrderBy can help ensure that sorting happens in-memory, free from any EF intervention.