Paging in ASP.NET Core MVC and EntityFramework Core

By Fons Sonnemans, 29-mrt-2017

Paging, sorting and filtering are common features in websites. Microsoft has written a tutorial how to implement these features in ASP.NET Core MVC with Entity Framework Core. The described solution works but I found it a bit too primitive. It triggered me to create a more powerful solution. Before you can start using my solution you should first read this tutorial, it explains how you can add Entity Framework Core to an ASP.NET Core MVC application.

Project Setup

My WebApplication is an ASP.NET Core Web Application (version 1.1) which uses EntityFramework Core 1.1. I use a commonly used Northwind SQL Server sample database which I created using the following tutorial. I have scaffold the Northwind database and created the controllers for the Suppliers and Products. Read this tutorial to learn how. I have added some extra navigation hyperlinks in the _Layout.cshtml file for the SuppliersController and ProductsController (Action Index).

The Index.cshtml file from the Views/Suppliers folder looks like this. I made some small adjustments like changing the h2 to an h1 and renaming the text 'Index' to 'Suppliers'. The most obvious change is that I removed some of the columns from the table to make it less wide.

@model IEnumerable<WebApplication8.Models.Database.Suppliers>

@{
    ViewData["Title"] = "Suppliers";
}

<h1>Suppliers</h1>

<p>
    <a asp-action="Create">Create New</a>
</p>
<table class="table table-striped">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.CompanyName)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.ContactName)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Address)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.City)
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model) {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.CompanyName)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.ContactName)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Address)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.City)
                </td>
                <td>
                    <a asp-action="Edit" asp-route-id="@item.SupplierId">Edit</a> |
                    <a asp-action="Details" asp-route-id="@item.SupplierId">Details</a> |
                    <a asp-action="Delete" asp-route-id="@item.SupplierId">Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

When you browse for the Suppliers Index page (http://localhost:2309/Suppliers) you get the following output. It shows you all suppliers and that list can be very long. Let's add Paging so we can limit the number of suppliers in the list.

Adding Paging

In the earlier mentioned tutorial a PaginatedList<T> class was used. My solution uses a similar approach but I named it PagingList<T>. This class and some other helper classes are packed in a NuGet package named ReflectionIT.Mvc.Paging. You can install the package using the NuGet browser in Visual Studio or by running the 'Install-Package ReflectionIT.Mvc.Paging' command from the Package Manager Console.

After installation you can add the paging services in the ConfigureServices() method of the Startup class. Add the services.AddPaging() call as shown below. This call will register an EmbeddedFileProvider which is used for rendering the View for the Pager ViewComponent. The View will be loaded from the ReflectionIT.Mvc.Paging assembly if it can not be found in your WebApplication.

public void ConfigureServices(IServiceCollection services)
{
    services.AddEntityFramework()
        .AddEntityFrameworkSqlServer()
        .AddDbContext<Models.Database.NorthwindContext>(options =>
            options.UseSqlServer(Configuration["Data:Northwind:ConnectionString"]));

    // Add framework services.
    services.AddMvc();

    services.AddPaging();
}

Next I modified the Index() method from the SuppliersController class. I added an optional 'page' parameter with the default value 1. I defined a query in which I retrieve the Suppliers from the context (NorthwindContext) with the AsNoTracking() method and an OrderBy(). The AsNoTracking() turns off change tracking which improves the performance. The OrderBy() is required to support Paging (Skip() & Take()) in EF. This query is used to create the PagingList. The page size is set to 10. This is the model which is passed to the View. The PagingList will fetch the data from the database asynchronously.

// GET: Suppliers
//public async Task<IActionResult> Index() {
//    return View(await _context.Suppliers.ToListAsync());
//}

public async Task<IActionResult> Index(int page = 1) {
    var qry = _context.Suppliers.AsNoTracking().OrderBy(p => p.CompanyName);
    var model = await PagingList.CreateAsync(qry, 10, page);
    return View(model);
}

The Index.cshtml view must also be modified. I changed the model type (line 1), added a using (line 2) and an addTagHelper (line 3). Lines 2 and 3 could also be moved to the _ViewImports.cshtml file which would add these lines to every view. I also added a Pager above the table. This is done by invoking the Pager View Component and passing the Model as the pagingList. It is placed inside a <nav> element with an aria-label which is used by screen readers. I have done the same below the <table> but there I used a new feature of ASP.NET Core 1.1. The View Component is invoked as a Tag Helper. It uses the <vc /> element and the class and parameters are translated to lower kebab case.  For more info read this article.

@model ReflectionIT.Mvc.Paging.PagingList<WebApplication8.Models.Database.Suppliers>
@using ReflectionIT.Mvc.Paging
@addTagHelper *, ReflectionIT.Mvc.Paging

@{
    ViewData["Title"] = "Suppliers";
}

<h2>Suppliers</h2>

<nav aria-label="Suppliers navigation example">
    @await this.Component.InvokeAsync("Pager", new { pagingList = this.Model })
</nav>

<p>
    <a asp-action="Create">Create New</a>
</p>
<table class="table table-striped">
   ...
</table>

<nav aria-label="Suppliers navigation example">
    <vc:pager paging-list="@Model" />
</nav>

This results in the following view in which you see two pagers which use bootstrap markup.

 

Adding Sorting

For sorting I added an extra sortExpression parameter to the Index() method of the SuppliersController class. The parameter is optional and uses the CompanyName as the default sort expression in case none is supplied. The OrderBy() method is removed from the query because the PagingList will take care of the ordering. The sortExpression and the default sort expression are now required to create the PageList model.

public async Task<IActionResult> Index(int page = 1, 
                                       string sortExpression = "CompanyName") {

    var qry = _context.Suppliers.AsNoTracking();
    var model = await PagingList.CreateAsync(
                            qry, 10, page, sortExpression, "CompanyName");
    return View(model);
}

In the View I modified the header of the table. I replaced the DisplayNameFor() calls by SortableHeaderFor() calls. I pass the Model as an extra parameter. This is not required but if you do you will get nice Up and Down indicators (glyphs) in the headers when you sort ascending or descending. You can also specify the SortColumn if you want a different one as the model property. In this example I used this for the City column. There I added the CompanyName as a second column to sort on.

<table class="table table-striped">
    <thead>
        <tr>
            <th>
                @Html.SortableHeaderFor(model => model.CompanyName, this.Model)
            </th>
            <th>
                @Html.SortableHeaderFor(model => model.ContactName, this.Model)
            </th>
            <th>
                @Html.SortableHeaderFor(model => model.Address, this.Model)
            </th>
            <th>
                @Html.SortableHeaderFor(model => model.City, "City, CompanyName", this.Model)
            </th>
            <th></th>
        </tr>
    </thead>

This results in the following view in which you can sort the table by clicking the headers of the columns. An up or down indicator shows you weather you are sorting ascending or descending.

Adding Filtering

To show filtering I will switch to the Products table, it has more records. The Index() method of the ProductsController has a filter parameter (besides page and sortExpression). The query is defined with an extra AsQueryable() method call. This method returns the the type IQueryable<Products>. This allowed me to append an extra Where "clause" to it when the filter is not empty.  In this example I used a Contains() which translates to a like '%...%' condition.

You have to set the RouteValue property of the model with all conditions used in the filter. This is used to build the correct URL in the pager and table headers.

public async Task<IActionResult> Index(string filter, int page = 1, 
                                       string sortExpression = "ProductName") {

    var qry = _context.Products.AsNoTracking()
        .Include(p => p.Category)
        .Include(p => p.Supplier)
        .AsQueryable();

    if (!string.IsNullOrWhiteSpace(filter)) {
        qry = qry.Where(p => p.ProductName.Contains(filter));
    }

    var model = await PagingList.CreateAsync(
                                 qry, 10, page, sortExpression, "ProductName");

    model.RouteValue = new RouteValueDictionary {
        { "filter", filter}
    };

    return View(model);
}

I added a form in the Index.cshtml with an input named filter and a submit button which has the method set to GET. This will execute the Index action of the controller.

@model ReflectionIT.Mvc.Paging.PagingList<WebApplication8.Models.Database.Products>
@using ReflectionIT.Mvc.Paging
@addTagHelper *, ReflectionIT.Mvc.Paging

@{
    ViewData["Title"] = "Products";
}

<h2>Products</h2>

<p>
    <a asp-action="Create">Create New</a>
</p>

<form method="get" class="form-inline">
    <input name="filter" class="form-control" placeholder="filter" 
           value="@Model.RouteValue["Filter"]" />
    <button type="submit" class="btn btn-info">
        <span class="glyphicon glyphicon-search" aria-hidden="true"></span> Search
    </button>
</form>

<nav aria-label="Products navigation example">
    <vc:pager paging-list="@Model" />
</nav>

<table class="table table-striped">
    <thead>
        <tr>
            <th>
                @Html.SortableHeaderFor(model => model.ProductName, this.Model)
            </th>
            <th>
                @Html.SortableHeaderFor(model => model.Supplier.CompanyName, 
                               "Supplier.CompanyName, ProductName", this.Model)
            </th>
            <th>
                @Html.SortableHeaderFor(model => model.Category.CategoryName, 
                              "Category.CategoryName, ProductName", this.Model)
            </th>
            <th>
                @Html.SortableHeaderFor(model => model.UnitPrice, this.Model)
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody>

This results in the following view in which you can filter on ProductName and you have paging and sorting.

Customize

You can customize this solutions in a few ways. You can for instance call the AddPaging method which you can use to set the PagingOptions. This allows you to specify which View is used for the Pager ViewComponent. By default a pager based on Bootstrap3 is used. But there is also already a Bootstrap4 view available. Bootstrap4 doesn't support glyphs any more so if you switch to it you also have to specify alternatives for the Up/Down indicators used in the sortable headers of the table.

services.AddPaging(options => {
    options.ViewName = "Bootstrap4";
    options.HtmlIndicatorDown = " <span>&darr;</span>";
    options.HtmlIndicatorUp = " <span>&uarr;</span>";
});

You can also create your own Pager view if you want to. You store it in the folder Views\Shared\Components\Pager. The easiest way to create a custom view is by coping the default Bootstrap3.cshtml from Github. You can remove the Bootstrap classes and add your own or you can generate your own html buttons.

You can also render the Pager using the Html.Partial() Html Helper instead of using the Pager ViewComponent. The following snippet shows the 'SmallPager.cshtml' which I stored in the Views/Shared folder. When you store it in this folder you can access it from all views.

<nav aria-label="Products navigation example">
    @Html.Partial("SmallPager", this.Model)
</nav>

Closure

You can download my sample app using the Download button below. I have published the code of the Pager on GitHub. It also contains a sample webapp which uses Bootstrap4. It's open source. So if you create a better pager please do a pull-request.

Fons

 

Leave a Comment

Leave a Comment
Name
Comment
4 + 3 =

39 Comments

  • qalb
    4 dec 2018 10:12
    how to change index method to any other method for paging ?
  • Augustus Wekey
    2 dec 2018 02:28
    zzzzzz內容zzzzzzz
  • Nichole Cockram
    1 dec 2018 08:05
    zzzzzz内容zzzzzz
  • Aly Chiman
    30 nov 2018 08:29
    Hello there, My name is Aly and I would like to know if you would have any interest to have your website here at reflectionit.nl promoted as a resource on our blog alychidesign.com ? We are in the midst of updating our broken link resources to include current and up to date resources for our readers. Our resource links are manually approved allowing us to mark a link as a do-follow link as well . If you may be interested please in being included as a resource on our blog, please let me know. Thanks, Aly
  • Azam Ali
    6 nov 2018 09:39
    Thanks for sharing, only issue, Sort doesn't work with nested object. @Html.SortableHeaderFor(m => m.City.Name) will generate column name and sort expression correctly "sortExpression=City.Name" but will throw error, when we actually try to sort that column. ReflectionIT.Mvc.Paging.LinqExtensions.GetPropertyInfo(Type objType, string name) in LinqExtensions.cs, line 13
  • Kübra
    1 nov 2018 09:33
    Thank you sooo much.
  • kübra
    17 okt 2018 04:13
    css does not work in paging numbers. what do i do with the core 2.1 reflection 3.1.0
  • kübra
    17 okt 2018 04:13
    css does not work in paging numbers. what do i do with the core 2.1 reflection 3.1.0
  • kübra
    17 okt 2018 04:13
    css does not work in paging numbers. what do i do with the core 2.1 reflection 3.1.0
  • kübra
    17 okt 2018 04:13
    css does not work in paging numbers. what do i do with the core 2.1 reflection 3.1.0
  • Joe
    3 okt 2018 02:13
    This is a good article, thanks!
  • gevorg
    18 sep 2018 01:55
    hi! it is work correct when i use inex() method from my controller! but if i can use ather method paging work incorrect-always return to index page but i need say in my acction page and paging it!
  • Aspal
    24 aug 2018 06:23
    How to use Custom root more page?
  • Fabiano nalin
    17 aug 2018 02:59
    Thank you very much. It helped me a lot
  • Manuel
    13 aug 2018 12:42
    Common causes of this issue: The application process failed to start The application process started but then stopped The application process started but failed to listen on the configured port Troubleshooting steps: Check the system event log for error messages Enable logging the application process' stdout messages Attach a debugger to the application process and inspect
  • Manuel
    13 aug 2018 12:41
    HTTP Error 502.5 - Process Failure
  • Franco
    3 aug 2018 09:43
    I get this error: Error CS0400: The type or namespace name '__Generated__PagerViewComponentTagHelper' could not be found in the global namespace (are you missing an assembly reference?)
  • Claudio Medina
    25 jul 2018 04:58
    EXCELLENT
  • Olayinka Wasiu
    8 jun 2018 01:01
    The is helpful, but how can I change the default Action in using this example, so that redirect it to my desire page.
  • Jterhune
    4 jun 2018 04:19
    Life saver!
  • Ndubuisi
    27 mei 2018 11:57
    You are an Excellent teacher.
  • Gigios74
    11 mei 2018 05:40
    Fantastic!!!! Thanks
  • Gareth
    27 apr 2018 01:57
    Before I try it and break my project - do you know if this works with Razor pages?
  • Yuriy
    22 apr 2018 10:09
    Can you provide sample how to change a pager style? I didn't find a css file where can I set a custom color or size of buttons.
  • Gabi Luca
    6 apr 2018 05:21
    Hi, Can I also do paging on inner collections?
  • Andrew
    24 mrt 2018 03:05
    I want to change a column name after sorting, how is it possible?
  • Antoin McCloskey
    23 feb 2018 01:44
    This is fantastic. Best solution I came across. Thanks!
  • Sam
    13 feb 2018 04:52
    Any advice for assigning a placeholder to the sortable headers?
  • Amen
    24 jan 2018 11:28
    Thank you. Simple, easy to implement, and life saving
  • Anas
    25 dec 2017 07:56
    Thanks , But i want to ask how i can keep filter and sorting during paging .
  • Mario
    13 dec 2017 06:21
    This is absolutely wonderful. Thank you ! You saved me for a lot of work.
  • Jake
    13 nov 2017 05:40
    How can you just do a normal paging providing your own collection of data. i.e. without using EF. in my case i call an API that gives me a collection back. thank you
  • pete timov
    3 nov 2017 01:57
    good article on getting started with core and ef core.
  • Mohamed
    5 okt 2017 11:30
    Great Article, thanks
  • Michelle
    6 sep 2017 05:40
    I am using View component to View List and I am getting Null in "sort Expression". Can you please help how to use paging and sorting in view component?
  • Hoàng Nd
    5 sep 2017 05:33
    Thanks :))
  • Mittal
    1 sep 2017 12:22
    How to sort multiple columns?
  • Dipak Moliya
    2 aug 2017 10:45
    Excellent! It works perfectly with model. It will be appreciated if you will suggest me how we can use with any ViewModel instead of model.
  • Tim Paque
    20 jul 2017 11:10
    Thanks! Nice, simple, elegant. You saved me a lot of work.

All postings/content on this blog are provided "AS IS" with no warranties, and confer no rights. All entries in this blog are my opinion and don't necessarily reflect the opinion of my employer or sponsors. The content on this site is licensed under a Creative Commons Attribution By license.