This Pattern Cleaned Up My EF Core Queries

Introduction

Take a look at a repository interface that has been in production for a few months:

public interface IOrderRepository
{
    Task<List<Order>> GetStalePendingAsync(DateTime placedBefore, CancellationToken ct = default);
    Task<int> CountStalePendingAsync(DateTime placedBefore, CancellationToken ct = default);
    Task<List<Order>> GetShippingToAsync(string city, CancellationToken ct = default);
    Task<List<Order>> GetLargeAsync(decimal minimumTotal, CancellationToken ct = default);
}

Four methods, four copies of the same Where, Include, OrderBy dance - and the rule for what makes an order "stale" is already written twice, once for the list and once for the count. The day someone decides cancelled orders shouldn't count, one of them gets fixed and the other doesn't. And every new screen adds a fifth method, then a sixth.

The Specification pattern fixes this. By the end, that interface has two methods - ListAsync(spec) and CountAsync(spec) - that never need to change again. Same endpoints, same JSON, and the same SQL going to the database. The refactoring changes where the query lives, not what it sends. In this video we build it from scratch on .NET 10 and EF Core 10, so you understand every line before you decide whether to pull in a library.

Subscribe to my .NET Newsletter:

Remigiusz Zalewski | Become A Better .NET Software Engineer

Blog about .NET/C#/Angular/React/Azure

remigiuszzalewski.com

---

What a specification actually is

A specification is a small class that describes one query in business terms: which rows you want, and how to load them - the related data and the sort order. The repository gets one method that can run any specification. So a new query means a new, named class, not another repository method and another change to the interface.

The idea goes back to Eric Evans and Martin Fowler in the Domain-Driven Design days. The flavour most .NET teams use with EF Core - and the one the popular Ardalis.Specification library is built around - carries includes and sorting along with the filter. That's the version built in the video.

The demo is an ASP.NET Core Minimal API with an Order entity, its OrderItems, a status, a shipping city and a created date, seeded with 40 orders on SQL Server LocalDB. Every SQL command is logged to the console - that's the proof, at every step, that nothing changes on the database side.

The base class: one filter, optional includes and sorting

The whole pattern rests on a small abstract Specification<TEntity> base class with four things on it:

• Criteria - the filter. It comes in through the primary constructor, so every specification must have one. You can't forget it.
• Includes - a read-only list of related data to load.
• OrderBy / OrderByDescending - an optional sort key.

Includes and sorting are set through protected helpers (AddInclude, ApplyOrderBy, ApplyOrderByDescending) that a specification calls from its own constructor, only when it needs them. Nobody outside can change them afterwards.

The one detail that makes or breaks the pattern with EF Core is the type of the criteria:

public Expression<Func<TEntity, bool>> Criteria { get; }

Not Func<TEntity, bool> - an Expression. A Func is compiled code, a black box. An Expression is data: a tree that describes the lambda ("property Status, equals, Pending, and property CreatedAt, less than, this value"). EF Core can walk that tree and turn it into a SQL WHERE clause. Getting this wrong doesn't throw - it just quietly gets slow. More on that in the pitfalls.

Your first specification reads like the business talks

With the base class in place, the first query moves out of the repository and into its own class, StalePendingOrders. The filter (pending, and created before a cut-off date) goes to the base constructor; the include of Items and the oldest-first sort are set in the constructor body.

Read it out loud: stale pending orders, with their items, oldest first. That's how the business talks about it, and now that's what the code is called. It's the same three pieces that lived in GetStalePendingAsync - just with a name.

The endpoint then becomes a one-liner that builds the spec and hands it over:

var stale = await orders.ListAsync(new StalePendingOrders(placedBefore), ct);

The evaluator: write it once, reuse it in every repository

Something has to turn a specification into an actual query. You could write that as a private method inside OrderRepository - but look at what it needs: the Criteria, the Includes, the sort. All of that lives on Specification<TEntity>. None of it is about orders. The only order-specific thing is where the query starts - dbContext.Orders.

So the video puts it in a generic SpecificationEvaluator instead: take any starting IQueryable, add the Where, add each Include, then apply the sort. The day you add a CustomerRepository, it uses the exact same class. It carries the same where TEntity : class constraint EF Core itself puts on DbSet<TEntity> and Include.

Crucially, it returns an IQueryable - nothing has hit the database yet. Each call just adds another node to the expression tree. Nothing runs until the repository calls ToListAsync, which is when EF Core translates the whole tree into one SQL statement.

That split is deliberate:

• The evaluator builds the query. It's generic and shared by every repository.
• The repository decides where the query starts and when it runs. It stays specific to its entity, and it's the natural place for read-side options - pass dbContext.Orders.AsNoTracking() as the starting point and the evaluator doesn't change at all.

Naming note: in eShopOnWeb and Ardalis.Specification this class is also called a SpecificationEvaluator. Those tutorials also show a SpecificationBuilder - that's a different thing. It's the fluent API you use inside a specification (Query.Where(...).Include(...)) to replace the protected helpers. The builder describes a query; the evaluator runs it against EF Core.

Run the endpoint and the console shows exactly what the old method sent: a SELECT from Orders with a LEFT JOIN to OrderItems, a WHERE on status and date, and an ORDER BY CreatedAt. EF Core also appends the order's Id to the ORDER BY - you never asked for it, but it needs each order's joined rows next to each other to group items back under the right Order object. The old method got exactly the same thing. And placedBefore shows up as a SQL parameter, not a hard-coded date, because the lambda captured it - same SQL text for any value, so SQL Server reuses the cached plan.

The payoff: list and count share one rule

Now the duplicated rule. CountAsync takes the very same StalePendingOrders the list endpoint uses. The "what counts as stale" rule exists exactly once - change it in the class and both endpoints change together.

CountAsync deliberately applies only Criteria. Joining order items and sorting rows you're only going to count is wasted work, and the console confirms it: a plain SELECT COUNT(*) with the same WHERE.

Descending sorts and computed totals

Two more specifications round out the API:

• OrdersShippingTo - orders for one city, newest first. It exercises the OrderByDescending branch of the evaluator, and since city is captured, it goes to SQL as a parameter: no string concatenation, no SQL injection.
• LargeOrders - orders worth at least a minimum total, biggest first. This is the interesting one. Both the filter and the sort are on a computed total - the sum of quantity × unit price across the items. There's no Total column.

: base(o => o.Items.Sum(i => i.Quantity * i.UnitPrice) >= minimumTotal)

Because it's all expressions, EF Core translates that Sum into a correlated subquery and uses it in both the WHERE and the ORDER BY. You get a computed filter and sort without loading a single order into memory first.

Delete the old methods

The satisfying part: delete GetStalePendingAsync, CountStalePendingAsync, GetShippingToAsync and GetLargeAsync. What's left:

public interface IOrderRepository
{
    Task<List<Order>> ListAsync(Specification<Order> specification, CancellationToken ct = default);
    Task<int> CountAsync(Specification<Order> specification, CancellationToken ct = default);
}

The next query your team needs is a new class in the Specifications folder. The interface doesn't change, the repository doesn't change, and in code review you're looking at one small, named file instead of a diff scattered across an interface and an implementation. Run all four requests again: same responses, and side by side, the same SQL.

Key Takeaways

• A specification is one named class that describes one query in business terms: the filter, the related data to load, and the sort order.
• A minimal Specification<TEntity> base class takes Criteria through its constructor and exposes Includes, OrderBy and OrderByDescending, set via protected helpers.
• A generic SpecificationEvaluator turns any specification into an IQueryable - written once, shared by every repository. The repository only decides where the query starts and when it runs.
• The criteria must be Expression<Func<TEntity, bool>>. A Func silently turns a SQL WHERE into a full table load filtered in memory.
• Reusing one specification for both the list and the count means a business rule like "stale pending order" lives in exactly one place - and the count applies only the criteria.
• The refactoring changes where the query lives, not what it sends: same JSON, same SQL, same parameterised plans.
• Don't use it everywhere: one-off queries are simpler as repository methods, and combinable search filters are better as chained Where calls.

Get the Full Source Code

The complete runnable solution - both the Before and After versions of the Orders API, the Specification<TEntity> base class, the evaluator, all three specifications, the seeded LocalDB database and the .http file to compare the SQL yourself - is available to Patreon supporters. If you'd rather run it and watch the console than rebuild it from the walkthrough above, you can find it on Patreon.

Comments

[Mehdi Mohseni]

Very clear write-up, and the detail that the count applies only the criteria is the one that matters most once a spec grows Skip and Take, because otherwise the total comes back as the page size. Ardalis.Specification landed on the same split with an evaluateCriteriaOnly flag, and its RepositoryBase passes true for AnyAsync as well as CountAsync, so if you add an existence check to the evaluator it's worth routing it through the same criteria-only path.