Pagination in Entity Framework using `Skip` and `Take`

In Entity Framework, we can implement pagination easily using the Skip() and Take() methods.

In applications that handle large volumes of data, retrieving all records from a database in a single query may not be viable because it would take too much time.

Pagination allows us to split the results into manageable chunks. This improves the user experience and also reduces resource consumption.

What are Skip and Take?

These methods belong to LINQ and are used to:

Together, they allow for very simple pagination implementation.

For example, if we have a Products table with 100 records and we want to show 10 per page, the second page would be obtained with:

var page2 = dbContext.Products
    .OrderBy(p => c.Id) 
    .Skip(10)  // Skips the first 10
    .Take(10)  // Takes the next 10
    .ToList();

Basic pagination implementation

Suppose we want to paginate a list of customers in a web application. The code would be:

public List<Client> GetPaginatedClients(int page, int recordsPerPage)
{
    using (var context = new AppDbContext())
    {
        return context.Clients
            .OrderBy(c => c.Name)  // Important to sort!
            .Skip((page - 1) * recordsPerPage)
            .Take(recordsPerPage)
            .ToList();
    }
}

public (List<Client> Data, int TotalPages) GetPaginatedClients(int page, int perPage)
{
    using (var context = new AppDbContext())
    {
        var total = context.Clients.Count();
				var totalPages = (int)Math.Ceiling(total / (double)perPage);
				
        var data = context.Clients
            .OrderBy(c => c.Name)
            .Skip((page - 1) * perPage)
            .Take(perPage)
            .ToList();

        return (data, totalPages);
    }
}

Avoiding Skip for large offsets

In tables with many (millions) of records, Skip() can be slow. This is because Skip must go through all the previous records before omitting them.

For example, if we are paginating products by their ID:

int lastId = 10; // Last ID from the previous page
int pageSize = 10;

var products = context.Products
    .Where(p => p.Id > lastId)
    .OrderBy(p => p.Id)
    .Take(pageSize)
    .ToList();