Paging in ASP.NET Core MVC and EntityFramework Core

By Fons Sonnemans, posted on
46717 Views 60 Comments

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

 

Download

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.

Leave a comment

Blog comments

Tim Paque

20-Jul-2017 11:10
Thanks! Nice, simple, elegant. You saved me a lot of work.

Dipak Moliya

02-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.

Mittal

01-Sep-2017 12:22
How to sort multiple columns?

Hoàng Nd

05-Sep-2017 5:33
Thanks :))

Michelle

06-Sep-2017 5: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?

Mohamed

05-Oct-2017 11:30
Great Article, thanks

pete timov

03-Nov-2017 1:57
good article on getting started with core and ef core.

Jake

13-Nov-2017 5: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

Mario

13-Dec-2017 6:21
This is absolutely wonderful. Thank you ! You saved me for a lot of work.

Anas

25-Dec-2017 7:56
Thanks , But i want to ask how i can keep filter and sorting during paging .

Amen

24-Jan-2018 11:28
Thank you. Simple, easy to implement, and life saving

Sam

13-Feb-2018 4:52
Any advice for assigning a placeholder to the sortable headers?

Antoin McCloskey

23-Feb-2018 1:44
This is fantastic. Best solution I came across. Thanks!

Andrew

24-Mar-2018 3:05
I want to change a column name after sorting, how is it possible?

Gabi Luca

06-Apr-2018 5:21
Hi, Can I also do paging on inner collections?

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.

Gareth

27-Apr-2018 1:57
Before I try it and break my project - do you know if this works with Razor pages?

Gigios74

11-May-2018 5:40
Fantastic!!!! Thanks

Ndubuisi

27-May-2018 11:57
You are an Excellent teacher.

Jterhune

04-Jun-2018 4:19
Life saver!

Olayinka Wasiu

08-Jun-2018 1:01
The is helpful, but how can I change the default Action in using this example, so that redirect it to my desire page.

Claudio Medina

25-Jul-2018 4:58
EXCELLENT

Franco

03-Aug-2018 9: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?)

Manuel

13-Aug-2018 12:41
HTTP Error 502.5 - Process Failure

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

Fabiano nalin

17-Aug-2018 2:59
Thank you very much. It helped me a lot

Aspal

24-Aug-2018 6:23
How to use Custom root more page?

gevorg

18-Sep-2018 1: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!

Joe

03-Oct-2018 2:13
This is a good article, thanks!

kübra

17-Oct-2018 4:13
css does not work in paging numbers. what do i do with the core 2.1 reflection 3.1.0

kübra

17-Oct-2018 4:13
css does not work in paging numbers. what do i do with the core 2.1 reflection 3.1.0

kübra

17-Oct-2018 4:13
css does not work in paging numbers. what do i do with the core 2.1 reflection 3.1.0

kübra

17-Oct-2018 4:13
css does not work in paging numbers. what do i do with the core 2.1 reflection 3.1.0

Kübra

01-Nov-2018 9:33
Thank you sooo much.

Azam Ali

06-Nov-2018 9: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

qalb

04-Dec-2018 10:12
how to change index method to any other method for paging ?

Michael Jacobs

29-Jan-2019 10:12
Does this work with Asp.Net Core 2.2? Paging does not seem to work.

Jarek

01-Mar-2019 11:09
Asp.Net Core 2.2 Paging not working How to fix?

Moshiur Rahman

28-Mar-2019 7:38
Page button does not work in asp.net core 2.2

Elijah

06-Apr-2019 1:22
How do I use the Different Controller and View? I have Home Controller and CalculatorsTestMVC razor view. How do I route that on the CalculatorsTestMVC.cshtml page when using @Html.SortableHeaderFor(model = >model.Id) my controller == return View(model);

Atalay

03-May-2019 1:41
Hi, my web app gives http 500 error since I added reflection.mvc.paging. Is it maybe because of some supporting issue?

Atalay

04-May-2019 12:16
Works just fine. I downgraded version before I fix unrelated problems so IAM not sure if versioning would still be a problem . Just bare in mind that latest version requires core 2.3 or newer. Thanks.

shweta

17-Jun-2019 12:21
we get the practice knowledge about dot net application after reading this blog.......... thanks for sharing.....keep going on.....

Thanks

25-Jun-2019 3:24
Just wanted to say thanks for the awesome Paging code. It has been a tremendous help!

Amin

29-Aug-2019 6:17
Hi sorry to ask this question i am just started aspnet core how to use this library with ajax ? i couldn't find a suitable library that use it with ajax thank you so much

Andrew

30-Aug-2019 6:05
It is a really bad idea to have the paging for a table rely on a Page parameter in the QueryString. Particularly where the actual PageSize may be greater than the search results, in which case a Page parameter of 2 in the querystring would be incorrect. I ripped this out of the project I am working on and replaced it with DataTables.

lakshmi

07-Oct-2019 11:07
one Quick Question Please If we have more than 10 columns in table and I would like to show only 6 columns initially in this case is there any way to implement paging for columns? If Yes please give provide me the related information. Thanks

Donprecious

27-Oct-2019 6:32
This Pagniation Library has poor documentation,

Donprecious

27-Oct-2019 6:32
This Pagniation Library has poor documentation,

Donprecious

27-Oct-2019 6:32
This Pagniation Library has poor documentation,

Hosein

21-Dec-2019 7:10
Hello! I want to use 3 pagination in one page. Is it possible with ReflectionIT Paging List?

efe

29-Jan-2020 8:22
Hi, How to use in viewmodel

Kinan Tassapehji

04-Mar-2020 12:38
I Think you should make pagination to comments at this article

Igor

02-Apr-2020 10:24
Have problem with filter , var qry = _context.Employees.AsNoTracking().Include(p => p.Name).Include(p => p.Email).AsQueryable(); if (string.IsNullOrWhiteSpace(filter)) { var query = _context.Employees.AsNoTracking().OrderBy(s => s.Id); var model = await PagingList.CreateAsync(query,3,page); return View(model); } else { qry = qry.Where(p => p.Name.Contains(filter)); var mode = await PagingList.CreateAsync(qry, 3, page, sortExpression, "ProductName"); mode.RouteValue = new RouteValueDictionary { { "filter", filter } }; return View(mode); } and returns : var mode = await PagingList.CreateAsync(qry, 3, page, sortExpression, "ProductName");

Alloylo

16-Apr-2020 2:12
I want the user to filter the data table not only by ProductName, but also with Id or other attributes how can I do it if (!string.IsNullOrWhiteSpace(filter)) { qry = qry.Where(p => p.ProductName.Contains(filter)); } model.RouteValue = new RouteValueDictionary { { "filter", filter} };

Athena Benge

07-Apr-2021 8:57
Quite! This has become a very great article. Thanks for providing this information and facts.

sina

12-Jul-2021 5:27
hi. thank you for this solution. can you tell me how to use this solution on asp.net core 5.0 (for example services.AddPaging(); not working on)

Алексей

18-Jul-2021 9:24
Здравствуйте. У меня такое решение выдает ошибку: System.NullReferenceException: "Object reference not set to an instance of an object."

Navid

15-Oct-2021 11:13
The pagination section really helped me thank you for this solution.

Al-Hadi

07-Mar-2023 7:13
Fantastic !!Article. It helped me a lot. Thank you so much. but have an issue in mode.RouteValue, when I use array or list in filter value (like: new RouteValueDictionary { { "selectedCountries", [3,5,7] } }) outcome: selectedCountries=3,5,7 but desired: selectedCountries=3&selectedCountries=5&selectedCountries=7 I think issue in here (page:PagingListOfT.cs, line: 75 -return dict.ToDictionary(kvp => kvp.Key, kvp => kvp.Value?.ToString());), not considered kvp.Value as array/list. Thanks