Paging and sorting in ASP.NET MVC and Entity Framework application

This article will walk you through about implementation of paging and sorting of table in ASP.NET MVC and Entity Framework application. For paging we will use PageList.MVC NuGet package, it is one of the best package for paging and sorting. We will display model list details in table format and perform paging and sorting on it.

For this tutorial we will use Products table from Northwind database. Northwind database.

Implementation of Paging and Sorting in ASP.NET MVC

1. Create New ASP.NET MVC Application

   Open Visual studio and create new ASP.NET MVC application clicking File -> New -> Project and select ASP.NET MVC4 Web Application name it as NorthwindApp
   From next window select Internet Application and Razor as view engine. Click Ok.

2. Add ASP.NET MVC Model for NorthwindEntities

   I assume that you have Northwind database on your local SQL Server or any accessible SQL Server to you.
   Right Click Models folder from Visual Studio Solution Explorer. And select Add -> New Item
   From next Window's Installed Pane select ADO.NET Entity Data Model
   
   From next window select Generate from database and click next.
   From next window if you have existing connection string to Northwind database select it or create new connection by clicking New Connection button. Click Next.
   From next window select Products table and click finish. Products.edmx file will be created under Models folder.

3. Install PageList.MVC NuGet Package

   Go to Solution Explorer and right click on References and select Manage NuGet Package.
   From next window's Installed Packages select Online tab and enter paged in search box which is on upper right corner of popup box.
   In search result you will see PageList.MVC click install and close. It will add required References and css files for paging.
   

4. Create ProductController

   Add new controller named as ProductController to handle product related requests.
   Right click on Controllers folder from Solution Explorer and select Add -> Controller. Give controller name as ProductController and select Empty MVC Controller.
   Add using statements for NorthwindApp.Models and PagedList to ProductController.
   Your controller code will look like this

   using NorthwindApp.Models;
   using PagedList;
   
   namespace NorthwindApp.Controllers
   {
       public class ProductController : Controller
       {
           northwindEntities _db;
   
           public ProductController()
           {
               _db = new northwindEntities(); 
           }
   
           public ActionResult Index(string sortOrder, string CurrentSort, int? page)
           {
               int pageSize = 10;
               int pageIndex = 1;
               pageIndex = page.HasValue ? Convert.ToInt32(page) : 1;
               
               ViewBag.CurrentSort = sortOrder;
   
               sortOrder = String.IsNullOrEmpty(sortOrder) ? "ProductID" : sortOrder;
   
               IPagedList<Product> products = null;
               
               switch (sortOrder)
               {
                   case "ProductID":
                       if(sortOrder.Equals(CurrentSort))  
                           products = _db.Products.OrderByDescending
                                   (m => m.ProductID).ToPagedList(pageIndex, pageSize);   
                       else
                           products = _db.Products.OrderBy
                                   (m => m.ProductID).ToPagedList(pageIndex, pageSize);   
                       break;
                   case "ProductName":
                       if (sortOrder.Equals(CurrentSort))  
                           products = _db.Products.OrderByDescending
                                     (m => m.ProductName).ToPagedList(pageIndex, pageSize);
                       else
                           products = _db.Products.OrderBy
                                       (m => m.ProductName).ToPagedList(pageIndex, pageSize);
                       break;
                   
                   // Add sorting statements for other columns
                   
                   case "Default":
                       products = _db.Products.OrderBy
                               (m => m.ProductID).ToPagedList(pageIndex, pageSize);
                       break; 
               }
               return View(products);
           }
       }
   }           

   • _db: variable declared to represent Northwind database.
   • Constructor: declared to create an instance of northwindEntities.
   • Controller Action Index: Created to fetch product list of Products from Northwind database. It accepts three parameters string sortOrder, string CurrentSort, int? page. If sortOrder and CurrentSort are same sorting will be done in desc order. Input parameter page indicates the page number.
   • If the parameter page is null then paging will be done for pageIndex 1. If sortOrder is null or empty then sorting will be done on "ProductID".
   • sortOrder parameter value saved to ViewBag.CurrentSort for deciding on desc order sorting on next sorting request.
   • .ToPagedList(pageIndex, pageSize) will perform the indexing depending on pageIndex and pageSize value.

5. Products View

   Open Solution Explorer and add new folder Product under Views.
   Add new view under Product folder by right clicking on Product folder and select Add -> View. Give View name as Index and select Razor as View engine.
   Add below HTML to Index.cshtml

   @model PagedList.IPagedList<northwindapp.models.product>
   
   @using PagedList.Mvc;
   @{
       ViewBag.Title = "Product List";    
       Layout = "~/Views/Shared/_Layout.cshtml";
   }
   
   <h2>Product List</h2>
   
   @using (Html.BeginForm())
   {      
       <table>
           <tr>
               <th style="border: 2px solid black; text-align: center; width: 12%">                
                    @Html.ActionLink("Product ID", "Index", 
                           new { sortOrder = "ProductID", CurrentSort = ViewBag.CurrentSort })
               </th>
               <th style="border: 2px solid black; text-align: center; width: 25%">
                    @Html.ActionLink("Product Name", "Index", 
                           new { sortOrder = "ProductName",  CurrentSort = ViewBag.CurrentSort })
               </th>
               <th style="border: 2px solid black; text-align: center; width: 15%;">
                   @Html.ActionLink("Qty", "Index", 
                           new { sortOrder = "QuantityPerUnit", CurrentSort = ViewBag.CurrentSort })
               </th>
               <th style="border: 2px solid black; text-align: center; width: 10%;">
                   @Html.ActionLink("Unit Price", "Index", 
                           new { sortOrder = "UnitPrice", CurrentSort = ViewBag.CurrentSort }) 
               </th>
               <th style="border: 2px solid black; text-align: center; width: 10%;">
                   @Html.ActionLink("Units In Stock", "Index", 
                           new { sortOrder = "UnitsInStock", CurrentSort = ViewBag.CurrentSort }) 
               </th>
           </tr>
           @foreach (var item in Model)
           {
               <tr>
                   <td style="border: 2px solid black; text-align: center; word-wrap: break-word;">
                       @Html.DisplayFor(modelItem => item.ProductID)
                   </td>
                   <td style="border: 2px solid black; text-align: center; word-wrap: break-word;">
                       @Html.DisplayFor(modelItem => item.ProductName)
                   </td>
                   <td style="border: 2px solid black; text-align: center; word-wrap: break-word;">
                       @Html.DisplayFor(modelItem => item.QuantityPerUnit)
                   </td>                
                   <td style="border: 2px solid black; text-align: center; word-wrap: break-word;">
                       @Html.DisplayFor(modelItem => item.UnitPrice)
                   </td>
                   <td style="border: 2px solid black; text-align: center; word-wrap: break-word;">
                       @Html.DisplayFor(modelItem => item.UnitsInStock)
                   </td>
               </tr>
           }
       </table> 
       <br />
       <div id='Paging' style="text-align:center">
           Page @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber)
           of @Model.PageCount
   
           @Html.PagedListPager(Model, page => Url.Action("Index", new { page }))
       </div>
   }           
               

   • This view is binded to PagedList.IPagedList<northwindapp.models.product> model.
   • _Layout.cshtml has set as master page.
   • Table is added to show product list.
   • Column headers are added as links, which executes the controller Index action method providing sortOrder and currentSort details.
   • Table rows and its cells are binded to Products Model data.
   • The div Paging will render paging controls.

6. Browse Product List

   Browse below url to view product list. Replace the port number with your application's port number.

               http://localhost:54342/product/index
               

   

If your paging controls are not displayed properly make sure PagedList.css located under Content folder is included in Index.cshtml or in Master page.