Paging Data with LINQ to SQL

In this article, I’ll be discussing how to implement the ability to page data with LINQ to SQL using an extension method. When dealing with large amounts of data, it will help your users and your applications performance to only retrieve and display small portions of a large amount of data. It’s also a good user interface idea to permit users to easily move from one page to the next and to allow them to select the amount of data to view at once. Unfortunately, this hasn’t been implemented well in a lot of code I’ve seen. The nice thing about LINQ is that it offers a very easy way to do it.

To make things even easier, we’ll be setting this up as an extension to IQueryable, the interface that LINQ to SQL is based upon. Remember that as with any extension, you’ll need to define the method as static and use the  ‘this’ keyword. Here’s the code…

public static IQueryable<TSource> Page<TSource>(this IQueryable<TSource> source,
int currentPage, int pageSize)
{
    return source.Skip((currentPage - 1) * pageSize).Take(pageSize);
}

Simple, isn’t it. As you can see, we pass in the current page and the page size. The Skip method is used to skip over records up to our calculated starting point. Then, the Take method is used to retrieve our specified number of records.

However, there is another function that will prove useful. We need a way to get the total number of pages. Here’s the code…

public static int PageCount<TSource>(this IQueryable<TSource> source, int pageSize)
{
    return (int)Math.Ceiling((decimal)source.Count() / (decimal)pageSize);
}

This method returns the number of pages present based on the provide page size. We use the Math.Ceiling method to get the smallest integer that’s greater than or equal to the number of records divided by the page size. Remember when you call this function that you want to use the full dataset and not the page query instance. You can use this value to validate movement within the data, such as with a previous/next button or page number selection, and to provide user feedback, such as Page X of Y displays. If you want, you could extend this even further with another function that provided a list of page numbers.