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>

Answer 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>

Answer 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);

Answer 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.