Querying, search & pagination

Understand the idea

A list page often begins as ToListAsync and grows into a performance problem as data accumulates. The key is to keep work in the database until you have applied the filters, ordering, and limits that define the result.

IQueryable represents a query that a provider can translate. Calling ToListAsync executes it. After that point you have in-memory objects, and later filtering no longer reduces database work or transfer size.

Compose the query before running it

Start with ownership and AsNoTracking. Add the search predicate only when a meaningful query is present. At this point no rows have been fetched. CountAsync executes a count against the filtered set so pagination describes the same records as the list.

Contains is translated by the database provider. Case sensitivity and matching behavior depend on the database and collation. Avoid promising identical search semantics across SQLite and SQL Server without testing them.

var query = db.Tasks.AsNoTracking()
    .Where(t => t.OwnerId == OwnerId);
if (!string.IsNullOrWhiteSpace(q))
    query = query.Where(t => t.Title.Contains(q));
var count = await query.CountAsync(ct);

Bound and order the result

Clamp the requested page to the available range. Order by CreatedUtc and then Id so equal timestamps do not make the ordering ambiguous. Only then apply Skip and Take.

Offset pagination is simple and supports jumping to numbered pages. At very large offsets, keyset pagination can be more efficient. Choose based on the navigation your users need, and measure with representative data before complicating a small feature.

const int pageSize = 6;
var totalPages = Math.Max(1, (int)Math.Ceiling(count / (double)pageSize));
page = Math.Clamp(page, 1, totalPages);
var tasks = await query
    .OrderByDescending(t => t.CreatedUtc).ThenBy(t => t.Id)
    .Skip((page - 1) * pageSize).Take(pageSize)
    .ToListAsync(ct);

Preserve the search through navigation

The next-page link carries both page and q. Without the search value, moving to page two would show a different result set. An empty list also deserves a deliberate state: show a helpful message and keep the search form available.

Count and page queries are separate round trips, so concurrent writes can change the set between them. That is generally acceptable for a task list. If you require a consistent snapshot, choose an appropriate transactional strategy and understand its cost.

@if (Model.Page < Model.TotalPages)
{
    <a asp-action="Index" asp-route-q="@Model.Query"
       asp-route-page="@(Model.Page + 1)">Next →</a>
}

Project only what a client needs

An API list usually needs fewer fields than an edit form. Select a response shape before materialization to avoid returning owner identifiers or large notes unnecessarily. The sample API caps the response at 100 rows; that bound is part of the example’s behavior.

Use SQL logging during development to inspect executed queries. Watch for repeated per-row queries, accidental early materialization, and indexes that do not match the actual predicates. Do not enable sensitive-data logging in a public environment.

var items = await db.Tasks.AsNoTracking()
    .Where(t => t.OwnerId == OwnerId)
    .OrderBy(t => t.Id).Take(100)
    .Select(t => new { t.Id, t.Title, t.IsComplete })
    .ToListAsync(ct);

When something doesn't work

Repeated or missing rows between page requests can occur when data changes. Stable ordering prevents arbitrary ties but does not freeze the dataset.

If a LINQ expression cannot translate, simplify it or move only the necessary final calculation to memory after a bounded query. Do not fix every translation error by loading the whole table.

Apply owner filtering before both CountAsync and ToListAsync.