Sort & paginate records in ASP.NET MVC

How to sort and paginate records in ASP.NET MVC?

To demonstrate the sorting and paging both functionality, we shall create new action methods and views that would be easy to explain and understand.

Let’s create an action method in the controller named PagingAndSorting. Understanding the action method would be difficult for now; but do not worry we would learn all codes line by line.

CONTROLLER CODE

   
      /// <summary>
        /// Sort and paginate records from the database
        /// </summary>
        /// <param name="sortOn">field to sort on field</param>
        /// <param name="orderBy">order by ascending or descending</param>
        /// <param name="pSortOn">previous sorted on field</param>
        /// <param name="page">page number to show</param>
        /// <returns></returns>
        public ActionResult PagingAndSorting(string sortOn, string orderBy, string
pSortOn, int? page)
        {
            int recordsPerPage = 3;
            if (!page.HasValue)
            {
               page = 1; // set initial page value
               if (string.IsNullOrWhiteSpace(orderBy) || orderBy.Equals("asc"))
               {
                   orderBy = "desc";
               }
               else
               {
                   orderBy = "asc";
               }
            }
            // override the sort order if the previous sort order and current 
            // request sort order is different
            if (!string.IsNullOrWhiteSpace(sortOn) && !sortOn.Equals(pSortOn,
StringComparison.CurrentCultureIgnoreCase))
            {
                orderBy = "asc";
            }

            ViewBag.OrderBy = orderBy;
            ViewBag.SortOn = sortOn;

            var list = db.PersonalDetails.AsQueryable();

            switch(sortOn)
            {
                case "FirstName" :
                    if (orderBy.Equals("desc"))
                    {
                        list = list.OrderByDescending(p => p.FirstName);
                    }
                    else
                    {
                        list = list.OrderBy(p => p.FirstName);
                    }
                    break;
                case "LastName":
                    if (orderBy.Equals("desc"))
                    {
                         list = list.OrderByDescending(p => p.LastName);
                    }
                    else
                    {
                         list = list.OrderBy(p => p.LastName);
                    }
                    break;
                case "Age":
                    if (orderBy.Equals("desc"))
                    {
                         list = list.OrderByDescending(p => p.Age);
                    }
                    else
                    {
                         list = list.OrderBy(p => p.Age);
                    }
                    break;
                default :
                    list = list.OrderBy(p => p.AutoId);
                    break;
            }

            var finalList = list.ToPagedList(page.Value, recordsPerPage);
            return View(finalList);
        }

Corresponding view of the above action method is below. Notice that the view of this action method is more or less similar to Pagination view (previous point) however there would be certainly difference in the header and pagination code as we have to make the header as hyperlink so that it is clickable and user can click fields to sort on. Paging link also needs change so that it persist the selected column to sort on.

It goes without saying that we will be using PagedList plugin to achieve pagination however for sorting we will write our own code.

VIEW CODE

@using PagedList;
@using PagedList.Mvc;
@model IPagedList<WebApplication1.Models.PersonalDetail>

 <h2>Pagination</h2>

    <p>
        @Html.ActionLink("Create New", "Create")
    </p>
    <table class="table">
        <tr>
            <th>Auto Id
            </th>
            <th>@Html.ActionLink("First Name", "PagingAndSorting", new { sortOn =
"FirstName", orderBy = ViewBag.OrderBy, pSortOn = ViewBag.SortOn })
            </th>
            <th>@Html.ActionLink("Last Name", "PagingAndSorting", new { sortOn =
"LastName", orderBy = ViewBag.OrderBy, pSortOn = ViewBag.SortOn })
            </th>
            <th>@Html.ActionLink("Age", "PagingAndSorting", new { sortOn = "Age",
orderBy = ViewBag.OrderBy, pSortOn = ViewBag.SortOn })
            </th>
            <th>Active
            </th>
            <th></th>
        </tr>
        @foreach (var item in Model) {
        <tr>
            <td>@Html.DisplayFor(modelItem => item.AutoId)
            </td>
            <td>@Html.DisplayFor(modelItem => item.FirstName)
            </td>
            <td>@Html.DisplayFor(modelItem => item.LastName)
            </td>
            <td>@Html.DisplayFor(modelItem => item.Age)
            </td>
            <td>@Html.DisplayFor(modelItem => item.Active)
            </td>
            <td>@Html.ActionLink("Edit", "Edit", new { id=item.AutoId }) |
@Html.ActionLink("Details", "Details", new { id=item.AutoId }) |
@Html.ActionLink("Delete", "Delete", new { id=item.AutoId })
            </td>
        </tr>
        }
    </table>

    <div class="pagedList">
        @Html.PagedListPager(Model, page => Url.Action("PagingAndSorting", new { page,
sortOn = ViewBag.SortOn, orderBy = ViewBag.OrderBy, pSortOn = ViewBag.SortOn }), PagedListRenderOptions.ClassicPlusFirstAndLast)
    </div>

In the above view, notice that the header of the table is @Html.ActionLink apart from AutoId and Action as we do not want them to be sorted on.

The @ActionLink parameters are as follows

1^st parameter - is the link text
2^nd parameter – is the action name to call when the link will be clicked
3^rd parameter - is the routeValues that will be passed as querystring to the action method url. Following are details about them

sortOn – is the respective field name to sort on, it should exactly match with the property name so that we do not need to write mapping values
orderBy – is the sort order in which to sort record
pSortOn – is the field name on which the displayed record is sorted on (this is to ensure that if different column is clicked on the sorted record then it loose the current sorting order and bring records sorted by new column in ascending order.

Note that we need to maintain the current sorting order while paginating records as well so we need all above parameters values along with paging links as well.

Notice the @Html.PagedListPager method in which apart from page, we have also passed sortOn, orderBy and pSortOn parameters in the same way we have passed in @Html.ActionLink in the header.

Now, when the page runs for the first time, it look like below

Notice the PagingAndSorting action method in the first code block above.

In the first run, sortOn, orderBy, pSortOn and page all are null. In the method itself we are setting the recordsPerPage value. If page doesn’t have value, we are setting default page as 1 and changing orderBy from asc (ascending order) to desc (for descending order). If order by is null we are setting it to asc.

If the current sort order “sortOn” is not equal to previous field sort order “pSortOn” then we are resetting the sort order to asc to make the sorting in ascending order. This case will happen in the scenario where user has sorted records on “First Name” in descending order and then clicked on “LastName” field; in this case we want the record to be sorted in “Last Name” ascending order not in the previously sorted order.

We are setting the value of current orderBy and sortOn to the ViewBag.

Now based on sortOn field value we are getting the sorted data from database either ascending or descending order.

That sorted record is being passed to PagedList that in turn is being returned to view to display.

Record sorted on First Name ascending order