After searching the web for MVC paging techniques, I realized that 90% or more of the examples I found on the web would not solve my problem. Some of them are so bad that they could get me fired if I followed their example. (copy -> paste). They so oversimplify the need, that they render their solutions useless. The intentions are good, but the implementation is lacking.
Have you:
- Wanted to add paging to your MVC site, but think it’s too complicated?
- Implemented it on one page and hope you never have to do it again?
- After implementing paging thought, “There’s got to be a better way?”
The most popular problem with most examples that I found is that they show pulling back the entire collection from the database and paging the results in the UI (in the controller). This is fine if you’re absolutely positive, sure beyond belief, willing to bet your job, that the results will never exceed a hundred records or so…
If you want to become an expert on how to implement fast, extremely flexible paging in all your MVC applications, then figure that this is going to take an hour or two. If you’re looking to “turn on paging”, then feel free to click on the “Back” button, I won’t be offended.
Not implementing paging, or implementing it incorrectly can cause serious problems with the performance of your web site. Below are some symptoms that I’ve seen that leads me to believe there could be a problem with the paging implementation on a web site:
- Complaints that the web site slows down, but nobody can see high utilization on the web server or database server (and no expensive queries)
- Complaints that the web site sometimes stops responding, occasionally for minutes at a time
- Complaints that the web site sometimes suddenly throws users back to the login screen while they’re working
- Complaints that the slow downs are intermittent, and can’t be reproduced in the test environment
I know you may be thinking, what does this have to do with paging? Well, it’s tangentially related, as it has to do with writing bad code without thinking about what the consequences are. The reason these issues don’t tax the servers is that doing a SELECT * FROM Clients doesn’t really tax the CPU that much. Now, if you were watching the NIC card traffic, that would be a different story.
The biggest issue that I see with code examples on the web:
- They show bringing back every row in the table and then page in the controller (No, no, please don’t do this!)
- They show using a LINQ query in the controller to Skip and Take
- They assume that I’m pulling back my data directly from the database in my Web Application
// Example of code that is ridiculous outside of a POC app
return View(MyRepository.GetClients().Skip((page - 1 ?? 0) * (rows ?? 3)).Take(rows ?? 3));
This LINQ query will work well if:
- The MyRepository is an EntityFramework repository
- AND you’re implementing your Repository in your web site
- AND it doesn’t dispose of the Entity context inside the method
- AND the GetClients() method doesn’t call ToList() inside of it
- AND GetClients() is not executing a stored procedure
If any of the above is not true, then this method is no better than the first method that pulls the entire table back.
Another issue with this example is that it assumes that the Repository is part of your web project AND you’re using the objects that are in your Entity Model. So no DTO’s, no business objects, and absolutely no Web Service in the middle.
The reason I’m showing this, is that I’ve never seen these issues discussed in the posts. A developer could easily implement this on a GetClients() that again, returns the entire table contents and then pages on the UI side.
Just in case you forgot, the web is stateless, so if IIS or most other Web Servers feel that they’ve had about enough with the application memory usage, they just reset themselves. Shouldn’t cause a problem right? Except if you’re depending on session to keep people logged in, then they get kicked back to the login screen. (BTW, this only happens if you have your own custom code looking for a session object to verify the user is logged in. If you use the proper MVC standard Authorization, they shouldn’t be sent to the login screen.) But not to minimize the impact, because all users get kicked out, not just the one user.
Almost every web, and even windows project I’ve ever worked on followed the Repository pattern in that there were separations of concerns, i.e.:
Database → DataLayer → ServiceLayer → WebService → ProxyClass → ServiceLayer → UI
I don’t want to digress into a discussion of patterns. But the ServiceLayer behind the WebService will convert the Entity Framework object or DataSet to a DTO collection. Then the ServiceLayer behind the UI will likely convert it to a ViewModel. We don’t want to do all that work on 5000 records so that we can ultimately display 25 to the user…
The reason I’m discussing this is to get your imagination started. If you didn’t want to transfer tons of data over the wire from your WebService, then where is the best place to implement Paging? You guessed it, in the DataLayer (also referred to as Data Access Layer (DAL)). As close to the data as you can possibly get.
Alright, let’s get to the nuts and bolts. I’m going to show you a full implementation, so you can trim it down if your particular need is less complicated.
Anatomy of a Paging Request
The following are the properties that I’ve been able to accomplish almost any paging task I’ve ever needed on the client side:
| Property | Type | Notes |
| SortedResults | List<T> | The list of results, without it, we don’t need paging! |
| TotalItems | int | The total number of items that would be returned, if we returned them all at once |
| CurrentPage | int | The current page the user is on |
| PageSize | int | The size of the page, usually an option for the user to select |
| TotalPages | int | The total number of pages (TotalItems/PageSize rounded up) |
| StartPage | int | The start page, for when you have more pages than will fit on one widget |
| EndPage | int | The end page, for when you have more pages than will fit on one widget |
| SearchFilter | string | A filter to use in addition to any search criteria, usually selected from a dropdown, i.e. year, division, salesman, etc. |
| SearchTerm | string | A search term, usually typed in a search box by the user |
| SortExpression | string | The column name to sort the results by |
| RecordCount | int | The quantity of records in this particular result set |
| SortOrder | string | The order that you want the result set, i.e.: “ASC”, “DESC” |
| ActiveOnly | bool | Another filter flag, if you have an IsActive flag in your table, or any other criteria to knock out inactive rows |
| RefId | int | Typically used when there is a Parent/Child relationship. i.e. Your SortedList is a list of Project tasks, then you’ll need to know the parent project PK Id |
