Ivan Ball-llovera

↑↓ to navigate Enter to open "…" all these words ANDOR to combine

Auth & the edge · No. 28

Generic entity controllers and the dynamic query contract (ADR-034)

Ivan Ball-llovera Also on Medium ↗

The write-once REST surface every entity inherits. How list, page, lookup, by-id, create, and delete come from two base classes, plus a bounded OData-lite query contract that is dynamic over the wire but never open SQL in the engine.

Here is a controller that looks fine until you have a second entity:

[ApiController]
[Route("products")]
public sealed class ProductsController : ControllerBase
{
    [HttpGet]
    public async Task<IActionResult> GetAll(
        string? sort, string? dir, int page = 1, int size = 20,
        string? name = null, decimal? minPrice = null)   // ad-hoc filter params
    {
        var q = _db.Products.AsQueryable();
        if (name is not null) q = q.Where(p => p.Name.Contains(name));      // hand-rolled
        if (minPrice is not null) q = q.Where(p => p.Price >= minPrice);    // hand-rolled
        if (sort == "name")
            q = dir == "desc" ? q.OrderByDescending(p => p.Name) : q.OrderBy(p => p.Name);
        var items = await q.Skip((page - 1) * size).Take(size).ToListAsync();
        return Ok(items);   // no total count, no page-size cap, leaks the entity to the wire
    }
    // ... GetById, Create, Delete, each subtly different from CategoriesController
}

Now write CategoriesController, OrdersController, and twenty more. A different person writes each one in a different sprint. One paginates with page/size, the next with pageNumber/pageSize. One filters with ?name=, another with ?nameContains=. One returns a total count, most do not. One forgets the Take clamp entirely, so a query with no filter streams the whole table into memory. Each leaks its EF entity straight to JSON, so a column rename is a silent wire break. This is the single largest pile of boilerplate a modular monolith accumulates as it grows, and the pile drifts in shape with every commit.

A client cannot learn one query dialect and reuse it. It learns one per resource, defensively, and still gets surprised. And the day you want a uniform feature (a max page size, a filter operator, a sparse-fieldset projection) you are editing dozens of controllers by hand and missing some.

Why it matters

Most entities need the same six things: list them, page through them, fetch a lightweight id/name list for a dropdown, get one by id, create one, delete one. That surface is so uniform that hand writing it per entity is pure repetition, and repetition without a single source of truth is how drift gets in. The shape that should be identical across a hundred entities ends up almost-identical, which is worse than identical because callers cannot rely on it.

There is also a safety dimension. A read endpoint with no upper bound on result size is one missing Where clause away from a full-table load that pins a database. A filter parameter assembled from raw client input is an injection and over-fetch surface. If those guards are the responsibility of each author, they are missing somewhere.

ADR-034 records the framework's opposite default: write the resource surface and the query contract once, on a base class, and let every entity inherit it. A concrete controller becomes a few lines that close the generic type parameters. The verbs, routes, filtering, sorting, pagination, field projection, include behavior, and the safety ceiling all come from the base. New entities cost almost nothing and cannot drift in shape.

The MMCA answer: two base classes and an OData-lite contract

The read surface is EntityControllerBase<TEntity, TEntityDTO, TIdentifierType>. It is an [ApiController] with [Route("[controller]")] and [ApiVersion("1.0")], and it exposes five GET routes that any entity inherits for free: [HttpGet] for the capped list, [HttpGet("paged")] for the filterable/sortable page, [HttpGet("export")] for a streamed CSV of the same filtered, sorted, projected rows, [HttpGet("lookup")] for an id/name dropdown collection (CollectionResult<BaseLookup<TIdentifierType>>), and [HttpGet("{id}")] for a single record.

The write surface is AggregateRootEntityControllerBase<TEntity, TEntityDTO, TIdentifierType, TCreateRequest>. It inherits all five reads and adds [HttpPost] create (returning 201 CreatedAtRoute) and [HttpDelete("{id}")] (returning 204 NoContent). The create action is decorated [Idempotent], so a retried POST carrying the same Idempotency-Key header replays the original response instead of creating a duplicate (ADR-017).

A concrete controller is the whole thing:

[Route("products")]
public sealed class ProductsController(
    IEntityQueryService<Product, ProductDTO, int> queryService,
    ICommandHandler<CreateProductRequest, Result<ProductDTO>> createHandler,
    ICommandHandler<DeleteEntityCommand<Product, int>, Result> deleteHandler,
    ILogger<EntityControllerBase<Product, ProductDTO, int>> logger)
    : AggregateRootEntityControllerBase<Product, ProductDTO, int, CreateProductRequest>(
        queryService, createHandler, deleteHandler, logger);

// That is the entire controller. GET, GET /paged, GET /export, GET /lookup,
// GET /{id}, POST, and DELETE /{id} are all inherited, with filter, sort, page,
// and field projection on the read routes and idempotent create on the write route.

What that inheritance buys is a uniform query contract on every list and detail route:

  • Sparse fieldsets. A comma-separated fields query parameter drives a server-side projection. QueryFieldService.ApplyFieldSelection builds a MemberInit expression that selects only the requested writable properties, so only those columns leave the database, and the compiled expression is cached per entity type and field set so a repeated fields= request does not rebuild the tree. That cache is capped at 512 entries, since the field set is client-supplied; past the cap a request skips server-side projection instead of growing the cache further, though the response body is still trimmed to the requested fields. Read-only and computed properties are rejected at validation, since the projection needs setters.
  • Dynamic per-type filtering. The paged route binds Dictionary<string, (string Operator, string Value)> filters through [ModelBinder(typeof(QueryFilterModelBinder))]. The binder parses ?filters[Name].operator=contains&filters[Name].value=shirt style keys (case-insensitive, incomplete pairs silently dropped) into the dictionary. QueryFilterService.ApplyFilters then resolves one IFilterStrategy per property CLR type from a strategy registry (string, bool, int, long, DateTime, decimal, Guid and their nullables out of the box), and each strategy declares the operator set it SupportedOperators. This is the load-bearing constraint: filtering is dynamic over the wire but it is not free-form expression evaluation. Every property routes through a registered strategy, and QueryFilterService.ValidateFilters rejects an unknown property or an unsupported operator before the database is touched.
  • Sort. sortColumn and sortDirection feed QueryFieldService.ApplySorting, an OrderBy("<col> ascending|descending") over the entity property the DTO name maps to.
  • Pagination and the X-Pagination header. The paged route clamps the requested page size with Math.Min(pageSize, MaxPageSize), where MaxPageSize reads IApplicationSettings.MaxPageSize and falls back to 500. The pagination metadata (total count, page size, current page) is serialized into the X-Pagination response header as JSON, so the body stays just the items.
  • A streamed CSV export. [HttpGet("export")] reuses the same filter, sort, and fields contract and writes rows to the response body a page at a time, so the file never lands in memory. It stops at MaxExportRows (IApplicationSettings.MaxExportRows, falling back to 100,000), announces that ceiling up front in an X-Export-Row-Limit header, and ends a truncated file with a trailing marker row: headers are frozen the moment the first body byte is flushed, so the marker is the only signal still writable once the export is under way. It takes no includeChildren, since a child collection has no faithful representation in a flat CSV cell.
  • A last-resort ceiling. Independent of the API clamp, EntityQueryPipeline.MaxUnboundedResultLimit = 1000 caps any unpaginated query with query.Take(MaxUnboundedResultLimit). Even a direct service caller who omits pagination entirely cannot trigger an unbounded full-table load.
  • Two include paths. includeFKs and includeChildren select navigation loading, and EntityQueryPipeline runs one of two strategies. PATH 1 handles source-supported includes via EF Core .Include() translated to SQL (forcing split-query when a child collection is included, so pagination does not truncate the JOIN-expanded rows). PATH 2 handles includes the source cannot JOIN by materializing the page first, then batch-loading related data via an INavigationPopulator (the cross-source loader of ADR-002).

Under all of this sits EntityQueryService, which validates the parameters up front, runs the pipeline, and projects the materialized entities to DTOs through an injected IEntityDTOMapper (the manual mapping of ADR-001). Every action returns a Result, and a failure flows through HandleFailure(result.Errors) into the same RFC 9457 Problem Details shape the rest of the API uses (ADR-013). The wire speaks DTO names; the engine speaks entity names; a per-service DTOToEntityPropertyMap translates between them for filtering and sorting.

The line where the user's value stops being code

There is one more constraint under all of this, and it is the one that decides whether a user-supplied filter DSL is a good idea or a liability. When a strategy builds its predicate, the property name is interpolated into the expression string but the user's value never is:

"CONTAINS" => query.Where(DynamicQueryConfig.Parameterized, $"{property}.Contains(@0)", value),
"EQUALS"   => query.Where(DynamicQueryConfig.Parameterized, $"{property} == @0", value),

The asymmetry is deliberate. property has already survived ValidateFilters, so it is a name from a known allow-list rather than user text. value is arbitrary user input, and it is passed as the @0 argument, never concatenated into the predicate.

That alone is the injection story, but the DynamicQueryConfig.Parameterized part is the half most teams miss. System.Linq.Dynamic.Core defaults UseParameterizedNamesInDynamicQuery to false, which turns each @0 argument into a ConstantExpression. EF inlines constants. So with the default config, filters["Name"] = ("EQUALS", "Widget") emits:

WHERE [Name] = 'Widget'

A distinct SQL string per distinct filter value. Every search term a user types costs its own SQL Server plan-cache entry and misses EF's compiled-query cache on the way in. The filter DSL you built for convenience quietly becomes a plan-cache eviction engine, and the symptom is not a slow query, it is the whole instance getting slower as the cache churns.

Flipping the flag on makes the value reachable through a member access instead, so EF parameterizes it and one plan serves every value. The config is a single shared static, because building a ParsingConfig per call would reintroduce the parse cost it exists to avoid, and QueryParameterizationTests is the guard that keeps a new strategy from forgetting to pass it.

This is worth internalizing as a general shape: the same change bought both a closed injection surface and a fixed plan-cache hit rate, because both problems have the same root cause, which is a user's value ending up in the text of a query instead of beside it.

Trade-offs, honestly

A generic, dynamically queryable contract coupled to the entity model is a real trade against narrow bespoke endpoints. ADR-034 names the costs rather than hiding them.

  • The wire contract tracks the entity model. What is filterable, sortable, and projectable is the entity's property set, so a model change is an API change by default. The boundary that decouples them is the DTO plus DTOToEntityPropertyMap: a subclass overrides the map to point a DTO field name (CategoryName) at an entity path (Category.Name), and the wire name stops moving with the column.
  • Dynamic filtering is an injection and over-fetch surface, and it is bounded, not open. Arbitrary client-supplied property/operator/value triples are an attack surface. The framework bounds it three ways: ValidateFilters rejects unknown properties and unsupported operators before any query runs, each type is filtered only by its registered IFilterStrategy rather than free-form expression evaluation, and MaxUnboundedResultLimit caps rows. This is per-type strategy filtering, deliberately narrower than full OData.
  • Generic endpoints are less self-documenting than bespoke ones. One uniform shape per entity is consistent but conveys less domain intent than a named, purpose-built endpoint. The filter-key syntax and operator names are learned once across the API rather than read off each endpoint.
  • Opting out means overriding, not abandoning. All five reads and both writes are virtual. A controller that needs bespoke behavior overrides the specific action and keeps the rest, but the default surface is opt-out, not opt-in: you inherit the whole contract whether or not you wanted every route.

Apply this even without MMCA

The pattern ports to any stack where you expose more than a handful of CRUD resources:

  1. Put the CRUD shape on a generic base and close the type parameters per entity. The verbs, routes, and response shapes belong in one place, inherited, not copied. A new entity should be a few lines, not a new file of near-duplicate actions.
  2. Parse filter, sort, and page once into a typed structure. A single model binder that turns a structured query string into a (property, operator, value) dictionary beats per-action parameter lists that drift in name and semantics.
  3. Filter through a per-type strategy registry with validated operators. Resolve a strategy by the property's CLR type and validate the operator up front. That is dynamic without being arbitrary, and it keeps free-form expression evaluation (the injection risk) out of the engine.
  4. Always cap an unpaginated read. A last-resort Take(N) ceiling in the query pipeline, independent of any API page-size clamp, means a forgotten pagination clause degrades to a bounded result instead of a database-pinning full scan.
  5. Mediate the wire contract through a DTO and a name map. Projecting entities straight to JSON couples your API to your schema. A DTO plus a DTO-to-entity property map lets a column rename stay an internal change instead of a breaking one.

What we covered: why hand writing a list/paged/by-id CRUD controller plus ad-hoc filter/sort/paging parsing per entity is the bulk of a monolith's boilerplate and drifts in shape, and how MMCA.Common answers with two base classes (EntityControllerBase for the five reads, AggregateRootEntityControllerBase for idempotent create and delete) over a shared EntityQueryService and EntityQueryPipeline, plus a bounded OData-lite query contract: sparse fields projection, QueryFilterModelBinder plus per-type IFilterStrategy filtering with up-front validation, sort, an X-Pagination header with a MaxPageSize clamp, a streamed CSV export bounded by MaxExportRows, a MaxUnboundedResultLimit ceiling, and a two-path include strategy, composing with manual DTO mapping (ADR-001), navigation populators (ADR-002), the Result edge (ADR-013), and idempotency (ADR-017).

Next in the series: resource-ownership authorization, the row-level axis that decides not just what a caller may do but which rows they may touch.

MMCA.Common is Apache-2.0 licensed and open source. Star the repo, read the 2-minute ADR-034 behind this pattern, or dotnet add package MMCA.Common.API and try it.

Tags: .NET, C Sharp, Web API, Software Architecture, Programming

← Back to The MMCA.Common series