Sorting, filtering and paging a list
Almost every app has a list page with search, sort and paging. This lesson builds one end to end: query string parameters, an IQueryable that stays in the database, a page model, and links that keep the current filters.
- StartQuery string?search=pen&sort=price&page=2
- Step 2WhereAdded only when a search was given
- Step 3OrderByFrom an allow-list of sort options
- Step 4Count + Skip/TakeTotal rows and one page
- EndViewRows plus links that keep the filters
The parameters
Keep search, sort and page in the query string so a list can be bookmarked and shared, and so the back button works. Bind them to a small class.
public class ProductQuery
{
public string? Search { get; set; }
public string Sort { get; set; } = "name";
public int Page { get; set; } = 1;
public const int PageSize = 20;
}
public record ProductPage(IReadOnlyList<ProductListItem> Items, ProductQuery Query, int Total)
{
public int Pages => (int)Math.Ceiling(Total / (double)ProductQuery.PageSize);
}The action
Build the query step by step. Nothing runs until CountAsync and ToListAsync, so filtering, sorting and paging all happen in SQL.
public async Task<IActionResult> Index([FromQuery] ProductQuery q, CancellationToken ct)
{
IQueryable<Product> query = db.Products.AsNoTracking();
if (!string.IsNullOrWhiteSpace(q.Search))
query = query.Where(p => p.Name.Contains(q.Search));
query = q.Sort switch // allow-list: never sort by raw user text
{
"price" => query.OrderBy(p => p.Price).ThenBy(p => p.Id),
"price_desc" => query.OrderByDescending(p => p.Price).ThenBy(p => p.Id),
_ => query.OrderBy(p => p.Name).ThenBy(p => p.Id),
};
var total = await query.CountAsync(ct);
q.Page = Math.Clamp(q.Page, 1, Math.Max(1, (int)Math.Ceiling(total / (double)ProductQuery.PageSize)));
var items = await query
.Skip((q.Page - 1) * ProductQuery.PageSize)
.Take(ProductQuery.PageSize)
.Select(p => new ProductListItem(p.Id, p.Name, p.Price, p.Category.Name, p.Reviews.Count))
.ToListAsync(ct);
return View(new ProductPage(items, q, total));
}The view
A GET form for search, and links that keep the other parameters with asp-route-*.
@model ProductPage
<form method="get">
<input name="search" value="@Model.Query.Search" placeholder="Search products" />
<button>Search</button>
</form>
<a asp-route-search="@Model.Query.Search" asp-route-sort="name">Name</a> |
<a asp-route-search="@Model.Query.Search" asp-route-sort="price">Price</a>
@foreach (var p in Model.Items) { <p>@p.Name: @p.Price.ToString("C")</p> }
@if (Model.Query.Page > 1)
{
<a asp-route-search="@Model.Query.Search" asp-route-sort="@Model.Query.Sort"
asp-route-page="@(Model.Query.Page - 1)">Previous</a>
}
<span>Page @Model.Query.Page of @Model.Pages</span>
@if (Model.Query.Page < Model.Pages)
{
<a asp-route-search="@Model.Query.Search" asp-route-sort="@Model.Query.Sort"
asp-route-page="@(Model.Query.Page + 1)">Next</a>
}Interview question
Why should filtering and paging use IQueryable instead of loading a List first?
IQueryable builds an expression that EF Core translates to SQL, so the WHERE, ORDER BY, OFFSET and FETCH run in the database and only one page of rows is sent to the app. Calling ToList first loads the whole table into memory and filters it there, which is slow and uses a lot of memory as the table grows.
Want the full details? Read the official Microsoft documentation for this topic.