ASP.NET Core MVC Search, Sorting, and Pagination with EF Core

The foundation: what the official documentation explains

Microsoft demonstrates sorting, filtering, and paging in MVC with EF Core. Offset pagination uses Skip and Take over an ordered query. Ordering should be fully unique to make page boundaries deterministic. Large offsets can become expensive; keyset pagination is an alternative when the interface only needs next and previous navigation.

• Microsoft Learn: MVC sorting, filtering, and paging
• Microsoft Learn: EF Core pagination

Implementation context: The examples use a .NET 10 MVC application organized into feature folders (Vertical Slice Architecture). Basic C# classes and async/await are assumed. Reference excerpts show selected parts of that application; separately labeled teaching adaptations explain alternatives. They are not complete standalone projects.

Start with the question the table asks

The Country Index screen uses a server-side table. A search or page change sends another request to /api/country; it does not merely hide rows already downloaded. The endpoint translates query parameters into DataTableRequest, and GetCountryListHandler builds the query. This separation lets a beginner inspect request interpretation independently from database filtering.

The handler begins with an IQueryable over Country. Composing Where, OrderBy, and Select builds the query expression. The database call occurs when an executing operation such as CountAsync or ToListAsync is awaited. Avoid calling ToList early just to make subsequent filtering look like familiar in-memory C#.

Reference excerpt: Areas/Admin/Country/Cqrs/GetCountryListHandler.cs

var query = _context.Country.AsQueryable();

        if (!string.IsNullOrWhiteSpace(request.Search))
        {
            var search = request.Search.ToLower();
            query = query.Where(x =>
                (x.Code != null && x.Code.ToLower().Contains(search)) ||
                (x.Name != null && x.Name.ToLower().Contains(search)) ||
                (x.Description != null && x.Description.ToLower().Contains(search)));
        }

Use an explicit sort contract

The handler maps integer column identifiers to known properties with a switch. This is easier to audit than constructing SQL from an arbitrary client-supplied column string. However, integers only work when both ends agree on their meaning. The current UI includes a selection-checkbox column, so its physical column positions must be reconciled with the backend switch.

The reference handler maps 0 to AutoNumber, 1 to Code, 2 to Name, and 3 to Description. The screen has additional display columns. Treat that mismatch as a contract check when adapting the pattern; copying the numeric index without checking can sort by the wrong property. A teaching adaptation can send a named sort key, or deliberately translate the UI index before making the request.

Project the response before executing the query

The handler projects into CountryListItem instead of serializing a complete entity graph. The DTO contains identifiers, display fields, and audit fields relevant to this list. The shared pagination helper receives that projected query. Projection keeps the response contract easy to find and avoids accidentally expanding related entities just because a navigation property was added.

Reference excerpt: Shared/QueryableExtensions.cs

var total = await query.CountAsync(cancellationToken);

        var items = await query
            .Skip((request.Page - 1) * request.PageSize)
            .Take(request.PageSize)
            .ToListAsync(cancellationToken);

Check page input before calculating offsets

The shared request type supports both page/pageSize and start/length forms. Its ResolvePaging method prefers start/length when those values are present under its condition. The defaults also matter: DefaultLength is 10, so a caller should not assume a supplied page value always survives normalization. For this implementation, use the table-style start and length consistently while learning the actual route.

For example, /api/country?start=10&length=10&search=ind asks for the second ten-row segment of matching results. In an adaptation, validate positive page sizes and nonnegative offsets at the boundary. Reject invalid sizes before division, and set an application-appropriate maximum rather than allowing an unbounded request.

Teaching adaptation: start and length are parsed query integers

// Teaching boundary guard before normalizing start/length:
if (start < 0 || length < 1 || length > 100)
{
    return Results.BadRequest("Use start >= 0 and length between 1 and 100.");
}

var page = (start / length) + 1;

Distinguish filtered totals from all rows

The helper counts the query it receives, which has already been filtered. Its Total therefore represents matching rows. The reference frontend assigns that value to both table totals. If the UI must display an independent unfiltered total, the server contract needs a separate count; it cannot recover that number from the filtered query result alone.

For deterministic paging, add a unique tie-breaker to a nonunique sort in an adaptation, such as Name followed by Id. Do not claim the current CreatedAt-only fallback provides that guarantee. For provider-specific performance, inspect the generated query and indexes rather than assuming a string ToLower expression uses the ideal index strategy.

Verify the list with small, observable data

Use a local dataset larger than one page with repeated names. Search for a known subset, sort each displayed column, and move between pages. Confirm the visible sort matches the clicked heading and that counts describe what the UI claims. Try invalid page lengths through an API client. These checks expose parameter-contract errors that a successful build will not catch.

Key Takeaways

• Compose filtering, sorting, and projection before materialization.
• Align UI columns with the backend sort contract.
• Validate paging input and distinguish matching totals from overall totals.

FAQ

Should I load every row and then paginate?

For a server-side list, keep paging in the database query so the server does not load the whole result set.

Why does clicking Name sort another field?

The table column position and server sort mapping may disagree, especially when selection or action columns are present.

Does Total mean every row in the table?

In the shown helper it counts the already-filtered query, so it means matching rows.