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 feasible because it would take too much time.

Pagination allows us to split the results into manageable chunks. This enhances 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 easy pagination.

For example, if we have a Products table with 100 records and want to display 10 per page, the second page can 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

Let’s suppose we want to paginate a list of clients 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);
    }
}

Avoid Skip on large offsets

In tables with many (millions) of records, Skip() can be slow. This is because Skip has to traverse all previous records before skipping 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();