Ivan Ball-llovera

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

Core patterns · No. 6

Specifications over LINQ spaghetti: composable, reusable query intent

Ivan Ball-llovera Also on Medium ↗

A .Where(s => s.SpeakerId == id && !s.IsDeleted && s.IsPublished) copied into nine controllers is nine places to forget the authorization clause. Here is the Specification pattern that makes query intent a first-class, composable, testable object, and a generic pipeline that keeps the Application layer from ever touching EF Core.

Find the bug:

// SessionsController.GetMine
var sessions = _db.Sessions
    .Where(s => s.SpeakerId == currentUserId && !s.IsDeleted)
    .ToList();

// SessionsController.GetMinePublished  (added three months later, different dev)
var sessions = _db.Sessions
    .Where(s => s.IsPublished && !s.IsDeleted)   // forgot the SpeakerId scope
    .ToList();

The second query leaked. It returns every published session in the system, not just the current speaker's, because the SpeakerId authorization clause lived as a loose lambda that someone had to remember to copy. The predicate that matters most (the one that scopes data to the caller who is allowed to see it) is the one most easily dropped, because it is just text in a .Where() that gets re-typed at every call site.

This is LINQ spaghetti: query intent scattered across controllers as ad-hoc lambdas, with no name, no reuse, and no way to test "does the own-sessions rule actually restrict to the owner" in isolation. The Specification pattern fixes it by turning a predicate into an object.

Why it matters

Query predicates are not throwaway plumbing. Some of them encode business rules ("a published event is one whose status is Published and whose start date has not passed") and some encode authorization ("only the questions this attendee asked"). When those live as inline lambdas:

  • They cannot be named or reused. "Published event" is a concept; .Where(e => e.Status == Published && e.StartsAt > now) is a spell you recite from memory.
  • They cannot be composed reliably. Combining two inline predicates with && works until one of them has to be applied conditionally, at which point you get branching IQueryable plumbing in the controller.
  • They cannot be tested in isolation. You can only test the controller action that uses them, so the rule and the HTTP plumbing are tangled in every test.
  • The security-critical ones get dropped, exactly as above.

A specification gives the predicate a type and a name, makes it composable with And/Or/Not, and lets the framework apply it server-side so the filter runs in the database, not in memory after a full-table load.

The MMCA answer: trusted specifications, untrusted filters, one pipeline

MMCA.Common splits the read side into three cooperating sub-systems, and the split is the point.

1. The Specification pattern (the trusted predicate)

ISpecification<TEntity, TIdentifierType> exposes two faces of the same rule:

  • a Criteria expression tree (Expression<Func<TEntity, bool>>) that EF Core translates to SQL, so the filter runs in the database, and
  • an IsSatisfiedBy(entity) predicate for in-memory evaluation.

The abstract base Specification<TEntity, TIdentifierType> lazy-compiles the expression once and caches the delegate, so repeated in-memory checks do not recompile the tree. Three combinators, AndSpecification, OrSpecification, and NotSpecification, compose specifications into a new one, and they do it by parameter substitution: an ExpressionVisitor rebinds the right-hand operand's parameter onto the left-hand lambda's own parameter, and the two bodies are joined with Expression.AndAlso / OrElse / Not, so the result is again a single Expression<Func<TEntity, bool>>, indistinguishable from a hand-written predicate. The obvious alternative, Expression.Invoke(spec.Criteria, parameter), is deliberately avoided, and the source says so in as many words: an InvocationExpression survives into the query tree, and while EF Core's relational providers can usually unwrap it, others (Cosmos in particular) throw at translation time, so an ANDed specification failed on exactly the engines the framework is meant to be portable across. Substitution translates everywhere. Each combinator also builds its composed expression once per instance and caches it in a lazy field, because the query pipeline reads Criteria at least once per request. That is how both the source XML docs and the onboarding chapter describe it. There is a fluent face as well: And, Or, and Not ship as extension members on ISpecification, so spec.And(other).Not() reads left to right instead of inside out while the abstract base stays untouched. Composition is live in production today, in five consumer read paths across two applications. Three are in ADC: the paged sessions read ANDs the public-session filter with the speaker-scoped one rather than substituting, because dropping the public filter for a speaker-scoped request would leak non-accepted sessions to non-privileged callers; the paged speakers read ANDs the public-speaker filter with an event-scoped one for the same reason; and a shared visibility helper ANDs the public-session status rule with an inline event-scope predicate before projecting session ids. Two are in MMCA.Store, where the public product-reviews endpoint and the domain-event handler that recomputes a product's rating both AND a by-product review specification with a published-reviews one. Each composed Criteria reaches the database as a single server-side WHERE. All five call the fluent .And(), and the hand-built new AndSpecification<...>(a, b) form appears nowhere in the three consumer repositories. Five call sites in two applications is still a narrow sample, though (MMCA.Helpdesk composes nothing), and Or and Not ship without a caller.

// A named, reusable, server-authored visibility rule (ADC, BR-49): a session is publicly
// visible when it is Accepted, or carries no status (organizer-created sessions never get one).
public sealed class PublicSessionStatusSpecification : Specification<Session, SessionIdentifierType>
{
    // Exposed on its own because other public read paths compose this predicate into larger expressions.
    public static readonly Expression<Func<Session, bool>> StatusCriteria =
        s => s.Status == null || s.Status == SessionStatuses.Accepted;

    // Criteria is an abstract get-only property on the base; override it with an expression body.
    public override Expression<Func<Session, bool>> Criteria => StatusCriteria;
}

// The paged sessions read: the speaker scope (itself a specification, a Session.Id IN (...) filter
// over the SessionSpeaker join) is ANDed with the public rule, never substituted for it.
return publicSpecification is null
    ? speakerResult.Value
    : publicSpecification.And(speakerResult.Value!);

The leaked-query bug from the opening cannot happen here. Both halves of the composed query are named objects, not text someone retypes at every call site: the speaker scope arrives as a specification built by a query handler, and the public visibility rule is a type the controller always ANDs in, because substituting one for the other would leak non-accepted sessions to non-privileged callers (the composing method's own doc comment says exactly that). The criteria are server-authored, so the client cannot tamper with them, and each rule is reusable and testable in isolation. This is a textbook Specification with security overtones: the authorization predicate is not something the request can override.

Scoping a query to the caller's own rows is common enough that the framework ships it: OwnedByUserSpecification<TEntity, TIdentifierType> filters on the CreatedBy audit field once, in the Domain layer, and is closed over the entity type at the call site. Its generic constraint is deliberately the concrete AuditableBaseEntity<TIdentifierType> rather than an interface, because a member access declared on an interface is not guaranteed to map to the entity's audit column and the criteria has to stay EF-translatable. ADC's question-answer controllers show the intended shape: an organizer gets null (no scoping at all), and everyone else gets that specification bound to their own user id.

2. Dynamic filtering (the untrusted input)

The other half of any list endpoint is user-driven shaping: the ?filter=...&sort=...&fields=... querystring. This is untrusted and is handled completely separately. Each CLR type gets an IFilterStrategy (StringFilterStrategy, IntFilterStrategy, DateTimeFilterStrategy, and so on) that knows which operators it supports, and a QueryFilterService registry dispatches by type. It runs in two phases: ValidateFilters checks that the property exists on the entity and the operator is legal for its type before the query (so a bad filter is a 400, not a SQL exception), and ApplyFilters builds the .Where() chain. Untrusted input is allow-listed against real entity metadata, never concatenated blindly.

The separation is deliberate and worth stating plainly: specifications are trusted and live with the domain; dynamic filters are untrusted and are validated, capped, and reflection-cached at the application boundary. Conflating the two is how authorization clauses end up overridable by a query string.

3. The pipeline and EntityQueryService (the orchestrator)

IEntityQueryPipeline (implemented by EntityQueryPipeline) executes against an IQueryable<TEntity> and an immutable EntityQueryParameters<TEntity> bundle carrying the specification's Criteria, the dynamic filters, sort, fields, paging, and include flags. The pipeline applies the criteria plus the user filters as translated SQL WHERE clauses, sorts, counts before paging, applies Skip/Take, projects the requested columns, and materializes. An unpaginated query is capped at MaxUnboundedResultLimit (1000) so a caller who forgets paging can never trigger an unbounded full-table load.

EntityQueryService<TEntity, TEntityDTO, TIdentifierType> is the public face that controllers inject. Its GetAllAsync is a three-step orchestration, and the source labels the steps: validate all parameters up front via Result.Combine (a bad fields is a 400 before any DB hit), execute the query pipeline (includes, criteria, filters, sort, pagination, field selection), and shape the output to the requested fields before wrapping the result in PaginationMetadata. Add a new entity and it inherits filtering, sorting, paging, sparse fieldsets, and eager-loading for free. The methods are virtual, so a module subclass can override one behavior without reimplementing the engine.

The Application-purity payoff: IQueryableExecutor

Here is the subtle part that makes the whole pipeline architecturally honest. The Application layer runs queries, but it must never reference EF Core directly (that is an Infrastructure concern, and the inward-dependency rule forbids it). The pipeline therefore works against an IQueryable<TEntity> abstraction, and the actual EF materialization (ToListAsync, CountAsync, and friends) sits behind an IQueryableExecutor abstraction implemented in Infrastructure (the interface lives in MMCA.Common.Application, exposing Include / AsSplitQuery / ToListAsync / CountAsync). Application code asks the abstraction to run the query; Infrastructure supplies the EF Core implementation. The result: the entire read engine lives in the Application layer with zero using Microsoft.EntityFrameworkCore, and the layer-dependency fitness functions stay green. The generic query pipeline is, in effect, EF Core used correctly from a layer that is not allowed to know EF Core exists.

The contract underneath: repository plus specification

A specification is only half of the data-access contract. The other half is what a handler is allowed to ask the repository for, and the framework splits that read surface by responsibility rather than shipping one fat interface. IEntityReader<TEntity, TIdentifierType> carries single-entity work (GetByIdAsync, GetByIdsAsync, and the two ExistsAsync overloads). IEntityQuerier<TEntity, TIdentifierType> carries collection work, and its shape is emphatically specification-first: alongside the parameter-driven GetAllAsync, GetProjectedAsync, GetAllForLookupAsync, and CountAsync, it declares reads that take an ISpecification outright (CountAsync(specification), ListAsync(specification), a projecting ListAsync<TResult>(specification, select), AnyAsync(specification), and FirstOrDefaultAsync(specification)), a keyset-paged GetPageByCursorAsync whose optional specification scopes the page, and expression-predicate aggregates that keep the arithmetic in the database (CountByAsync, SumByAsync, and the soft-delete-aware FindIncludingDeletedAsync). The premise of this whole article, that query intent belongs in an object, is what the read contract itself assumes. IReadRepository composes exactly those two, and it alone exposes the raw queryables: Table, TableNoTracking, TableNoTrackingSingleQuery, and TableNoTrackingSplitQuery. A handler that declares the narrow dependency cannot reach an IQueryable at all, because the member is not on the interface it asked for.

Why ban the queryable from a use-case handler? Because a handler written against one is EF-coupled: its query shape means something only to the provider that will translate it, so the same call cannot be answered across a gRPC boundary, and the module quietly loses the monolith-to-microservice extraction promise. GetByIdAsync, GetProjectedAsync, and CountAsync each map onto a request/response message; a composed IQueryable does not. So the rule is not left to code review: ApplicationLayer_DoesNotUseRawQueryableSurfaces is a fitness function that reads the .cs files of each mapped module's Application project, flags .Table / .TableNoTracking* member access, and fails the build on a hit. It is deliberately a textual line scan rather than IL analysis (the testing package carries no Roslyn or IL dependency on purpose), and its limits are documented on the rule itself. Existing violations go into an AllowedFiles list used as an adoption ratchet, so new code is clean immediately while the list shrinks. The rule is opt-in per repository by subclassing the base: MMCA.Common, MMCA.ADC, and MMCA.Store subclass it today (Store adopted with an empty AllowedFiles, because its Application layer had zero raw-queryable uses at adoption), so only MMCA.Helpdesk's Application code is not scanned yet.

Trade-offs, honestly

The pattern earns its keep, but a few real, specific gaps are worth stating plainly rather than glossing:

  • Two query vocabularies coexist. Specifications (typed, programmer-authored) and dynamic filters (string, user-authored) are different mechanisms with different trust levels. That is the right design, but a newcomer has to learn which is which and resist the temptation to express an authorization rule as a dynamic filter.
  • The generic pipeline trades some control for reuse. Free filtering/sorting/paging for every entity is a large win, but a query with genuinely bespoke shape may fit the pipeline awkwardly and is better written directly. The virtual override points exist for exactly this, but knowing when to override versus when to bypass is judgment.
  • The queryable ban is a textual scan, and it is opt-in. It skips only whole-line // comments, so a match inside a string literal or a trailing comment is a false positive, and it cannot see through variable indirection or an alias that re-exposes a queryable. Three of the four repositories subclass the rule today; only MMCA.Helpdesk relies on review. The allowlisted files are real coupling, not paperwork: each one would need rework if its module moved behind a transport boundary.

None of these argue against the pattern. They argue for testing your composed specifications and for keeping the trusted/untrusted boundary crisp.

Apply this even without MMCA

The core ideas port to any stack and any ORM:

  1. Give predicates a type and a name. A PublishedEvent specification object beats a copied .Where() lambda. Named intent is reusable, testable, and hard to forget.
  2. Keep authorization scopes server-authored. The clause that restricts data to the caller should be an object the server supplies, never something the request can shape. Conflating "what the user filtered for" with "what the user is allowed to see" is a security bug waiting to happen.
  3. Validate untrusted query input against real metadata. Allow-list filter properties and operators against the entity's actual type before they reach the database, so a bad filter is a 400, not a 500 or an injection.
  4. Cap unpaginated reads. A safety ceiling on result size turns "someone forgot Take" from an outage into a clamped response.
  5. Combine expression trees by substitution, not invocation. Expression.Invoke is the tempting way to glue two lambdas together, and it leaves an InvocationExpression in the tree that some providers refuse to translate. Rebinding one lambda's parameter onto the other with an ExpressionVisitor yields a tree indistinguishable from hand-written code, so it translates everywhere. Then still write a test that asserts the combined query runs in the database rather than falling back to in-memory evaluation.
  6. Do not hand a queryable to a use-case handler. Give it methods it can call (by id, projected, counted) and predicates it can pass, not an IQueryable it can compose. Method calls map onto messages; a composed query means something only to the provider that will translate it, which is the difference between a module you can move later and one you cannot.

The rule of thumb: query intent that matters (business rules and authorization scopes) deserves to be a named, composable, tested object, not a lambda you re-type at every call site.

What we covered: why inline LINQ predicates scatter and leak, the Specification pattern with its Criteria expression tree and its And/Or/Not combinators that compose by parameter substitution rather than Expression.Invoke, the separation of trusted specifications from untrusted dynamic filters, the generic EntityQueryPipeline / EntityQueryService that gives every entity filtering/sorting/paging/sparse-fieldsets for free, the IQueryableExecutor abstraction that keeps the Application layer free of EF Core, the ISP-split read contract (IEntityReader and IEntityQuerier under IReadRepository) with a build gate that bans raw queryables from Application code.

Next in the series: the CQRS decorator pipeline, where logging, caching, validation, and transactions wrap every command and query without touching a handler.

MMCA.Common is Apache-2.0 licensed and open source. Star the repo, read the querying chapter, or dotnet add package MMCA.Common.API and try it.

Tags: .NET, C Sharp, Software Architecture, Programming, Entity Framework

← Back to The MMCA.Common series