ASP.Net MVC PagedList not working properly

Question

I am using PagedList to display paging on my search payment results page. I want to display only 5 payments on each page. The search criteria I am testing returns 15 records. I am expecting only 5 records on first page with page numbers 1,2,3 at bottom. I see the page numbers as expected at the bottom but all 15 records get displayed on every page. I have debugged the code and found out that StaticPagedList function is returning 15 records instead of 5. My controller action code is as given below:

public ViewResult ViewPayment(int? billerId, int? billAccount, int? page)
{
    var pageIndex = (page ?? 1) - 1;
    var pageSize = 5;

    List<Payment> paymentList = new List<Payment>();
    paymentList = _paymentBusiness.GetPayments(billerId, billAccount);
    var paymentsAsIPagedList = new StaticPagedList<Payment>(paymentList, pageIndex + 1, pageSize, paymentList.Count);

    ViewBag.OnePageOfPayments = paymentsAsIPagedList;

    return View(paymentList);
}

Please let me know if I have mistaken anything.

You code is using `StaticPagedList` which will correctly return only 5 records, but your assigning it to `ViewBag.OnePageOfPayments`, and still returning the all 15 records to the view using `return View(paymentList);` (not `return View(paymentsAsIPagedList );`). But this code is very inefficient since you use of `paymentList.Count` means that your `GetPayments()` method returns `List` not `IQueryable` so you have loaded the whole collection into memory, defeating the purpose of efficient paging — , Jan 29 '17 at 20:55
You should be using a method that returns `IQueryable` and use `.Count()` to get the total number of elements. — , Jan 29 '17 at 20:56
Thanks Stephen for your suggestions! I am going to modify it as per your comments to make it efficient. — gliese 581 g, Jan 30 '17 at 07:18

juunas · Accepted Answer · 2017-01-30T07:12:21.243

You should be querying only 5 records from your business layer. Right now you are not passing the page number or anything there. It's a bit of a waste to query all of them if you are going to only display some of them anyway.

public ViewResult ViewPayment(int? billerId, int? billAccount, int? page)
{
    int pageNum = page ?? 1;
    int pageSize = 5;

    IPagedList<Payment> paymentPage = _paymentBusiness.GetPayments(billerId, billAccount, page, pageSize);

    return View(paymentPage);
}

// Business layer
public IPagedList<Payment> GetPayments(int? billerId, int? billAccount, int page, int pageSize)
{
    IQueryable<Payment> payments = db.Payments.Where(p => ....).OrderBy(p => ...);

    return new PagedList<Payment>(payments, page, pageSize);
}

I would suggest you do something like the above. Change it so the business/data layer gives you back the paged list. It can get the 5 results, and the total count with two queries, and return your controller the page model.

The example gets a page using PagedList<T> which runs Skip() and Take() internally. Remember to order your results before creating the page.

Importantly, now we do not fetch all the items from the database, only the small subset we are interested in.

If you are using e.g. ADO.NET that requires you to use plain SQL, you can use a query like:

SELECT * FROM Payments ORDER BY id OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Offset should be set to (page - 1) * pageSize, and the number after FETCH NEXT is the page size. Note this only works on SQL Server 2012+. Other databases have similar abilities.

Also, with ADO.NET you will have to make the two queries needed manually (page + total count), and use StaticPagedList instead of PagedList, which allows you to give it the subset directly.

We are using ADO.NET and we get a list of Payments from business layer. Can I fetch subset using ADO.NET? — gliese 581 g, Jan 29 '17 at 18:33
Yes, you can paginate with plain SQL. Check this answer for the nice SQL Server 2012+ way: http://stackoverflow.com/a/10639172/1658906. Check the accepted answer there for something that will work on older ones. — juunas, Jan 29 '17 at 18:35
This is not necessary at all. OP is using `StaticPagedList` which already uses `.Skip()` and `.Take()` — , Jan 29 '17 at 20:41
No it does not. StaticPagedList takes the subset. PagedList takes the superset and runs Skip and Take on it. Though now that you say it, might make more sense to just use that directly. — juunas, Jan 29 '17 at 20:47
You can check the source here: https://github.com/troygoode/PagedList/blob/master/src/PagedList/StaticPagedList.cs — juunas, Jan 29 '17 at 20:48
I know the source code :). Nothing wrong with your answer - it just did not address the real issues with OP's current code — , Jan 29 '17 at 20:59

score 0 · Answer 2 · answered Jan 30 '17 at 09:18

An alternate approach to using PagedList (which does not provide async methods), is to use DataTables.net (https://datatables.net).

It is a client side javascript framework for paged tables and can be configured down to very low levels. This would allow you to do what you need, and also have the ability for custom sorting, caching, searching, and many other features out of the box.

Just a suggestion, as I have used PagedList library myself in the past, and since discovering DataTables.Net, I have not looked back. Great library, and makes your life easy.

ASP.Net MVC PagedList not working properly

2 Answers2