.NET Programming With Me

Logging and Structured Diagnostics in .NET 8 with Serilog and PostgreSQL Sink

2026-05-31T15:51:26.396+05:45

When something breaks in production at 2 AM, plain-text log lines like "Error processing order" are nearly useless. You cannot filter them, you cannot correlate them across a request, and you cannot query them. Structured logging fixes this by treating every log entry as data with named properties, not a sentence. Serilog is the de facto standard for it in .NET.

By the end of this post you will have a .NET 8 API that logs structured events through Serilog, enriches every entry with request context, writes them to PostgreSQL where you can query them with SQL, and correlates all logs from a single request together.

1 Prerequisites

Before starting, make sure you have:

.NET 8 SDK and an existing ASP.NET Core Web API project
A running PostgreSQL instance to act as the log sink
The Serilog.AspNetCore and Serilog.Sinks.PostgreSQL NuGet packages

Note: A database is a convenient and queryable sink for moderate volumes, but high-throughput systems usually ship logs to a dedicated platform such as Seq, Elasticsearch, or a cloud log service. The structured-logging principles here apply identically; only the sink changes.

2 Wire up Serilog

The goal is to replace the default logging provider with Serilog as early as possible, so that even startup errors are captured. .NET 8's minimal hosting model makes this clean with a two-stage bootstrap.

Bootstrap logger plus full configuration

The first logger catches failures during host build; the second, configured from appsettings.json, takes over once the app is running.

// Program.cs
Log.Logger = new LoggerConfiguration()
    .WriteTo.Console()
    .CreateBootstrapLogger();

try
{
    var builder = WebApplication.CreateBuilder(args);

    builder.Host.UseSerilog((context, services, config) => config
        .ReadFrom.Configuration(context.Configuration)
        .ReadFrom.Services(services)
        .Enrich.FromLogContext());

    var app = builder.Build();
    app.UseSerilogRequestLogging(); // one tidy log line per HTTP request
    app.Run();
}
catch (Exception ex)
{
    Log.Fatal(ex, "Application terminated unexpectedly.");
}
finally
{
    Log.CloseAndFlush();
}

UseSerilogRequestLogging() is a quiet hero: it collapses the framework's noisy per-request log spam into a single structured line with method, path, status code, and elapsed time. CloseAndFlush() in the finally block guarantees buffered logs reach the sink before the process exits, which matters most when the app is crashing, exactly when you need those logs.

3 Write structured events

This is the habit shift that makes everything else pay off. Use message templates with named placeholders rather than string interpolation. Serilog captures each value as a queryable property instead of baking it into an opaque string.

// Do this: the values become structured properties
_logger.LogInformation(
    "Order {OrderId} placed by user {UserId} for {Amount:C}",
    order.Id, userId, order.Total);

// Not this: the values are lost inside one flat string
_logger.LogInformation(
    $"Order {order.Id} placed by user {userId} for {order.Total:C}");

With the first form, you can later query "all orders over $500" or "every event for OrderId 4827" directly, because OrderId, UserId, and Amount are real fields. The second form throws that structure away the moment it is written. The difference looks cosmetic; operationally it is enormous.

4 Sink to PostgreSQL

Now we route those structured events into a PostgreSQL table where each property can map to its own column. Defining the column map explicitly keeps the schema predictable and queryable.

// Program.cs — configure the PostgreSQL sink
var columns = new Dictionary<string, ColumnWriterBase>
{
    ["message"]      = new RenderedMessageColumnWriter(),
    ["level"]        = new LevelColumnWriter(),
    ["timestamp"]    = new TimestampColumnWriter(),
    ["exception"]    = new ExceptionColumnWriter(),
    ["properties"]   = new LogEventSerializedColumnWriter(),
    ["request_id"]   = new SinglePropertyColumnWriter("RequestId")
};

builder.Host.UseSerilog((context, services, config) => config
    .ReadFrom.Configuration(context.Configuration)
    .Enrich.FromLogContext()
    .WriteTo.PostgreSQL(
        connectionString: context.Configuration.GetConnectionString("Logs")!,
        tableName: "app_logs",
        columnOptions: columns,
        needAutoCreateTable: true));

With needAutoCreateTable set, the sink creates app_logs on first run. The properties column stores the full structured payload as JSON, while common fields get dedicated columns. That hybrid gives you fast filtering on the columns plus the full detail in JSON when you need to dig deeper.

-- Querying logs becomes ordinary SQL
SELECT timestamp, level, message, request_id
FROM app_logs
WHERE level = 'Error'
  AND timestamp > now() - interval '1 hour'
ORDER BY timestamp DESC;

Performance note: writing each log line synchronously to a database will throttle a busy API. Use the sink's batching options (period and batch size) so writes are buffered and flushed in groups, and never log at Debug level in production unless you are actively investigating.

5 Correlate logs per request

The final piece is correlation. When a single request produces ten log lines across services, you need to tie them together. Enriching every log within a request with a shared identifier lets you reconstruct the full story of any one call.

// Middleware that pushes a correlation id into the log context
public sealed class CorrelationMiddleware(RequestDelegate next)
{
    public async Task InvokeAsync(HttpContext context)
    {
        var correlationId = context.Request.Headers["X-Correlation-ID"]
            .FirstOrDefault() ?? Guid.NewGuid().ToString();

        context.Response.Headers["X-Correlation-ID"] = correlationId;

        using (LogContext.PushProperty("CorrelationId", correlationId))
        {
            await next(context);
        }
    }
}

// Program.cs — register before the endpoints run
app.UseMiddleware<CorrelationMiddleware>();

Because Enrich.FromLogContext() is already configured, every log written inside that using block automatically carries the CorrelationId property, with no extra code at the call sites. Returning the id in the response header also lets clients quote it in support tickets, so you can jump straight to their exact request: WHERE properties ->> 'CorrelationId' = '...'.

Wrapping up

You now have an API that logs structured, queryable events through Serilog, enriches them with per-request correlation ids, and persists them to PostgreSQL where ordinary SQL answers your operational questions. That is the difference between guessing what happened in production and knowing.

This closes out Series 5 on real-world .NET 8 API features. Across the five posts you have built authentication, authorization, background processing, caching, and now diagnostics, the cross-cutting concerns that turn a working API into a production-ready one.

Got a question or ran into a problem? Drop a comment below and I will reply.

Caching Strategies in a .NET 8 API: In-Memory, Distributed, and Response Cache

2026-05-31T14:48:56.829+05:45

Caching is the cheapest performance win in most APIs and one of the easiest to get subtly wrong. The hard part is rarely storing a value; it is choosing the right layer, picking sensible expirations, and invalidating stale data without serving someone else's. .NET 8 gives you three distinct caching tools, each suited to a different job.

By the end of this post you will understand when to use in-memory caching, distributed caching with Redis, and HTTP response caching, and you will have correct, idiomatic code for all three, including the cache-stampede protection most tutorials leave out.

1 Prerequisites

Before starting, make sure you have:

.NET 8 SDK and an existing ASP.NET Core Web API project
A Redis instance for the distributed caching section (a local Docker container works)
The Microsoft.Extensions.Caching.StackExchangeRedis package for that same section

Note: The golden rule of caching is to cache data that is read far more often than it changes and that you can tolerate being slightly stale. If correctness on every read is non-negotiable, caching may not be your answer; pick the layer to match how stale you can afford to be.

2 In-memory caching

IMemoryCache stores objects in the process's own memory. It is the fastest option because there is no network hop and no serialization, which makes it ideal for small, frequently read, expensive-to-compute data on a single instance.

The naive version and its flaw

A first attempt usually looks like a get-or-create. It works, but under load it has a hidden problem.

// Cache-aside: check cache, fall back to source, store result
public async Task<Product?> GetProductAsync(int id)
{
    return await _cache.GetOrCreateAsync($"product:{id}", async entry =>
    {
        entry.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(10);
        entry.SlidingExpiration = TimeSpan.FromMinutes(2);
        return await _db.Products.FindAsync(id);
    });
}

The flaw is the cache stampede: when a popular key expires, many concurrent requests all miss at once and all hit the database simultaneously. On a hot key under traffic, that thundering herd can knock over the very database the cache was meant to protect.

Protecting against the stampede

A per-key lock ensures only one request rebuilds the value while the rest wait for it.

private readonly SemaphoreSlim _gate = new(1, 1);

public async Task<Product?> GetProductSafeAsync(int id)
{
    var key = $"product:{id}";
    if (_cache.TryGetValue(key, out Product? cached))
        return cached;

    await _gate.WaitAsync();
    try
    {
        // Double-check: another thread may have populated it while we waited.
        if (_cache.TryGetValue(key, out cached))
            return cached;

        var product = await _db.Products.FindAsync(id);
        _cache.Set(key, product, TimeSpan.FromMinutes(10));
        return product;
    }
    finally
    {
        _gate.Release();
    }
}

The double-checked pattern inside the lock is what makes this correct: by the time a waiting thread acquires the gate, the value is usually already cached, so it returns immediately without a redundant query.

3 Distributed caching with Redis

In-memory caching breaks down the moment you scale to more than one instance: each server has its own cache, so hit rates drop and invalidation becomes inconsistent. A distributed cache like Redis gives every instance a single shared store.

// Program.cs
builder.Services.AddStackExchangeRedisCache(options =>
{
    options.Configuration = builder.Configuration.GetConnectionString("Redis");
    options.InstanceName = "yourapp:";
});

Because Redis stores bytes, you serialize on the way in and deserialize on the way out. Wrapping that in a small helper keeps call sites clean.

public async Task<T?> GetOrSetAsync<T>(
    string key, Func<Task<T>> factory, TimeSpan ttl)
{
    var cached = await _distributedCache.GetStringAsync(key);
    if (cached is not null)
        return JsonSerializer.Deserialize<T>(cached);

    var value = await factory();

    await _distributedCache.SetStringAsync(
        key,
        JsonSerializer.Serialize(value),
        new DistributedCacheEntryOptions
        {
            AbsoluteExpirationRelativeToNow = ttl
        });

    return value;
}

The tradeoff is real: Redis adds a network round trip and serialization cost, so it is slower per call than in-memory. You accept that latency in exchange for a cache that is consistent across every instance and survives an individual app restart.

Edge case: always handle the cache being unavailable. A Redis outage should degrade you to "slower, hitting the database" rather than taking the whole API down. Wrap cache calls so a connection failure falls through to the source instead of throwing.

4 HTTP response caching

The two layers above cache data inside your code. Response caching works at the HTTP level, instructing clients, proxies, and the framework to reuse a whole response. It is the most efficient option when an entire endpoint's output is cacheable, because a cache hit can skip your action method entirely.

// Program.cs
builder.Services.AddResponseCaching();

var app = builder.Build();
app.UseResponseCaching();

[HttpGet("categories")]
[ResponseCache(Duration = 300, Location = ResponseCacheLocation.Any, VaryByQueryKeys = ["page"])]
public IActionResult GetCategories(int page = 1)
{
    return Ok(_catalog.GetCategories(page));
}

VaryByQueryKeys is essential here: without it, the cache could serve page 1's data for a page 2 request. Response caching only applies to GET and HEAD requests that return a success status and carry no Authorization header by default, which is exactly right for public, anonymous, read-heavy endpoints.

Worth knowing: .NET 8 also ships output caching (AddOutputCache), a more powerful server-side cache with tag-based invalidation. Response caching is the standards-based HTTP option; output caching gives you finer control. For new projects, evaluate output caching for server-controlled scenarios.

5 Choosing and testing the right layer

The decision comes down to scope and freshness. Use in-memory for single-instance, ultra-hot, small data. Use Redis when you run multiple instances and need a shared, consistent cache. Use response or output caching when an entire endpoint response is reusable and you want to skip the work altogether.

// A quick way to prove caching works: log and time the source call
public async Task<Product?> GetProductAsync(int id)
{
    return await GetOrSetAsync($"product:{id}", async () =>
    {
        _logger.LogInformation("CACHE MISS for product {Id}", id);
        return await _db.Products.FindAsync(id);
    }, TimeSpan.FromMinutes(10));
}

Call the endpoint twice and you should see exactly one "CACHE MISS" log line, with the second response served from cache and noticeably faster. If you see the miss on every call, your key is varying when it should not (a common cause is including a timestamp or request-specific value in the key).

Wrapping up

You now have three caching layers and a clear rule for each: in-memory for raw speed on one box, Redis for consistency across many, and response or output caching to skip work entirely. Just as importantly, you have stampede protection and graceful degradation, the details that separate a cache that helps from one that becomes a liability.

The final post in this series turns to observability, so you can actually see what your cached, authenticated, job-running API is doing in production: structured logging with Serilog and a PostgreSQL sink.

Got a question or ran into a problem? Drop a comment below and I will reply.

Background Jobs in .NET 8 Web API with IHostedService and Hangfire

2026-05-31T14:46:50.321+05:45

Some work has no business running inside an HTTP request. Sending a welcome email, resizing an upload, generating a report, or cleaning up stale records all make the caller wait for something they do not need to wait for. Worse, if the process restarts mid-request, that work is lost. Background jobs solve both problems.

By the end of this post you will know when to reach for the built-in IHostedService versus Hangfire, and you will have working examples of both: a recurring cleanup task with a hosted service, and a durable, retryable fire-and-forget job with Hangfire backed by PostgreSQL.

1 Prerequisites

Before starting, make sure you have:

.NET 8 SDK and an existing ASP.NET Core Web API project
A running PostgreSQL instance for the Hangfire storage example
The Hangfire.AspNetCore and Hangfire.PostgreSql NuGet packages for the second half

Note: The two tools solve different problems. IHostedService is in-process and ephemeral, ideal for periodic in-memory tasks. Hangfire persists jobs to storage so they survive restarts and retry on failure, which is what you want for work that must not be lost. Choosing the wrong one is the most common mistake here.

2 A recurring task with IHostedService

For periodic in-process work, .NET 8 gives you BackgroundService, a base class over IHostedService that runs alongside your app for its entire lifetime. We will build a worker that purges expired refresh tokens every hour.

Implement the worker

The critical detail is scope. A hosted service is a singleton, but most useful work needs scoped dependencies like a DbContext. You must create a scope per iteration rather than injecting scoped services directly.

// TokenCleanupService.cs
public sealed class TokenCleanupService(
    IServiceScopeFactory scopeFactory,
    ILogger<TokenCleanupService> logger) : BackgroundService
{
    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        using var timer = new PeriodicTimer(TimeSpan.FromHours(1));

        while (await timer.WaitForNextTickAsync(stoppingToken))
        {
            try
            {
                using var scope = scopeFactory.CreateScope();
                var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();

                var removed = await db.RefreshTokens
                    .Where(t => t.ExpiresUtc < DateTime.UtcNow)
                    .ExecuteDeleteAsync(stoppingToken);

                logger.LogInformation("Purged {Count} expired tokens.", removed);
            }
            catch (Exception ex)
            {
                logger.LogError(ex, "Token cleanup iteration failed.");
            }
        }
    }
}

// Program.cs
builder.Services.AddHostedService<TokenCleanupService>();

Three things make this production-safe: PeriodicTimer gives clean, allocation-light scheduling that respects the cancellation token; the per-iteration scope prevents the captured-dependency bug; and the try/catch ensures one failed run does not tear down the whole loop. Without that catch, a single transient database hiccup would silently kill the service for the rest of the process lifetime.

3 Durable jobs with Hangfire

IHostedService is great until the requirement becomes "this email must be sent even if the server restarts." That is Hangfire's domain. It persists every enqueued job to storage (PostgreSQL here), executes it on a background worker, and retries automatically on failure.

Register Hangfire

// Program.cs
builder.Services.AddHangfire(config => config
    .SetDataCompatibilityLevel(CompatibilityLevel.Version_180)
    .UseSimpleAssemblyNameTypeSerializer()
    .UseRecommendedSerializerSettings()
    .UsePostgreSqlStorage(options =>
        options.UseNpgsqlConnection(
            builder.Configuration.GetConnectionString("Hangfire"))));

builder.Services.AddHangfireServer();

var app = builder.Build();
app.UseHangfireDashboard("/jobs"); // protect this in production

Hangfire creates its own schema in PostgreSQL on first run. The dashboard at /jobs is a genuinely useful operational window, but it exposes job data, so wrap it in an authorization filter before shipping; an unprotected dashboard is a real security risk.

4 Enqueue and schedule work

With Hangfire registered, queueing work from a controller is a single call that returns immediately. The job runs out-of-band on a worker, and the HTTP response is fast.

// In a controller, after registering a user
[HttpPost("register")]
public async Task<IActionResult> Register([FromBody] RegisterDto dto)
{
    var user = await _userService.CreateAsync(dto);

    // Fire-and-forget: returns instantly, runs in the background.
    _backgroundJobs.Enqueue<IEmailSender>(
        sender => sender.SendWelcomeEmailAsync(user.Email));

    return Ok(new { user.Id });
}

Hangfire also handles delayed and recurring jobs. A nightly report or a "remind me in 24 hours" email needs no custom timer code:

// Delayed: run once, later
_backgroundJobs.Schedule<IEmailSender>(
    sender => sender.SendReminderAsync(userId),
    TimeSpan.FromHours(24));

// Recurring: cron-scheduled, survives restarts
_recurringJobs.AddOrUpdate<IReportService>(
    "nightly-report",
    service => service.GenerateDailyReportAsync(),
    Cron.Daily(2)); // 2 AM every day

Because Hangfire passes a type and an expression rather than an instance, it resolves your service from DI at execution time and serializes only the arguments. Keep those arguments small and serializable; passing entire entities is a common pitfall that bloats storage and breaks when the type changes.

5 Running and verifying

Start the app and register a user. The HTTP response returns immediately, then the welcome-email job appears in the Hangfire dashboard, moving from Enqueued to Processing to Succeeded. The hosted cleanup service logs its purge count once an hour.

// Make a job fail deliberately to watch retries in the dashboard
public Task SendWelcomeEmailAsync(string email)
{
    if (string.IsNullOrWhiteSpace(email))
        throw new InvalidOperationException("No email address.");
    // ... send logic
    return Task.CompletedTask;
}

Throw an exception on purpose and you will see Hangfire move the job to the Failed state and then retry it on a back-off schedule, all visible in the dashboard. That automatic retry, with full visibility, is precisely what a hand-rolled timer cannot give you and the main reason to reach for Hangfire when work must not be lost.

Idempotency matters: because Hangfire retries, a job can run more than once. Design jobs so a second execution is harmless (check whether the email was already sent, use upserts, guard with a dedupe key). Assuming exactly-once execution is the single biggest source of background-job bugs.

Wrapping up

You now have both tools in your kit: IHostedService for lightweight, in-process recurring work, and Hangfire for durable, retryable, observable jobs backed by PostgreSQL. The decision rule is simple: if losing the work is acceptable, a hosted service is enough; if it is not, persist it with Hangfire.

Next we tackle another lever for responsiveness and cost: caching, looking at in-memory, distributed, and response caching strategies in a .NET 8 API.

Got a question or ran into a problem? Drop a comment below and I will reply.

Role-Based Authorization in ASP.NET Core with EF Core and PostgreSQL

2026-05-31T14:40:12.384+05:45

Authentication tells you who is calling. Authorization decides what they are allowed to do, and getting it wrong is one of the most common, and most damaging, API security failures. Role-based authorization (RBAC) is the workhorse approach: assign users to roles, then gate endpoints by role.

By the end of this post you will have an ASP.NET Core API that stores users and roles in PostgreSQL via EF Core, projects those roles into the user's claims, and enforces them on endpoints with both attribute-based and policy-based authorization.

1 Prerequisites

Before starting, make sure you have:

.NET 8 SDK and a running PostgreSQL instance (local Docker container is fine)
The Npgsql.EntityFrameworkCore.PostgreSQL package installed
A working JWT authentication setup, since roles are carried as claims inside the token (see the previous post in this series)

Note: Roles can be modeled in two places: inside the token as claims, or looked up from the database on each request. This post puts them in the token for performance, which means role changes only take effect when a new token is issued. That tradeoff matters; we revisit it at the end.

2 Model users and roles in EF Core

RBAC is fundamentally a many-to-many relationship: a user can hold several roles, and a role belongs to many users. We model that explicitly with a join entity so the schema stays clean and queryable in PostgreSQL.

The entities

Keeping roles as their own table (rather than a comma-separated string column) means you can query, audit, and extend them later without a painful migration.

// Entities
public sealed class AppUser
{
    public Guid Id { get; set; }
    public string Email { get; set; } = string.Empty;
    public string PasswordHash { get; set; } = string.Empty;
    public ICollection<UserRole> UserRoles { get; set; } = new List<UserRole>();
}

public sealed class AppRole
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty; // "Admin", "Manager", "User"
    public ICollection<UserRole> UserRoles { get; set; } = new List<UserRole>();
}

public sealed class UserRole
{
    public Guid UserId { get; set; }
    public AppUser User { get; set; } = null!;
    public int RoleId { get; set; }
    public AppRole Role { get; set; } = null!;
}

Configure the join in the DbContext

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<UserRole>()
        .HasKey(ur => new { ur.UserId, ur.RoleId });

    modelBuilder.Entity<UserRole>()
        .HasOne(ur => ur.User)
        .WithMany(u => u.UserRoles)
        .HasForeignKey(ur => ur.UserId);

    modelBuilder.Entity<UserRole>()
        .HasOne(ur => ur.Role)
        .WithMany(r => r.UserRoles)
        .HasForeignKey(ur => ur.RoleId);

    modelBuilder.Entity<AppRole>()
        .HasData(
            new AppRole { Id = 1, Name = "Admin" },
            new AppRole { Id = 2, Name = "Manager" },
            new AppRole { Id = 3, Name = "User" });
}

The composite primary key on UserRole prevents duplicate assignments at the database level, and seeding the roles with HasData guarantees they exist after the first migration. Run dotnet ef migrations add InitRoles followed by dotnet ef database update to apply this to PostgreSQL.

3 Project roles into claims at login

For attribute-based role checks to work, the role names must be present in the authenticated user's claims. When a user logs in, we load their roles from PostgreSQL and add each one as a ClaimTypes.Role claim on the token.

// During login, after verifying the password
var user = await _db.Users
    .Include(u => u.UserRoles)
        .ThenInclude(ur => ur.Role)
    .FirstOrDefaultAsync(u => u.Email == request.Email);

var roleNames = user!.UserRoles.Select(ur => ur.Role.Name).ToList();
var accessToken = _tokenService.CreateAccessToken(user.Id, user.Email, roleNames);

The eager Include and ThenInclude matter: without them the role collection is empty and every role check silently fails. ASP.NET Core maps ClaimTypes.Role claims into the role system automatically, so User.IsInRole("Admin") just works once the token carries them.

4 Enforce roles on endpoints

There are two ways to gate access, and mature APIs use both. Attribute-based checks are simple and declarative; policy-based checks centralize complex rules so they are not duplicated across controllers.

Attribute-based, for simple gates

[Authorize(Roles = "Admin")]
[HttpDelete("users/{id:guid}")]
public async Task<IActionResult> DeleteUser(Guid id)
{
    // Only Admins reach this line.
    await _userService.DeleteAsync(id);
    return NoContent();
}

[Authorize(Roles = "Admin,Manager")]
[HttpGet("reports")]
public IActionResult GetReports() => Ok(_reportService.GetAll());

Policy-based, for reusable rules

When the same combination of roles guards many endpoints, define it once as a named policy. Changing the rule then happens in a single place rather than across dozens of attributes.

// Program.cs
builder.Services.AddAuthorization(options =>
{
    options.AddPolicy("CanManageContent", policy =>
        policy.RequireRole("Admin", "Manager"));

    options.AddPolicy("AdminOnly", policy =>
        policy.RequireRole("Admin"));
});

[Authorize(Policy = "CanManageContent")]
[HttpPost("articles")]
public async Task<IActionResult> CreateArticle([FromBody] ArticleDto dto)
{
    var id = await _articleService.CreateAsync(dto);
    return CreatedAtAction(nameof(CreateArticle), new { id }, null);
}

A request from a user without the required role returns 403 Forbidden (they are authenticated but not permitted), while a request with no valid token returns 401 Unauthorized. Distinguishing those two responses correctly is a sign your pipeline is configured properly.

5 Test the authorization matrix

The fastest way to gain confidence is to test the full matrix: each role against each protected endpoint, plus the anonymous case. A small integration test makes regressions impossible to miss.

[Theory]
[InlineData("Admin",   "/api/users/{id}", "DELETE", 204)]
[InlineData("Manager", "/api/users/{id}", "DELETE", 403)]
[InlineData("User",    "/api/reports",    "GET",    403)]
[InlineData(null,      "/api/reports",    "GET",    401)]
public async Task Endpoints_enforce_roles(
    string? role, string path, string method, int expectedStatus)
{
    var client = _factory.CreateAuthenticatedClient(role);
    var response = await client.SendAsync(new HttpRequestMessage(
        new HttpMethod(method), path));

    Assert.Equal(expectedStatus, (int)response.StatusCode);
}

When this theory passes, you have proof that Admins can delete, Managers cannot, Users are blocked from reports, and anonymous callers get a clean 401. That table is also excellent living documentation of your access rules.

The tradeoff to remember: because roles live in the token, revoking a role does not take effect until the token expires or is refreshed. If you need instant revocation, validate critical roles against the database inside an authorization handler instead, accepting the extra query cost for security-sensitive operations.

Wrapping up

You now have a PostgreSQL-backed RBAC model in EF Core, roles flowing into JWT claims at login, and endpoints protected by both attribute and policy-based authorization, all verified by a test matrix. That is a production-grade foundation for controlling access in any .NET 8 API.

Next in the series we shift from security to throughput: running work outside the request thread with background jobs using IHostedService and Hangfire.

Got a question or ran into a problem? Drop a comment below and I will reply.

JWT Authentication in a .NET 8 Web API: Setup, Claims, and Refresh Tokens

2026-05-31T14:35:24.052+05:45

Almost every real-world API eventually needs to answer one question on every request: who is calling, and are they allowed to? JWT (JSON Web Token) authentication is the most common answer in the .NET world, but most tutorials stop at "here is how to validate a token" and skip the parts that actually bite you in production: claims design, token expiry, and refresh tokens.

By the end of this post you will have a .NET 8 Web API that issues signed access tokens, embeds meaningful claims, validates them on protected endpoints, and hands out refresh tokens so users are not forced to log in every fifteen minutes.

1 Prerequisites

Before starting, make sure you have:

.NET 8 SDK installed (verify with dotnet --version)
Visual Studio 2022 (17.8 or later) or VS Code with the C# Dev Kit
A working ASP.NET Core Web API project and basic familiarity with dependency injection and middleware ordering

Note: JWT signing keys are secrets. The hard-coded key in this post is for local development only. In production, load it from a secrets manager, Azure Key Vault, or environment variables, never from appsettings.json committed to source control.

2 Configure JWT authentication

The first step is wiring the JWT bearer handler into the request pipeline so that the framework knows how to read, validate, and reject tokens. We start by defining the token settings, then registering the authentication scheme.

Define your token settings

Keeping the issuer, audience, key, and lifetimes in a strongly typed options object avoids magic strings scattered across the codebase.

// JwtSettings.cs — bound from configuration
public sealed class JwtSettings
{
    public string Issuer { get; init; } = string.Empty;
    public string Audience { get; init; } = string.Empty;
    public string SigningKey { get; init; } = string.Empty;
    public int AccessTokenMinutes { get; init; } = 15;
    public int RefreshTokenDays { get; init; } = 7;
}

// appsettings.json
{
  "Jwt": {
    "Issuer": "https://api.yourapp.com",
    "Audience": "https://yourapp.com",
    "SigningKey": "DEV-ONLY-replace-with-a-32+-char-secret-from-vault",
    "AccessTokenMinutes": 15,
    "RefreshTokenDays": 7
  }
}

Register the bearer handler

This is where the validation rules live. Notice that every Validate* flag is explicitly set to true; relying on defaults is how subtle security holes slip in.

// Program.cs
var jwt = builder.Configuration.GetSection("Jwt").Get<JwtSettings>()!;
builder.Services.AddSingleton(jwt);

builder.Services
    .AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
    .AddJwtBearer(options =>
    {
        options.TokenValidationParameters = new TokenValidationParameters
        {
            ValidateIssuer = true,
            ValidateAudience = true,
            ValidateLifetime = true,
            ValidateIssuerSigningKey = true,
            ValidIssuer = jwt.Issuer,
            ValidAudience = jwt.Audience,
            IssuerSigningKey = new SymmetricSecurityKey(
                Encoding.UTF8.GetBytes(jwt.SigningKey)),
            ClockSkew = TimeSpan.FromSeconds(30)
        };
    });

builder.Services.AddAuthorization();

The key detail most people miss is ClockSkew. It defaults to five minutes, which means a "15 minute" token actually lives for up to 20. Tightening it to 30 seconds keeps short-lived tokens genuinely short-lived. Also remember that UseAuthentication() must come before UseAuthorization() in the middleware pipeline, or every request will be treated as anonymous.

3 Issue access tokens with claims

A token is only as useful as the claims it carries. Claims are the key-value statements the API trusts because the token is signed. Good claim design means you rarely have to hit the database again just to know who the caller is or what they can do.

// TokenService.cs
public sealed class TokenService(JwtSettings settings)
{
    public string CreateAccessToken(Guid userId, string email, IEnumerable<string> roles)
    {
        var claims = new List<Claim>
        {
            new(JwtRegisteredClaimNames.Sub, userId.ToString()),
            new(JwtRegisteredClaimNames.Email, email),
            new(JwtRegisteredClaimNames.Jti, Guid.NewGuid().ToString())
        };

        claims.AddRange(roles.Select(role => new Claim(ClaimTypes.Role, role)));

        var key = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(settings.SigningKey));
        var creds = new SigningCredentials(key, SecurityAlgorithms.HmacSha256);

        var token = new JwtSecurityToken(
            issuer: settings.Issuer,
            audience: settings.Audience,
            claims: claims,
            expires: DateTime.UtcNow.AddMinutes(settings.AccessTokenMinutes),
            signingCredentials: creds);

        return new JwtSecurityTokenHandler().WriteToken(token);
    }
}

The sub claim holds the stable user identifier, jti gives each token a unique ID (useful if you ever need to blocklist a specific token), and the role claims drive authorization. Keep tokens lean; do not stuff a user's entire profile in here, because the token travels on every request and large tokens hurt both bandwidth and header size limits.

4 Add refresh tokens

Access tokens are deliberately short-lived, so without a refresh mechanism your users would be logged out constantly. A refresh token is a long-lived, single-use, server-tracked secret that can be exchanged for a fresh access token. Because it is stored and revocable, it gives you a way to invalidate sessions that a stateless JWT alone cannot.

// RefreshToken.cs — persisted entity
public sealed class RefreshToken
{
    public Guid Id { get; set; }
    public Guid UserId { get; set; }
    public string TokenHash { get; set; } = string.Empty;
    public DateTime ExpiresUtc { get; set; }
    public DateTime? RevokedUtc { get; set; }
    public bool IsActive => RevokedUtc is null && DateTime.UtcNow < ExpiresUtc;
}

// AuthController.cs — the refresh endpoint
[HttpPost("refresh")]
[AllowAnonymous]
public async Task<IActionResult> Refresh([FromBody] RefreshRequest request)
{
    var hash = TokenHasher.Hash(request.RefreshToken);
    var stored = await _db.RefreshTokens
        .FirstOrDefaultAsync(t => t.TokenHash == hash);

    if (stored is null || !stored.IsActive)
        return Unauthorized("Invalid or expired refresh token.");

    // Rotate: revoke the old token and issue a new pair.
    stored.RevokedUtc = DateTime.UtcNow;

    var user = await _db.Users.FindAsync(stored.UserId);
    var (newRefresh, newHash) = TokenHasher.Generate();

    _db.RefreshTokens.Add(new RefreshToken
    {
        UserId = user!.Id,
        TokenHash = newHash,
        ExpiresUtc = DateTime.UtcNow.AddDays(_settings.RefreshTokenDays)
    });
    await _db.SaveChangesAsync();

    var accessToken = _tokenService.CreateAccessToken(
        user.Id, user.Email, user.Roles);

    return Ok(new { accessToken, refreshToken = newRefresh });
}

Two non-negotiable practices appear here. First, store only a hash of the refresh token, never the raw value, so a database leak does not hand attackers live sessions. Second, rotate on every use: each refresh revokes the old token and issues a new one, which lets you detect token theft (a revoked token being replayed is a strong signal something is wrong).

Edge case: If a revoked refresh token is presented, treat it as a possible breach. A robust implementation revokes the entire token family for that user, forcing a fresh login. This is the difference between a tidy demo and a system you can defend.

5 Protect endpoints and test the flow

With issuance and refresh in place, protecting an endpoint is a single attribute. The framework reads the bearer token, validates the signature and claims, and populates User for you.

[Authorize]
[HttpGet("me")]
public IActionResult Me()
{
    var userId = User.FindFirstValue(JwtRegisteredClaimNames.Sub);
    var email  = User.FindFirstValue(JwtRegisteredClaimNames.Email);
    return Ok(new { userId, email });
}

To verify the full loop, call your login endpoint to receive an access and refresh token, then call a protected endpoint with the access token in the header:

GET /api/auth/me
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

A valid token returns 200 with your claims. Wait for the access token to expire and the same call returns 401; hit the refresh endpoint with your refresh token and you get a fresh pair, confirming the rotation works end to end.

Wrapping up

You now have a .NET 8 API that issues signed, claim-bearing access tokens, validates them strictly, and supports rotating refresh tokens backed by hashed, revocable storage. That covers the authentication half of the security story: proving who the caller is.

The natural next step is authorization, deciding what an authenticated caller is allowed to do. That is exactly what the next post in this series tackles with role-based authorization using EF Core and PostgreSQL.

Got a question or ran into a problem? Drop a comment below and I will reply.

Connection Pooling with Npgsql and .NET 8: PgBouncer, Multiplexing, and Best Practices

2026-05-24T16:19:06.479+05:45

Connection pooling is more critical with PostgreSQL than it is with SQL Server, and the reason is architectural. Each PostgreSQL client connection spawns a dedicated OS process on the server, making connections expensive to establish and memory-intensive to hold. Under load, exhausting that process limit is one of the most common scaling failures for .NET APIs backed by PostgreSQL.

This post walks through three layers of the solution: Npgsql's built-in client-side pool, the multiplexing mode introduced in Npgsql 7, and PgBouncer as a server-side proxy for high-concurrency deployments. By the end you will have a clear picture of which layer solves which problem and what configuration to use in a .NET 8 API.

1 Prerequisites

Before starting, make sure you have:

.NET 8 SDK or later
PostgreSQL 14 or later
Npgsql 8.x (the standalone driver) or Npgsql.EntityFrameworkCore.PostgreSQL 8.x
PgBouncer installed locally or via Docker if you want to follow the PgBouncer section

Note: PostgreSQL's default max_connections is 100. Each connection consumes roughly 5–10 MB of RAM on the server. A single .NET API instance with a default Npgsql pool size of 100 can exhaust max_connections on its own, before any other clients connect.

2 Npgsql's built-in client-side pool

Npgsql maintains a pool of physical connections per unique connection string. When your code opens a logical connection (DbContext method call, NpgsqlConnection.OpenAsync()), Npgsql hands out a connection from the pool rather than opening a new one. When the logical connection is closed or the DbContext is disposed, the physical connection returns to the pool for reuse.

Configuring the pool via NpgsqlDataSourceBuilder

In .NET 8, the recommended approach is to build an NpgsqlDataSource once at startup and register it with the DI container. This is more efficient than passing a raw connection string because Npgsql can reuse prepared statement caches and configuration across the application lifetime.

// Program.cs
var connectionString = builder.Configuration.GetConnectionString("Default")!;

var dataSource = new NpgsqlDataSourceBuilder(connectionString)
    .Build();

builder.Services.AddSingleton(dataSource);

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(dataSource));

Key connection string pool parameters

// appsettings.json connection string with explicit pool settings
// "Server=localhost;Port=5432;Database=mydb;Username=api;Password=secret;
//  Minimum Pool Size=2;Maximum Pool Size=20;
//  Connection Idle Lifetime=300;Connection Pruning Interval=10"

var builder = new NpgsqlConnectionStringBuilder(connectionString)
{
    MinPoolSize = 2,            // Keep at least 2 connections open at all times
    MaxPoolSize = 20,           // Never exceed 20 physical connections per instance
    ConnectionIdleLifetime = 300,  // Close idle connections after 5 minutes
    ConnectionPruningInterval = 10 // Check for idle connections every 10 seconds
};

Size the pool relative to your workload and the number of application instances. If you run 5 replicas each with MaxPoolSize=20, you need PostgreSQL's max_connections set above 100 (5 × 20), with headroom for admin connections. A common production formula is MaxPoolSize = max_connections / number_of_instances - buffer.

Tip: Do not set MaxPoolSize too high. More connections means more RAM consumed on the PostgreSQL server, more lock contention, and more context switching. A smaller pool with a short wait timeout is often faster than a large pool where the server is thrashing. Start at 20 per instance and tune from there.

3 Multiplexing mode in Npgsql 7+

Npgsql's multiplexing mode allows multiple concurrent commands to share a single physical connection through pipelining. Instead of each async operation waiting for an exclusive connection from the pool, multiplexing queues commands and sends them over shared connections, reducing the total number of physical connections needed under high concurrency.

Enabling multiplexing

var dataSource = new NpgsqlDataSourceBuilder(connectionString)
    .EnableDynamicJson()  // optional; keeps JSON handling flexible
    .Build();

// Multiplexing is enabled via the connection string parameter
// Add "Multiplexing=true;Max Auto Prepare=20" to your connection string:
// "...;Multiplexing=true;Max Auto Prepare=20"

// Or configure it on the builder directly:
var csBuilder = new NpgsqlConnectionStringBuilder(connectionString)
{
    Multiplexing = true,
    MaxAutoPrepare = 20   // Npgsql auto-prepares the 20 most-used statements
};

var dataSource = new NpgsqlDataSourceBuilder(csBuilder.ConnectionString).Build();

When multiplexing helps and when it does not

Multiplexing reduces the connection count under high-concurrency, short-duration query workloads. It is most effective when many requests are running simple queries simultaneously, such as a read-heavy API endpoint with hundreds of concurrent users.

Multiplexing is not compatible with all Npgsql features. Do not enable it if your code uses any of the following, as they require exclusive connection ownership:

Explicit transactions (BeginTransactionAsync)
COPY operations
LISTEN / NOTIFY
Cursors or portal-based streaming

Note: EF Core wraps most operations in implicit transactions, which are incompatible with multiplexing. If you use EF Core as your primary data access layer with SaveChangesAsync(), multiplexing provides limited benefit and may cause errors. It is better suited to scenarios using raw NpgsqlCommand or Dapper without explicit transactions.

4 PgBouncer as a server-side pool

When you have many application instances (microservices, containerised replicas, serverless functions) each maintaining their own Npgsql pool, the total connection count against PostgreSQL can still grow beyond what the server handles efficiently. PgBouncer is a lightweight connection pooler that sits between your .NET applications and PostgreSQL, multiplexing many client connections into a smaller number of server connections.

PgBouncer pooling modes

Session mode assigns one server connection per client connection for the full session lifetime. This is the most compatible mode but provides the least reduction in server-side connections. Use it as a safe default when first introducing PgBouncer.

Transaction mode assigns a server connection only for the duration of a single transaction, then releases it immediately. This is the most efficient mode and provides the largest reduction in server-side connections, but it is incompatible with session-level features: prepared statements, advisory locks, SET settings, and temporary tables all break in transaction mode unless handled carefully.

Configuring Npgsql for PgBouncer transaction mode

// When using PgBouncer in transaction mode, disable Npgsql's prepared statement
// cache and set No Reset On Close to skip the session-cleanup command
var csBuilder = new NpgsqlConnectionStringBuilder(connectionString)
{
    // Point at PgBouncer's port (default 6432), not PostgreSQL directly
    Host = "pgbouncer-host",
    Port = 6432,

    // Disable server-side prepared statements (not supported in transaction mode)
    MaxAutoPrepare = 0,
    NoResetOnClose = true,   // Skip "DISCARD ALL" on connection return

    // Keep Npgsql's client pool small; PgBouncer owns the server-side pool
    MaxPoolSize = 10
};

var dataSource = new NpgsqlDataSourceBuilder(csBuilder.ConnectionString).Build();
builder.Services.AddDbContext<AppDbContext>(o => o.UseNpgsql(dataSource));

Minimal PgBouncer configuration for a .NET API

; pgbouncer.ini
[databases]
mydb = host=postgres-host port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000    ; total clients PgBouncer accepts
default_pool_size = 25    ; server connections per database/user pair
server_reset_query =      ; empty: skip DISCARD ALL in transaction mode

Tip: Run PgBouncer as a sidecar container alongside each application pod in Kubernetes rather than as a single shared instance. A sidecar keeps the network hop local and removes a single point of failure, while each pod's PgBouncer contributes its default_pool_size to the total server-side connection count you need to budget for.

5 Running and verifying your pool

After configuring pooling, verify the actual connection count on the PostgreSQL server to confirm your settings are having the intended effect.

-- Count active connections by application and state
SELECT
    application_name,
    state,
    count(*) AS connection_count
FROM pg_stat_activity
WHERE datname = 'mydb'
GROUP BY application_name, state
ORDER BY connection_count DESC;

-- Check if any connections are waiting for a pool slot (wait_event_type = 'Client')
SELECT pid, wait_event_type, wait_event, query_start, state
FROM pg_stat_activity
WHERE wait_event_type = 'Client'
  AND datname = 'mydb';

// In .NET 8, log Npgsql pool events to watch for pool exhaustion at startup
builder.Logging.AddFilter("Npgsql", LogLevel.Information);

// Pool exhaustion looks like this in logs:
// The connection pool has been exhausted, either raise MaxPoolSize
// or set ConnectionTimeout to a higher value.

Wrapping up

Connection pooling with PostgreSQL and .NET requires thinking at three levels. Npgsql's client-side pool handles the common case for a single application instance: size it conservatively and use NpgsqlDataSourceBuilder for lifetime management. Multiplexing is a useful additional tool for raw command workloads with very high concurrency and no transactions. PgBouncer steps in when you have many application instances and need to cap the total server-side connection count below what PostgreSQL can efficiently handle.

The most important thing you can do before tuning is measure: check pg_stat_activity under realistic load, watch for pool exhaustion in logs, and size your pool relative to max_connections and the number of application replicas actually running.

Got a question or ran into a problem? Drop a comment below and I will reply.

Indexing Strategies in PostgreSQL That Every .NET Developer Should Know

2026-05-24T16:17:42.639+05:45

Most .NET developers let EF Core generate their PostgreSQL indexes and then wonder why certain queries are slow in production. The default behaviour is reasonable, but PostgreSQL offers several index types that SQL Server does not, and knowing when to reach for each one is the difference between a fast API and a scaling problem.

This post covers the four index strategies that matter most for .NET API workloads: B-Tree, GIN, partial, and expression indexes. Each section explains what the index is for, shows how to create it (both via EF Core and raw SQL), and includes a EXPLAIN ANALYZE pattern to verify it is being used.

1 Prerequisites

Before starting, make sure you have:

.NET 8 SDK and PostgreSQL 14 or later
Npgsql.EntityFrameworkCore.PostgreSQL 8.x
Access to psql, pgAdmin, or DataGrip to run EXPLAIN ANALYZE
A working EF Core project with at least one entity mapped to a table

Note: Index decisions should always be validated with realistic data volumes. An index that helps at 100,000 rows may be ignored at 500 rows because the query planner considers a sequential scan cheaper on small tables.

2 B-Tree indexes — the default

Every HasIndex() call in EF Core creates a B-Tree index. B-Tree is the right choice for equality and range filters on scalar columns: integers, GUIDs, dates, strings, and decimals. If you are filtering by UserId, sorting by CreatedAt, or looking up by Email, B-Tree is what you want.

Creating B-Tree indexes with EF Core

modelBuilder.Entity<Order>(entity =>
{
    // Single-column index for equality lookups
    entity.HasIndex(o => o.CustomerId);

    // Composite index — column order matters
    // Queries that filter on Status AND sort by CreatedAt benefit from this
    entity.HasIndex(o => new { o.Status, o.CreatedAt });

    // Unique index — enforced at the database level
    entity.HasIndex(o => o.ReferenceNumber).IsUnique();
});

Composite index column ordering

For composite B-Tree indexes, put the highest-cardinality equality filter column first, followed by range or sort columns. A query that filters on Status = 'Pending' and orders by CreatedAt DESC uses the index above efficiently. A query that only filters on CreatedAt cannot use the leftmost prefix and will do a sequential scan.

-- Confirm index usage for the composite index
EXPLAIN ANALYZE
SELECT * FROM "Orders"
WHERE "Status" = 'Pending'
ORDER BY "CreatedAt" DESC
LIMIT 50;
-- Look for: Index Scan Backward using "IX_Orders_Status_CreatedAt"

Tip: A covering index (PostgreSQL 11+) stores additional columns in the index leaf nodes, allowing the query to be satisfied without touching the heap. Use IncludeProperties() in EF Core to add include columns to a B-Tree index.

// Covering index: the query planner can serve the SELECT entirely from the index
entity.HasIndex(o => new { o.Status, o.CreatedAt })
    .IncludeProperties(o => new { o.Id, o.TotalAmount });

3 GIN indexes — for JSONB, arrays, and full-text search

GIN (Generalised Inverted Index) indexes the internal structure of a composite value rather than the value itself. Use GIN for jsonb columns, PostgreSQL array columns, and tsvector full-text search columns. A B-Tree index on a jsonb column is useless for containment queries; GIN is what makes them fast.

GIN index on a JSONB column

// EF Core does not expose GIN natively; use a raw SQL migration
public partial class AddMetadataGinIndex : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Full-document GIN index — covers all keys and values in the document
        migrationBuilder.Sql(
            """
            CREATE INDEX ix_products_metadata_gin
            ON "Products"
            USING GIN ("Metadata");
            """
        );
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            """DROP INDEX IF EXISTS ix_products_metadata_gin;"""
        );
    }
}

GIN index on a tsvector column

// When using HasGeneratedTsVectorColumn, chain HasMethod("GIN")
modelBuilder.Entity<Article>()
    .HasGeneratedTsVectorColumn(a => a.SearchVector, "english",
        a => new { a.Title, a.Body })
    .HasIndex(a => a.SearchVector)
    .HasMethod("GIN");  // Npgsql honours this for tsvector columns

Tip: GIN indexes are larger and slower to build than B-Tree indexes, but they are dramatically faster for containment and match queries on complex types. Use gin_pending_list_limit (default 4 MB) to control how much data is buffered in a pending list before being merged into the main index structure.

4 Partial indexes — index a subset of rows

A partial index includes only the rows that match a WHERE clause. If 95% of your Orders table has Status = 'Completed', an index that covers all statuses is bloated with rows you rarely query. A partial index on Status = 'Pending' is smaller, faster to scan, and faster to update because only new pending orders touch it.

Creating a partial index with EF Core

modelBuilder.Entity<Order>(entity =>
{
    // Only index rows where the order is not yet completed
    entity.HasIndex(o => o.CreatedAt)
        .HasFilter("\"Status\" != 'Completed'");
});

-- Equivalent SQL (generated by the migration):
CREATE INDEX "IX_Orders_CreatedAt"
ON "Orders" ("CreatedAt")
WHERE "Status" != 'Completed';

Soft-delete pattern with a partial index

// A very common pattern: index only non-deleted rows
modelBuilder.Entity<Customer>(entity =>
{
    entity.HasIndex(c => c.Email)
        .IsUnique()
        .HasFilter("\"IsDeleted\" = false");
    // This also enforces unique emails only among active customers
});

This pattern is particularly useful for soft-delete implementations: the unique constraint fires only for active records, and the index is small because deleted rows are excluded.

5 Expression indexes — index a computed value

An expression index (also called a functional index) indexes the result of an expression rather than a raw column value. Use this when your queries consistently apply a function to a column before filtering, such as lower-casing an email for case-insensitive lookups or extracting a date from a timestamp.

Case-insensitive email lookup

-- Create via a raw SQL migration (EF Core HasIndex does not support expressions)
CREATE INDEX ix_customers_email_lower
ON "Customers" (lower("Email"));

// Your LINQ query must use the same expression for the index to apply
var customer = await context.Customers
    .Where(c => c.Email.ToLower() == email.ToLower())
    .FirstOrDefaultAsync();

// EF Core + Npgsql translates ToLower() to lower(), which matches the index

Indexing a date extracted from a timestamp

-- Queries that filter on the date part of a timestamptz column
CREATE INDEX ix_orders_created_date
ON "Orders" (date_trunc('day', "CreatedAt" AT TIME ZONE 'UTC'));

Note: The expression in a LINQ Where clause must match the index expression exactly for the planner to use it. If the index is on lower("Email") but your query sends "Email" ILIKE $1, the index will not be used. Always verify with EXPLAIN ANALYZE.

6 Finding missing and unused indexes

PostgreSQL tracks index usage statistics in pg_stat_user_indexes. After running your application under realistic load, query this view to identify indexes that are never used (candidates for removal) and tables with high sequential scan counts but no supporting index (candidates for a new index).

-- Indexes that have never been used since the last statistics reset
SELECT
    schemaname,
    relname    AS table_name,
    indexrelname AS index_name,
    idx_scan   AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY relname;

-- Tables with high sequential scans (potential missing indexes)
SELECT
    relname    AS table_name,
    seq_scan,
    idx_scan,
    n_live_tup AS row_estimate
FROM pg_stat_user_tables
WHERE seq_scan > 100
  AND schemaname = 'public'
ORDER BY seq_scan DESC;

An unused index still incurs write overhead on every INSERT, UPDATE, and DELETE. Remove indexes that have never been used after several weeks of production traffic. Statistics are reset by pg_stat_reset() or when PostgreSQL restarts, so collect data over a meaningful time window before drawing conclusions.

Wrapping up

PostgreSQL's index toolkit is broader than most SQL Server developers realise. B-Tree covers the common cases that EF Core's HasIndex() already generates. GIN unlocks efficient querying of jsonb documents, arrays, and full-text search vectors. Partial indexes keep write-heavy tables lean by excluding rows you rarely read. Expression indexes push function application into the index so query-time function calls do not bypass it.

The consistent discipline across all four types is the same: define the index in a migration, load representative data, run EXPLAIN ANALYZE on your actual queries, and check pg_stat_user_indexes after the application has been under load. Never assume an index is being used without verifying it.

Got a question or ran into a problem? Drop a comment below and I will reply.

Full-Text Search in PostgreSQL from a .NET 8 EF Core Controller API

2026-05-24T16:16:36.059+05:45

Adding keyword search to an API often leads .NET developers toward Elasticsearch or Azure Cognitive Search before they have even looked at what their database already supports. PostgreSQL's built-in full-text search, backed by a GIN index, handles a large class of search requirements without any external service, and Npgsql exposes it cleanly in LINQ.

This post walks through configuring a generated tsvector column on a PostgreSQL table, indexing it correctly, and wiring up a working search endpoint in an ASP.NET Core Controller API using EF Core 8.

1 Prerequisites

Before starting, make sure you have:

.NET 8 SDK or later
PostgreSQL 14 or later
Npgsql.EntityFrameworkCore.PostgreSQL 8.x NuGet package
An ASP.NET Core Web API project with a configured DbContext

Note: All FTS examples use the english language configuration. PostgreSQL ships with configurations for many languages. Run SELECT cfgname FROM pg_ts_config; in psql to see what is available on your instance.

2 How PostgreSQL full-text search works

PostgreSQL FTS uses two building blocks. A tsvector is a pre-processed, sorted list of lexemes (normalised word roots) derived from a text document. A tsquery is a search expression with AND (&), OR (|), NOT (!), and phrase (<->) operators. The @@ match operator checks whether a tsquery matches a tsvector.

-- What tsvector looks like for a sentence:
SELECT to_tsvector('english', 'Building scalable APIs with .NET 8 and PostgreSQL');
-- Result: '.net':5 '8':6 'api':4 'build':1 'postgresql':8 'scalabl':3

-- What tsquery looks like for a user search term:
SELECT plainto_tsquery('english', 'scalable apis');
-- Result: 'scalabl' & 'api'

-- The match operator:
SELECT to_tsvector('english', 'Building scalable APIs with .NET 8')
    @@ plainto_tsquery('english', 'scalable apis');
-- Result: true

The key insight is that both sides are reduced to lexemes: "Building" becomes "build", "APIs" becomes "api", "scalable" becomes "scalabl". This is why FTS finds "APIs" when you search for "api" and why it ignores stop words like "with" and "and".

3 Adding a generated tsvector column

Rather than recomputing the tsvector on every search query, store it as a generated (computed) column that PostgreSQL keeps up to date automatically whenever the source columns change. Npgsql exposes a dedicated fluent API for this pattern.

The entity

using NpgsqlTypes;

public class Article
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public string Body { get; set; } = string.Empty;
    public string Author { get; set; } = string.Empty;
    public DateTime PublishedAt { get; set; }

    // Populated automatically by PostgreSQL; never set this from C#
    public NpgsqlTsVector SearchVector { get; set; } = null!;
}

Configuring the generated column and GIN index

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Article>(entity =>
    {
        entity.HasKey(a => a.Id);

        // Tell Npgsql to create a GENERATED ALWAYS AS tsvector column
        // weighting Title more heavily than Body (A = highest, D = lowest)
        entity.HasGeneratedTsVectorColumn(
                a => a.SearchVector,
                "english",
                a => new { a.Title, a.Body })
            .HasIndex(a => a.SearchVector)
            .HasMethod("GIN");
    });
}

What the migration generates

-- Npgsql emits a STORED generated column backed by to_tsvector:
ALTER TABLE "Articles"
    ADD COLUMN "SearchVector" tsvector
    GENERATED ALWAYS AS (
        to_tsvector('english',
            coalesce("Title", '') || ' ' || coalesce("Body", ''))
    ) STORED;

CREATE INDEX "IX_Articles_SearchVector"
    ON "Articles" USING GIN ("SearchVector");

Run dotnet ef migrations add AddArticleSearchVector and dotnet ef database update. Inspect the migration to confirm both the generated column and the GIN index are present before applying.

Tip: If you need to weight the title higher than the body (so that a title match ranks above a body match), use a raw SQL migration to create the generated column manually with setweight(to_tsvector('english', "Title"), 'A') || setweight(to_tsvector('english', "Body"), 'B'). Npgsql's HasGeneratedTsVectorColumn concatenates columns without weights.

4 Writing the search endpoint

With the column and index in place, the Controller endpoint is straightforward. Use PlainToTsQuery rather than ToTsQuery for user-supplied input: PlainToTsQuery treats the input as plain text without requiring valid tsquery syntax, so it will not throw on arbitrary search strings.

The Controller

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using NpgsqlTypes;

[ApiController]
[Route("api/[controller]")]
public class ArticlesController(AppDbContext context) : ControllerBase
{
    [HttpGet("search")]
    public async Task<IActionResult> Search(
        [FromQuery] string q,
        [FromQuery] int page = 1,
        [FromQuery] int pageSize = 20)
    {
        if (string.IsNullOrWhiteSpace(q))
            return BadRequest("A search term is required.");

        // PlainToTsQuery is safe for arbitrary user input
        var tsQuery = EF.Functions.PlainToTsQuery("english", q.Trim());

        var results = await context.Articles
            .Where(a => a.SearchVector.Matches(tsQuery))
            .OrderByDescending(a => a.SearchVector.Rank(tsQuery))
            .Select(a => new ArticleSearchResult(
                a.Id,
                a.Title,
                a.Author,
                a.PublishedAt,
                a.Body.Length > 300 ? a.Body.Substring(0, 300) + "…" : a.Body))
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .ToListAsync();

        return Ok(results);
    }
}

public record ArticleSearchResult(
    int Id,
    string Title,
    string Author,
    DateTime PublishedAt,
    string Excerpt);

What EF Core sends to PostgreSQL

SELECT a."Id", a."Title", a."Author", a."PublishedAt",
    CASE WHEN length(a."Body") > 300
         THEN substring(a."Body", 1, 300) || '…'
         ELSE a."Body" END
FROM "Articles" AS a
WHERE a."SearchVector" @@ plainto_tsquery('english', $1)
ORDER BY ts_rank(a."SearchVector", plainto_tsquery('english', $1)) DESC
LIMIT $2 OFFSET $3

Tip: WebSearchToTsQuery is an alternative to PlainToTsQuery available in PostgreSQL 11+. It understands Google-style syntax: quoted phrases, the -word exclusion operator, and OR. Use it when you want to expose advanced search syntax to users without building a custom parser.

5 Running and testing it

Seed the table with a few articles, then test the endpoint with representative queries to confirm the GIN index is being used.

// Quick seed in Program.cs (development only)
if (app.Environment.IsDevelopment())
{
    using var scope = app.Services.CreateScope();
    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    await db.Database.MigrateAsync();

    if (!await db.Articles.AnyAsync())
    {
        db.Articles.AddRange(
            new Article { Title = "Getting started with EF Core 8", Body = "Entity Framework Core 8 introduces JSON columns, bulk updates, and complex type mappings.", Author = "Anaya", PublishedAt = DateTime.UtcNow.AddDays(-10) },
            new Article { Title = "PostgreSQL indexing strategies", Body = "B-Tree, GIN, and partial indexes each serve a different purpose in PostgreSQL.", Author = "Anaya", PublishedAt = DateTime.UtcNow.AddDays(-5) }
        );
        await db.SaveChangesAsync();
    }
}

-- Confirm the GIN index is used (run in psql or pgAdmin)
EXPLAIN ANALYZE
SELECT * FROM "Articles"
WHERE "SearchVector" @@ plainto_tsquery('english', 'entity framework');

-- Look for: Bitmap Index Scan on "IX_Articles_SearchVector"
-- or: Index Scan using "IX_Articles_SearchVector"

A successful result shows a Bitmap Index Scan or Index Scan against IX_Articles_SearchVector, not a sequential scan. If you see a sequential scan on a freshly populated table, run ANALYZE "Articles"; to update the planner statistics and re-check.

Wrapping up

PostgreSQL's built-in full-text search, combined with Npgsql's HasGeneratedTsVectorColumn and the Matches LINQ extension, gives you a capable search endpoint without any external service. The generated column keeps the tsvector current automatically, the GIN index keeps searches fast at scale, and PlainToTsQuery handles arbitrary user input safely.

For the next step, consider adding highlighted snippets using PostgreSQL's ts_headline() function via EF.Functions or a raw SQL projection, and look at pg_trgm if you need fuzzy matching for misspellings alongside FTS relevance ranking.

Got a question or ran into a problem? Drop a comment below and I will reply.

JSONB Columns in PostgreSQL with EF Core: When and How to Use Them

2026-05-24T16:01:47.133+05:45

PostgreSQL's jsonb column type is not a glorified text field for stashing serialised strings. It is a binary-indexed, queryable, first-class storage format that gives you document-style flexibility inside a relational schema, without reaching for a separate document database. If you have ever pushed a JSON string into an nvarchar(max) column and then written ugly string parsing to query it, jsonb is the answer you were looking for.

EF Core 8 introduced proper JSON column support that Npgsql maps directly to jsonb. This post walks through defining the mapping, writing LINQ queries that PostgreSQL executes efficiently, and adding the right index so that power is not wasted on full scans.

1 Prerequisites

Before starting, make sure you have:

.NET 8 SDK or later
PostgreSQL 14 or later (16 is recommended)
Npgsql.EntityFrameworkCore.PostgreSQL 8.x NuGet package
Familiarity with EF Core migrations and basic LINQ queries

Note: EF Core's owned entity ToJson() mapping requires EF Core 7 or later. All examples here use EF Core 8. Npgsql maps ToJson() to a jsonb column by default; it does not emit json (text) or nvarchar.

2 Defining a JSONB column

EF Core 8 uses owned entity types configured with ToJson() to project a C# object into a single JSON column. You define the C# shape normally, then tell EF Core to store it as JSON rather than as a separate table.

The entity model

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }

    // This navigation property will become a single jsonb column
    public ProductDetails? Details { get; set; }
}

public class ProductDetails
{
    public string? Sku { get; set; }
    public int StockQuantity { get; set; }
    public List<string> Tags { get; set; } = new();
    public Dictionary<string, string> Attributes { get; set; } = new();
}

Configuring the mapping

public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>(entity =>
        {
            entity.HasKey(p => p.Id);

            entity.OwnsOne(p => p.Details, details =>
            {
                details.ToJson(); // Npgsql emits: "Details" jsonb
            });
        });
    }
}

What the migration generates

// The generated migration contains:
migrationBuilder.AddColumn<string>(
    name: "Details",
    table: "Products",
    type: "jsonb",      // Npgsql sets this; SQL Server would emit nvarchar(max)
    nullable: true);

Run dotnet ef migrations add AddProductDetails followed by dotnet ef database update. Inspect the generated migration file to confirm the column type is jsonb before applying it.

Tip: jsonb stores JSON in a decomposed binary format. PostgreSQL parses and validates the document on write, making writes slightly heavier than the json (plain text) type, but reads are faster because the document does not need re-parsing on every access.

3 Querying JSONB columns with LINQ

This is where jsonb earns its place. EF Core translates LINQ expressions against the owned type into PostgreSQL JSON path operators, which execute server-side without pulling every row into memory.

Filtering on a scalar JSON property

// Find all products currently in stock
var inStock = await context.Products
    .Where(p => p.Details != null && p.Details.StockQuantity > 0)
    .OrderBy(p => p.Name)
    .ToListAsync();

// PostgreSQL executes something equivalent to:
// WHERE (details ->> 'StockQuantity')::int > 0

Filtering with a JSON array containment check

// Find products tagged as "clearance"
var clearance = await context.Products
    .Where(p => p.Details != null && p.Details.Tags.Contains("clearance"))
    .ToListAsync();

Combining relational and JSON filters

// Under $50, in stock, and tagged as "sale"
var saleItems = await context.Products
    .Where(p =>
        p.Price < 50m &&
        p.Details != null &&
        p.Details.StockQuantity > 0 &&
        p.Details.Tags.Contains("sale"))
    .Select(p => new
    {
        p.Id,
        p.Name,
        p.Price,
        p.Details!.Sku
    })
    .ToListAsync();

Updating a JSON document

// Load, modify in memory, and save — EF Core tracks the change
var product = await context.Products
    .FirstOrDefaultAsync(p => p.Id == productId);

if (product?.Details is not null)
{
    product.Details.StockQuantity -= quantitySold;
    product.Details.Tags.Add("low-stock");
    await context.SaveChangesAsync();
}
// EF Core sends the full updated jsonb document back to PostgreSQL

Note: EF Core replaces the entire jsonb document on update, not individual fields. For high-frequency partial updates to large documents, consider a raw SQL jsonb_set() call via ExecuteSqlRawAsync to minimise the payload.

4 Adding a GIN index for performance

Without an index every jsonb query performs a sequential scan. A GIN (Generalised Inverted Index) lets PostgreSQL push filters into the index structure rather than reading every row.

Full-document GIN index via a custom migration

EF Core's fluent API does not expose GIN index creation natively, so you add it in a raw SQL migration.

public partial class AddProductDetailsGinIndex : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            """
            CREATE INDEX ix_products_details_gin
            ON "Products"
            USING GIN ("Details");
            """
        );
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            """DROP INDEX IF EXISTS ix_products_details_gin;"""
        );
    }
}

Targeted expression indexes for specific paths

A full-document GIN index covers every key in the document but can grow large. If you only filter on one or two paths, a targeted expression index is leaner and faster for those specific queries.

-- Index just the StockQuantity path, cast to integer
CREATE INDEX ix_products_stock_qty
ON "Products" (( ("Details" ->> 'StockQuantity')::int ));

-- GIN index on only the Tags array within the document
CREATE INDEX ix_products_tags_gin
ON "Products"
USING GIN (("Details" -> 'Tags'));

Verifying the index is used

-- Run in psql or pgAdmin after loading representative data
EXPLAIN ANALYZE
SELECT * FROM "Products"
WHERE ("Details" ->> 'StockQuantity')::int > 0;

-- Look for "Index Scan using ix_products_stock_qty" in the output.
-- If you see "Seq Scan", run ANALYZE "Products"; to refresh statistics.

Note: PostgreSQL's query planner may choose a sequential scan on small tables even with an index present, because the cost model favours sequential I/O at low row counts. Test index usage with a realistic data volume before concluding the index is not working.

Wrapping up

EF Core 8 and Npgsql together make jsonb columns a first-class part of your .NET data model. You get strongly typed C# objects, LINQ translation to efficient PostgreSQL JSON path operators, and index support that keeps reads fast as rows accumulate.

Use jsonb when your schema genuinely varies across rows or when you need semi-structured metadata alongside relational data. Do not use it as an excuse to avoid proper schema design; a relational model still outperforms jsonb for structured data with a known, stable shape.

Got a question or ran into a problem? Drop a comment below and I will reply.

PostgreSQL vs. SQL Server: What .NET Developers Need to Know Before Switching

2026-05-24T16:01:05.211+05:45

You have shipped .NET APIs backed by SQL Server for years, and the next project lands with a PostgreSQL requirement. Or maybe your team is evaluating whether to drop SQL Server licence costs entirely. Either way, you are about to discover that "it is just another relational database" is only partially true.

Both SQL Server and PostgreSQL are mature, ACID-compliant databases with excellent EF Core support, but they make very different decisions around licensing, data types, and SQL dialect. This post unpacks those differences with real code so you can make the switch with confidence instead of surprises.

By the end you will have a side-by-side code comparison, a clear breakdown of the tradeoffs, and a decision framework you can use in your next architecture conversation.

1 A quick history first

SQL Server grew out of Microsoft's partnership with Sybase in the late 1980s and became the default relational database for Windows-based enterprise .NET applications. It is proprietary, commercially licensed, and deeply integrated with the Microsoft tooling ecosystem.

PostgreSQL descended from the POSTGRES project at UC Berkeley, went fully open-source in 1996, and has since grown into one of the most feature-rich databases in existence, with a reputation for standards compliance and extensibility.

Both use a cost-based query planner, support full ACID transactions, foreign keys, triggers, and stored procedures, and both have first-class EF Core providers that track releases closely.

2 The same task, two ways

To make the differences concrete, here is the same EF Core setup performed for each database: registering the context, applying a migration, and executing a simple parameterised query.

SQL Server approach

// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("Default"),
        sql => sql.MigrationsHistoryTable("__EFMigrationsHistory", "dbo")
    ));

// AppDbContext.cs
public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .Property(p => p.Name)
            .HasMaxLength(256)
            .IsRequired();
    }
}

PostgreSQL approach

// Program.cs — only the provider registration changes
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(
        builder.Configuration.GetConnectionString("Default"),
        npgsql => npgsql.MigrationsHistoryTable("__ef_migrations_history", "public")
    ));

// AppDbContext.cs — identical; EF Core abstracts the provider completely
public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .Property(p => p.Name)
            .HasMaxLength(256)
            .IsRequired();
    }
}

The DbContext and entity configuration are identical. The provider swap is contained entirely in the Program.cs registration. The first gotcha surfaces in migrations: PostgreSQL folds unquoted identifiers to lowercase, so a column named Name becomes name on disk. The Npgsql provider handles this transparently in generated migrations, but any raw SQL you have written against SQL Server conventions will need a review pass before it works on PostgreSQL.

3 The real tradeoffs

1. Licensing and cost

SQL Server Standard edition starts at around $900 per core; Enterprise runs into the tens of thousands. Developer edition is free but cannot be used in production. PostgreSQL is completely free under the PostgreSQL Licence with no core count or server caps. For teams running many environments (dev, staging, pre-prod, multiple production regions), the licence difference is significant. Azure Database for PostgreSQL Flexible Server also prices lower than Azure SQL on equivalent compute tiers, though your specific configuration will vary.

Does this matter to you? If you are building SaaS and want to avoid per-core fee growth as you scale, PostgreSQL wins on economics. If your organisation already covers SQL Server under a Microsoft Enterprise Agreement, the marginal cost may already be absorbed.

2. Data types

PostgreSQL ships with native types that SQL Server lacks or emulates awkwardly: jsonb (binary JSON with index support), uuid (first-class UUID without the byte-order quirks of uniqueidentifier), typed arrays, range types, and the citext extension for case-insensitive text. SQL Server stores JSON as plain nvarchar(max) and queries it through scalar functions like JSON_VALUE and OPENJSON, without binary storage or index support on that data.

// EF Core 8: JSONB column via owned entity (PostgreSQL maps ToJson() to jsonb)
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public ProductMetadata? Metadata { get; set; }
}

public class ProductMetadata
{
    public string? Sku { get; set; }
    public List<string> Tags { get; set; } = new();
}

// In OnModelCreating:
modelBuilder.Entity<Product>()
    .OwnsOne(p => p.Metadata, b => b.ToJson());
// Npgsql emits: "Metadata" jsonb
// SQL Server emits: "Metadata" nvarchar(max)

Same EF Core API; different storage reality. On PostgreSQL you can index into that jsonb column with a GIN index and run efficient containment queries. On SQL Server you are doing a full table scan through a function call.

3. SQL dialect differences

PostgreSQL is stricter about ANSI SQL compliance. Several T-SQL patterns break immediately: TOP becomes LIMIT, string concatenation with + must become || or CONCAT(), and GETDATE() becomes NOW() or CURRENT_TIMESTAMP. LINQ queries translated by EF Core are provider-aware and generally safe, but anything written with FromSqlRaw, Dapper, or stored procedures needs a dialect review. The case-sensitivity difference also catches teams off-guard: SQL Server string comparisons are case-insensitive by default (depending on collation); PostgreSQL is case-sensitive by default.

4. Tooling

SQL Server Management Studio is a mature, battle-tested GUI with profiler integration, visual execution plan analysis, and a large ecosystem of DBA tooling. PostgreSQL's first-party tool, pgAdmin 4, has improved significantly but still lags SSMS for complex query plan visualisation. Third-party tools such as DBeaver Community and JetBrains DataGrip fill that gap well. For .NET developers, the EF Core CLI (dotnet ef migrations add, dotnet ef database update) works identically across providers.

5. Extensions and ecosystem

PostgreSQL's extension system is one of its biggest differentiators. PostGIS adds production-grade geospatial support. TimescaleDB turns it into a time-series database. pg_trgm enables trigram similarity search. pgvector adds vector embeddings for AI workloads. These capabilities require separate licences or entirely different database products under SQL Server.

4 Which one should you choose?

The decision comes down to your licensing economics, the PostgreSQL-specific features your domain genuinely needs, and your team's operational familiarity.

Choose SQL Server when:

Your organisation covers SQL Server licences under a Microsoft EA and switching has no financial upside
Your team depends on SSMS, SQL Profiler, SQL Server Agent, or SSIS for operational workflows
You are integrating with SSRS, SSIS, or other SQL Server ecosystem components
Your team has deep T-SQL expertise and limited bandwidth to ramp on PostgreSQL dialect differences

Choose PostgreSQL when:

You want to eliminate per-core licence costs, particularly across many environments
Your domain benefits from native jsonb, arrays, range types, or extensions like PostGIS or pgvector
You are deploying to Linux containers, AWS RDS, or Azure Database for PostgreSQL Flexible Server
SQL portability and ANSI standards compliance matter to your architecture
You need full-text search, geospatial queries, or vector similarity without additional licences

You can also run both in the same solution. EF Core's per-context provider model makes it straightforward to keep an existing SQL Server context alongside a new PostgreSQL context for a service that needs JSONB or cost efficiency.

// Running two providers side by side in the same .NET 8 application
builder.Services.AddDbContext<LegacyDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("Legacy")));

builder.Services.AddDbContext<CatalogDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("Catalog")));

5 Quick reference summary

Concern	SQL Server	PostgreSQL
Licence	Commercial (free Developer edition, not for prod)	Open-source, free in all environments
EF Core NuGet	Microsoft.EntityFrameworkCore.SqlServer	Npgsql.EntityFrameworkCore.PostgreSQL
Native JSON storage	nvarchar(max) with scalar functions	jsonb with binary indexing
UUID type	uniqueidentifier (non-standard byte order)	uuid (RFC 4122, native)
Case sensitivity	Case-insensitive by default (collation-dependent)	Case-sensitive by default
Full-text search	Separate FT Catalog and Index, CONTAINS/FREETEXT	Built-in via tsvector and tsquery
Arrays	Not supported natively	First-class typed array columns
Extensions	Limited; additional features require licences	PostGIS, pgvector, TimescaleDB, pg_trgm, and more
Primary GUI	SQL Server Management Studio	pgAdmin 4, DBeaver, DataGrip
Docker image	mcr.microsoft.com/mssql/server	postgres (official, minimal, fast to pull)

Final take

If you are starting a new project today and do not have SQL Server licences already in place, PostgreSQL is the stronger default for .NET work in 2024. The Npgsql EF Core provider is excellent, the type system is richer, cloud-managed costs are lower, and the extension ecosystem opens doors that would require separate products under SQL Server.

If your team lives in SSMS, your SQL Server licences are paid for, and your domain has no need for JSONB or PostgreSQL-specific extensions, there is no compelling reason to switch for its own sake. The migration effort is real, and "it's free" is not a good enough reason on its own. Use PostgreSQL when the specific features or the economics give you a clear return on the investment.

What's next?

Spin up PostgreSQL locally: Run docker run --name pg-dev -e POSTGRES_PASSWORD=dev -p 5432:5432 -d postgres:16 and point your first EF Core migration at it
Explore JSONB: The next post in this series covers JSONB columns in EF Core 8 with query examples, GIN indexing, and performance considerations
Audit your raw SQL: Before migrating an existing project, grep your codebase for FromSqlRaw and Dapper queries and flag any T-SQL-specific syntax for rewriting

Got a question or a scenario I did not cover? Drop a comment below and I will reply.

Boxing and Unboxing in C#: The Silent Performance Tax You Might Be Paying

2026-04-29T11:24:47.964+05:45

Boxing and unboxing are two of the most common sources of silent, hard-to-diagnose performance regressions in .NET applications. They do not throw exceptions, they do not show up in compiler warnings, and they will not break your unit tests. They simply make your application slower, one heap allocation at a time.

By the end of this post you will understand exactly what boxing and unboxing are, why they carry a real cost, which everyday code patterns trigger them without you realising it, and how modern C# gives you the tools to avoid them entirely on hot paths.

1 The foundation: value types vs. reference types

Boxing only makes sense once you have a clear mental model of where the runtime stores data. The CLR uses two memory regions with very different characteristics.

The stack

Think of the stack like your desk: fast, tidy, and everything is right in front of you. Value types live here. The variable is the data, stored at a fixed, compile-time-known size. When a method returns, the stack frame is unwound and those values disappear instantly. No garbage collector involved.

Common value types: int, long, double, bool, char, decimal, DateTime, Guid, TimeSpan, and any struct you define yourself.

The heap

The heap is more like a filing cabinet: more room, but you need a label (a reference, or pointer) to find your file. Reference types live here. The variable holds a managed reference to data allocated on the heap, and the garbage collector is responsible for reclaiming that memory when nothing holds a reference to it anymore.

Common reference types: string, object, every class you define, arrays (even int[]), List<T>, Task, and Delegate.

// Value types: stored directly on the stack
int    count   = 42;
double price   = 19.99;
bool   isReady = true;

// Copying a value type produces independent copies
int a = 10;
int b = a;
b = 99;
Console.WriteLine(a); // 10 — a is unchanged

// Reference types: stack holds a pointer; data lives on the heap
string    name    = "Alice";
List<int> numbers = new() { 1, 2, 3 };

// Copying a reference type copies the pointer, not the data
var list1 = new List<int> { 1, 2, 3 };
var list2 = list1;   // same heap object!
list2.Add(4);
Console.WriteLine(list1.Count); // 4 — both variables see the same list

This distinction is the prerequisite for everything that follows. Boxing is what happens when the runtime is forced to treat a value type as a reference type.

2 Boxing: wrapping a value in an object

Boxing is the implicit conversion of a value type to object (or to an interface the value type implements). When it happens, the runtime allocates a new object on the heap, copies the value into it, and returns a reference to that object. The original stack value and the new heap object are completely independent from that point on.

The key word is implicit. The compiler inserts the boxing instruction for you with no visible cast syntax, which is exactly what makes accidental boxing so easy to miss.

// Implicit boxing: no cast keyword, but a heap allocation happens here
int    num = 42;
object o   = num;  // 1. allocates object on heap
                   // 2. copies 42 into it
                   // 3. o holds a reference to the new object

// Boxing to an interface also allocates
IComparable comp = num; // boxes — IComparable is a reference type

// The two copies are now independent
int x     = 10;
object bx = x;
x = 99;
Console.WriteLine(bx); // still 10 — bx points to a separate heap object

// Legacy collections box every element they store
ArrayList old = new ArrayList();
old.Add(1);   // boxes 1   → new heap object
old.Add(2);   // boxes 2   → new heap object
old.Add(3);   // boxes 3   → new heap object (3 allocations for 3 ints)

Performance note: Every boxing operation is a heap allocation. In a tight loop that runs a million times, that is a million objects queued for garbage collection. The GC will eventually collect them, but the collection pauses and the memory pressure accumulate silently and degrade throughput in ways that are very difficult to attribute to a root cause after the fact.

3 Unboxing: extracting the value back out

Unboxing is the reverse operation: extracting a value type from a boxed object. Unlike boxing, unboxing is never implicit. You must write an explicit cast. The runtime validates the cast at execution time, not at compile time, which means a wrong cast produces an InvalidCastException at runtime rather than a build error.

// Step 1: box an int
int    original = 42;
object boxed    = original;  // boxing

// Step 2: unbox with the exact original type
int extracted = (int)boxed; // correct
Console.WriteLine(extracted); // 42

// Step 3: wrong cast type throws at runtime, not at compile time
object o = 42; // was boxed as int
try
{
    double wrong = (double)o; // InvalidCastException!
}
catch (InvalidCastException ex)
{
    Console.WriteLine(ex.Message);
}

// Step 4: safe unboxing with pattern matching (modern C#, preferred)
object result = GetFromLegacyApi(); // returns object, type unknown

if (result is int value)
{
    Console.WriteLine($"Got int: {value}"); // safe and concise
}

// 'as' with Nullable<T> — also safe, returns null on mismatch
int? safe = result as int?;
if (safe.HasValue)
    Console.WriteLine(safe.Value);

Critical trap: The unboxing cast must match the exact type used during boxing, not a compatible one. A value boxed as int cannot be unboxed as long, even though an int normally widens to long without issue. The runtime checks the type metadata stamped on the heap object, not numeric compatibility. Prefer is int value pattern matching over a bare cast whenever the type is not guaranteed.

4 Pitfalls: where boxing hides in everyday code

The four patterns below are the most common sources of unintentional boxing in production .NET code. Each one compiles cleanly and runs correctly; the cost is purely in allocation count and GC pressure.

1. Legacy non-generic collections

// Every Add call boxes the integer — avoid
var bad = new ArrayList();
bad.Add(1);
bad.Add(2);
bad.Add(3);

// No boxing; values stored inline in the backing array
var good = new List<int> { 1, 2, 3 };

2. String.Format with value-type arguments

int age = 30;

// string.Format accepts object[] — every value-type argument is boxed
var s1 = string.Format("Age: {0}", age); // boxes age

// String interpolation compiles to FormattableString/string.Create paths
// that avoid boxing for known types
var s2 = $"Age: {age}"; // no boxing

3. Boxing inside a hot loop

// 1,000,000 heap allocations — do not do this on a hot path
for (int i = 0; i < 1_000_000; i++)
{
    object o = i;    // boxes every iteration
    Process(o);
}

// Use a generic method instead — zero allocation
for (int i = 0; i < 1_000_000; i++)
{
    Process(i);      // T is inferred as int; no boxing
}

static void Process<T>(T value) => Console.WriteLine(value);

4. Casting a struct to an interface it implements

struct Temperature : IComparable<Temperature>
{
    public double Celsius { get; init; }
    public int CompareTo(Temperature other) => Celsius.CompareTo(other.Celsius);
}

// Storing as the interface boxes the struct
IComparable<Temperature> t1 = new Temperature { Celsius = 22.5 }; // boxed!

// Keep the concrete type; use the interface only in generic constraints
Temperature t2 = new Temperature { Celsius = 22.5 };               // no boxing

5 Writing boxing-aware code every day

The good news is that modern C# makes the boxing-free path the natural one. These five habits cover the vast majority of cases.

Always use generic collections

// Reach for these in all new code
List<int>                ids;
Dictionary<string, int>  scores;
HashSet<Guid>            seen;
Queue<DateTime>          events;
Stack<decimal>           prices;

// ArrayList and Hashtable exist only for pre-2.0 interop; do not use them in new code

Write generic methods instead of object parameters

// Forces boxing for any value-type argument
void Log(object value) => Console.WriteLine(value);

// No boxing; the JIT specialises the method per type
void Log<T>(T value) => Console.WriteLine(value?.ToString());

// With a numeric constraint — .NET 7+
T Square<T>(T x) where T : INumber<T> => x * x;

Use Span<T> for high-performance slicing

int[] data = { 1, 2, 3, 4, 5 };

// AsSpan returns a stack-only view — no allocation, no boxing
Span<int> slice = data.AsSpan(1, 3);
foreach (var item in slice)
    Console.WriteLine(item); // 2, 3, 4

Prefer pattern matching for safe unboxing of unknown types

// Common in plugin systems, legacy APIs, or deserialization scenarios
object result = plugin.Execute();

switch (result)
{
    case int n:
        ProcessNumber(n);
        break;
    case string s:
        ProcessText(s);
        break;
    case null:
        HandleEmpty();
        break;
    default:
        throw new NotSupportedException($"Unexpected type: {result.GetType().Name}");
}

Know when boxing is legitimate

Not every boxing site is a problem. These scenarios are genuinely fine:

// COM interop: the API requires object
xlRange.Value2 = (object)42;

// Reflection: GetValue always returns object
var val = fieldInfo.GetValue(instance);

// Heterogeneous data (rare, but sometimes the right model)
object[] mixed = { 42, "hello", true, 3.14 };

// Low-frequency startup configuration: the GC cost is negligible
settings["timeout"] = (object)30;

Tooling tip: JetBrains Rider and the Heap Allocation Viewer extension for Visual Studio surface boxing sites as inline hints. BenchmarkDotNet with [MemoryDiagnoser] will give you exact allocation counts per benchmark iteration, which makes it easy to confirm a fix actually eliminated the allocation.

Wrapping up

Boxing converts a value type to a heap-allocated object implicitly and silently; unboxing reverses that with an explicit cast that can fail at runtime if the type does not match exactly. Neither operation announces itself in compiler output, which is what makes them worth understanding at a conceptual level rather than just memorising a list of rules.

The practical takeaway is straightforward: use generic collections, write generic methods, lean on pattern matching for unknown types, and reach for Span<T> on hot paths. With those habits in place, boxing becomes something you deliberately opt into for COM interop or reflection, rather than something that accumulates quietly across your codebase. A follow-up post on readonly struct and ref struct is a natural next step for anyone who wants to push further into allocation-free patterns.

Got a question or ran into a problem? Drop a comment below and I will reply.

Versioning Your ASP.NET Core API Without Breaking Existing Clients

2026-04-24T13:01:04.933+05:45

Sooner or later every API that has real consumers needs to change in ways that are not backward-compatible. You rename a field, remove a property, change a response shape, or restructure a route. Without versioning, any of those changes breaks existing clients immediately. With versioning, you introduce the new contract alongside the old one, communicate a deprecation timeline, and let clients migrate at a pace that works for them.

By the end of this post you will have a .NET 8 Web API that supports URL segment versioning, query string versioning, and custom header versioning simultaneously using the official Asp.Versioning.Http library. You will also see how to mark versions as deprecated so API consumers get a clear signal that they need to migrate.

1 Prerequisites

Before starting, make sure you have:

.NET 8 SDK (8.0.100 or later)
An existing ASP.NET Core Web API project targeting .NET 8
NuGet package: Asp.Versioning.Mvc (for controller-based APIs) or Asp.Versioning.Http (for Minimal APIs)

Note: The Asp.Versioning.* packages are the official successor to the archived Microsoft.AspNetCore.Mvc.Versioning packages. If your project still references the old packages, plan a migration; they are no longer maintained and do not support .NET 7 or .NET 8 correctly.

2 Install and configure versioning

Add the package to your API project and register the versioning services in Program.cs.

// From the terminal — add the NuGet package
// dotnet add package Asp.Versioning.Mvc
// dotnet add package Asp.Versioning.Mvc.ApiExplorer   // for Swagger integration

// Program.cs — versioning service registration
var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();

builder.Services.AddApiVersioning(options =>
{
    options.DefaultApiVersion          = new ApiVersion(1, 0);
    options.AssumeDefaultVersionWhenUnspecified = true;
    options.ReportApiVersions          = true;             // adds api-supported-versions header
    options.ApiVersionReader           = ApiVersionReader.Combine(
        new UrlSegmentApiVersionReader(),
        new HeaderApiVersionReader("X-Api-Version"),
        new QueryStringApiVersionReader("api-version")
    );
})
.AddApiExplorer(options =>
{
    options.GroupNameFormat           = "'v'VVV";
    options.SubstituteApiVersionInUrl = true;
});

var app = builder.Build();
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();

The ApiVersionReader.Combine call means ASP.NET Core will accept the version from a URL segment, a custom header, or a query string. If more than one is present, the URL segment takes precedence. ReportApiVersions adds api-supported-versions and api-deprecated-versions response headers so clients can discover what is available without reading documentation.

3 Version your controllers with URL segments

URL segment versioning is the most explicit strategy and the one most API consumers expect to see: /api/v1/products, /api/v2/products. Each major version lives in its own controller class inside a versioned namespace.

// Controllers/V1/ProductsController.cs
namespace Catalog.Api.Controllers.V1;

[ApiController]
[ApiVersion("1.0")]
[Route("api/v{version:apiVersion}/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly IProductService _service;

    public ProductsController(IProductService service)
        => _service = service;

    [HttpGet("{id:int}")]
    [ProducesResponseType(typeof(ProductResponseV1), StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<ActionResult<ProductResponseV1>> GetById(
        int id, CancellationToken ct)
    {
        var product = await _service.GetByIdAsync(id, ct);
        return product is null ? NotFound() : Ok(ProductResponseV1.From(product));
    }
}

// Controllers/V2/ProductsController.cs
namespace Catalog.Api.Controllers.V2;

[ApiController]
[ApiVersion("2.0")]
[Route("api/v{version:apiVersion}/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly IProductService _service;

    public ProductsController(IProductService service)
        => _service = service;

    [HttpGet("{id:int}")]
    [ProducesResponseType(typeof(ProductResponseV2), StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<ActionResult<ProductResponseV2>> GetById(
        int id, CancellationToken ct)
    {
        var product = await _service.GetByIdAsync(id, ct);
        return product is null ? NotFound() : Ok(ProductResponseV2.From(product));
    }
}

Separating each version into its own namespace keeps the folder structure self-documenting. A developer navigating to Controllers/V2/ immediately knows they are looking at the current contract.

Tip: Create separate response DTO types per version rather than adding optional fields to a single type. Separate DTOs make it obvious what changed, keep serialization clean, and avoid nullable sprawl in your shared models.

4 Support header and query string versioning on the same endpoints

Because ApiVersionReader.Combine is already registered, no additional controller changes are required. A client can reach the same v2 endpoint three ways.

// Three equivalent requests for the v2 endpoint:

// 1. URL segment (most explicit — preferred)
// GET /api/v2/products/42

// 2. Query string (useful for quick testing and exploration)
// GET /api/products/42?api-version=2.0

// 3. Custom header (common in B2B integrations where the URL must not change)
// GET /api/products/42
// X-Api-Version: 2.0

When none of the three is supplied and AssumeDefaultVersionWhenUnspecified is true, ASP.NET Core uses the default version defined in DefaultApiVersion, which is 1.0 in this configuration. Clients that have never heard of versioning continue to work against the v1 contract without modification.

5 Deprecate old versions gracefully

Marking a version as deprecated does not remove it. It signals to consumers through response headers that the version has an end-of-life date and they should plan to migrate. The endpoint continues to function normally.

// Controllers/V1/ProductsController.cs — mark version 1.0 as deprecated
[ApiController]
[ApiVersion("1.0", Deprecated = true)]
[Route("api/v{version:apiVersion}/[controller]")]
public class ProductsController : ControllerBase
{
    // ... existing v1 actions unchanged
}

With Deprecated = true and ReportApiVersions = true, every response to a v1 endpoint now includes both headers, giving clients a clear machine-readable signal.

// Response headers returned on any v1 endpoint call:
// api-supported-versions: 2.0
// api-deprecated-versions: 1.0

Complement the headers with a Sunset header indicating the retirement date. You can inject this via middleware or by overriding the response in a filter.

// Api/Filters/SunsetHeaderFilter.cs
public class SunsetHeaderFilter : IActionFilter
{
    private static readonly DateTimeOffset V1Sunset =
        new DateTimeOffset(2025, 12, 31, 0, 0, 0, TimeSpan.Zero);

    public void OnActionExecuting(ActionExecutingContext context) { }

    public void OnActionExecuted(ActionExecutedContext context)
    {
        var requestedVersion = context.HttpContext
            .GetRequestedApiVersion()?.ToString();

        if (requestedVersion == "1.0")
        {
            context.HttpContext.Response.Headers["Sunset"] =
                V1Sunset.ToString("R");   // RFC 1123 date format
        }
    }
}

// Program.cs — register as a global filter
builder.Services.AddControllers(options =>
    options.Filters.Add<SunsetHeaderFilter>());

6 Running and testing it

Run the project and confirm that the correct controller action is invoked based on the version provided.

// dotnet run --project Catalog.Api

// URL segment — should return v2 response shape
// GET https://localhost:7001/api/v2/products/1

// Query string — should return v1 response shape with deprecation headers
// GET https://localhost:7001/api/products/1?api-version=1.0

// No version — should return v1 response (default) with deprecation headers
// GET https://localhost:7001/api/products/1

// Unsupported version — should return 400 with Problem Details
// GET https://localhost:7001/api/v99/products/1

When a client requests a version that no controller supports, ASP.NET Core returns a 400 Bad Request with a Problem Details body automatically. No additional error handling code is needed.

// Integration test verifying version routing
[Theory]
[InlineData("/api/v1/products/1", "1.0")]
[InlineData("/api/v2/products/1", "2.0")]
public async Task GetById_RoutesToCorrectVersion(string url, string expectedVersion)
{
    await using var factory = new WebApplicationFactory<Program>();
    var client = factory.CreateClient();

    var response = await client.GetAsync(url);

    Assert.True(response.IsSuccessStatusCode);

    // Confirm the supported version header is present and correct
    Assert.True(response.Headers.Contains("api-supported-versions"));
}

Wrapping up

A versioned .NET 8 Web API needs four things: the Asp.Versioning.Mvc package, a versioning configuration that accepts the version from URL segments, headers, and query strings, controller classes organized by version namespace, and the Deprecated flag on any version that has an end-of-life date. Together these give you a versioning strategy that is transparent to consumers, easy to extend, and capable of signaling retirement without breaking anything.

This post completes the ASP.NET Core Controller API Patterns series. The patterns covered across these posts, from routing model selection through project structure, validation, error handling, and versioning, form a baseline that scales from a small internal API to a multi-team public contract.

Got a question or ran into a problem? Drop a comment below and I will reply.

Error Handling and Problem Details in ASP.NET Core Controller APIs

2026-04-24T12:53:28.939+05:45

Returning a raw 500 with an exception stack trace is not an error handling strategy; it is an absence of one. API consumers need consistent, machine-readable error responses so they can distinguish a "not found" from a "validation failure" from a genuine server fault and react accordingly. ASP.NET Core has supported the Problem Details standard since .NET 5, and in .NET 7 and .NET 8 the built-in support became comprehensive enough that you no longer need a third-party library to get it right.

By the end of this post you will have a working error handling pipeline that maps domain exceptions to Problem Details responses, produces consistent JSON for validation errors and unhandled exceptions, and never leaks internal stack traces to API clients.

1 Prerequisites

Before starting, make sure you have:

.NET 8 SDK (8.0.100 or later)
An existing ASP.NET Core Web API project targeting .NET 8
Basic familiarity with middleware and the ASP.NET Core request pipeline

Note: The AddProblemDetails and UseExceptionHandler APIs described here are available from .NET 7 onward. On .NET 6 you will need a slightly different setup; the middleware exists but the unified IProblemDetailsService abstraction does not.

2 What Problem Details actually is

Problem Details is a machine-readable format for HTTP error responses defined in RFC 9457 (which supersedes the earlier RFC 7807). An ASP.NET Core controller decorated with [ApiController] already returns validation errors in this format automatically. The goal of this post is to extend that same format to all other error conditions: domain exceptions, authorization failures, and unhandled exceptions.

A minimal Problem Details response looks like this:

// Typical 404 Problem Details JSON response
// {
//   "type":   "https://tools.ietf.org/html/rfc9110#section-15.5.5",
//   "title":  "Not Found",
//   "status": 404,
//   "detail": "Product with id 42 was not found.",
//   "traceId": "00-a1b2c3d4e5f6a7b8-c9d0e1f2a3b4c5d6-00"
// }

The type field is a URI that identifies the problem type. The title is a short, human-readable label. The detail is specific to this occurrence. The traceId field, added automatically by ASP.NET Core, lets you correlate the response to a specific request in your logging system. You can also add custom extension members alongside these standard fields.

3 Register the built-in Problem Details service

Add AddProblemDetails and configure UseExceptionHandler to point at the built-in Problem Details pipeline. This is the minimum setup that gives you consistent JSON errors for unhandled exceptions.

// Program.cs
var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.AddProblemDetails();                    // register IProblemDetailsService

var app = builder.Build();

app.UseExceptionHandler();                               // use built-in exception handler
app.UseStatusCodePages();                                // convert 4xx with no body to Problem Details

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();

With just these three lines, any unhandled exception that escapes a controller action is caught by UseExceptionHandler and converted to a Problem Details response with a 500 status code. In development the response includes the exception detail and stack trace; in production it is suppressed automatically.

Important: Call app.UseExceptionHandler() before any other middleware that produces responses. If you call it after app.MapControllers(), it will never intercept exceptions from controller actions.

4 Define domain exceptions and map them to status codes

The built-in handler covers unhandled exceptions with a 500. What it does not do by default is map your domain-specific exceptions, such as a NotFoundException, to the correct HTTP status code. The cleanest way to handle this is a custom IExceptionHandler registered before the built-in fallback.

// Exceptions/NotFoundException.cs
public class NotFoundException : Exception
{
    public NotFoundException(string resourceName, object key)
        : base($"{resourceName} with id {key} was not found.")
    {
        ResourceName = resourceName;
        Key = key;
    }

    public string ResourceName { get; }
    public object Key { get; }
}

// Exceptions/ConflictException.cs
public class ConflictException : Exception
{
    public ConflictException(string message) : base(message) { }
}

// Exceptions/ValidationException.cs — distinct from FluentValidation's own type
public class DomainValidationException : Exception
{
    public DomainValidationException(string message) : base(message) { }
}

// Infrastructure/ExceptionHandlers/DomainExceptionHandler.cs
public class DomainExceptionHandler : IExceptionHandler
{
    private readonly IProblemDetailsService _problemDetails;

    public DomainExceptionHandler(IProblemDetailsService problemDetails)
        => _problemDetails = problemDetails;

    public async ValueTask<bool> TryHandleAsync(
        HttpContext httpContext,
        Exception exception,
        CancellationToken ct)
    {
        (int statusCode, string title) = exception switch
        {
            NotFoundException nfe     => (StatusCodes.Status404NotFound,     "Resource not found"),
            ConflictException         => (StatusCodes.Status409Conflict,     "Conflict"),
            DomainValidationException => (StatusCodes.Status422UnprocessableEntity, "Validation error"),
            _                         => (0, string.Empty)   // not handled here
        };

        if (statusCode == 0)
            return false;  // let the next handler take it

        httpContext.Response.StatusCode = statusCode;

        return await _problemDetails.TryWriteAsync(new ProblemDetailsContext
        {
            HttpContext = httpContext,
            Exception   = exception,
            ProblemDetails = new ProblemDetails
            {
                Status = statusCode,
                Title  = title,
                Detail = exception.Message
            }
        });
    }
}

// Program.cs — register before AddProblemDetails fallback
builder.Services.AddExceptionHandler<DomainExceptionHandler>();
builder.Services.AddProblemDetails();

ASP.NET Core calls IExceptionHandler implementations in registration order. If DomainExceptionHandler returns false, the next handler in the chain gets the exception. The built-in fallback added by UseExceptionHandler() is always last, so unrecognized exceptions always get a 500 response.

5 Customize the Problem Details output

If you need to add extension fields to every Problem Details response, such as a correlation ID, an error code, or an environment name, configure the AddProblemDetails options delegate.

// Program.cs — custom Problem Details fields
builder.Services.AddProblemDetails(options =>
{
    options.CustomizeProblemDetails = ctx =>
    {
        ctx.ProblemDetails.Extensions["traceId"] =
            Activity.Current?.Id ?? ctx.HttpContext.TraceIdentifier;

        ctx.ProblemDetails.Extensions["requestId"] =
            ctx.HttpContext.TraceIdentifier;

        if (ctx.Exception is NotFoundException nfe)
        {
            ctx.ProblemDetails.Extensions["resource"] = nfe.ResourceName;
            ctx.ProblemDetails.Extensions["key"]      = nfe.Key;
        }
    };
});

The CustomizeProblemDetails delegate runs for every Problem Details response, including automatic validation error responses from [ApiController]. This is the single place to add cross-cutting fields without modifying any handler.

6 Running and testing the error responses

Call an endpoint that throws a NotFoundException and inspect the response body to confirm the Problem Details format is correct.

// Controllers/ProductsController.cs — throw domain exception
[HttpGet("{id:int}")]
public async Task<ActionResult<ProductResponse>> GetById(int id, CancellationToken ct)
{
    var product = await _productService.GetByIdAsync(id, ct);

    if (product is null)
        throw new NotFoundException(nameof(Product), id);

    return Ok(product);
}

// Expected JSON response for GET /api/products/999 (non-existent)
// HTTP 404
// {
//   "type":      "https://tools.ietf.org/html/rfc9110#section-15.5.5",
//   "title":     "Resource not found",
//   "status":    404,
//   "detail":    "Product with id 999 was not found.",
//   "traceId":   "00-a1b2c3d4e5f6a7b8-c9d0e1f2a3b4c5d6-00",
//   "resource":  "Product",
//   "key":       999
// }

Write an integration test against WebApplicationFactory<T> to ensure the error shape does not regress as the codebase evolves.

// Tests/ErrorHandlingIntegrationTests.cs
[Fact]
public async Task GetById_WithNonExistentId_ReturnsProblemDetails404()
{
    await using var factory = new WebApplicationFactory<Program>();
    var client = factory.CreateClient();

    var response = await client.GetAsync("/api/products/999999");

    Assert.Equal(HttpStatusCode.NotFound, response.StatusCode);

    var body = await response.Content.ReadFromJsonAsync<ProblemDetails>();
    Assert.NotNull(body);
    Assert.Equal(404, body.Status);
    Assert.Equal("Resource not found", body.Title);
}

Wrapping up

A complete error handling pipeline in .NET 8 needs three things: AddProblemDetails to register the service, UseExceptionHandler to catch unhandled exceptions, and one or more IExceptionHandler implementations to map domain exceptions to the right HTTP status codes. The CustomizeProblemDetails delegate handles any cross-cutting fields you need in every response.

The next post in this series covers API versioning: how to support multiple versions of your API simultaneously without breaking existing clients and without duplicating controller code.

Got a question or ran into a problem? Drop a comment below and I will reply.

Validation in ASP.NET Core: FluentValidation vs. Data Annotations

2026-04-24T12:47:23.856+05:45

Validation is one of the first real decisions you make when building an ASP.NET Core API, and the answer is almost always one of two options: Data Annotations or FluentValidation. Both plug into the ASP.NET Core model binding pipeline, both return 400 responses with structured error details when validation fails, and both have years of production use behind them. What differs is where the rules live, how complex they can get, and how easily you can test them in isolation.

Data Annotations are attributes placed directly on your request DTO properties. FluentValidation is a separate library where validation rules live in dedicated validator classes. This post compares both approaches using the same request model, walks through five tradeoffs, and gives you a clear framework for choosing the right tool for your project.

By the end you will have working code for both approaches, an understanding of where each breaks down, and a decision you can commit to before you write your first endpoint.

1 A quick history first

Data Annotations ship with the .NET base class library under System.ComponentModel.DataAnnotations. They predate ASP.NET Core by over a decade, originating in WPF and WCF validation scenarios. In ASP.NET Core, the [ApiController] attribute triggers automatic model state validation before the action method is called, so invalid requests are rejected without any filter or explicit check in the action body.

FluentValidation is a popular open-source library by Jeremy Skinner, available on NuGet as FluentValidation.AspNetCore. It integrates with ASP.NET Core's model validation pipeline through an adapter, so it participates in the same automatic validation behavior that Data Annotations use. Rules are expressed in C# via a fluent builder API inside a dedicated validator class, keeping the DTO free of attributes.

Both libraries ultimately populate ModelState and produce the same 400 response shape when integrated correctly. The difference is entirely in developer ergonomics and long-term maintainability.

2 The same task, two ways

Consider a CreateProductRequest that requires a non-empty name, a positive price, a stock count between zero and ten thousand, and an optional description capped at 500 characters.

Data Annotations approach

// Models/Requests/CreateProductRequest.cs
public class CreateProductRequest
{
    [Required(ErrorMessage = "Name is required.")]
    [MaxLength(200, ErrorMessage = "Name cannot exceed 200 characters.")]
    public string Name { get; set; } = string.Empty;

    [Range(0.01, 999999.99, ErrorMessage = "Price must be between 0.01 and 999,999.99.")]
    public decimal Price { get; set; }

    [Range(0, 10000, ErrorMessage = "Stock must be between 0 and 10,000.")]
    public int Stock { get; set; }

    [MaxLength(500, ErrorMessage = "Description cannot exceed 500 characters.")]
    public string? Description { get; set; }
}

FluentValidation approach

// Models/Requests/CreateProductRequest.cs — no attributes
public class CreateProductRequest
{
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int Stock { get; set; }
    public string? Description { get; set; }
}

// Validators/CreateProductRequestValidator.cs
public class CreateProductRequestValidator
    : AbstractValidator<CreateProductRequest>
{
    public CreateProductRequestValidator()
    {
        RuleFor(x => x.Name)
            .NotEmpty().WithMessage("Name is required.")
            .MaximumLength(200).WithMessage("Name cannot exceed 200 characters.");

        RuleFor(x => x.Price)
            .InclusiveBetween(0.01m, 999_999.99m)
            .WithMessage("Price must be between 0.01 and 999,999.99.");

        RuleFor(x => x.Stock)
            .InclusiveBetween(0, 10_000)
            .WithMessage("Stock must be between 0 and 10,000.");

        RuleFor(x => x.Description)
            .MaximumLength(500)
            .WithMessage("Description cannot exceed 500 characters.")
            .When(x => x.Description is not null);
    }
}

// Program.cs — FluentValidation registration
builder.Services.AddControllers();
builder.Services.AddValidatorsFromAssemblyContaining<CreateProductRequestValidator>();

// If you want FluentValidation to replace Data Annotations in ModelState:
builder.Services.AddFluentValidationAutoValidation()
                .AddFluentValidationClientsideAdapters();

Both approaches cause ASP.NET Core to return a 400 with a structured error body when the incoming request fails validation. The controller action is never reached for invalid input.

3 The real tradeoffs

1. Separation of concerns

Data Annotations embed validation rules directly on the model class as attributes. This is convenient for simple cases but couples the model's shape to its validation policy. If the same DTO is used in different contexts where different rules should apply (create vs. update, admin vs. regular user), attributes cannot express that distinction without branching into custom attribute implementations.

FluentValidation keeps each set of rules in its own class. You can have CreateProductRequestValidator and AdminCreateProductRequestValidator with different rule sets for the same DTO, registered conditionally. The model stays a plain data carrier.

2. Complex and conditional validation

This is where Data Annotations reach their practical ceiling. Rules that depend on the values of multiple properties, on data retrieved from a database, or on conditional logic require custom attribute classes that become non-trivial to write and test.

// FluentValidation handles multi-property and async rules cleanly
public class CreateOrderRequestValidator : AbstractValidator<CreateOrderRequest>
{
    private readonly IProductRepository _products;

    public CreateOrderRequestValidator(IProductRepository products)
        => _products = products;

    public CreateOrderRequestValidator()
    {
        RuleFor(x => x.DeliveryDate)
            .GreaterThan(x => x.OrderDate)
            .WithMessage("Delivery date must be after the order date.");

        RuleFor(x => x.ProductId)
            .MustAsync(async (id, ct) => await _products.ExistsAsync(id, ct))
            .WithMessage("The specified product does not exist.");
    }
}

Data Annotations cannot express either of those rules without a custom attribute that takes constructor parameters, which quickly becomes awkward to maintain and impossible to inject services into cleanly.

3. Reusability and composition

FluentValidation supports reusing rule sets via SetValidator and the ChildRules pattern. If a dozen request models all share an address sub-object, one AddressValidator class can be composed into each parent validator. Data Annotations on nested objects require the [ValidateNever] and [ValidateAlways] markers plus explicit recursion to achieve the same effect.

// FluentValidation composition example
public class AddressValidator : AbstractValidator<AddressDto>
{
    public AddressValidator()
    {
        RuleFor(x => x.Street).NotEmpty().MaximumLength(200);
        RuleFor(x => x.PostCode).NotEmpty().Matches(@"^\d{4,10}$");
        RuleFor(x => x.CountryCode).NotEmpty().Length(2);
    }
}

public class CreateCustomerRequestValidator
    : AbstractValidator<CreateCustomerRequest>
{
    public CreateCustomerRequestValidator()
    {
        RuleFor(x => x.Email).NotEmpty().EmailAddress();
        RuleFor(x => x.ShippingAddress).SetValidator(new AddressValidator());
        RuleFor(x => x.BillingAddress).SetValidator(new AddressValidator());
    }
}

4. Error messages and localization

Data Annotations support ErrorMessage string properties and resource-file-based localization through ErrorMessageResourceType and ErrorMessageResourceName. It works, but the setup is verbose and the pattern is unfamiliar to developers who have not worked with .NET resource files before. FluentValidation has a built-in localization pipeline with language packs available as separate NuGet packages, and adding a custom message store is straightforward.

5. Testability

FluentValidation validators are plain classes with no framework dependency. You can instantiate a validator and call ValidateAsync on a test object in a regular xUnit test without any mocking, middleware, or HTTP context.

// Direct unit test of a FluentValidation validator
[Fact]
public async Task Price_BelowMinimum_FailsValidation()
{
    var validator = new CreateProductRequestValidator();
    var request = new CreateProductRequest { Name = "Widget", Price = -1m, Stock = 10 };

    var result = await validator.ValidateAsync(request);

    Assert.False(result.IsValid);
    Assert.Contains(result.Errors,
        e => e.PropertyName == nameof(CreateProductRequest.Price));
}

[Fact]
public async Task ValidRequest_PassesValidation()
{
    var validator = new CreateProductRequestValidator();
    var request = new CreateProductRequest
    {
        Name = "Widget",
        Price = 9.99m,
        Stock = 100
    };

    var result = await validator.ValidateAsync(request);
    Assert.True(result.IsValid);
}

Testing Data Annotations rules in unit tests requires calling Validator.TryValidateObject from System.ComponentModel.DataAnnotations, which is workable but less clean. More commonly, Data Annotations are tested indirectly through integration tests that exercise the full HTTP pipeline.

4 Which one should you choose?

The right choice depends on the complexity of your validation logic and your team's appetite for an additional dependency.

Choose Data Annotations when:

Your validation rules are simple: required fields, length limits, range checks, and regex patterns
You want zero additional NuGet dependencies and are comfortable keeping rules on the model
The project is small or a proof of concept that does not need complex conditional rules
Your team is new to .NET and you want to minimize the number of patterns they need to learn at once

Choose FluentValidation when:

Any validation rule depends on values from multiple properties or on external data (database lookups, HTTP calls)
The same DTO needs different rule sets in different contexts (create vs. update, admin vs. standard user)
You want to unit-test validators in isolation without spinning up an HTTP pipeline
You are building a multi-team or enterprise API where consistency and composability of validation logic matter
You have nested or shared sub-object validation that needs composable validators

A common middle-ground approach is to use Data Annotations for simple structural constraints (non-null, length limits) and FluentValidation for business rule validation (cross-field checks, database existence checks). Both validators run in sequence, and ModelState accumulates all errors from both.

5 Quick reference summary

Concern	Data Annotations	FluentValidation
Location of rules	On the model class as attributes	Separate validator class
Simple rules	Excellent; terse and familiar	Good; more verbose for trivial rules
Multi-property rules	Requires custom attributes	First-class; built-in cross-property support
Async validation	Not supported	First-class via `MustAsync`
Composability	Limited; nested object recursion only	Strong; `SetValidator` and child rules
Unit testability	Requires `Validator.TryValidateObject`	Instantiate and call directly
External dependencies	None; ships with .NET	FluentValidation NuGet package required
Localization	Resource file-based; verbose setup	Built-in pipeline; language packs available

Final take

For any API that will run in production with real business logic, FluentValidation is the stronger default. The separation of validation rules from model definitions pays for itself the first time you encounter a conditional rule or a cross-field dependency. Data Annotations remain the right choice for small projects and prototypes where adding a NuGet package and learning a new library is unnecessary overhead.

If your current project uses Data Annotations and you are hitting their limits, you can introduce FluentValidation incrementally: register it alongside existing annotations and migrate validators one DTO at a time.

What's next?

Error handling: The next post in this series covers how to use ASP.NET Core's Problem Details standard to return consistent, spec-compliant error responses across your entire API.
FluentValidation docs: The official FluentValidation documentation at docs.fluentvalidation.net covers every built-in rule, extension points, and async scenarios with clear examples.
Custom validators: Once you are comfortable with the basics, look into AbstractValidator constructor injection to build validators that query your database or call external services during validation.

Got a question or a scenario I did not cover? Drop a comment below and I will reply.

Structuring a .NET 8 Web API

2026-04-24T12:46:19.015+05:45

A .NET 8 Web API that starts as a tidy three-file project has a way of becoming a navigational puzzle by the time it reaches twenty endpoints and three developers. The folder structure you choose in week one silently shapes every refactor, every onboarding session, and every pull request review that follows. Getting it right early is far cheaper than untangling it later.

By the end of this post you will have a concrete, battle-tested folder layout, a layer model with clear responsibilities, and a set of naming conventions you can copy directly into your next project or use to evaluate your current one.

1 Prerequisites

Before starting, make sure you have:

.NET 8 SDK (8.0.100 or later)
Visual Studio 2022 17.8+, JetBrains Rider, or VS Code with the C# Dev Kit extension
Familiarity with dependency injection and the ASP.NET Core request pipeline

Note: The folder structure described here targets controller-based APIs. If your team has chosen Minimal APIs, the layer model and naming conventions still apply; only the Controllers folder becomes an Endpoints folder of static extension classes.

2 Start with a solution, not just a project

Even for a single API, wrapping it in a solution from day one pays dividends when you add a test project or an SDK library later. Create the solution structure with the CLI so the layout is deliberate rather than IDE-generated defaults.

// Run these in your terminal — not C# code, shown here for clarity
// dotnet new sln -n Catalog
// dotnet new webapi -n Catalog.Api --no-openapi
// dotnet new classlib -n Catalog.Core
// dotnet new classlib -n Catalog.Infrastructure
// dotnet new xunit -n Catalog.Tests
// dotnet sln add **/*.csproj

This gives you a four-project solution from the start. Catalog.Api owns HTTP concerns. Catalog.Core owns domain logic and interfaces. Catalog.Infrastructure owns data access and external integrations. Catalog.Tests tests all three. Project references flow inward: Api references Core and Infrastructure; Infrastructure references Core; Core references nothing in the solution.

Tip: Resist the urge to collapse everything into a single project to keep things simple. A single project forces you to enforce layer boundaries by convention alone; separate projects make accidental cross-layer dependencies a compile error rather than a code-review catch.

3 The folder layout inside each project

The structure below is organized by layer first and by feature second inside each layer. This is the layout that scales most predictably for teams of two to ten developers on a single API.

Catalog.Api

// Catalog.Api/
// ├── Controllers/
// │   ├── ProductsController.cs
// │   └── CategoriesController.cs
// ├── Filters/
// │   └── ValidationExceptionFilter.cs
// ├── Models/                        // Request and response DTOs for the HTTP layer
// │   ├── Requests/
// │   │   ├── CreateProductRequest.cs
// │   │   └── UpdateProductRequest.cs
// │   └── Responses/
// │       └── ProductResponse.cs
// ├── Extensions/
// │   └── ServiceCollectionExtensions.cs
// └── Program.cs

Catalog.Core

// Catalog.Core/
// ├── Domain/
// │   ├── Entities/
// │   │   └── Product.cs
// │   └── ValueObjects/
// │       └── Money.cs
// ├── Interfaces/
// │   ├── IProductRepository.cs
// │   └── IProductService.cs
// └── Services/
//     └── ProductService.cs

Catalog.Infrastructure

// Catalog.Infrastructure/
// ├── Persistence/
// │   ├── CatalogDbContext.cs
// │   ├── Configurations/
// │   │   └── ProductConfiguration.cs
// │   └── Repositories/
// │       └── ProductRepository.cs
// └── Extensions/
//     └── ServiceCollectionExtensions.cs

Keep the Extensions folder in each project; it is where IServiceCollection extension methods live. This prevents Program.cs from becoming a registration dump that grows to 200 lines.

4 Naming conventions that communicate intent

Consistent naming reduces the cognitive load of reading unfamiliar code. Apply these rules across the entire solution and enforce them in pull request reviews.

Controllers

Use the plural resource name followed by Controller: ProductsController, OrdersController, CategoriesController. Route templates use the conventional [controller] token so the route mirrors the class name automatically. Every controller action should return Task<IActionResult> or a typed ActionResult<T> rather than a bare type, so the framework can generate accurate OpenAPI response descriptions.

// Controllers/ProductsController.cs
[ApiController]
[Route("api/v{version:apiVersion}/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly IProductService _productService;

    public ProductsController(IProductService productService)
        => _productService = productService;

    [HttpGet("{id:int}")]
    [ProducesResponseType(typeof(ProductResponse), StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<ActionResult<ProductResponse>> GetById(
        int id, CancellationToken ct)
    {
        var product = await _productService.GetByIdAsync(id, ct);
        return product is null ? NotFound() : Ok(product);
    }
}

DTOs and request/response models

Name request bodies with the verb and the resource: CreateProductRequest, UpdateProductRequest. Name response models with the resource and the suffix Response: ProductResponse, ProductSummaryResponse. Never expose your domain entity directly as an API response type; the shape of your persistence model and the shape of your public API contract will diverge, and the day they need to diverge is not the day you want to write a refactor.

Interfaces and services

Prefix interfaces with I and name them after the capability: IProductService, IProductRepository. Implementations drop the prefix: ProductService, ProductRepository. Avoid descriptive suffixes like ProductServiceImpl or ConcreteProductRepository; they carry no information a developer cannot infer from the file location.

// Core/Interfaces/IProductService.cs
public interface IProductService
{
    Task<ProductResponse?> GetByIdAsync(int id, CancellationToken ct = default);
    Task<IReadOnlyList<ProductSummaryResponse>> GetAllAsync(CancellationToken ct = default);
    Task<int> CreateAsync(CreateProductRequest request, CancellationToken ct = default);
    Task UpdateAsync(int id, UpdateProductRequest request, CancellationToken ct = default);
    Task DeleteAsync(int id, CancellationToken ct = default);
}

5 Dependency registration as extension methods

Each project that registers services should expose a single AddX extension method on IServiceCollection. Program.cs then reads as a declaration of intent rather than an implementation listing.

// Infrastructure/Extensions/ServiceCollectionExtensions.cs
public static class InfrastructureServiceCollectionExtensions
{
    public static IServiceCollection AddInfrastructure(
        this IServiceCollection services,
        IConfiguration configuration)
    {
        services.AddDbContext<CatalogDbContext>(opts =>
            opts.UseSqlServer(configuration.GetConnectionString("Catalog")));

        services.AddScoped<IProductRepository, ProductRepository>();

        return services;
    }
}

// Api/Extensions/ServiceCollectionExtensions.cs
public static class ApiServiceCollectionExtensions
{
    public static IServiceCollection AddApplicationServices(
        this IServiceCollection services)
    {
        services.AddScoped<IProductService, ProductService>();
        return services;
    }
}

// Program.cs — lean and declarative
var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.AddApplicationServices();
builder.Services.AddInfrastructure(builder.Configuration);
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();

Notice that Program.cs does not know which concrete types are registered. Adding a new service or repository is a change inside one extension method, not a change to the entry point. This matters when merge conflicts and code ownership come into play across a team.

6 Running and verifying the structure

With the structure in place, run the project and open the Swagger UI to verify that your controllers, their routes, and their response types are all reflected correctly in the generated document.

// From the solution root
// dotnet build
// dotnet run --project Catalog.Api

// Expected output includes:
// info: Microsoft.Hosting.Lifetime[14]
//       Now listening on: https://localhost:7001
// Navigate to https://localhost:7001/swagger to see the generated API document

At this point your Swagger UI should list every controller action with its route, HTTP method, and declared response types. If a controller action is missing, check that MapControllers() is called in Program.cs and that the controller class is in the same assembly as the API project or is explicitly included via AddApplicationPart.

Wrapping up

A well-structured .NET 8 Web API separates concerns across projects, organizes code by layer inside each project, applies consistent naming conventions, and keeps Program.cs declarative through extension methods. None of these choices are exotic; their value is in being applied uniformly so that every developer on the team can navigate, extend, and test the codebase without a guide.

The next post in this series covers validation: specifically whether FluentValidation or Data Annotations is the right choice for your API, with working code for both.

Got a question or ran into a problem? Drop a comment below and I will reply.

Controller vs. Minimal API in .NET 8

2026-04-24T12:40:54.804+05:45

If you have started a new ASP.NET Core project in the last year, you have almost certainly paused at the same fork: controllers or Minimal APIs? In .NET 8 both approaches are mature, well-documented, and production-ready. They share the same middleware pipeline, the same dependency injection container, and they produce identical HTTP responses on the wire. What differs is the programming model, the amount of ceremony, and how well each scales as your team and codebase grow.

Controller-based APIs and Minimal APIs are the two official routing models in ASP.NET Core today. Both get you to a working endpoint; the question this post answers is which model fits your project size, your team's background, and your long-term maintenance plan.

You will walk away with runnable side-by-side examples, an honest breakdown of five real tradeoffs, and a summary table suitable for your team's architecture decision record.

1 A quick history first

Controller-based APIs trace their lineage to ASP.NET MVC, which shipped in 2009 and was rebuilt as ASP.NET Core from the ground up in 2016. The model is object-oriented: a class annotated with [ApiController] groups related endpoints, and the framework uses attribute routing, convention-based model binding, and action filters to wire everything together.

Minimal APIs arrived in .NET 6 as a deliberate reaction to the ceremony that controllers introduced for small or service-oriented scenarios. Instead of classes and attributes, you call app.MapGet, app.MapPost, and their siblings directly on the WebApplication instance or in extension methods. The model is functional: a route maps to a delegate.

Critically, both models compile to the same underlying endpoint routing infrastructure introduced in ASP.NET Core 3. There is no meaningful difference in the HTTP contract they produce; every observable difference lives in how you write and organize your code.

2 The same task, two ways

To ground the comparison, here is a GET /api/products/{id} endpoint backed by an injected service, written both ways. Both examples handle cancellation, declare response types for OpenAPI, and return a 404 when the product is not found.

Controller-based approach

// Controllers/ProductsController.cs
[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly IProductService _service;

    public ProductsController(IProductService service)
        => _service = service;

    [HttpGet("{id:int}")]
    [ProducesResponseType(typeof(ProductDto), StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<IActionResult> GetById(int id, CancellationToken ct)
    {
        var product = await _service.GetByIdAsync(id, ct);
        return product is null ? NotFound() : Ok(product);
    }
}

Minimal API approach

// Endpoints/ProductEndpoints.cs
public static class ProductEndpoints
{
    public static IEndpointRouteBuilder MapProductEndpoints(
        this IEndpointRouteBuilder routes)
    {
        var group = routes.MapGroup("/api/products")
                          .WithTags("Products");

        group.MapGet("/{id:int}", async (
            int id,
            IProductService service,
            CancellationToken ct) =>
        {
            var product = await service.GetByIdAsync(id, ct);
            return product is null ? Results.NotFound() : Results.Ok(product);
        })
        .Produces<ProductDto>()
        .ProducesProblem(StatusCodes.Status404NotFound);

        return routes;
    }
}

Notice that the Minimal API version uses MapGroup to avoid repeating the base path and is placed in a dedicated static class rather than Program.cs. That is the idiomatic .NET 8 pattern for organizing Minimal API endpoints at any meaningful scale. Dumping all MapGet calls into Program.cs works for demos but does not survive a real API.

3 The real tradeoffs

1. Boilerplate and ceremony

Controllers require inheriting from ControllerBase, marking the class with [ApiController], and defining a class per resource group. For a 30-endpoint API this structure is a feature; it is predictable and easy to navigate. For a three-endpoint microservice it feels like architecture for architecture's sake. Minimal APIs strip the class and attributes away, replacing them with terse lambda handlers and fluent builder calls.

Does this matter to you? If your API has more than ten distinct route templates or your team rotates developers frequently, the controller convention repays its upfront cost. If you are building a self-contained service that does one thing well, the Minimal API ceremony saving is real.

2. Testability

Controllers are plain classes. You can instantiate them in a unit test, inject a mock, and call action methods directly without spinning up an HTTP server.

// Direct unit test of a controller action
[Fact]
public async Task GetById_ReturnsNotFound_WhenProductMissing()
{
    var mockService = new Mock<IProductService>();
    mockService.Setup(s => s.GetByIdAsync(99, default))
               .ReturnsAsync((ProductDto?)null);

    var controller = new ProductsController(mockService.Object);
    var result = await controller.GetById(99, default);

    Assert.IsType<NotFoundResult>(result);
}

Minimal API handlers are delegates rather than public methods on an instantiable class, so the same pattern is not available. The canonical testing approach is WebApplicationFactory<T> integration tests, which are valuable but carry more setup weight per test. Teams that want fast, isolated unit tests close to the HTTP boundary will find controllers more accommodating.

3. Cross-cutting concerns

Controllers support action filters, result filters, and exception filters applied via attributes or registered globally through MvcOptions. This is a mature, well-understood model for concerns like audit logging, caching headers, and conditional response transformation.

// Attribute-applied action filter on a controller action
[ServiceFilter(typeof(AuditLogFilter))]
[HttpPost]
public async Task<IActionResult> Create([FromBody] CreateProductRequest request,
                                         CancellationToken ct) { ... }

Minimal APIs support endpoint filters via .AddEndpointFilter<T>() and .AddEndpointFilterFactory(), which covers most of the same ground. The ecosystem of pre-built filters is smaller, however, and the model is less familiar to teams coming from an MVC background. For application-wide concerns such as exception handling and authentication, middleware applies equally to both models.

4. Code organization at scale

As an API grows past a handful of routes, the structural question becomes more important than the syntactic one. Controllers encode the convention: one class per resource, one action per HTTP verb. New developers know exactly where to look. With Minimal APIs the structure is self-imposed; MapGroup combined with one static extension method per feature area is the current community standard, but enforcing it consistently across a team requires deliberate decision-making that controllers handle for you automatically.

5. OpenAPI support

In .NET 8 both models have first-class OpenAPI support. The built-in Microsoft.AspNetCore.OpenApi package handles document generation for either model; controllers use [ProducesResponseType] attributes while Minimal APIs use the fluent .Produces<T>() builder methods. Neither approach produces materially better documentation. Teams already invested in Swashbuckle will find the controller attribute style familiar, but the Minimal API fluent style is equally expressive.

4 Which one should your team use?

The right choice depends on project scope and team dynamics more than on any benchmark. Here is a practical decision guide.

Choose Controllers when:

Your team has existing ASP.NET Core MVC experience and the controller pattern is already second nature
The API has many endpoints across multiple resource types and you need the framework to enforce organizational conventions
You rely on action filters for cross-cutting concerns and want attribute-based application
You want to unit-test action methods directly without a full WebApplicationFactory setup
You are extending or maintaining an existing controller-based codebase

Choose Minimal APIs when:

You are building a microservice or a focused API with a small, stable route surface
Your team is comfortable defining its own organizational structure and does not need the framework to enforce it
You prefer integration tests over action-level unit tests and already have a WebApplicationFactory test project in place
Startup latency matters, such as in Azure Functions or container workloads with fast cold-start requirements
You want to minimize the number of abstractions new team members need to learn

Mixing both in a single application is also a legitimate pattern. A common approach is using controllers for the primary resource API while using Minimal APIs for lightweight utility endpoints.

// Program.cs — valid mixed configuration
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();
builder.Services.AddScoped<IProductService, ProductService>();

var app = builder.Build();

app.MapControllers();                                    // controller-based routes
app.MapHealthChecks("/health");                          // lightweight utility endpoint
app.MapGet("/version", () => Results.Ok(new            // simple info endpoint
{
    Version = "2.1.0",
    Runtime = Environment.Version.ToString()
}));

app.Run();

5 Quick reference summary

Concern	Controllers	Minimal APIs
Ceremony / boilerplate	Higher; class, attributes, inheritance required	Lower; delegates and fluent builders
Unit testability	Direct; instantiate the class in any test	Indirect; integration tests preferred
Filters and cross-cutting	Mature attribute-based ecosystem	Supported via endpoint filters; smaller ecosystem
Organization at scale	Convention enforced by the framework	Self-imposed; requires team discipline
OpenAPI / Swagger	First-class; attribute-driven	First-class; fluent builder-driven
Startup performance	Slightly higher route registration cost	Marginally leaner startup
Learning curve (MVC background)	Low; same model as ASP.NET MVC	Medium; new patterns to internalize

Final take

For most teams building a multi-resource API with five or more controllers' worth of endpoints, controllers remain the pragmatic default. The conventions are well understood, the tooling is battle-tested, and onboarding a new developer into a controller-based project requires almost no orientation. Minimal APIs earn their place in focused microservices and internal tools where the reduction in ceremony translates directly into a codebase that is faster to read and easier to keep lean.

Lean toward controllers when you are building a product API that will grow. Lean toward Minimal APIs when you are building a service that does one thing and should stay small.

What's next?

Project structure: The next post in this series covers how to organize folders, layers, and naming conventions in a .NET 8 Web API so the codebase stays navigable as it grows.
Validation strategy: Once your routing model is settled, the next meaningful decision is FluentValidation versus Data Annotations, covered later in this series.
Official reference: The ASP.NET Core Minimal APIs comparison docs at learn.microsoft.com are kept up to date with each .NET release and are worth bookmarking.

Got a question or a scenario I did not cover? Drop a comment below and I will reply.

Raw SQL, FromSqlRaw, and Dapper Side-by-Side in a .NET 8 API

2026-04-22T14:58:45.014+05:45

EF Core's LINQ query builder handles the vast majority of data access scenarios cleanly, but every production application eventually hits a query complex enough that generating it through LINQ is more effort than writing the SQL directly. At that point you have three realistic options: EF Core's own raw SQL APIs (FromSql and ExecuteSqlRaw), Dapper running over the same database connection, or a mix of both.

All three approaches talk to the same database, can share the same connection and transaction, and produce parameterized queries. The differences are in what they give back, how much ceremony they require, and where they break down.

By the end of this post you will have working code for all three approaches solving the same query, a clear decision framework for choosing between them, and a pattern for combining EF Core and Dapper in the same API without fighting over the connection.

1 A quick history first

EF Core raw SQL (FromSqlRaw, FromSql, ExecuteSqlRaw) was added to give EF developers an escape hatch when the LINQ translator cannot express a query, while still returning tracked entities and allowing LINQ composition on top of the raw SQL result.

Dapper is a micro-ORM by the Stack Overflow team built on top of IDbConnection. It maps query results to objects via property name matching and supports multi-mapping for joins. It has no change tracking, no relationship model, and no query builder; you write SQL, Dapper materializes the result.

Both evolved alongside LINQ-to-SQL and NHibernate as answers to the same question: what do you do when a generated query is not good enough?

2 The same task, three ways

The scenario: fetch orders for a given customer that include their line items and the product name for each line. This query involves a join, a filter, and a result shape that does not map cleanly to a single entity.

EF Core LINQ (baseline)

var orders = await _context.Orders
    .AsNoTracking()
    .Include(o => o.Lines)
        .ThenInclude(l => l.Product)
    .Where(o => o.CustomerId == customerId
             && o.Status == OrderStatus.Pending)
    .OrderByDescending(o => o.PlacedAt)
    .ToListAsync(ct);

EF Core FromSql approach

// FromSql (EF Core 8+) uses interpolated strings safely — parameters are never inlined
var orders = await _context.Orders
    .FromSql($@"
        SELECT o.*
        FROM   orders o
        WHERE  o.customer_id = {customerId}
          AND  o.status      = {(int)OrderStatus.Pending}
        ORDER BY o.placed_at DESC")
    .AsNoTracking()
    .Include(o => o.Lines)
        .ThenInclude(l => l.Product)
    .ToListAsync(ct);

Dapper approach

using var connection = _context.Database.GetDbConnection();
await connection.OpenAsync(ct);

var orderDict = new Dictionary<int, Order>();

await connection.QueryAsync<Order, OrderLine, Product, Order>(
    sql: @"
        SELECT o.id, o.customer_id, o.status, o.placed_at,
               l.id, l.order_id, l.quantity, l.unit_price,
               p.id, p.name, p.sku
        FROM   orders       o
        JOIN   order_lines  l ON l.order_id  = o.id
        JOIN   products     p ON p.id        = l.product_id
        WHERE  o.customer_id = @CustomerId
          AND  o.status      = @Status
        ORDER BY o.placed_at DESC",
    map: (order, line, product) =>
    {
        if (!orderDict.TryGetValue(order.Id, out var existing))
        {
            existing = order;
            existing.Lines = [];
            orderDict[order.Id] = existing;
        }
        line.Product = product;
        existing.Lines.Add(line);
        return existing;
    },
    param: new { CustomerId = customerId, Status = (int)OrderStatus.Pending },
    splitOn: "id,id");

var orders = orderDict.Values.ToList();

The SQL in the EF and Dapper versions is nearly identical. The biggest visible difference is that EF Core can compose Include on top of the raw SQL, while Dapper requires you to write the full join and wire up the object graph yourself.

3 The real tradeoffs

1. What you get back

EF Core raw SQL returns full entity instances that participate in the change tracker and can have navigation properties populated via Include. Dapper returns plain objects: whatever class you tell it to map to, with no change tracking and no navigation resolution built in. If you want the graph assembled from a join, you write the multi-mapping lambda yourself as shown above.

Does this matter? If you need to modify returned records or rely on navigation lazy loading, EF Core raw SQL is the right tool. If you are building a read model or projecting to a DTO, Dapper's lack of overhead is an advantage.

2. SQL injection safety

All three options support parameterized queries and are safe when used correctly, but the APIs make it easier or harder to do the wrong thing.

// Safe: FromSql with interpolated string — EF Core parameterizes {customerId} automatically
var orders = await _context.Orders
    .FromSql($"SELECT * FROM orders WHERE customer_id = {customerId}")
    .ToListAsync(ct);

// UNSAFE: FromSqlRaw with string interpolation — inlines the value directly into SQL
var orders = await _context.Orders
    .FromSqlRaw($"SELECT * FROM orders WHERE customer_id = {customerId}") // never do this
    .ToListAsync(ct);

// Safe: FromSqlRaw requires explicit parameter objects
var orders = await _context.Orders
    .FromSqlRaw("SELECT * FROM orders WHERE customer_id = {0}", customerId)
    .ToListAsync(ct);

The rule for FromSqlRaw: treat the first argument exactly like a string.Format call and never pass a user-derived value as anything other than a positional placeholder argument. For new code in EF Core 8, prefer FromSql (the interpolated string overload) because the compiler enforces correct usage.

3. LINQ composability

EF Core raw SQL results can have additional LINQ operators chained after them as long as the raw SQL forms the entire FROM clause:

// Legal: filter and sort on top of raw SQL
var page = await _context.Orders
    .FromSql($"SELECT * FROM orders WHERE customer_id = {customerId}")
    .Where(o => o.Status == OrderStatus.Pending)
    .OrderByDescending(o => o.PlacedAt)
    .Skip(offset).Take(pageSize)
    .ToListAsync(ct);

Dapper has no composition layer: once the SQL is written, the result is fixed. Any further filtering or pagination must happen either in SQL or in memory after the query returns, which can be a significant limitation for dynamic filter scenarios.

4. Non-query commands: ExecuteSqlRaw vs Dapper Execute

For UPDATE, DELETE, or stored procedure calls that do not return rows, both approaches are equivalent in ceremony:

// EF Core: bulk update without loading entities
int rows = await _context.Database.ExecuteSqlRawAsync(
    "UPDATE orders SET status = {0} WHERE customer_id = {1} AND placed_at < {2}",
    (int)OrderStatus.Cancelled, customerId, cutoff, ct);

// Dapper: same operation
using var connection = _context.Database.GetDbConnection();
int rows = await connection.ExecuteAsync(
    "UPDATE orders SET status = @Status WHERE customer_id = @CustomerId AND placed_at < @Cutoff",
    new { Status = (int)OrderStatus.Cancelled, CustomerId = customerId, Cutoff = cutoff });

For bulk operations in EF Core 8, prefer ExecuteUpdateAsync and ExecuteDeleteAsync over raw SQL commands because they compose with Global Query Filters and keep the tenant and soft-delete predicates active.

5. Sharing a transaction across EF Core and Dapper

Because Dapper works directly on an IDbConnection, it can participate in the same transaction that EF Core opened:

using var tx = await _context.Database.BeginTransactionAsync(ct);

// EF Core write
_context.Orders.Add(newOrder);
await _context.SaveChangesAsync(ct);

// Dapper write in the same transaction
var connection   = _context.Database.GetDbConnection();
var dbTransaction = _context.Database.CurrentTransaction!.GetDbTransaction();

await connection.ExecuteAsync(
    "INSERT INTO audit_log (order_id, action, ts) VALUES (@OrderId, @Action, @Ts)",
    new { OrderId = newOrder.Id, Action = "created", Ts = DateTimeOffset.UtcNow },
    transaction: dbTransaction);

await tx.CommitAsync(ct);

This pattern lets you use EF Core's change tracking for the entities it manages well and fall back to Dapper for queries or commands that do not fit the EF Core model, all within the same transactional boundary.

4 Which one should you choose?

Choose based on what you need from the result, not on habit or familiarity with one library.

Use EF Core LINQ when:

The query can be expressed in LINQ without gymnastics and the generated SQL is acceptable.
You need to modify returned entities in the same unit of work.
You want Global Query Filters (soft delete, multi-tenancy) applied automatically.
The query benefits from Include navigation loading without writing a join manually.

Use EF Core FromSql when:

The LINQ translation produces a suboptimal query (extra joins, missing index hints, missing CTEs) and you want to write the SQL yourself.
You still need to return tracked entities and compose LINQ on top of the result.
You are calling a view or a stored procedure that returns a shape matching an existing entity.
You want the safety of parameterized queries with minimal friction over raw ADO.NET.

Use Dapper when:

The result does not map to an entity: reporting queries, aggregations, complex multi-table projections, or custom DTO shapes.
You need multi-mapping (assembling a graph from a single join query) with fine-grained control over the mapping logic.
Performance is critical and you want the thinnest possible layer between SQL and the materialized object.
You are integrating a legacy stored-procedure-heavy database where no EF entity model exists.

5 Quick reference summary

Concern	EF Core LINQ	EF Core FromSql	Dapper
Change tracking	Yes	Yes (unless AsNoTracking)	No
LINQ composability	Full	Partial (on top of FROM)	None
Global Query Filters	Automatic	Automatic	Must write manually
Custom SQL control	None	Full for the FROM clause	Full
Non-entity result shapes	Via Select projection	Requires keyless entity type	Any class or primitive
Multi-mapping joins	Via Include	Via Include	Manual but flexible
Transaction sharing with EF	Native	Native	Via GetDbTransaction()
SQL injection safety	Automatic	Safe with FromSql/positional params	Safe with @param syntax

Final take

These three tools are not competitors; they are layers. Start with EF Core LINQ. Drop to FromSql when the generated SQL is the problem but you still want entity tracking and LINQ composition. Drop to Dapper when the result shape does not fit the entity model at all or when you need the absolute minimum between your SQL and your object.

If you find yourself using FromSqlRaw everywhere because LINQ frustrates you, that is a signal your entity model may be fighting your query patterns; revisit the schema or consider a dedicated read model with Dapper for query-side endpoints.

What's next?

Add a keyless entity type: If you have reporting queries that return custom shapes, register them with modelBuilder.Entity<ReportRow>().HasNoKey().ToView(null) and use FromSqlRaw to return them through EF Core with full LINQ composability.
Benchmark your heavy queries: Use BenchmarkDotNet with both approaches on your most-called endpoints and measure allocations, not just execution time.
Review your stored procedures: If your team owns legacy stored procedures, Dapper's commandType: CommandType.StoredProcedure support makes it the cleanest integration path without touching EF's entity model.

Got a question or a scenario I did not cover? Drop a comment below and I will reply.

Handling Concurrency Conflicts in EF Core with PostgreSQL

2026-04-22T14:55:11.558+05:45

Optimistic concurrency assumes conflicts are rare but makes them visible when they happen. Without it, the last write silently wins: two users edit the same record, the first save succeeds, the second save overwrites it with stale data, and no one knows. EF Core's concurrency support turns that silent overwrite into an explicit exception you can handle on your terms.

PostgreSQL makes this particularly clean because every row carries a built-in system column called xmin that changes on every update. By the end of this post you will have EF Core using xmin as a concurrency token, a typed exception handler that covers all three resolution strategies (client wins, database wins, and custom merge), and a tested endpoint that demonstrates the full conflict cycle.

1 Prerequisites

Before starting, make sure you have:

.NET 8 SDK
EF Core 8 with the Npgsql provider: Npgsql.EntityFrameworkCore.PostgreSQL version 8.x
A running PostgreSQL instance (local or Docker)
A working DbContext with at least one entity you intend to protect

Note: If you are on SQL Server instead of PostgreSQL, replace UseXminAsConcurrencyToken() with a [Timestamp] attribute or .IsRowVersion() in fluent config. Everything from section 3 onward applies identically.

2 Understanding the concurrency problem

Optimistic concurrency works by recording the version of a row when it is loaded and then asserting that version has not changed when the update is applied. The SQL EF Core generates looks like this conceptually:

UPDATE Products
SET    Price = @newPrice,
       UpdatedAt = @now
WHERE  Id = @id
AND    xmin = @originalXmin;  -- the version we loaded

If another process updated the row between our read and our write, xmin will have advanced and the WHERE clause will match zero rows. EF Core checks the affected row count: zero rows updated means a conflict, and it throws DbUpdateConcurrencyException.

What is xmin? Every PostgreSQL row stores the transaction ID of the last transaction that inserted or updated it in a hidden system column called xmin. It is monotonically increasing per-row and requires no application-level migration to use.

3 Configuring the concurrency token

The Npgsql EF Core provider exposes a single fluent call that maps xmin as the concurrency token. Add it to your entity configuration and add a uint property to the entity class to hold the shadow value.

Entity class

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int StockQuantity { get; set; }
    public DateTimeOffset UpdatedAt { get; set; }

    // Maps to PostgreSQL's xmin system column
    public uint Version { get; set; }
}

Fluent configuration in OnModelCreating

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>(product =>
    {
        product.HasKey(p => p.Id);

        product.Property(p => p.Price)
            .HasColumnType("numeric(18,4)")
            .IsRequired();

        product.Property(p => p.Version)
            .IsRowVersion()
            .HasColumnName("xmin")
            .HasColumnType("xid");

        product.UseXminAsConcurrencyToken();
    });
}

The combination of UseXminAsConcurrencyToken() and mapping the Version property ensures EF Core reads xmin after every query and includes it in the WHERE clause of every UPDATE. You do not need to set or manage Version yourself; PostgreSQL owns it.

Migrations note: xmin is a system column; it does not appear in your migration's CreateTable call. If you generate a migration after adding UseXminAsConcurrencyToken(), confirm that the migration contains no column additions for xmin or Version. If it does, remove them manually before applying.

4 Catching and handling DbUpdateConcurrencyException

When a concurrency conflict occurs, EF Core throws DbUpdateConcurrencyException. The exception carries an Entries collection: one EntityEntry per entity involved in the failed save. Use the entry to inspect proposed values, original values, and the current database state, then apply one of three resolution strategies.

The resolution helper

public enum ConcurrencyResolution
{
    ClientWins,
    DatabaseWins,
    Merge
}

public async Task<bool> TrySaveWithRetryAsync(
    ConcurrencyResolution resolution,
    Func<EntityEntry, Task>? mergeStrategy = null,
    CancellationToken ct = default)
{
    const int maxRetries = 3;
    for (int attempt = 1; attempt <= maxRetries; attempt++)
    {
        try
        {
            await _context.SaveChangesAsync(ct);
            return true;
        }
        catch (DbUpdateConcurrencyException ex)
        {
            if (attempt == maxRetries) throw;

            foreach (var entry in ex.Entries)
            {
                var dbValues = await entry.GetDatabaseValuesAsync(ct);

                if (dbValues is null)
                    throw new InvalidOperationException(
                        $"Row for {entry.Metadata.Name} was deleted concurrently.");

                switch (resolution)
                {
                    case ConcurrencyResolution.ClientWins:
                        // Tell EF the "original" version is now the DB version,
                        // so the next save sees no conflict.
                        entry.OriginalValues.SetValues(dbValues);
                        break;

                    case ConcurrencyResolution.DatabaseWins:
                        // Overwrite proposed values with what is in the database.
                        entry.CurrentValues.SetValues(dbValues);
                        entry.OriginalValues.SetValues(dbValues);
                        break;

                    case ConcurrencyResolution.Merge:
                        if (mergeStrategy is not null)
                            await mergeStrategy(entry);
                        entry.OriginalValues.SetValues(dbValues);
                        break;
                }
            }
        }
    }
    return false;
}

GetDatabaseValuesAsync issues a fresh SELECT to fetch the current row state from the database. It returns null when the row was deleted between your read and your write, which you should treat as a non-retriable error.

5 Applying the strategies in a real endpoint

Here is a complete update endpoint that demonstrates all three strategies. In a real application you would choose one strategy per entity type based on business rules, not expose all three to the caller.

Client wins: the user's changes always prevail

public async Task<IResult> UpdateProductPriceAsync(
    int id, decimal newPrice, uint clientVersion, CancellationToken ct)
{
    var product = await _context.Products.FindAsync([id], ct);
    if (product is null) return Results.NotFound();

    product.Price     = newPrice;
    product.UpdatedAt = DateTimeOffset.UtcNow;

    try
    {
        await _context.SaveChangesAsync(ct);
        return Results.NoContent();
    }
    catch (DbUpdateConcurrencyException ex)
    {
        var entry     = ex.Entries.Single();
        var dbValues  = await entry.GetDatabaseValuesAsync(ct)
            ?? throw new InvalidOperationException("Product was deleted.");

        // Update the original token so the next attempt succeeds
        entry.OriginalValues.SetValues(dbValues);
        await _context.SaveChangesAsync(ct);
        return Results.NoContent();
    }
}

Database wins: preserve what is already committed

catch (DbUpdateConcurrencyException ex)
{
    var entry    = ex.Entries.Single();
    var dbValues = await entry.GetDatabaseValuesAsync(ct)
        ?? throw new InvalidOperationException("Product was deleted.");

    // Discard the user's proposed values entirely
    entry.CurrentValues.SetValues(dbValues);
    entry.OriginalValues.SetValues(dbValues);

    // No SaveChanges call needed; nothing to write
    return Results.Conflict(new { message = "Your changes were overridden by a concurrent update." });
}

Custom merge: combine fields from both versions

catch (DbUpdateConcurrencyException ex)
{
    var entry    = ex.Entries.Single();
    var dbValues = await entry.GetDatabaseValuesAsync(ct)
        ?? throw new InvalidOperationException("Product was deleted.");

    var dbProduct       = (Product)dbValues.ToObject();
    var proposedProduct = (Product)entry.Entity;

    // Business rule: user owns the price; the database owns the stock count
    proposedProduct.StockQuantity = dbProduct.StockQuantity;
    proposedProduct.UpdatedAt     = DateTimeOffset.UtcNow;

    // Refresh the concurrency token so the retry succeeds
    entry.OriginalValues.SetValues(dbValues);
    await _context.SaveChangesAsync(ct);
    return Results.NoContent();
}

Wrapping up

Optimistic concurrency with xmin in PostgreSQL is one of the lowest-friction concurrency control options available: no application-managed columns, no migration schema changes, and no locks. EF Core's DbUpdateConcurrencyException gives you everything you need to implement client wins, database wins, or a custom field-level merge in a small, testable helper method.

Next in this series: Raw SQL, FromSqlRaw, and Dapper side-by-side in a .NET 8 API, covering when to step outside the LINQ query builder and which tool to reach for when you do.

Got a question or ran into a problem? Drop a comment below and I will reply.

Tracking vs. No-Tracking Queries in EF Core: When Each One Hurts You

2026-04-22T14:52:25.455+05:45

If you have ever profiled an EF Core application under load and wondered why memory climbs steadily through a read-heavy endpoint, the change tracker is likely the culprit. Tracking queries are the EF Core default for good reasons, but applying them to every query in every scenario is one of the most common sources of unnecessary overhead in .NET API projects.

Tracking queries attach every loaded entity to the DbContext change tracker so EF Core can detect modifications and translate them to SQL on SaveChanges. No-tracking queries skip that bookkeeping entirely. Both exist because the right choice genuinely depends on what you are doing with the data.

By the end of this post you will understand what each mode costs, when the cost is worth paying, and how to update entities correctly in a no-tracking world.

1 A quick history first

Tracking queries have been the EF default since the original Entity Framework, designed for connected scenarios where you load an entity, modify it in the same unit of work, and call SaveChanges before the DbContext is disposed.

No-tracking queries (via AsNoTracking()) were added to address a growing class of read-only use cases, such as API list endpoints, reporting queries, and projections, where the caller never modifies the returned objects and paying the tracking cost is pure waste.

Both operate against the same SQL-generating LINQ pipeline; the difference is entirely in what happens after rows arrive from the database.

2 The same task, two ways

Suppose you are building a GET endpoint that returns a paginated list of products in a given category. Here is what each approach looks like for that exact query.

Tracking approach

// Default behavior: every returned Product is attached to the change tracker
var products = await _context.Products
    .Where(p => p.CategoryId == categoryId && p.IsActive)
    .OrderBy(p => p.Name)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync(ct);

No-tracking approach

// AsNoTracking: entities are materialized but never registered with the change tracker
var products = await _context.Products
    .AsNoTracking()
    .Where(p => p.CategoryId == categoryId && p.IsActive)
    .OrderBy(p => p.Name)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync(ct);

The SQL generated is identical. The difference is invisible in the query itself but measurable in memory and CPU once you scale to hundreds of rows or high request concurrency.

3 The real tradeoffs

1. Memory and change tracker overhead

For every tracked entity, EF Core allocates an EntityEntry, stores a snapshot of the original property values, and adds the entity to an internal identity map keyed by primary key. On a list endpoint returning 200 rows with 15 columns each, that overhead is measurable, not catastrophic, but it is completely avoidable if you have no intention of modifying those objects.

Does this matter in practice? Yes, under high concurrency. Because DbContext is scoped per request, tracking overhead does not accumulate across requests, but it does accumulate within a single request that loads large result sets. No-tracking cuts the per-entity overhead roughly in half in benchmarks from the EF Core team.

2. Identity resolution behavior

The tracking change tracker deduplicates: if two navigations both load the same row (same primary key), you get back the same object instance. No-tracking queries by default do not deduplicate, so you can end up with two separate Product instances representing the same database row after a join.

// No-tracking with identity resolution: faster than tracking, still deduplicates instances
var orders = await _context.Orders
    .AsNoTrackingWithIdentityResolution()
    .Include(o => o.Lines)
    .ThenInclude(l => l.Product)
    .ToListAsync(ct);

AsNoTrackingWithIdentityResolution() is the middle ground: no change-tracking bookkeeping, but a single pass of deduplication ensures you get one Product instance per primary key even if it appears in multiple order lines. Use it any time you load a graph with potentially shared child entities and care about reference equality.

3. Updating data after a no-tracking query

The most common mistake with no-tracking is trying to modify a returned entity and calling SaveChanges, expecting EF to detect the change. It will not; the entity is not in the change tracker, so nothing happens.

// Wrong: entity is not tracked, SaveChanges does nothing
var product = await _context.Products
    .AsNoTracking()
    .FirstAsync(p => p.Id == id, ct);

product.Price = 49.99m; // change not detected
await _context.SaveChangesAsync(ct); // no UPDATE issued

For disconnected update scenarios, use one of two patterns. If you have all the updated values, use ExecuteUpdateAsync to issue a targeted UPDATE without loading the row at all:

// Preferred for simple updates: no load required
await _context.Products
    .Where(p => p.Id == id)
    .ExecuteUpdateAsync(s =>
        s.SetProperty(p => p.Price, 49.99m)
         .SetProperty(p => p.UpdatedAt, DateTimeOffset.UtcNow),
        ct);

If you need to attach an already-materialized entity and mark specific properties as modified, use the Attach and Entry pattern:

// Attach pattern: re-registers the entity and marks only the changed columns
var product = new Product { Id = id, Price = 49.99m, UpdatedAt = DateTimeOffset.UtcNow };
_context.Attach(product);
_context.Entry(product).Property(p => p.Price).IsModified = true;
_context.Entry(product).Property(p => p.UpdatedAt).IsModified = true;
await _context.SaveChangesAsync(ct);

4. Projections supersede the tracking question

If you project directly to a DTO using Select, neither tracking mode matters for performance: EF Core does not materialize entities at all, so there is nothing to track. AsNoTracking() on a projection query has no measurable effect.

// AsNoTracking here is harmless but redundant — no entity is materialized
var dtos = await _context.Products
    .Where(p => p.CategoryId == categoryId)
    .Select(p => new ProductDto(p.Id, p.Name, p.Price))
    .ToListAsync(ct);

Project to DTOs whenever the caller needs only a subset of columns. It is faster than loading full entities in either tracking mode.

4 Which one should you choose?

The rule is simple: use tracking when you intend to modify the loaded entity within the same DbContext lifetime; use no-tracking everywhere else.

Use tracking queries when:

You load an entity, modify properties, and call SaveChanges in the same request.
You need EF Core to automatically detect which properties changed (without specifying them manually).
You are working with complex graphs where EF needs to track state across multiple related entities.
You are using the default lazy-loading proxies, which require tracked entities.

Use no-tracking queries when:

You are building read-only endpoints: GET lists, detail views, reports, feeds.
You are loading entities to pass to a view or serialize to JSON without modification.
You are running background jobs that read large batches of data for processing or export.
You are loading navigation-heavy graphs and using AsNoTrackingWithIdentityResolution to retain object deduplication.

A practical team convention: set AsNoTracking as the default on the DbContext for read services or query handlers, and opt in to tracking only in write handlers. This way the safe default is the fast one.

// Set no-tracking as the context default for a read-optimized DbContext
public class ReadDbContext : AppDbContext
{
    public ReadDbContext(
        DbContextOptions<AppDbContext> options,
        ITenantProvider tenantProvider)
        : base(options, tenantProvider)
    {
        ChangeTracker.QueryTrackingBehavior =
            QueryTrackingBehavior.NoTracking;
    }
}

5 Quick reference summary

Concern	Tracking	No-Tracking
Change detection on SaveChanges	Automatic	Not supported; use ExecuteUpdateAsync or Attach
Memory per entity	Higher (snapshot + EntityEntry)	Lower (entity instance only)
Identity resolution (dedup)	Automatic via identity map	Disabled by default; use AsNoTrackingWithIdentityResolution
Generated SQL	Identical	Identical
Safe for read-only endpoints	Yes, but wasteful	Yes, and optimal
Required for lazy loading	Yes	No
Projection queries (Select to DTO)	No benefit either way	No benefit either way

Final take

Tracking is the right default when you genuinely need it; no-tracking is the right default when you do not. The problem is that EF Core ships with tracking on, so you have to actively choose no-tracking for read-heavy endpoints, and most teams never do until a profiler shows them the cost.

Teams doing classic CRUD with load-modify-save in a single request should leave tracking on and not overthink it. Teams building read APIs, CQRS query sides, or any endpoint that never modifies returned objects should make AsNoTracking the default and opt in to tracking explicitly.

What's next?

Profile your current endpoints: Run a quick benchmark with and without AsNoTracking() on your heaviest list queries and measure the difference in allocations using BenchmarkDotNet or dotnet-counters.
Try ExecuteUpdateAsync: Replace any pattern where you load a single entity just to change one field with a direct ExecuteUpdateAsync call and observe the reduction in round trips.
Read about concurrency: The next post in this series covers how tracking interacts with concurrency tokens in PostgreSQL and how to handle DbUpdateConcurrencyException gracefully.

Got a question or a scenario I did not cover? Drop a comment below and I will reply.

EF Core Relationships Explained: One-to-Many, Many-to-Many, and Owned Entities

2026-04-22T14:41:49.844+05:45

EF Core's relationship API has enough surface area that it is easy to configure something that compiles cleanly and then behaves wrong at runtime: a cascade delete you did not intend, a join table with the wrong primary key, or an owned entity serialized into a separate table when you expected column-inlining. Getting the configuration right the first time saves painful migrations later.

This post covers the three relationship shapes you will use in almost every application: one-to-many, many-to-many (with and without a payload), and owned entities. For each shape you will see the entity classes, the fluent configuration, and the key behavioral detail that trips developers up.

1 Prerequisites

Before starting, make sure you have:

.NET 8 SDK
EF Core 8 (Microsoft.EntityFrameworkCore plus a provider)
A working DbContext with migrations enabled
Familiarity with OnModelCreating and the ModelBuilder API

Note: EF Core can infer many relationships by convention, but this post uses explicit fluent configuration throughout. Convention-based mapping is fragile under refactoring; fluent configuration is not.

2 One-to-many relationships

One-to-many is the most common relationship shape: a Blog has many Post records; each Post belongs to exactly one Blog. The principal entity (Blog) holds the collection; the dependent entity (Post) holds the foreign key.

Entity classes

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Url { get; set; } = string.Empty;

    public ICollection<Post> Posts { get; set; } = [];
}

public class Post
{
    public int Id { get; set; }
    public int BlogId { get; set; }
    public string Title { get; set; } = string.Empty;
    public string Body { get; set; } = string.Empty;
    public DateTimeOffset PublishedAt { get; set; }

    public Blog Blog { get; set; } = null!;
}

Fluent configuration

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Post>()
        .HasOne(p => p.Blog)
        .WithMany(b => b.Posts)
        .HasForeignKey(p => p.BlogId)
        .IsRequired()
        .OnDelete(DeleteBehavior.Cascade);
}

Always specify OnDelete explicitly. EF Core defaults to Cascade for required relationships and ClientSetNull for optional ones, but the correct behavior depends on your domain and leaving it implicit is a migration-time surprise waiting to happen.

Querying with Include

// Load a blog with all its posts
var blog = await _context.Blogs
    .Include(b => b.Posts)
    .FirstOrDefaultAsync(b => b.Id == blogId, ct);

// Load posts with their parent blog (useful for display-only reads)
var posts = await _context.Posts
    .AsNoTracking()
    .Include(p => p.Blog)
    .Where(p => p.PublishedAt >= cutoff)
    .OrderByDescending(p => p.PublishedAt)
    .ToListAsync(ct);

Tip: Never load a collection navigation just to count it. Use .CountAsync(p => p.BlogId == blogId) directly; EF Core translates it to a COUNT(*) without loading any rows.

3 Many-to-many relationships

Many-to-many maps each record in one table to zero or more records in another table, and vice versa. EF Core 5 and later can create the join table implicitly, but as soon as the join needs extra data, such as a timestamp or an ordering column, you must define an explicit join entity.

Implicit join table (no payload)

When the join carries no extra information, the shortest configuration is:

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public ICollection<Tag> Tags { get; set; } = [];
}

public class Tag
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public ICollection<Post> Posts { get; set; } = [];
}

modelBuilder.Entity<Post>()
    .HasMany(p => p.Tags)
    .WithMany(t => t.Posts)
    .UsingEntity(j => j.ToTable("PostTags"));

EF Core creates the PostTags join table with a composite primary key of (PostsId, TagsId). UsingEntity lets you rename the table and columns without introducing a full entity class.

Explicit join entity (with payload)

When you need to store when a tag was applied, or who applied it, you need an entity class for the join row:

public class PostTag
{
    public int PostId { get; set; }
    public int TagId { get; set; }
    public DateTimeOffset TaggedAt { get; set; }
    public string TaggedBy { get; set; } = string.Empty;

    public Post Post { get; set; } = null!;
    public Tag Tag { get; set; } = null!;
}

modelBuilder.Entity<PostTag>()
    .HasKey(pt => new { pt.PostId, pt.TagId });

modelBuilder.Entity<Post>()
    .HasMany(p => p.Tags)
    .WithMany(t => t.Posts)
    .UsingEntity<PostTag>(
        right => right
            .HasOne(pt => pt.Tag)
            .WithMany()
            .HasForeignKey(pt => pt.TagId),
        left => left
            .HasOne(pt => pt.Post)
            .WithMany()
            .HasForeignKey(pt => pt.PostId));

// Update the navigation collections to use PostTag
public class Post
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public ICollection<PostTag> PostTags { get; set; } = [];
}

public class Tag
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public ICollection<PostTag> PostTags { get; set; } = [];
}

Once you add payload, replace the ICollection<Tag> navigation with ICollection<PostTag>. You gain access to the extra columns; you lose the convenience of direct cross-navigation. That is the intentional trade-off.

Adding a tag to a post

var post = await _context.Posts.FindAsync(postId);
var tag  = await _context.Tags.FindAsync(tagId);

_context.Set<PostTag>().Add(new PostTag
{
    PostId   = post!.Id,
    TagId    = tag!.Id,
    TaggedAt = DateTimeOffset.UtcNow,
    TaggedBy = currentUser
});

await _context.SaveChangesAsync(ct);

4 Owned entities

An owned entity is a class that has no identity of its own outside its owner. It maps to columns in the owner's table by default and is deleted automatically when the owner is deleted. Use owned entities for value objects: addresses, money, coordinates, contact details.

Defining owner and owned type

public class Order
{
    public int Id { get; set; }
    public string CustomerName { get; set; } = string.Empty;
    public DateTimeOffset PlacedAt { get; set; }
    public Address ShippingAddress { get; set; } = null!;
    public Address BillingAddress { get; set; } = null!;
}

public class Address
{
    public string Street { get; set; } = string.Empty;
    public string City { get; set; } = string.Empty;
    public string PostalCode { get; set; } = string.Empty;
    public string Country { get; set; } = string.Empty;
}

Fluent configuration with column name overrides

modelBuilder.Entity<Order>(order =>
{
    order.OwnsOne(o => o.ShippingAddress, addr =>
    {
        addr.Property(a => a.Street).HasColumnName("ShipStreet").IsRequired();
        addr.Property(a => a.City).HasColumnName("ShipCity").IsRequired();
        addr.Property(a => a.PostalCode).HasColumnName("ShipPostalCode").IsRequired();
        addr.Property(a => a.Country).HasColumnName("ShipCountry").IsRequired();
    });

    order.OwnsOne(o => o.BillingAddress, addr =>
    {
        addr.Property(a => a.Street).HasColumnName("BillStreet").IsRequired();
        addr.Property(a => a.City).HasColumnName("BillCity").IsRequired();
        addr.Property(a => a.PostalCode).HasColumnName("BillPostalCode").IsRequired();
        addr.Property(a => a.Country).HasColumnName("BillCountry").IsRequired();
    });
});

Without explicit column name overrides, EF Core generates names in the form ShippingAddress_Street, which is fine but can be verbose in a schema you share with other teams.

OwnsMany for collections of value objects

When an order can have multiple line items that are fully owned (not referenced from anywhere else), use OwnsMany. EF Core maps the collection to a separate table with a foreign key back to the owner:

public class Order
{
    public int Id { get; set; }
    public string CustomerName { get; set; } = string.Empty;
    public IList<OrderNote> Notes { get; set; } = [];
}

public class OrderNote
{
    public string Text { get; set; } = string.Empty;
    public DateTimeOffset AddedAt { get; set; }
}

// Configuration
modelBuilder.Entity<Order>()
    .OwnsMany(o => o.Notes, note =>
    {
        note.ToTable("OrderNotes");
        note.WithOwner().HasForeignKey("OrderId");
        note.Property(n => n.Text).IsRequired();
    });

Tip: Owned entities cannot be queried directly via a DbSet. You always access them through the owner. If you need to query the child type independently, it should be a regular entity with its own primary key, not an owned type.

5 Verifying the schema with a migration preview

After writing your configuration, run a dry-run migration to verify EF Core generated the schema you intended before touching any database:

// Add a migration to inspect the generated SQL
// dotnet ef migrations add InitialRelationships --project YourProject

// Preview the SQL without applying it
// dotnet ef migrations script --idempotent

Look for three things: the foreign key columns with the correct names, the composite primary key on any join table, and the owned-entity columns inlined into the owner table (or separated into the expected table for OwnsMany). If anything looks wrong, it is far cheaper to fix the configuration and drop the migration now than after it has been applied to a shared database.

Wrapping up

One-to-many is your default; configure the foreign key and delete behavior explicitly. Many-to-many with an implicit join table is convenient for pure link tables, but move to an explicit entity the moment the join needs any extra columns. Owned entities are the right tool for value objects: they keep the class model clean while mapping to flat table columns.

Next in this series: Tracking vs. No-Tracking Queries in EF Core, covering where the change tracker hurts performance, when you genuinely need it, and how to update entities safely in a no-tracking world.

Got a question or ran into a problem? Drop a comment below and I will reply.

Global Query Filters in EF Core: Soft Delete, Multi-Tenancy, and More

2026-04-22T14:13:00.463+05:45

Almost every production application applies the same predicates to every query: exclude soft-deleted rows, scope results to the current tenant, hide unpublished records. Writing those predicates in every LINQ expression is repetitive and dangerous; miss one and you have a data leak or a cross-tenant exposure. EF Core's Global Query Filters let you register those predicates once on the entity type and have EF Core apply them automatically to every query that touches that type.

By the end of this post you will have a working DbContext that enforces both soft-delete and tenant-scoping filters automatically, supports injecting the tenant identity from the current HTTP request, and exposes clean per-query escape hatches via IgnoreQueryFilters().

1 Prerequisites

Before starting, make sure you have:

.NET 8 SDK or later
EF Core 8 (Microsoft.EntityFrameworkCore plus a provider: Npgsql.EntityFrameworkCore.PostgreSQL or Microsoft.EntityFrameworkCore.SqlServer)
A working DbContext with at least one entity mapped to a table
Familiarity with fluent configuration inside OnModelCreating

Note: Global Query Filters were introduced in EF Core 2.0 and the API has not changed shape since. Everything in this post applies equally to EF Core 8 and EF Core 9.

2 How Global Query Filters work

A Global Query Filter is a LINQ predicate registered against an entity type in OnModelCreating. EF Core appends it to every LINQ query that targets that entity, including queries reached through navigation properties. The filter is composed with any additional predicates the caller adds, so nothing about the behavior changes from the caller's perspective.

Registering a filter

The entire API surface is a single call on the entity builder:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Article>()
        .HasQueryFilter(a => !a.IsDeleted);
}

That one line means every dbContext.Articles query now contains WHERE IsDeleted = 0 in the generated SQL without any caller needing to know about it. Only one filter is allowed per entity type; if you need to combine conditions, combine them in a single lambda.

Important: A filter is registered on the entity type, not the table. If two entity types share a table via table splitting, register the filter on each entity type separately.

3 Soft delete: mark rows invisible without removing them

Soft delete stores a flag on the row instead of issuing a DELETE statement. Combined with a Global Query Filter, flagged rows become completely invisible to normal application queries. The data is preserved for auditing or restoration, and no call site needs to remember to filter it out.

The shared interface

Define an interface so any entity can opt in without repeating the same two properties:

public interface ISoftDelete
{
    bool IsDeleted { get; set; }
    DateTimeOffset? DeletedAt { get; set; }
}

public class Article : ISoftDelete
{
    public int Id { get; set; }
    public Guid TenantId { get; set; }
    public string Title { get; set; } = string.Empty;
    public string Body { get; set; } = string.Empty;
    public bool IsDeleted { get; set; }
    public DateTimeOffset? DeletedAt { get; set; }
}

Registering the filter for all soft-delete entities at once

Rather than calling HasQueryFilter on every entity individually, iterate the model and use System.Linq.Expressions to build the predicate dynamically. This approach scales to any number of entities with no per-entity boilerplate:

using System.Linq.Expressions;

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    foreach (var entityType in modelBuilder.Model.GetEntityTypes())
    {
        if (!typeof(ISoftDelete).IsAssignableFrom(entityType.ClrType))
            continue;

        var parameter  = Expression.Parameter(entityType.ClrType, "e");
        var property   = Expression.Property(parameter, nameof(ISoftDelete.IsDeleted));
        var falseValue = Expression.Constant(false);
        var body       = Expression.Equal(property, falseValue);
        var lambda     = Expression.Lambda(body, parameter);

        modelBuilder.Entity(entityType.ClrType).HasQueryFilter(lambda);
    }
}

Performing a soft delete

A generic helper method keeps the pattern consistent across repositories:

public async Task SoftDeleteAsync<T>(
    T entity,
    CancellationToken ct = default)
    where T : class, ISoftDelete
{
    entity.IsDeleted = true;
    entity.DeletedAt = DateTimeOffset.UtcNow;
    await _context.SaveChangesAsync(ct);
}

The entity stays in the database. Every subsequent query on that type simply never returns it because the filter hides it.

4 Multi-tenancy: scope every query to the current tenant automatically

A tenant filter follows the same pattern as soft delete, but it needs to read the current tenant identifier at query time rather than at model-building time. Because the filter lambda can close over a service instance, EF Core re-reads the tenant value on every query, which means the filter stays correct across the lifetime of a scoped DbContext.

The tenant provider

public interface ITenantProvider
{
    Guid TenantId { get; }
}

public class HttpContextTenantProvider : ITenantProvider
{
    private readonly IHttpContextAccessor _accessor;

    public HttpContextTenantProvider(IHttpContextAccessor accessor)
        => _accessor = accessor;

    public Guid TenantId
    {
        get
        {
            var raw = _accessor.HttpContext?
                .User.FindFirstValue("tenant_id");

            return Guid.TryParse(raw, out var id) ? id : Guid.Empty;
        }
    }
}

Wiring the provider into DbContext

public class AppDbContext : DbContext
{
    private readonly ITenantProvider _tenantProvider;

    public AppDbContext(
        DbContextOptions<AppDbContext> options,
        ITenantProvider tenantProvider)
        : base(options)
    {
        _tenantProvider = tenantProvider;
    }

    public DbSet<Article> Articles => Set<Article>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Article>()
            .HasQueryFilter(a =>
                a.TenantId == _tenantProvider.TenantId
                && !a.IsDeleted);
    }
}

The lambda closes over _tenantProvider, the provider object itself, not a captured Guid value. EF Core evaluates the expression tree and emits _tenantProvider.TenantId as a SQL parameter, so the query plan is cache-friendly and the correct tenant value is read on every call.

Warning: Never close over a plain Guid variable captured at DbContext construction time. EF Core would treat it as a constant baked into the query, and all requests would silently query the same tenant regardless of who is calling.

Registering the services

// Program.cs
builder.Services.AddHttpContextAccessor();
builder.Services.AddScoped<ITenantProvider, HttpContextTenantProvider>();
builder.Services.AddDbContext<AppDbContext>(opt =>
    opt.UseNpgsql(builder.Configuration.GetConnectionString("Default")));

5 Bypassing filters with IgnoreQueryFilters

Some operations legitimately need unfiltered access: an admin panel that lists all tenants, a background cleanup job that hard-deletes soft-deleted rows older than 90 days, or a restore operation that retrieves a specific deleted record. Use IgnoreQueryFilters() to opt out on a per-query basis.

// Retrieve a soft-deleted article by ID for an admin restore
var deleted = await _context.Articles
    .IgnoreQueryFilters()
    .FirstOrDefaultAsync(a => a.Id == id && a.IsDeleted, ct);

// Super-admin: list all articles across all tenants
var all = await _context.Articles
    .IgnoreQueryFilters()
    .OrderByDescending(a => a.Id)
    .ToListAsync(ct);

// Cleanup job: hard-delete rows soft-deleted more than 90 days ago
var cutoff = DateTimeOffset.UtcNow.AddDays(-90);
await _context.Articles
    .IgnoreQueryFilters()
    .Where(a => a.IsDeleted && a.DeletedAt < cutoff)
    .ExecuteDeleteAsync(ct);

IgnoreQueryFilters() removes all registered filters for that query. If you need to bypass only the tenant filter but keep the soft-delete filter, the cleanest option is to replicate the soft-delete predicate inline in that specific query. Selective filter bypassing per-filter is not supported natively but is on the EF Core backlog.

Wrapping up

Global Query Filters let you encode cross-cutting data rules, soft delete, tenant scoping, publication gating, directly into the model. Every query that touches the entity inherits the filter automatically, and bypassing it is a deliberate, visible opt-out at the call site.

Next in this series: EF Core Relationships Explained, covering one-to-many, many-to-many with and without payload, and owned entities as value objects.

Got a question or ran into a problem? Drop a comment below and I will reply.

Pagination in .NET 8 Web API with EF Core and PostgreSQL: Offset vs. Keyset

2026-04-19T19:32:00.001+05:45

If you have added pagination to a .NET 8 API recently, you almost certainly reached for Skip and Take. They are familiar, they work, and for a table with a few thousand rows no one notices the cost. Add a few hundred thousand rows, point a load test at page 500, and watch what happens to your query times and CPU on the PostgreSQL host.

Offset pagination and keyset pagination both deliver pages of data, but they do it in fundamentally different ways with very different performance curves and API surface areas. This post compares them with real EF Core code against PostgreSQL so you can choose deliberately rather than by default.

You will walk away with working implementations of both approaches, an understanding of where each one breaks down, and a decision framework you can use immediately.

1 A quick history first

Offset pagination is as old as SQL itself. The OFFSET n ROWS FETCH NEXT m ROWS ONLY clause (or PostgreSQL's LIMIT m OFFSET n) instructs the database to scan through the first n rows and discard them before returning the next m. It is the natural fit for numbered pages and random access ("jump to page 47").

Keyset pagination, sometimes called cursor-based or seek-method pagination, avoids OFFSET entirely. Instead of telling the database how many rows to skip, the query filters to rows whose sort key is greater than (or less than) the last value seen on the previous page. The database never scans rows you do not need.

Both patterns sit on top of the same PostgreSQL B-tree index structures. The difference is how much of that index each query is forced to walk.

2 The same task, two ways

Both snippets retrieve a page of products ordered by Id. The offset version accepts a page number; the keyset version accepts the last Id seen on the previous page.

Offset pagination approach

// Request model
public record OffsetPageRequest(int Page = 1, int PageSize = 20);

// Response model
public record PagedResult<T>(List<T> Items, int TotalCount, int Page, int PageSize);

// EF Core query
public async Task<PagedResult<ProductSummary>> GetPageOffsetAsync(
    OffsetPageRequest request, CancellationToken ct)
{
    var query = _db.Products.OrderBy(p => p.Id);

    var totalCount = await query.CountAsync(ct);

    var items = await query
        .Skip((request.Page - 1) * request.PageSize)
        .Take(request.PageSize)
        .Select(p => new ProductSummary(p.Id, p.Name, p.Price))
        .ToListAsync(ct);

    return new PagedResult<ProductSummary>(items, totalCount, request.Page, request.PageSize);
}

Keyset pagination approach

// Request model — cursor replaces page number
public record KeysetPageRequest(int? AfterCursor, int PageSize = 20);

// Response model — includes next cursor
public record CursorPageResult<T>(List<T> Items, int? NextCursor, bool HasMore);

// EF Core query
public async Task<CursorPageResult<ProductSummary>> GetPageKeysetAsync(
    KeysetPageRequest request, CancellationToken ct)
{
    // Fetch one extra row to determine whether a next page exists
    var items = await _db.Products
        .Where(p => request.AfterCursor == null || p.Id > request.AfterCursor)
        .OrderBy(p => p.Id)
        .Take(request.PageSize + 1)
        .Select(p => new ProductSummary(p.Id, p.Name, p.Price))
        .ToListAsync(ct);

    var hasMore = items.Count > request.PageSize;
    if (hasMore) items.RemoveAt(items.Count - 1);

    var nextCursor = hasMore ? items[^1].Id : (int?)null;
    return new CursorPageResult<ProductSummary>(items, nextCursor, hasMore);
}

Notice that the keyset version fetches PageSize + 1 rows to detect the presence of a next page without a separate COUNT query. It also never needs to know how many total rows exist. These two differences are where most of the performance gap comes from.

3 The real trade-offs

1. Performance at scale

PostgreSQL must scan and discard the first n rows for every OFFSET n query. On a table with 500,000 rows and a page size of 20, page 500 requires PostgreSQL to read and discard 9,980 rows before returning the 20 you need. The work grows linearly with page depth. For an indexed sort key, the overhead is index scans rather than heap scans, but the work still accumulates.

Keyset pagination changes the access pattern entirely. The query is always WHERE id > $cursor ORDER BY id LIMIT 20. PostgreSQL uses an index range scan starting at $cursor and reads exactly 20 rows. Page depth becomes irrelevant because the cursor always puts the database at the right starting point in the index.

Does this matter to you? On tables under roughly 50,000 rows with well-cached indexes, the difference is rarely measurable in practice. On tables over 500,000 rows with deep page access, the difference can be one to two orders of magnitude. Measure against your actual data distribution before optimizing.

2. Consistency across pages

Offset pagination has a classic consistency problem: if a row is inserted or deleted between page 1 and page 2, page 2 will skip or duplicate a row. PostgreSQL offers no snapshot isolation at the query level for paginated reads without explicit transaction management.

Keyset pagination does not suffer from this problem in the typical case. Because it filters on a sort key value, insertions before the cursor position do not affect the next page, and deletions before the cursor position are simply absent. Deletions at or after the cursor can still cause a row to be skipped, but this is a much narrower window.

3. API surface complexity and client expectations

Offset pagination maps cleanly to what most API consumers expect: a page number, a page size, and a total count. Admin UIs, data export tools, and report generators need random access ("export page 47") that keyset pagination cannot satisfy without traversing all prior pages.

Keyset pagination trades random access for performance and consistency. Clients must store the cursor and send it back. "Jump to page 47" is not possible. This is an acceptable trade-off for feeds, infinite-scroll UIs, and high-volume API consumers, but it changes the client contract in a way that requires explicit buy-in.

4 Which one should you choose?

Match the pagination strategy to the access pattern your clients actually need, not to what is easiest to implement.

Choose offset pagination when:

Clients need random page access or user-controlled page numbers (admin dashboards, report generators)
The table is small or page depth rarely exceeds 20 to 30 pages in practice
Clients expect a total row count in the response for rendering pagination controls
The data is largely static and consistency skips between pages are not a concern

Choose keyset pagination when:

The table has more than 100,000 rows and clients may page deep into the result set
The use case is a feed, infinite scroll, or a sequential data sync
You need consistent pages despite concurrent inserts and deletes
You want to eliminate the cost of a separate COUNT(*) query on every page request

Both strategies can coexist in the same API. A product catalog endpoint used by an admin UI is a natural fit for offset pagination. An order history feed consumed by a mobile app is a natural fit for keyset. The controller implementation for each is independent.

// Composing both strategies on separate endpoints in the same controller
[HttpGet("admin")]
public Task<PagedResult<ProductSummary>> GetAdminPageAsync(
    [FromQuery] OffsetPageRequest req, CancellationToken ct) =>
    _paginationService.GetPageOffsetAsync(req, ct);

[HttpGet("feed")]
public Task<CursorPageResult<ProductSummary>> GetFeedAsync(
    [FromQuery] KeysetPageRequest req, CancellationToken ct) =>
    _paginationService.GetPageKeysetAsync(req, ct);

5 Quick reference summary

Concern	Offset pagination	Keyset pagination
PostgreSQL query cost at page 1	Low	Low
PostgreSQL query cost at page 500	High (scans and discards 9,980 rows)	Low (index seek at cursor position)
Random page access	Supported	Not supported
Total count in response	Requires a COUNT query	Not available without a COUNT query
Consistency under concurrent writes	Rows can be skipped or duplicated	Consistent for inserts before cursor
Client contract complexity	Simple: page number + page size	Moderate: cursor must be stored and sent
Best fit	Admin UIs, reports, static data	Feeds, infinite scroll, large tables

Final take

Offset pagination is the right default for most admin and reporting endpoints where tables are small, page depth is shallow, and clients expect page numbers. It is not the right default for any endpoint where a large table will be paged through sequentially or at high volume.

Reach for keyset pagination whenever the access pattern is sequential (feeds, syncs, infinite scroll) or whenever you measure offset queries degrading beyond acceptable latency thresholds under realistic data volumes. The cursor-based API contract is a minor client-side inconvenience that pays for itself quickly at scale.

What's next?

Add sorting flexibility to keyset: Multi-column keyset pagination (sorting by name, id rather than just id) requires a compound cursor. It is more complex but follows the same seek-method logic.
Benchmark on your data: Run both implementations against a table seeded with 500,000 rows and compare query plans with EXPLAIN ANALYZE. The index seek vs. index scan difference becomes visible immediately.
Explore EF Core compiled queries: For high-frequency pagination endpoints, wrapping keyset queries in EF Core compiled queries eliminates the per-request LINQ expression tree compilation overhead.

Got a question or a scenario I did not cover? Drop a comment below and I will reply.

Repository Pattern vs. Direct DbContext in a .NET 8 Controller API

2026-04-19T19:28:00.004+05:45

If you have started a .NET 8 API project recently, someone on your team has probably asked it: do we wrap EF Core in a Repository, or do we inject the DbContext directly into controllers? Both approaches are used in production codebases. Both have genuine trade-offs. And the debate often generates more heat than signal because the answer depends on the kind of application you are building.

This post examines both options with realistic code, identifies the actual trade-offs across testability, query expressiveness, and team ergonomics, and gives you a concrete decision framework so the conversation stays short next time.

You will walk away with working code for both patterns, a clear set of scenarios where each one earns its keep, and a summary table you can paste into a design doc.

1 A quick history first

The Repository pattern originates in Domain-Driven Design (Evans, 2003) and describes a collection-like abstraction over a domain's persistence layer. The pattern was widely adopted in .NET during the NHibernate and LINQ-to-SQL era, when data access code was genuinely hard to test in isolation.

Direct DbContext usage became the mainstream recommendation as EF Core matured. DbContext is already an abstraction over database access, DbSet already looks and behaves like a generic in-memory collection, and EF Core's in-memory and SQLite test providers make the DbContext itself testable without a Repository in front of it.

Both approaches build on the same EF Core engine. The difference is whether your controller sees IProductRepository or CatalogDbContext.

2 The same task, two ways

Both snippets fetch a filtered, projected list of products. The first uses a Repository interface; the second injects the DbContext directly.

Repository pattern approach

// Abstraction
public interface IProductRepository
{
    Task<List<ProductSummary>> GetPageAsync(
        string? category, int page, int pageSize, CancellationToken ct = default);
}

// Implementation
public class ProductRepository : IProductRepository
{
    private readonly CatalogDbContext _db;
    public ProductRepository(CatalogDbContext db) => _db = db;

    public Task<List<ProductSummary>> GetPageAsync(
        string? category, int page, int pageSize, CancellationToken ct) =>
        _db.Products
            .Where(p => category == null || p.Category == category)
            .OrderBy(p => p.Name)
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .Select(p => new ProductSummary(p.Id, p.Name, p.Price))
            .ToListAsync(ct);
}

// Controller
[ApiController, Route("api/products")]
public class ProductsController : ControllerBase
{
    private readonly IProductRepository _repo;
    public ProductsController(IProductRepository repo) => _repo = repo;

    [HttpGet]
    public Task<List<ProductSummary>> GetAsync(
        [FromQuery] string? category, [FromQuery] int page = 1,
        [FromQuery] int pageSize = 20, CancellationToken ct = default) =>
        _repo.GetPageAsync(category, page, pageSize, ct);
}

Direct DbContext approach

// Controller — DbContext injected directly
[ApiController, Route("api/products")]
public class ProductsController : ControllerBase
{
    private readonly CatalogDbContext _db;
    public ProductsController(CatalogDbContext db) => _db = db;

    [HttpGet]
    public Task<List<ProductSummary>> GetAsync(
        [FromQuery] string? category, [FromQuery] int page = 1,
        [FromQuery] int pageSize = 20, CancellationToken ct = default) =>
        _db.Products
            .Where(p => category == null || p.Category == category)
            .OrderBy(p => p.Name)
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .Select(p => new ProductSummary(p.Id, p.Name, p.Price))
            .ToListAsync(ct);
}

The query logic is identical. The difference is where it lives (a dedicated class vs. the controller action) and what the controller declares as its dependency.

3 The real trade-offs

1. Testability

This is the most cited reason for adding a Repository, but it no longer holds the weight it once did. EF Core ships with an in-memory provider (UseInMemoryDatabase) and integrates cleanly with SQLite in-memory mode for tests that need relational behavior. Both options let you test controller logic without a real database and without mocking an interface.

That said, the Repository interface is still the right choice when you need to test a service class in complete isolation, without spinning up any EF Core infrastructure at all. Mocking IProductRepository takes three lines; setting up an in-memory DbContext takes more.

2. Query expressiveness and the leaky abstraction problem

The Repository pattern works cleanly for simple CRUD. It starts to buckle when requirements grow. Callers start asking for GetByIdWithDetailsAsync, GetPageWithCategoryFilterAsync, GetCountByCategoryAsync, and before long the interface has fifteen methods. The alternative is to return IQueryable<T> from the Repository, which leaks the EF Core abstraction straight through the layer the Repository was supposed to hide.

Direct DbContext usage sidesteps this entirely. The controller or service composes the query inline, using the full EF Core query API including Include, AsSplitQuery, ExecuteUpdateAsync, and PostgreSQL-specific operators exposed by Npgsql.

// A query that a Repository interface handles awkwardly
var result = await _db.Orders
    .AsSplitQuery()
    .Include(o => o.Lines)
    .Where(o => o.Status == OrderStatus.Pending
             && o.PlacedAt < DateTimeOffset.UtcNow.AddDays(-7))
    .OrderByDescending(o => o.PlacedAt)
    .Select(o => new StaleOrderDto(o.Id, o.CustomerEmail, o.Lines.Count))
    .ToListAsync(ct);

3. Code volume, indirection, and onboarding cost

For a ten-entity API, the Repository pattern adds ten interfaces, ten implementation classes, and ten DI registrations. That is not inherently bad, but it is cost that needs to deliver proportional value. On a team of two or three developers building an internal API, the indirection often obscures more than it clarifies. On a larger team with strict bounded-context boundaries, the same interfaces become a meaningful contract between feature teams.

4 Which one should you choose?

Use the context of your project, not a blanket rule, to make the call.

Choose the Repository pattern when:

Your domain model is complex and you are applying DDD; repositories map to aggregate roots
You need to swap data sources (for example, a cache-backed implementation alongside a database-backed one)
Your team writes service classes with many dependencies and values the ability to mock them in unit tests without any EF Core setup
Multiple bounded contexts share entity types but need different persistence strategies per context

Choose direct DbContext when:

You are building a CRUD-heavy or query-heavy API where queries vary widely per endpoint
Your team is small or the API is a single-bounded-context service
You want full access to EF Core features without working around interface boundaries
You use integration tests with a real (or SQLite in-memory) database and do not need interface mocks

A practical middle ground is to keep DbContext in controllers for most operations, but extract a dedicated service class for any business operation that is called from more than one place. That service class can own a partial Repository interface scoped to only the methods it actually needs, rather than a complete repository per entity.

// A scoped service that encapsulates one business operation
// and exposes a minimal interface for testing
public interface IOrderFulfillmentService
{
    Task FulfillAsync(int orderId, CancellationToken ct = default);
}

public class OrderFulfillmentService : IOrderFulfillmentService
{
    private readonly CatalogDbContext _db;
    public OrderFulfillmentService(CatalogDbContext db) => _db = db;

    public async Task FulfillAsync(int orderId, CancellationToken ct)
    {
        var order = await _db.Orders
            .Include(o => o.Lines)
            .FirstOrDefaultAsync(o => o.Id == orderId, ct)
            ?? throw new InvalidOperationException($"Order {orderId} not found.");

        order.Status = OrderStatus.Shipped;
        order.FulfilledAt = DateTimeOffset.UtcNow;
        await _db.SaveChangesAsync(ct);
    }
}

5 Quick reference summary

Concern	Repository pattern	Direct DbContext
Unit testability	Easy to mock with any mocking library	Requires in-memory or SQLite provider setup
Integration testability	Works; no advantage over direct DbContext	Clean with EF Core test helpers
Query flexibility	Constrained by interface shape; IQueryable leaks	Full EF Core API available inline
Code volume	High: interfaces, impls, DI registrations	Low: one class, one dependency
DDD fit	Natural; maps to aggregate root access	Awkward for complex domain models
EF Core feature access	Limited without leaking IQueryable	Full, including bulk operations
Multi-source support	Straightforward via implementation swap	Requires refactoring to add a second source

Final take

The Repository pattern solves a real problem. That problem is not "how do I use EF Core." It is "how do I isolate domain logic from any particular persistence technology." If you are not building for that isolation, the pattern adds cost without delivering its core benefit.

Lean on direct DbContext injection for API projects that are CRUD-heavy, single-context, or built by small teams. Reach for repositories when your domain model is complex, your team practices DDD, or you have a genuine need to swap persistence strategies per context.

What's next?

Read the EF Core testing documentation: The official docs cover the SQLite in-memory approach and its trade-offs against full integration tests with a real database.
Explore the next post in this series: Pagination in a .NET 8 Web API with EF Core and PostgreSQL, covering offset-based and keyset-based approaches and when each one breaks down at scale.
Try the scoped service pattern: Extract one business operation from a controller into a dedicated service class and write a unit test against its interface. It is a useful calibration exercise for deciding where interface boundaries add real value.

Got a question or a scenario I did not cover? Drop a comment below and I will reply.

EF Core Migrations with PostgreSQL: Patterns, Pitfalls, and CI-Safe Deployments

2026-04-19T19:25:00.000+05:45

EF Core migrations feel straightforward until you add PostgreSQL to the picture. Enum type management, case-sensitive identifiers, schema-qualified objects, and the question of who runs dotnet ef database update in production all demand answers before your first deployment. Getting those answers wrong produces corrupted migration histories, failed releases, and schema drift that is hard to untangle.

This post covers how EF Core migrations work with Npgsql under the hood, the pitfalls that are specific to PostgreSQL, and the patterns that make migration-based deployments safe to run in CI/CD pipelines without surprises.

1 Prerequisites

Before starting, make sure you have:

A .NET 8 Web API project with Npgsql.EntityFrameworkCore.PostgreSQL 8.x installed
EF Core CLI tool: dotnet tool install --global dotnet-ef
A working DbContext with at least one entity and an existing migration history

Note: The migration patterns in this post apply whether you are using controllers or minimal APIs. The deployment strategies apply to any hosting model: Azure App Service, Docker containers, or bare metal.

2 How EF Core migrations work with Npgsql

When you run dotnet ef migrations add MigrationName, EF Core compares your current model against the compiled model snapshot stored in Migrations/CatalogDbContextModelSnapshot.cs. The difference produces a new migration file with Up and Down methods. Npgsql translates those method calls into PostgreSQL-compatible SQL when you apply the migration.

// A generated migration file (simplified)
public partial class AddProductCategory : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "category",
            table: "products",
            type: "character varying(64)",
            maxLength: 64,
            nullable: false,
            defaultValue: "");

        migrationBuilder.CreateIndex(
            name: "ix_products_category",
            table: "products",
            column: "category");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(
            name: "ix_products_category",
            table: "products");

        migrationBuilder.DropColumn(
            name: "category",
            table: "products");
    }
}

EF Core records each applied migration in __EFMigrationsHistory. Before running any migration, it checks this table and skips migrations already present. This is why the history table is the source of truth for schema state, not the migration files themselves.

3 Naming conventions and organizing migration files

Use descriptive names that make the migration's intent clear from the file listing alone. Avoid generic names like Update1 or Fix; they are meaningless in a git blame six months later.

// Good migration names
dotnet ef migrations add AddOrdersAndOrderLines
dotnet ef migrations add AddProductCategoryIndex
dotnet ef migrations add RenameCustomerEmailToContactEmail
dotnet ef migrations add SeedDefaultRoles

For large teams where multiple feature branches produce migrations in parallel, each developer should generate migrations against a local database and rebase before merging. Two migrations generated from the same snapshot base will conflict in the snapshot file. Rebasing one branch on top of the other and regenerating its migration resolves the conflict cleanly.

Tip: Keep migrations small and focused on a single schema concern. A migration that adds three tables, renames two columns, and seeds reference data is hard to roll back safely. Splitting it into two or three migrations costs almost nothing and gives you meaningful rollback points.

4 PostgreSQL-specific pitfalls

Several PostgreSQL behaviors catch developers off guard after working primarily with SQL Server.

1. Case sensitivity and identifier quoting

PostgreSQL folds unquoted identifiers to lowercase. EF Core, by default, generates table and column names in PascalCase (for example, "Products" and "CreatedAt"). Npgsql quotes these identifiers in the generated DDL, which means your schema works but every query requires quoted names. If a developer runs a raw query without quoting, it silently targets different names.

The fix is to establish snake_case naming in your entity configurations using ToTable and HasColumnName, or to apply a global naming convention through a model convention class.

// Global snake_case convention using EF Core 8 model-building conventions
public class SnakeCaseNamingConvention : IModelFinalizingConvention
{
    public void ProcessModelFinalizing(
        IConventionModelBuilder builder,
        IConventionContext<IConventionModelBuilder> context)
    {
        foreach (var entity in builder.Metadata.GetEntityTypes())
        {
            entity.SetTableName(ToSnakeCase(entity.GetTableName()!));

            foreach (var property in entity.GetProperties())
                property.SetColumnName(ToSnakeCase(property.GetColumnName()!));

            foreach (var key in entity.GetKeys())
                key.SetName(ToSnakeCase(key.GetName()!));

            foreach (var index in entity.GetIndexes())
                index.SetDatabaseName(ToSnakeCase(index.GetDatabaseName()!));

            foreach (var fk in entity.GetForeignKeys())
                fk.SetConstraintName(ToSnakeCase(fk.GetConstraintName()!));
        }
    }

    private static string ToSnakeCase(string name) =>
        System.Text.RegularExpressions.Regex
            .Replace(name, "(?<=[a-z0-9])([A-Z])", "_$1")
            .ToLower();
}

// Register in DbContext
protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    configurationBuilder.Conventions.Add(_ => new SnakeCaseNamingConvention());
}

2. Enum types

Npgsql supports PostgreSQL-native enum types through MapEnum, but they require extra steps in migrations. When you rename a value or drop an enum type, PostgreSQL requires the column referencing it to be migrated first. The simpler approach for most APIs is to store enums as text using HasConversion<string>(). Reserve native enums for cases where you need database-level type checking or PostgreSQL functions that operate on them.

3. Schema-qualified tables

If your PostgreSQL database uses multiple schemas (for example, catalog and orders), set the default schema on the DbContext rather than qualifying every table name individually.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema("catalog");
    modelBuilder.ApplyConfigurationsFromAssembly(typeof(CatalogDbContext).Assembly);
}

EF Core includes the schema in all generated DDL and in __EFMigrationsHistory. If you change the default schema after migrations exist, generate a new migration; EF Core will emit the necessary MoveTable calls.

5 Applying migrations safely in CI/CD

There are two mainstream strategies for applying EF Core migrations in a pipeline. Choose based on how much schema downtime you can tolerate.

Strategy A: Apply at application startup with MigrateAsync

This approach is simple and works well for small teams and staging environments. The application applies any pending migrations when it starts, before accepting traffic.

// Program.cs  - apply migrations on startup
var app = builder.Build();

using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<CatalogDbContext>();
    await db.Database.MigrateAsync();
}

app.UseAuthorization();
app.MapControllers();
app.Run();

The downside is that if two instances start simultaneously (common in rolling deployments), both will try to acquire a migration lock. Npgsql handles this gracefully with an advisory lock, but the startup time increases proportionally to the number of pending migrations.

Strategy B: Dedicated migration step in the pipeline

For production deployments, run migrations as a separate pipeline step before the new application version is deployed. This decouples schema changes from application restarts and gives you a clear gate to stop the pipeline if the migration fails.

// A minimal migration runner console app or a separate entrypoint
// Add this to your csproj: <ItemGroup><ProjectReference Include="..\Catalog.Api\Catalog.Api.csproj" /></ItemGroup>

using Catalog.Api.Data;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;

var services = new ServiceCollection();
services.AddDbContext<CatalogDbContext>(options =>
    options.UseNpgsql(Environment.GetEnvironmentVariable("ConnectionStrings__Catalog")));

var provider = services.BuildServiceProvider();
using var scope = provider.CreateScope();
var db = scope.ServiceProvider.GetRequiredService<CatalogDbContext>();
await db.Database.MigrateAsync();

Console.WriteLine("Migrations applied.");

Tip: In a Docker-based pipeline, build a separate migration image from your project and run it as an init container or a pipeline job step before rolling out the main API containers. The migration container exits after applying changes; your API containers never need database admin credentials at runtime.

6 Generating and reviewing idempotent SQL scripts

Before any production deployment, generate an idempotent SQL script and review it manually (or have a DBA review it). An idempotent script checks __EFMigrationsHistory before applying each migration, so it is safe to run multiple times without duplicating changes.

dotnet ef migrations script --idempotent --output ./deploy/migrations.sql

The generated file contains one SQL block per migration, wrapped in an existence check against the history table. Commit this file to source control alongside your migration files so reviewers can see the exact DDL that will run in production without needing a local EF Core toolchain.

-- Example of idempotent guard generated by EF Core
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240315120000_AddOrdersAndOrderLines')
BEGIN
    CREATE TABLE orders ( ... );
    INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
    VALUES ('20240315120000_AddOrdersAndOrderLines', '8.0.0');
END;

Note: For PostgreSQL, EF Core emits DO $$ BEGIN ... END $$ blocks instead of IF NOT EXISTS syntax. The idempotency logic is functionally equivalent; the exact syntax depends on the provider.

Wrapping up

You now have a clear picture of how EF Core migrations interact with PostgreSQL, where the common pitfalls are (case sensitivity, enum types, schema qualification), and two deployment strategies to choose from based on your team's needs. The idempotent script workflow gives you a reviewable, repeatable artifact for every deployment.

The next post in this series tackles a question that comes up on almost every API project: should you wrap your DbContext in a Repository pattern, or use it directly in your controllers? Both approaches are defensible and the right answer depends on factors that are worth examining carefully.

Got a question or ran into a problem? Drop a comment below and I will reply.

Designing Your DbContext and Entity Models for PostgreSQL in .NET 8

2026-04-19T19:18:00.001+05:45

A first-draft EF Core entity is easy to write and easy to regret six months later. Column type mismatches, missing indexes, and a DbContext clogged with hundreds of lines of Fluent API calls are all fixable problems, but they are far cheaper to avoid upfront. This post covers the model design patterns that hold up well when PostgreSQL is your target database.

By the end you will have a DbContext organized with IEntityTypeConfiguration classes, column types chosen deliberately for PostgreSQL semantics, and indexes defined in the right layer of the application.

1 Prerequisites

Before starting, make sure you have:

The project from the first post in this series, or any .NET 8 Web API with Npgsql.EntityFrameworkCore.PostgreSQL installed
EF Core CLI tool: dotnet tool install --global dotnet-ef
Basic familiarity with EF Core Fluent API conventions

Note: All examples target .NET 8 with nullable reference types enabled and Npgsql.EntityFrameworkCore.PostgreSQL 8.x. The LegacyTimestampBehavior AppContext switch is assumed to be off, which is the default for new projects.

2 What EF Core infers by convention

Understanding the convention defaults lets you apply Fluent API only where you need to override them, which keeps configuration classes focused.

// A convention-friendly pair of entities
public class Order
{
    public int Id { get; set; }               // PK inferred from "Id"
    public string CustomerEmail { get; set; } = string.Empty;
    public decimal Total { get; set; }
    public OrderStatus Status { get; set; }
    public DateTimeOffset PlacedAt { get; set; }
    public List<OrderLine> Lines { get; set; } = [];
}

public class OrderLine
{
    public int Id { get; set; }
    public int OrderId { get; set; }          // FK inferred from nav + "TypeNameId" pattern
    public Order Order { get; set; } = null!;
    public string Sku { get; set; } = string.Empty;
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
}

public enum OrderStatus { Pending, Confirmed, Shipped, Cancelled }

EF Core infers the primary key from a property named Id or <TypeName>Id. It infers a foreign key when a navigation property and a matching <TypeName>Id shadow property exist on the dependent entity. String properties without HasMaxLength become text columns in PostgreSQL, which is valid but worth making explicit in any column that benefits from a database-enforced length constraint.

3 IEntityTypeConfiguration: one class per entity

Rather than putting all Fluent API calls in OnModelCreating, implement IEntityTypeConfiguration<T> in a dedicated class per entity. Each class lives in a Data/Configurations folder and is discovered automatically through assembly scanning.

// Data/Configurations/OrderConfiguration.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.ToTable("orders");

        builder.Property(o => o.CustomerEmail)
            .IsRequired()
            .HasMaxLength(256);

        builder.Property(o => o.Total)
            .HasColumnType("numeric(18,4)");

        builder.Property(o => o.Status)
            .HasConversion<string>()
            .HasMaxLength(32);

        builder.Property(o => o.PlacedAt)
            .HasColumnType("timestamp with time zone");

        builder.HasIndex(o => o.CustomerEmail)
            .HasDatabaseName("ix_orders_customer_email");
    }
}

Storing the enum as a string with HasConversion<string>() makes the column readable without a lookup table and keeps migrations straightforward. If you prefer PostgreSQL-native enum types, Npgsql supports them via MapEnum, but that approach carries extra migration discipline that the next post covers in detail.

OrderLine configuration

// Data/Configurations/OrderLineConfiguration.cs
public class OrderLineConfiguration : IEntityTypeConfiguration<OrderLine>
{
    public void Configure(EntityTypeBuilder<OrderLine> builder)
    {
        builder.ToTable("order_lines");

        builder.Property(l => l.Sku)
            .IsRequired()
            .HasMaxLength(64);

        builder.Property(l => l.UnitPrice)
            .HasColumnType("numeric(18,4)");

        builder.HasIndex(l => new { l.OrderId, l.Sku })
            .HasDatabaseName("ix_order_lines_order_sku");

        builder.HasOne(l => l.Order)
            .WithMany(o => o.Lines)
            .HasForeignKey(l => l.OrderId)
            .OnDelete(DeleteBehavior.Cascade);
    }
}

4 Register configurations via assembly scan

Call ApplyConfigurationsFromAssembly in OnModelCreating to pick up every IEntityTypeConfiguration<T> class in the assembly automatically.

// Data/CatalogDbContext.cs
using Microsoft.EntityFrameworkCore;

public class CatalogDbContext : DbContext
{
    public CatalogDbContext(DbContextOptions<CatalogDbContext> options)
        : base(options) { }

    public DbSet<Order> Orders => Set<Order>();
    public DbSet<OrderLine> OrderLines => Set<OrderLine>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(CatalogDbContext).Assembly);
    }
}

Adding a new entity now requires only creating a new configuration class. The DbContext itself stays unchanged. This is the single biggest quality-of-life improvement you can make in a DbContext that will grow over time.

5 PostgreSQL type mappings worth being explicit about

Npgsql provides sensible defaults, but a few mappings need an explicit call to behave correctly in production.

Decimal precision

EF Core maps decimal to numeric but does not set precision and scale unless you tell it to. Without an explicit HasColumnType("numeric(p,s)"), the generated column uses the database's default precision, which can cause silent rounding. Always set precision and scale for money or measurement columns.

DateTime vs DateTimeOffset

With LegacyTimestampBehavior off (the default in new .NET 8 projects), Npgsql maps DateTime only if it is UTC-kind and emits a compile-time warning for anything else. Use DateTimeOffset for timestamps that cross time zones and map them to timestamp with time zone explicitly in your configuration.

text vs character varying

In PostgreSQL, text and varchar have identical storage and performance. Use text for open-ended string columns and call HasMaxLength only where you want the database to enforce a maximum length.

jsonb columns

// Mapping a metadata bag to jsonb
builder.Property(o => o.Metadata)
    .HasColumnType("jsonb")
    .HasDefaultValueSql("'{}'");

Npgsql can serialize and deserialize .NET types directly to and from jsonb. Indexing a jsonb column with a GIN index is possible through raw SQL in a migration, which the next post demonstrates.

6 Verify with a migration

Generate the migration and inspect the SQL before applying it to any shared environment.

dotnet ef migrations add AddOrdersSchema
dotnet ef migrations script --idempotent

In the generated script, check that every decimal column shows the correct numeric(p,s), that all timestamp columns are timestamp with time zone, and that your named indexes appear with the names you defined. Once satisfied, apply with dotnet ef database update.

Tip: Table names in these examples use lowercase snake_case (orders, order_lines). PostgreSQL folds unquoted identifiers to lowercase, so using snake_case in your Fluent API avoids the quoting gymnastics that arise when EF Core generates PascalCase table names by default.

Wrapping up

You now have a DbContext built around per-entity IEntityTypeConfiguration classes, column types locked in deliberately for PostgreSQL, and a consistent naming strategy that avoids identifier quoting issues at runtime.

The next post goes deeper into migrations: how EF Core and Npgsql generate them, the pitfalls specific to PostgreSQL (enum types, schema-qualified names, case sensitivity), and how to apply migrations safely in a CI/CD pipeline without blocking deployments or corrupting the migration history.

Got a question or ran into a problem? Drop a comment below and I will reply.