I was looking for an universal approach for doing paging with LINQ to SQL. The main goal of the last hour of research and try and error was to find a more efficient way to do this job.
To now I was using a combination of Skip() and Take() to get only the records I need in addition to a Count(). The reason for calling the Count() method is that I need the total amount of records to calculate upcoming page numbers for extended pager scenarios (prev | 1 | 2 | 3 | 4 | next) as well as simpler scenarios (prev | next) since I have at least always to know whether the current page is the last one or not.
The main "problem" is that this combination results in two single requests fired to the database by LINQ to SQL, and I'm sure this isn't very efficient. But I didn't find an alternative without using a custom Stored Procedure and doing the job inside the database. If you know one, please don't hesitate to let me know about it.
So at the end of the last hour I decided to keep my old approach but putting it into an extension method, according to the DRY (don't repeat yourself) principle, which I broke before. I don't had to invent the weel new and could take the snippet posted by Rob Conery and customize it a bit.
The PagedList class
1: public class PagedList<T> : List<T>
2: {
3:
4: public PagedList(IQueryable<T> source, int index, int pageSize)
5: {
6:
7: index = index > 1 ? index - 1 : 0;
8: totalCount = source.Count();
9: itemsRemaining = totalCount - ((index * pageSize) + pageSize);
10:
11: if (itemsRemaining < 0)
12: {
13: index = totalCount/pageSize;
14: itemsRemaining = 0;
15: }
16:
17: AddRange(source.Skip(index * pageSize).Take(pageSize));
18:
19: }
20:
21: private readonly int itemsRemaining;
22: public int ItemsRemaining
23: {
24: get { return itemsRemaining; }
25: }
26:
27: private readonly int totalCount;
28: public int TotalCount
29: {
30: get { return totalCount; }
31: }
32:
33: }
It's a bit simpler as Rob's one. When you're retrieving paged data you always have to know 2 parameters: the page you are on (index) and the amount of records you want to display on this page (pageSize). With these two parameters you want to get your records and as additional information the amount of records remaining (for the reasons see above).
The extension method
1: public static class DataPaginationExtension
2: {
3: public static PagedList<T> ToPagedList<T>(this IQueryable<T> source,
4: int index, int pageSize)
5: {
6: return new PagedList<T>(source, index, pageSize);
7: }
8: }
This simple extension method is the key for a simple using. Just put it in a namespace which is linked to your data access classes.
Implementation sample
1: public partial class Test : System.Web.UI.Page
2: {
3: protected void Page_Load(object sender, EventArgs e)
4: {
5:
6: int index = int.Parse(Request.QueryString["Page"]);
7: int pageSize = 3;
8:
9: DatabaseDataContext database = new DatabaseDataContext();
10:
11: var result = (from a in database.Users
12: select a).ToPagedList(index, pageSize);
13:
14: foreach (var user in result)
15: {
16: Response.Write(user.Username + "<br />");
17: }
18:
19: Response.Write("<br>total: " + result.TotalCount + "verbleibend:" +
20: result.ItemsRemaining + "<br><br><hr>");
21: }
22: }
As you can see the implementation is very simple. Just use the method as any other method. It returns a List<> of your items. If you prefere an IQueryable<> instead of a list just customize it to your needs.
By the way: I have two server side ASP.NET paging controls working with ASP.NET MVC as well as with ASP.NET WebForms I wanted to blog about for months. I promise I will do that in the next weeks ;-).
