DEV Community: Remigiusz Zalewski

This Pattern Cleaned Up My EF Core Queries

Remigiusz Zalewski — Fri, 16 Oct 2026 07:03:00 +0000

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.

EF Core 10 Is INSANE - Overview of Game Changer features

Remigiusz Zalewski — Sat, 03 Oct 2026 11:59:56 +0000

Introduction

Most EF Core releases are a bag of small quality-of-life fixes. EF Core 10 has a few of those too, but it also lands several changes that remove long-standing workarounds: you no longer hand-write a GroupJoin plus SelectMany plus DefaultIfEmpty to get a left join, you no longer have one all-or-nothing global query filter, and you can now do vector similarity search against SQL Server without a bolt-on.

This video runs through the headline features against a small blog/book/author model on .NET 10 and SQL Server. Here is what each one is and when you would reach for it.

🎬 Watch the full video here:

LeftJoin and RightJoin as real operators

Before EF Core 10, a left join in LINQ-to-Entities was the incantation everyone had to look up: GroupJoin, then SelectMany, then DefaultIfEmpty. It worked, it translated fine, and nobody remembered the shape.

EF Core 10 adds LeftJoin and RightJoin as first-class query operators:

await context.Authors
    .LeftJoin(context.Books,
        author => author.Id,
        book => book.AuthorId,
        (author, book) => new { author.Name, book.Title })
    .ToListAsync();

Same generated SQL as the old pattern, a fraction of the code, and it reads like the join you meant. This is the change you will use most often.

Named query filters

Global query filters (soft delete, multi-tenancy, "only verified rows") have always had one painful limitation: one filter per entity, and IgnoreQueryFilters() turned off all of them. If you had soft-delete and tenant filtering on the same entity, you could not disable one without disabling the other.

EF Core 10 lets you name filters and toggle them individually:

modelBuilder.Entity<Author>()
    .HasQueryFilter("SoftDeletionFilter", a => !a.IsDeleted)
    .HasQueryFilter("VerifiedFilter", a => a.IsVerified);

// disable just one, keep the rest
await context.Authors
    .IgnoreQueryFilters(["SoftDeletionFilter"])
    .ToListAsync();

An admin screen that needs to see soft-deleted rows but must still respect tenant isolation is now a one-liner instead of a manual re-implementation of the other filter.

Complex types mapped to JSON

EF Core 10 lets a complex type be stored as a JSON document in a column:

modelBuilder.Entity<Book>()
    .ComplexProperty(x => x.Attributes, x => x.ToJson());

BookAttributes (genre, page count, language, a string[] of tags) lives in one nvarchar column as JSON, and you can still query into it in LINQ:

await context.Books
    .Where(b => b.Attributes.Pages > 300)
    .ToListAsync();

EF Core translates the predicate to SQL JSON path access. This is the pragmatic middle ground between a rigid column-per-field schema and an untyped blob - useful for attribute bags that vary and are not worth their own table.

Vector search for RAG, built in

This is the flashiest one. SQL Server 2025 has a native vector type, and EF Core 10 can map to it and translate distance functions:

[Column(TypeName = "vector(1536)")]
public SqlVector<float> Vector { get; set; }

await context.BlogPosts
    .OrderBy(p => EF.Functions.VectorDistance("cosine", p.Vector, queryVector))
    .Take(3)
    .ToListAsync();

Combined with Microsoft.Extensions.AI to generate embeddings (the demo uses an Azure OpenAI embedding model), you get the retrieval half of a RAG pipeline - "find the three most semantically similar posts to this text" - as an ordinary EF Core query, with no separate vector database.

Also worth knowing

ExecuteUpdateAsync continues to expand what it can express, including updates driven by other columns, so more bulk updates avoid loading entities.
Various LINQ translation gaps closed, and better SQL for existing queries - the kind of thing you get for free by upgrading.

Common pitfalls

Vector search needs the right SQL Server. The native vector type is SQL Server 2025 / recent Azure SQL. Older targets do not have it.
Named filters are opt-in by name. If you migrate existing single filters, give them names and update every IgnoreQueryFilters() call site, or behavior shifts.
JSON-mapped complex types query well but index differently. Filtering b.Attributes.Pages > 300 works, but performance depends on JSON indexing support in your database - do not assume it is free on a large table.
LeftJoin result selectors can yield nulls. The right-side entity is null when there is no match; project defensively.

Key Takeaways

LeftJoin / RightJoin are now first-class operators - retire the GroupJoin + SelectMany + DefaultIfEmpty pattern.
Query filters can be named and disabled individually with IgnoreQueryFilters(["Name"]), fixing the all-or-nothing problem for soft delete plus multi-tenancy.
Complex types can be stored as JSON in a single column and still be queried in LINQ.
SQL Server's native vector type plus EF.Functions.VectorDistance gives you similarity search - the retrieval side of RAG - without a dedicated vector store.
Upgrading also brings a batch of translation improvements you do not have to ask for.

Get the Full Source Code

The complete runnable solution - the blog/book/author model, the named query filters, the JSON complex type, and the vector-search endpoints wired to an embedding generator - is available to Patreon supporters. If you want to run the RAG query against real embeddings instead of rebuilding it from the walkthrough above, you can find it on Patreon.

Resources

Api Key Authentication in ASP .NET Core

Remigiusz Zalewski — Sat, 12 Sep 2026 16:08:34 +0000

Introduction

Not every API needs users, tokens, and a login flow. An internal service that another service calls, a webhook receiver, a partner integration - these often just need "prove you are an allowed caller", and an API key in a header does that with almost no ceremony. The client sends a secret string, the server checks it, done.

This video implements API key authentication in ASP.NET Core on .NET 9 three different ways, because where you put the check matters: middleware for the whole app, an MVC authorization filter for controllers, and an endpoint filter for minimal API routes. All three read the header name and expected key from configuration through the Options pattern.

🎬 Watch the full video here:

https://youtu.be/RPd1md_9oRM

Where the key and header name live

Both values come from appsettings.json, bound to a typed options class rather than read as loose strings:

public class ApiKeyOptions
{
    public const string AuthenticationApiKey = "Authentication:ApiKey";
    public required string HeaderName { get; set; }
    public required string Key { get; set; }
}

builder.Services.Configure<ApiKeyOptions>(
    builder.Configuration.GetSection(ApiKeyOptions.AuthenticationApiKey));

Every enforcement approach below injects IOptions<ApiKeyOptions>. In production the actual key value belongs in a secret store (user secrets locally, Key Vault or environment variables in the cloud), never committed in appsettings.json.

Approach 1: middleware

Middleware runs for every request in the pipeline. It reads the configured header, compares it to the configured key, and short-circuits with 401 if it does not match:

if (!context.Request.Headers.TryGetValue(_options.HeaderName, out var extractedApiKey)
    || extractedApiKey != _options.Key)
{
    context.Response.StatusCode = StatusCodes.Status401Unauthorized;
    await context.Response.WriteAsync("Api Key is invalid");
    return;
}

await _next(context);

Use middleware when the entire API is key-protected and there are no public endpoints. It is the bluntest instrument - there is no per-route opt-out without adding path checks inside it, which gets ugly fast.

Approach 2: MVC authorization filter

For controllers, an IAuthorizationFilter runs as part of MVC's filter pipeline and can be applied exactly where you want it:

public class ApiKeyAuthFilter : IAuthorizationFilter
{
    public void OnAuthorization(AuthorizationFilterContext context)
    {
        if (/* header missing or wrong */)
            context.Result = new UnauthorizedObjectResult("Api Key is invalid");
    }
}

Because it has a constructor dependency (IOptions<ApiKeyOptions>), register it in DI and apply it with [ServiceFilter(typeof(ApiKeyAuthFilter))] on a controller or action. Now [AllowAnonymous]-style selectivity is trivial: put the attribute on the controllers that need it and leave the rest open.

Approach 3: minimal API endpoint filter

Minimal APIs have their own filter abstraction, IEndpointFilter, added per route or per route group:

app.MapGet("/weatherforecastminimal", () => /* ... */)
   .AddEndpointFilter<ApiKeyEndpointFilter>();

The filter's InvokeAsync checks the header and either writes a 401 or calls next(context) to continue. Apply it to a MapGroup and every route in that group is protected in one line. This is the idiomatic choice for a minimal API codebase.

A real gotcha in the demo

The endpoint filter in the video checks the key, writes the 401 body on failure, but then still calls await next(context). To actually short-circuit, it needs to return immediately after writing the unauthorized response instead of falling through to the next delegate. It is a one-word fix and a good reminder that with filters, not calling next is how you stop the pipeline.

Constant-time comparison

extractedApiKey != _options.Key is a normal string comparison, which can leak timing information to an attacker probing the key byte by byte. For a real deployment, compare with a fixed-time method (CryptographicOperations.FixedTimeEquals over the UTF-8 bytes) so every comparison takes the same time regardless of how many characters match.

When API keys are the wrong tool

You need to know which user is acting, not just which application. Use real user auth.
Keys are going to end up in browser code. A key in client-side JavaScript is public - anyone can read it.
You need granular, revocable, per-scope permissions. That is OAuth's job.

API keys shine for trusted server-to-server calls where the client can keep a secret.

Common pitfalls

Committing the key. Treat it like a password: secret store, not source control.
No revocation story. A single hardcoded key means a leak forces a redeploy. Real systems store hashed keys in a database so individual keys can be rotated and revoked.
Forgetting to short-circuit. Writing a 401 body but still invoking the rest of the pipeline runs the endpoint anyway.
Plain == comparison. Use a fixed-time comparison.
Mixing approaches. Pick one enforcement point for a given surface; stacking middleware and a filter doing the same check is just confusing.

Key Takeaways

API key auth is a good fit for server-to-server APIs where OAuth is overkill and you only need to identify the calling application.
Bind the header name and key to a typed ApiKeyOptions via the Options pattern; keep the real value in a secret store.
Middleware protects the whole app; an MVC IAuthorizationFilter (via [ServiceFilter]) protects chosen controllers; an IEndpointFilter protects chosen minimal API routes or groups.
To short-circuit a filter, write the response and return - do not call next.
Compare keys in constant time and design for rotation and revocation from the start.
Never ship an API key in client-side code.

Get the Full Source Code

The complete runnable solution - all three enforcement approaches, the options binding, and both controller and minimal API endpoints to test against - is available to Patreon supporters. If you want to try each approach with a real header instead of rebuilding it from the walkthrough above, you can find it on Patreon.

Originally published at remigiuszzalewski.com