Is there a paging solution for ASP.NET MVC that does paging in the database?

Question

Most of the ASP.NET MVC paging solutions I have found by googling look like they get all rows from a database table in the form of a IEnumerable collection, perform some paging conversion on the IEnumerable collection, and then return the results to the view. I want to be able to page on the DB side but still have some paging class to do the page number calculations and HTML generation. Is there a solution out there that does this? Or are the ones i've been looking at do this, but i'm not seeing it because i'm looking at them wrong?

here's what i've been looking at:

• http://blogs.taiga.nl/martijn/2008/08/27/paging-with-aspnet-mvc/
• http://www.codeproject.com/KB/aspnet/pagination_class.aspx
• http://www.squaredroot.com/2009/06/15/return-of-the-pagedlist/

Answer 1

You're wrong. PagedList will do it on the DB server, as it has IQueryable extensions.

Answer 2

Look at the Gu's Nerdinner sample.

var upcomingDinners = dinnerRepository.FindUpcomingDinners();  
var paginatedDinners = upcomingDinners.Skip(10).Take(20).ToList(); 

Even though FindUpcomingDinners() gets all the upcoming dinners, the query isn't executed at the database until you call ToList() in the next line. And that is after you Skip 10 rows and only get
the next 20.

Answer 3

ScottGu has a very nice multi-part blog series on using LINQ in Asp.Net (including MVC). I recommend reading the entire series starting at Part 1, but Part 3 covers exectly what you're looking for -- the section titled "Paging our Query Results" specifically deals with paging in the database.

Answer 4

wouldn't it be more efficient to implement a stored procedure that takes @StartPage and @PageSize parameters?

this way you are only retrieving the subset of data that is actually being used

just have an out parameter called totalCount or something similar so that you know how many page links to create and each link onclick event will pass the page number to a javascript function that will asynchronously load the div or other HTML element with more data

easy