Paging with PagedList, is it efficient?

Question

I have been trying to implement paging for quite a while now and I found this tutorial for paging with MVC: ASP.NET MVC Paging Done Perfectly

Now, in this solution, I query the database for the entire set of clients and then I return a paged list of clients instead of a normal list.

I find this disturbing, because I only plan to show 10 or 20 entries per page, and my database will easily have over a million of them. Thus, querying the entire database each time I want to show the Index page seems to be a poor solution at best.

If I am understanding something wrong, please feel free to cut me right now, but for me this solution is anything but perfect.

Have I misunderstood something? Is there a more efficient solution or library out there for pagination with MVC?

Answer 1

Naturally paging will require knowledge of the total result count in order for the logic to determine how many pages there are etc. However instead of bringing down all the results just build your query to the Database to return the paged amount (e.g 30) and as well as the count of all the results.

For example, if you were using Entity Framework, or LINQ2SQL you could do something like this

IQueryable<Result> allResults = MyRepository.RetrieveAll();

var resultGroup = allResults.OrderByDescending(r => r.DatePosted)
                                               .Skip(60)
                                               .Take(30)
                                               .GroupBy(p => new {Total = allResults.Count()})
                                               .First();

var results = new ResultObject
{
    ResultCount = resultGroup.Key.Total,
    Results = resultGrouping.Select(r => r)
};

Because we haven't done a .ToList() on our result set until we have finalised what we want, we haven't brought the results into memory. This is done when we call the .First() on our result set.

Finally our Object that we end up with (ResultObject) can be used to then do the paging later on. As we have the count, we already know what page we are on (3 as we skipped 60, with 30 per page) and we have the results to display.

Further Reading and Information

How To: Page through Query Results

Server Side Paging with Entity Frame