1

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:

Initial Pagination

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:

Post-sort Pagination

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.

Community
  • 1
  • 1
Matt G
  • 15
  • 6
  • show full code and View Code too i gess then we can help – teo van kot May 24 '16 at 18:36
  • Looking at the linked question, `viewModel.PORequests` already contains paginated data, so the above code apparently would have always one page. You should apply all filtering/ordering on the source queryable and call `ToPagedList` on the final query, – Ivan Stoev May 24 '16 at 18:46
  • @teo-van-kot I tried to reference, rather than re-post the code, since the question guidelines warn against verbosity. I will bring it over if it'll help. – Matt G May 25 '16 at 14:55

1 Answers1

0

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);
Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343
  • Thanks for the response. I updated my question after using your code. I might need some further clarification on how to get the 'IQueryable' version of my code into a paged list. – Matt G May 25 '16 at 14:57
  • You should not get *Cannot convert from 'PagedList.IPagedList' to 'System.Linq.IQueryable'* because `query` variable should already be `IQueryable`. Make sure you copy/past the whole exact snippet from the answer. Note that I removed a lot of `ToPagedList` calls from your original code, including the initial one. The snippet is supposed to replace everything after `var viewModel = new POItemData();` except the `return View(viewModel);` – Ivan Stoev May 25 '16 at 16:47
  • I dropped the code in as-is. Here is it in its current iteration: [Current Version](https://dotnetfiddle.net/aIRoCs) I tried changing the model to IEnumerable, which took out the PagedList to IQueryable error, but left these errors: 'IQueryable' does not contain a definition for 'PageCount' 'IQueryable' does not contain a definition for 'PageNumber' – Matt G May 26 '16 at 14:01
  • Wow, there was absolutely no need to change your `POItemData` class. It should stay as [here](http://stackoverflow.com/questions/37285220/mvc-5-ef-6-how-to-search-a-table-using-related-table-object-criteria). – Ivan Stoev May 26 '16 at 14:21
  • So setting the model back to PagedList.IPagedList works with your code (thanks again). So after all that, the problem was using 'viewModel.PORequests'? I don't understand why using 'query' then setting `viewModel.PORequests = query.ToPagedList(pageNumber, 5);` works, but just using 'viewModel.PORequests' in place of 'query' does not. What are they referencing differently? – Matt G May 26 '16 at 15:58
  • The difference is that the original code was using `viewModel.PORequests` as variable to store intermediate **paged** results, and base the next operations on that results. – Ivan Stoev May 26 '16 at 16:03