PagedList using LINQ Skip and Take, but show paging using Count of results

Question

I am trying to display a filtered list of of products, based on Category filter and ItemsPerPage but I'm having some issues when trying to use it with PagedList.

Someone with PagedList expertise could advice me if I need to write my own pagination code or is there a way to get the results I need using PagedList.

I am using LINQ's Skip & Take functions to get only the number of rows that need to be displayed on the current page, but I would still like paging links to show pages based on the filter's total count.

E.g.: my search filter finds 50 results, but since my rows per page is say 10 items, I use LINQ's Skip() & Take() to get only 10 rows back. I still need to show in my View.cshtml the page links << 1 | 2 | 3 | 4 | 5 >> Right now with default PagedList, I only get << 1 >>, I know why I only see one page but just wanted to know how can I make it work to show correct number of page links, while only getting a subset of results.

**My goal is to write optimized queries to the Database so the web page response performance will be fast.

Here is what my code for the Action Method looks like. The code is getting the correct results but the pagination is not working as I need it to be:

public ViewResult List(int page =1, string category =null)
{
    if (category != null) this.CurrentCategory = category;

    var products = repository.Products
                    .Where(p => this.CurrentCategory == null || p.Category == this.CurrentCategory)
                    .OrderBy(p => p.ProductID)
                    .Skip((page -1) * PageSize)
                    .Take(PageSize);

    return View(products.ToList().ToPagedList(page, PageSize));
}

Here is the code snippet from the View that deals with pagination. I looked into the project's Github site but could not find an extension method to provide custom pages. I think it will only render number of pages based on 'items per page' and the Count() of products in the @Model:

@model IPagedList<Product>

//foreach loop that renders the @Model

//Code that displays the pagination using PagedList
<div style="text-align:center">
    @Html.PagedListPager(Model, page => Url.Action("List", new { page = page, category =  ViewBag.CurrentCategory }), PagedListRenderOptions.OnlyShowFivePagesAtATime
    )
</div>

Solution 1

I had the exactly same problem and I ended up using StaticPagedList. You can do something like this

public ViewResult List(int page =1, string category =null)
{
    if (category != null) this.CurrentCategory = category;

    var products = repository.Products
                   .Where(p => this.CurrentCategory == null || p.Category == this.CurrentCategory)
                   .OrderBy(p => p.ProductID)
                   .Skip((page -1) * PageSize)
                   .Take(PageSize);

var count = repository.Products
                   .Where(p => this.CurrentCategory == null || p.Category == this.CurrentCategory).Count();

var resultAsPagedList = new StaticPagedList<Product>(products, page, PageSize, count);

    return View(resultAsPagedList);
}

as for the view, you just need to replace the model type

@model StaticPagedList<Product>

Solution 2

You will still likely have to ask for a count separately.

var products = repository.Products
                         .Where(p => this.CurrentCategory == null || p.Category == this.CurrentCategory);
var numProds = products.Count();
var mypage = products.OrderBy(p => p.ProductID)
                     .Skip((page -1) * PageSize)
                     .Take(PageSize);

Solution 3

ToPagedList uses Take and Skip internally and when you use the extension to the IQueryable<T> class, that will result in the database query you require. It also retrieves the TotalItemCount into its Metadata.

Sometimes you may need to bring the query results into memory because you are using a method that cannot be translated into sql. That means you have to convert the PagedList back to an Enumerable. You can get around this problem by using the StaticPagedList method like this:

   var superset= repository.Products
      .Where(p => this.CurrentCategory == null 
             || p.Category == this.CurrentCategory)
      .OrderBy(p => p.ProductID)
      .ToPagedList(page, PageSize);

   var subset = superset
      .AsEnumerable()
      .Select(p => new ProductViewModel
      {
          OtherData = p.UntranslateableMethod()  
      })

    var model = new StaticPagedList<ProductViewModel>(subset,
       superset.GetMetaData());
}

From the summary of the method in comments:

Initializes a new instance of the PagedList.StaticPagedList class that contains the already divided subset and information about the size of the superset and the subset's position within it.

Solution 4

If you use PagedList's methods, you do not need to also use LINQ .skip() and .take(). If you do, then you are pre-limiting the list, and it would be pointless to use the library because you are first filtering down the list to get pagesize results by .skip() and .take() then passing those pagesize results to be filtered again down to pagesize, which will of course be the same, but then PagedList doesn't know what the total is.

If you want to ensure you are not loading all results, simply send an IQueryable to PagedList instead of an Enumerable or List. PagedList will add the .Skip() and .Take() for you, and your database will only return those results.

Solution 5

The above explanation is correct. But when you are assigning Static select list..

new StaticPagedList<Product>(products, page, PageSize, count);.

It displays less number of page link from actual count. If Count is 10 it show only 2 pages if your page size is 5..

If you want to show all pages link availabe in count, then

pass count*5 ie count*pagesize

new StaticPagedList<Product>(products, page, PageSize, count*5);//in my case page size is 5

or new StaticPagedList(products, page, PageSize, count*pagesize);

thus it provide all available counts in pages.

View more solutions

Comments

[Johnny]

Did you ever get this answered? I have the same problem and I don't see myself retrieving all the records so that we can have a proper paging number display. Please let me know

[badboy11]

Hi Johnny, the answer is for custom filtering, you would need to write your own code to handle pagination. PagedList is a great pagination library but it does not provide filtering and returning only subset for optimization for a huge list of data.

[Vinod]

Then What is the finial Answer to achieve this ?????

[badboy11]

Thank you for your response. My question is how to customize PagedList framework to display a custom paging scheme. I know how to filter my search and get a results.Count(). But so far all my tries to plug the Count() into the PagedList has failed. Before I start writing my own custom pagination I wanted to see if I could achieve the customization using PagedList, since it is a nice pagination framework.

[Michael Dunlap]

Ahh. You'll have to share your view code as well to see if there's something wrong there.

[badboy11]

Hi DigitalD, I updated my first posting to show the View's Razor/C# code to display the pagination code.

[badboy11]

Thank you Juventus18 for your feedback, but please read the question carefully and understand it before trying to answer.

[Artjom B.]

You are referencing this answer, right?

[Liam]

I do the same with a list of rows returned from a service call. Only difference is that I leave the view model as IPagedList. E.g. @model IPagedList<Product> in your case.

[Chef_Code]

@badboy11 and understand it seems a bit harsh. If someone does not already understand something... how likely is it... that they would miraculously come to the realization that they are misunderstanding the question. please read the question carefully in my opinion... was all that was needed to be said. Don't discourage people, or at the least be more constructive with your criticism.

[solidau]

@badbou11 - I understand what you wrote perfectly. Maybe you didn't write your question very well. "how can I make it work to show correct number of page links, while only getting a subset of results." As I said, this is because you are pre-limiting your list before passing it to PagedList. Do not use .Skip() and .Take() in your query, as PagedList will do that for you. if you pass paged list only 10 items (or, a query that returns only 10 items) as you have done and tell it 10 items per page, that is only one page, so it will only render one page.

[solidau]

If you want to ensure your database only returns the paged results instead of the entire set, send PagedList an IQueryable from your repo instead of IEnumerable or IList (it is not clear from your example what is returned by your repos). In either case, you do not need to use Skip() or Take() before passing the result to PagedList

[mcfroob]

This is the correct answer. You can get the total list of items by just calling 'OnePageOfItems.TotalItemCount' I ran through the SQL and timing with miniprofiler. You can make two calls (one to count the total objects, and then one to get the list of objects themselves), and pass them into a static paged list. However, making a call to IPagedList with an IOrderedQueryable will result in exactly the same SQL calls internally. So it makes more sense to do that and then call .TotalItemCount.

[Shilpa Soni]

You are a life saviour

[Monojit Sarkar]

very curious to know what is the difference between PagedList and StaticPagedList ?

[Michael Dunlap]

@MonojitSarkar Yes it would hit the DB twice. Once for the total count, and once for loading the filtered page of results.