13

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:

JulianR
  • 16,213
  • 5
  • 55
  • 85
gabe
  • 1,873
  • 2
  • 20
  • 36

4 Answers4

14

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.

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
jvanderh
  • 2,925
  • 4
  • 24
  • 28
  • i'll check it out the sampler. ah, i see. your last sentence greatly enlightened me! thx much! – gabe Jul 09 '09 at 17:39
  • I recommend you check out the whole sample, but the link I provided is the chapter that explains in more detail how IQueryable works. – jvanderh Jul 09 '09 at 17:44
5

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

Craig Stuntz
  • 125,891
  • 12
  • 252
  • 273
  • so what would the controller action syntax be? everything i've been seeing does something like productRepository.GetAllProducts().ToPagedList(pageIndex, pageSize). – gabe Jul 09 '09 at 17:36
  • 1
    Yes, that's just right. This converts **the selected page only** to IEnumerable. The paging is done on the DB server via IQueryable. Ditto for Count. – Craig Stuntz Jul 09 '09 at 18:00
  • 1
    my problem was that i didn't know what IQueryable did (i.e. "deferred execution"). your comments as well as the others pointed me in the right direction for understanding. thx! – gabe Jul 10 '09 at 16:20
  • how will PagedList do it on the DB server? – DevDave Jul 30 '13 at 09:29
  • 1
    @DevDave: Because it preserves use of `IQueryable` and doesn't convert to `IEnumberable` – Craig Stuntz Jul 30 '13 at 14:16
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.

Sean Reilly
  • 21,526
  • 4
  • 48
  • 62
2

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

Will Asrari
  • 29
  • 1
  • LINQ does the same thing with Skip() and Take(). If your data is accessed by a multitude of applications then I suppose a stored procedure is a good idea. Otherwise, LINQ is much easier to implement, read, and debug. – jrjensen Feb 09 '15 at 16:36