A couple new projects I've been working on have given me my first sizable projects for which I have used ASP.NET MVC 2 (link) with ASP.NET/C# 4.0, as well as LINQ to SQL (link). I have been very pleased with my experience with all of the frameworks involved, and will certainly be using them as much as possible moving forward. I've also got a fairly large number of blog posts coming out of all the things I've learned, tools I've found/created, and reusable code I've put together. Today, I will cover a reusable class I created to facilitate paging result sets.
The PagedSequence class (PagedSequence.zip) accepts an IQueryable<T> source, and handles the details of paging that data for display across multiple web pages, for example. In keeping with the DRY principle (Don't Repeat Yourself), I endeavored to assure the class is as flexible as possible. I'll start with the primary constructor:
1: public PagedSequence(IQueryable <T> input, int pageIndex,
2: int totalItems, string linkFormat, int pageSize) {
3: PageIndex = pageIndex;
4: LinkFormat = linkFormat;
5: PageSize = pageSize;
6: TotalItemCount = totalItems;
7: if (TotalItemCount > 0) {
8: TotalPages = (int )Math .Ceiling(TotalItemCount / (double )PageSize);
9: } else {
10: TotalPages = totalItems;
11: }
12: AsQueryable = input.Skip(pageIndex * PageSize).Take(PageSize);
13: ItemCount = AsQueryable.Count();
14: }
AsQueryable is an IQueryable<T> public property, and would be the primary method of getting the data back for display in an MVC View or a Web Form. Overloaded constructors pass input.Count() for the totalItems parameter, and pass an int const value for pageSize. linkFormat is a bit of bad behavior on my part to facilitate the paging links in the View; I will cover that later. The important part, though, is that it has no effect whatsoever on the behavior of PagedSequence. The 'input' value should be the entire result set you want to list; the entire Customer list, perhaps; or even the results of a user-entered search. The calls to Skip() and Take() are what do the work of getting just the paged data you are looking for. Note that your input value must already be sorted.
LINQ to SQL Deferred Queries
The fantastic thing about LINQ to SQL/Entity Framework here is that queries are deferred until the sequence is iterated, or a non-deferred operator is called. I mentioned overloaded constructors automatically passing input.Count() in for the parameter keeping track of the total items. This property is meant to have the total size of the result set - not just the number of items being displayed on the particular page. The beauty of deferred execution is that the call to input.Count() does not cause the whole result set to be queried to the database; Likewise, the call at the end of the constructor to AsQueryable.Count() does not cause the whole query to be executed; just one that grabs the size of the current page. (it could be less than the pageSize value if there are fewer total records than that, or if you are on the last page of data).
In addition to the AsQueryable property, there is also an AsList property, and a Select() method, which accepts a delegate for a selector function. AsList causes the query to be built and executed, then returned as a List<T>. AsQueryable lets you perform additional chaining of LINQ operators. Finally, the Select() method is simply a shortcut for calling AsQueryable.Select(). This allows some additional magic, particularly enabling you to shape the returned data so that, for example, you are not passing large varbinary or nvarchar(MAX)data back and forth across the network between the web and db servers, needlessly - and letting you even do it in the View, if you like. Pardon me while I duck some slings/arrows from the MVC purists. (I actually welcome discussion on that particular topic, as I do feel like I'm taking a cheap shortcut, a little bit. Although, one that works very well for me.)
Using the PagedSequence to Paginate Your Data
So, how about an example; this represents creation of a PagedSequence in a controller action method:
1: var users = new PagedSequence<User>(rep.GetAllUsers(), pageIndex );
2: return View(users);
The rep.GetAllUsers() call is being made on a instance of the User Repository class. That method returns an IQueryable<User> already sorted. This is a minimalistic call to the constructor, causing the default page size to be used and getting the size as noted above.
In my view, I can use it like so: (assuming a strongly-typed view, so that the Model property is of type PagedSequence<User>)
1: <% foreach (var item in Model.AsList) { %>
2: ...output desired fields here...
3: <% } %>
The fields which contain the information in the page index, page size, total result set size and such are there for generating the pager interface itself, which I will cover separately. The PagedSequence.AsQueryable and .Select() members allow you to narrow the information being queried by constructing an anonymous or named type containing just the fields you need:
1: <% foreach (var item in Model.Select(user => new { Username=user.Username, EMail=user.EMail })) { %>
2: ...output desired fields here...
3: <% } %>
Yes, that feels a little bit wrong to me there, since what we're doing is affecting the actual SQL query that is being executed. That is something that the Model should be doing, in the repository class most likely. However, you could also think of it this way; I'm defining what information I want to display in the View. One could argue that the View is the place where that should be done.
More to Come
The source code linked at the top of this article also contains an interface, IPagedSequence, which PagedSequence implements. This enables easy use of the PagedSequence class for generating the paging interface via a Partial View, no matter what model class is being held by a particular instance. I'll be following up with a post to share and explain the actual paging interface, which is contained in a single ViewUserControl.