Filtering and Sorting

A caller that wants the active employees in the support department, newest first, has two options. Fetch everything and work it out locally, or tell you what it wants. The first wastes a network round trip proportional to your table size, so real APIs offer the second.

The query string is where that goes: /api/employees?departmentId=3&status=active&sort=-startDate. The design question is not whether to support it. It is which fields you are willing to be asked about, and that list needs to be finite and chosen by you.

# Employees in department 3, newest start date first
GET /api/employees?departmentId=3&sort=-startDate&page=1&pageSize=20

# Two statuses, treated as "either"
GET /api/employees?status=active&status=onLeave

# A date range, with both ends named explicitly
GET /api/orders?placedFrom=2024-09-01&placedTo=2024-09-30

# An unknown field: rejected rather than ignored
GET /api/employees?sort=salary   ->   400 Bad Request

• Filter names match the field names in your responses. A caller who read departmentName and departmentId in a response can guess the filter without consulting documentation.
• A repeated parameter is the plain way to express several accepted values, and OR is what a caller expects from it.
• Ranges use two named parameters rather than an expression such as placed>2024-09-01. Named parameters cannot be turned into anything other than what you designed.
• The last line is a decision worth making deliberately: sorting by salary is not offered, so the request fails with a message naming the fields that are. Ignoring the parameter instead would return the whole collection in the default order, and the caller would believe it was sorted.

static readonly string[] SortableFields = ["fullName", "startDate"];

static IQueryable<Employee>? ApplySort(IQueryable<Employee> query, string? sort)
{
    var descending = sort is not null && sort.StartsWith('-');
    var field = sort?.TrimStart('-');

    return field switch
    {
        null or "" or "fullName" => descending
            ? query.OrderByDescending(e => e.FullName).ThenBy(e => e.Id)
            : query.OrderBy(e => e.FullName).ThenBy(e => e.Id),
        "startDate" => descending
            ? query.OrderByDescending(e => e.StartDate).ThenBy(e => e.Id)
            : query.OrderBy(e => e.StartDate).ThenBy(e => e.Id),
        _ => null
    };
}

• The switch is the allow-list. The caller's value selects one of the branches you wrote; it never becomes part of a query itself.
• Adding a sortable field means editing this method, which is the natural moment to check that an index supports the new ordering.
• An unrecognised field returns null rather than falling back to a default order, so the handler can tell the caller their request was not honoured.
• Every branch ends with ThenBy(e => e.Id). Ordering has to be deterministic for the paging this endpoint will grow into, and a unique column last is what makes it so.

app.MapGet("/api/employees", async (
    AppDbContext db, CancellationToken ct,
    int? departmentId = null, string? sort = null) =>
{
    IQueryable<Employee> query = db.Employees;

    if (departmentId is not null)
    {
        query = query.Where(e => e.DepartmentId == departmentId);
    }

    var sorted = ApplySort(query, sort);
    if (sorted is null)
    {
        return Results.ValidationProblem(new Dictionary<string, string[]>
        {
            ["sort"] = [$"Sort must be one of: {string.Join(", ", SortableFields)}."]
        });
    }

    var items = await sorted
        .Take(20)
        .Select(e => new EmployeeListItem(e.Id, e.FullName, e.Department!.Name))
        .ToListAsync(ct);

    return Results.Ok(new { items, count = items.Count });
});

• departmentId is typed as int?, so departmentId=banana is rejected by model binding before your code runs. Typing a filter parameter does validation work for you.
• The filter is applied only when it was supplied, and the query is not executed until ToListAsync, so the database receives one statement containing exactly the clauses that were asked for.
• An unknown sort returns 400 with a message naming the fields that are allowed. That tells the caller what to do without describing your schema to them.
• Take(20) stands in for the paging from the previous lesson. A collection endpoint with filters and no limit still returns the whole table whenever the filters are omitted.