MVC 5 EF 6 ViewModel (Page, Sort, Search) - Sorting Limits Paged Record Results

Question

I have an issue with sorting while pagination is implemented in a ViewModel-based MVC application.

My code remains largely unchanged from a previous question I posted concerning this application (aside from that specific, implemented fix).

My Code:

MVC 5 EF 6 - How to Search a Table Using Related Table Object Criteria

What happens is, after commenting-out the default sort case, the pagination will initially function as expected, but only when no default sort order is specified in the "sortOrder" parameter. The results will be something like:

It displays Page 1 of x, and I can navigate through the pages fine. When I try to sort, however, all records beyond the first page drop out:

The page drops down to 1, and the records that were on that page are now all there is. If I change the pagination to show 50 records per page, after I sort there will be only 50 records total, out of the 1900 or so. If I change it to 20, there will only be 20 records, and so on.

The issue seems to relate to the specified sort in this part of the code:

switch (sortOrder)
{
    case "PONumber_desc":
        viewModel.PORequests = viewModel.PORequests.OrderByDescending(s => s.PONumber).ToPagedList(pageNumber, 5);
        break;
    case "AppropNumber":
        viewModel.PORequests = viewModel.PORequests.OrderBy(s => s.AppropNumber).ToPagedList(pageNumber, 5);
        break;

If I comment that code, or avoid sorting, it will work. The sorting does work in the records that are there. If I disable paging, and leave the sort code, it will also function properly. When I use both the paging AND sorting functionality together, my records are being "trimmed."

The issue seems to relate to the fact that I am using a ViewModel, with a related table. Otherwise, I have had success in both the tutorials and my own applications.

How can I get it to successfully sort the records without breaking my pagination and searches, or changing my ViewModel approach? Am I just going about this all wrong?

Update

Thanks for the response(s). I tried building it as you suggest (and a similar way previously using IEnumerable<> instead of IQueryable<>), but I get errors like

Error CS1061 'IQueryable' does not contain a definition for 'PageCount' and no extension method 'PageCount' accepting a first argument of type 'IQueryable'

If it makes a difference, using the PagedList.IPagedList<> in the ViewModel is implemented in the tutorial I used as a guideline:

ASP EF MVC Tutorial

They conveniently leave sorting out for the paging example, and switch to a different part of the application for sorting.

I tried dropping your code into place, but on 'viewModel.PORequests = query.ToPagedList(pageNumber, 5);' I get the error:

Cannot convert from 'PagedList.IPagedList' to 'System.Linq.IQueryable'

This person had a similar issue, but resolved it using the code in the tutorial, which doesn't work with sorting.

Similar Issue 1

This person had a somewhat similar issue, but resolved it using a static paged list. Most of the code in his snippet is over my head.

Similar Issue 2

Any further help would be greatly appreciated.

Answer 1

On the first step you assign ToPagedList(pageNumber, 5) to viewModel.PORequests. Since on the next steps you are using that variable as a source and apply again the same ToPagedList, apparently the result is a s ingle page.

What you should do is to build dynamically the query using a variable and call ToPagedList on the final query like this:

// Base query
var query = db.PORequests
           .Include(i => i.POItems)
           .AsQueryable();
// Filter
if (!string.IsNullOrEmpty(poNumber))
{
    query = query.Where(s => s.PONumber.Contains(poNumber));
}
if (!string.IsNullOrEmpty(AppropNumber))
{
    query = query.Where(x => x.AppropNumber.Contains(AppropNumber));
}
if (!string.IsNullOrEmpty(ContractNumber))
{
    query = query.Where(x => x.ContractNumber.Contains(ContractNumber));
}
if (!string.IsNullOrEmpty(ItemDescription))
{
   query = query.Where(x => x.POItems.Any(i => i.Description.Contains(ItemDescription)));
}
// Sort
switch (sortOrder)
{
    case "PONumber_desc":
        query = query.OrderByDescending(s => s.PONumber);
        break;
    case "AppropNumber":
        query = query.OrderBy(s => s.AppropNumber);
        break;
    case "AppropNumber_desc":
        query = query.OrderByDescending(s => s.AppropNumber);
        break;
    case "ContractNumber":
        query = query.OrderBy(s => s.ContractNumber);
        break;
    case "ContractNumber_desc":
        query = query.OrderByDescending(s => s.ContractNumber);
        break;
    default: 
        query = query.OrderBy(s => s.PONumber);
        break;
}
// Final paginated result
viewModel.PORequests = query.ToPagedList(pageNumber, 5);