I spend most of my day in Claude Code these days. Most of my development processes changed after having access to my own personal assistant. In this post, I'll show you a real-life example of how bug fixes are often done on elmah.io now. I hope it will inspire someone to optimize their workflow and get even more out of their elmah.io subscription.

Monitoring elmah.io itself and fixing bugs when errors occur are a large part of keeping elmah.io's quality high. We use various ways to monitor that everything is working as intended, the big one being elmah.io itself. We receive new errors from the various websites and services through elmah.io and a shared Slack channel. In the old days, fixing errors was mostly a manual task, where only the build and publish part were fully automated. We would look up the error manually through the elmah.io UI and inspect the various pieces of information, trying to understand what was going wrong. Alongside the Source Code window in elmah.io, we would have Visual Studio open with the solution containing the failing code.

With the release of the elmah.io MCP server, this changed towards a much more automated approach. If you haven't run into MCP yet, the short version is that it's a protocol that lets AI tools like Claude Code call out to external services in a structured, secure way. Instead of me copy-pasting a stack trace into a chat window and asking "what does this mean", Claude Code can now go and look at the actual error itself, plus the uptime checks, heartbeats, and deployments around it. It's less "ask an AI about your errors" and more "give your AI tool the same access to elmah.io that you have". Let's try to set it up.

Connecting Claude Code to elmah.io

You'll need Claude Code installed and an elmah.io account, and everything happens from the terminal, so there's no config file to hand-edit and no API key to generate and paste in somewhere. API keys are also supported on the MCP server, but for this post, we'll use the UI to sign in.

First, register the MCP server:

This just tells Claude Code that a server called elmahio exists and where to find it. It doesn't authenticate anything yet. To do that, launch Claude Code and run /mcp:

You'll see a list of configured MCP servers, and elmahio should show up with a status of "needs authentication". Select it and click Authenticate, which opens a browser window with a normal OAuth login screen:

Sign in with your elmah.io account like you would on app.elmah.io, approve the request, and you're done. Claude Code doesn't see your password, just a token scoped to your account.

To make sure it actually worked, run /mcp list inside Claude Code:

Check that elmahio shows up as "connected". If it doesn't, the most common issue in my experience is that the OAuth flow got closed before finishing. Running /mcp again and re-authenticating should fix it.

If you'd rather not write your own prompts and just want a working setup out of the box, there's also an AI Plugin that installs the MCP server together with a handful of ready-made skills and a background monitoring agent. I'll stick to the raw MCP setup here, since that's what most of the examples below build on.

What a real fix looks like end to end

Rather than list out the individual things you can ask, let's stick to the scenario specified in the beginning: receiving a new error notification. It usually starts with a Slack message or an email from elmah.io saying a new error was just logged in production. Instead of opening a browser tab, I switch to Claude Code, which already has the MCP server connected. I'll open Claude Code in the base directory containing subfolders for all of elmah.io's sites and services. Then ask it to fetch the error details:

The first time you do this, Claude Code will ask the elmah.io MCP for a list of logs you have access to, to try and find the right log. After running through this workflow for each individual project and log, Claude will remember which log belongs to which project. You can also tell Claude the mapping between folders and log IDs up front and have it save it to memory.

Claude goes and fetches the actual message, stack trace, URL, and whatever custom data got attached. The same thing I'd otherwise be looking at on app.elmah.io.

From there I'll usually just ask it to try and fix the error:

Because Claude Code already has the codebase open, it has both the error and the code that caused it in the same context. It can go find the line the stack trace points to, work out why it's throwing, and make the change. This is the part that still feels a little unreal to type out, but it's genuinely how it goes most of the time now. I'm reading a diff instead of writing one.

Once I'm happy with the fix, I'll have it commit and push, the same way I would with any other change it makes for me. No copy-pasting a stack trace into a chat window, no switching context to go write the fix myself. The whole loop from "here's an error" to "here's a pushed commit" happens in one conversation. I always do a code review of the changes and often still launch the project locally to check that everything is working as expected. In many cases, testing on our staging environment is just as good. Claude also extends or writes a new unit test to verify the fix when it makes sense. And using the Playwright MCP server, I often let it take the application for a spin in the browser.

The part I think people skip when they talk about AI fixing bugs is what happens after it's live. That's where I use elmah.io's MCP server just as much. Once the fix has deployed, I'll go back to the same Claude Code session and ask it to monitor if new errors are being logged:

If it's a web app, I'll also ask it to check the uptime checks for that service:

This is to make sure the deploy itself didn't break anything unrelated. If it's a background service instead, like a scheduled job, Azure Function, or something without a URL to ping, I'll ask about heartbeats instead, since that's how we track whether those are still checking in on schedule:

That follow-up step is a small thing, but it's the one that made this feel trustworthy enough to actually use for real fixes.

Conclusion

We build the range of dashboards on app.elmah.io because sometimes, that's the right interface for looking at errors. But the better AI clients are becoming, a good chunk of the time I actually want to look at an error, I'm already in Claude Code doing something else. Switching tools costs more than the two seconds it sounds like. Having elmah.io reachable from the same place I'm already working is a small thing, but it's the kind of small thing that changes how often you actually check on your errors instead of waiting for them to page you.

In this post, I showed a simple example where Claude Code was able to fix everything from just looking at the error and the codebase. Of course, in real life, it's a cooperation between me as the developer who knows a lot of details about the elmah.io code base and the AI. Simple bugs can be fixed fast by the AI, while more complex issues require more input from me.

If you want to try it yourself, the docs page has the same setup steps in reference form, along with the equivalent instructions for Claude Desktop and other MCP-compatible tools.

As much as the web is evolving, cyber threats are also growing. Securing user details with a single password may not be enough to stop bad guys. Modern applications require stronger authentication. Two-factor authentication (2FA) and passkeys are two effective ways to apply efficient login. In today's post, I will walk you through how to implement 2FA and passkeys in an ASP.NET Core API. Well, that is going to be a long ride, so stay with me.

What is Two-Factor Authentication (2FA)?

Two-Factor Authentication is a security method that requires a user to provide two forms of identity verification. Unlike the traditional password login, 2FA requires users to confirm their identity using two distinct factors. Its 2-step authentication prevents unauthorized access even if the password is compromised.

What is Passkey?

A passkey is a highly secure, passwordless authentication method that provides a simple and secure way to sign in to applications. Instead of password authentication, it uses public-key cryptography. The private key stays securely on the user's device and is unlocked using biometrics (Face ID, fingerprint) or device PIN. Passkeys are based on the WebAuthn (Web Authentication API) standard and follow the FIDO2 specification.

Implementing 2FA and Passkey in ASP.NET Core .NET 10 API

I will go with the same project to implement both authentication methods. The following steps are shared between both types.

Step 1: Create a project

dotnet new web -n TwoFaNET10

Step 2: Add Swagger

.NET 10 does not contain Swagger by default. However, I will use it for testing the API.

dotnet add package Swashbuckle.AspNetCore 

if (app.Environment.IsDevelopment())
{
    app.MapOpenApi();
    app.UseSwaggerUI(options =>
    {
        options.SwaggerEndpoint("/openapi/v1.json", "v1");
    });
}

Two-factor authentication in .NET 10

Step 1: Install required packages

dotnet add package Microsoft.AspNetCore.Identity.EntityFrameworkCore
dotnet add package Microsoft.AspNetCore.Identity.UI
dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

Step 2: Create models

using Microsoft.AspNetCore.Identity;

namespace TwoFaNET10.Models;

public class ApplicationUser : IdentityUser
{
    public string? FirstName { get; set; }
    public string? LastName { get; set; }

    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;

    public string FullName =>
        $"{FirstName} {LastName}".Trim();
}

IdentityUser is provided by AspNetCore's Identity namespace. Here I am creating the sub-class ApplicationUser with IdentityUser as its parent. It is a built-in user model containing necessary fields such as Email and UserName. With Microsoft's own user class, I don't have to implement password hashing, login, registration, or other authentication operations.

DTO for registering a user

public class RegisterDtoInp
{
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string Password { get; set; } = string.Empty;
}

Login model

public class LoginDtoInp
{
    public string Email { get; set; } = string.Empty;
    public string Password { get; set; } = string.Empty;
    public bool RememberMe { get; set; } 
}

TwoFactorLoginDtoInp

public class TwoFactorLoginDtoInp
{
    public string TwoFactorCode { get; set; } = string.Empty;
    public bool RememberMe { get; set; }
    public bool RememberMachine { get; set; } 
}

Model for recovery of 2FA.

public class RecoveryCodeLoginDtoInp
{
    public string RecoveryCode { get; set; } = string.Empty;
}

Step 3: Create the Data context

using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using TwoFaNET10.Models;

namespace TwoFaNET10.Data;

public class ApplicationDbContext: IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
    }
}

By inheriting from IdentityDbContext<ApplicationUser>, I am leveraging the Identity namespace again to manage Identity tables using ApplicationUser.

Step 4: Add Database connection details

In appsettings.json add:

"ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Port=5432;Database=TwoFaDb;Username=postgres;Password=pass"
},

In Program.cs:

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseNpgsql(connectionString));

Step 5: Define Controller for the 2FA endpoints

We injected Identity's classes SignInManager and UserManager into the controller. As their names indicate, the former handles authentication operations, while the latter abstracts away users' methods.

[HttpPost("register")]
public async Task<IActionResult> Register([FromBody] RegisterDtoInp model)
{
    if (!ModelState.IsValid)
        return ValidationProblem(ModelState);

    var user = new ApplicationUser
    {
        UserName = model.Email,
        Email = model.Email,
        FirstName = model.FirstName,
        LastName = model.LastName,
        CreatedAt = DateTime.UtcNow
    };

    var result = await userManager.CreateAsync(user, model.Password);

    if (result.Succeeded)
    {
        logger.LogInformation("New user registered: {Email}", model.Email);

        return Ok(new
        {
            Success = true,
            Email = model.Email,
            Message = "User registered successfully."
        });
    }

    return BadRequest(result.Errors.Select(x => x.Description));
}

userManager's CreateAsync method handles all the user creation abstraction itself. Registration includes validating and hashing the password and saving the user, all within a single method.

[HttpPost("login")]
public async Task<IActionResult> Login([FromBody] LoginDtoInp model)
{
    if (!ModelState.IsValid)
        return ValidationProblem(ModelState);

    var result = await signInManager.PasswordSignInAsync(
        model.Email,
        model.Password,
        model.RememberMe,
        lockoutOnFailure: true);

    if (result.Succeeded)
    {
        logger.LogInformation("User {Email} logged in.", model.Email);

        return Ok(new
        {
            Success = true,
            Message = "Login successful."
        });
    }

    if (result.RequiresTwoFactor)
    {
        return Ok(new
        {
            RequiresTwoFactor = true,
            RememberMe = model.RememberMe
        });
    }

    if (result.IsLockedOut)
    {
        return BadRequest(new
        {
            Message = "Account locked out due to multiple failed attempts. Try again later."
        });
    }

    return Unauthorized(new
    {
        Message = "Invalid email or password."
    });
}

Again, we don't need to peek inside the PasswordSignInAsync. Surprisingly, it will handle login regardless of whether 2FA is enabled. If 5 login attempts go wrong, Identity will lock the account for 5 minutes.

[Authorize]
[HttpGet("2fa/setup-uri")]
public async Task<IActionResult> Get2FaUri()
{
    var user = await userManager.GetUserAsync(User);

    if (user == null)
        return Unauthorized();

    var key = await userManager.GetAuthenticatorKeyAsync(user);

    if (string.IsNullOrEmpty(key))
    {
        await userManager.ResetAuthenticatorKeyAsync(user);
        key = await userManager.GetAuthenticatorKeyAsync(user);
    }

    var email = user.Email;

    var uri = $"otpauth://totp/TwoFaNET10:{email}?secret={key}&issuer=TwoFaNET10&digits=6";

    return Ok(new
    {
        qrUri = uri,
        secret = key
    });
}

This endpoint generates the secret key and QR Code URI that the user scans in their authenticator app. The action is authorized, and the user must be logged in to use this endpoint. First, await userManager.GetUserAsync(User) gets the current user. Again, using userManager to get the user authenticator secret from GetAuthenticatorKeyAsync. If the user does not have one, then ResetAuthenticatorKeyAsync creates a new one. Next, you get a URI:

$"otpauth://totp/TwoFaNET10:{email}secret{key}&issuer=TwoFaNET10
&digits=6";

This qrUri can be used by a library or any other QR code generator to create a QR code.

[Authorize]
[HttpPost("2fa/enable")]
public async Task<IActionResult> Enable2FA([FromBody] string code)
{
    var user = await userManager.GetUserAsync(User);

    if (user == null)
        return Unauthorized();

    var result = await userManager.VerifyTwoFactorTokenAsync(
        user,
        TokenOptions.DefaultAuthenticatorProvider,
        code);

    if (!result)
        return BadRequest("Invalid code");

    await userManager.SetTwoFactorEnabledAsync(user, true);

    return Ok(new
    {
        Success = true,
        Message = "2FA enabled"
    });
}

The enable endpoint is responsible for verifying the QR code. After the user scans the QR code, it will take the 6-digit code you got from the authenticator app. VerifyTwoFactorTokenAsync will check that the code is valid, while the SetTwoFactorEnabledAsync method will enable it for the user. Hence, 2FA is now enabled, and on your next login, ASP.NET Core Identity will prompt you for the code.

[HttpPost("2fa/login")]
public async Task<IActionResult> TwoFactorLogin([FromBody] TwoFactorLoginDtoInp model)
{
    if (!ModelState.IsValid)
        return ValidationProblem(ModelState);

    var user = await signInManager.GetTwoFactorAuthenticationUserAsync();

    if (user is null)
        return Unauthorized(new
        {
            Message = "2FA session expired."
        });

    var code = model.TwoFactorCode
        .Replace(" ", "")
        .Replace("-", "");

    var result = await signInManager.TwoFactorAuthenticatorSignInAsync(
        code,
        model.RememberMe,
        model.RememberMachine);

    if (result.Succeeded)
    {
        logger.LogInformation("User {Id} logged in with 2FA.", user.Id);

        return Ok(new
        {
            Success = true,
            Message = "2FA login successful."
        });
    }

    if (result.IsLockedOut)
    {
        return BadRequest(new
        {
            Message = "Account locked out."
        });
    }

    return Unauthorized(new
    {
        Message = "Invalid authenticator code."
    });
}

In the chronology, next is the login method. GetTwoFactorAuthenticationUserAsync will keep the user after verifying the password while waiting for the one-time password (OTP). I kept the OTP code flexible by allowing spaces or '-' before verifying it with TwoFactorAuthenticatorSignInAsync. Identity will verify the incoming code against the stored secret to authenticate the sign-in. The field RememberMe extends the login session, while RememberMachine prevents the need to enter an OTP on the same device next time.

[HttpPost("2fa/recovery-code")]
public async Task<IActionResult> LoginWithRecoveryCode(
    [FromBody] RecoveryCodeLoginDtoInp model)
{
    if (!ModelState.IsValid)
        return ValidationProblem(ModelState);

    var user = await signInManager.GetTwoFactorAuthenticationUserAsync();

    if (user is null)
        return Unauthorized(new
        {
            Message = "2FA session expired."
        });

    var recoveryCode = model.RecoveryCode.Replace(" ", "");

    var result =
        await signInManager.TwoFactorRecoveryCodeSignInAsync(recoveryCode);

    if (result.Succeeded)
    {
        logger.LogInformation(
            "User {Id} logged in with a recovery code.",
            user.Id);

        return Ok(new
        {
            Success = true,
            Message = "Recovery code login successful."
        });
    }

    if (result.IsLockedOut)
    {
        return BadRequest(new
        {
            Message = "Account locked out."
        });
    }

    return Unauthorized(new
    {
        Message = "Invalid recovery code."
    });
}

The final endpoint is an emergency button. It uses a recovery code generated earlier to set up 2FA again in case the authenticator app is unavailable, or the mobile is lost. TwoFactorRecoveryCodeSignInAsync checks whether the recovery code is unused, since it is a one-time recovery option.

Step 6: Inject Identity into the dependencies

In Program.cs add the following code:

builder.Services.AddIdentity<ApplicationUser, IdentityRole>()
    .AddEntityFrameworkStores<ApplicationDbContext>()
    .AddDefaultTokenProviders();

Step 7: Run migrations

dotnet ef migrations add Initial

To reflect the migration.

dotnet ef database update

Step 8: Run the project

dotnet run

Our Swagger UI is ready.

First, register a user.

Now log in.

If the credentials are wrong.

On multiple invalid attempts, we get.

Two-factor authentication URI.

Still, my user is already logged in. The endpoint is decorated with [Authorize]. I am using an external website to generate the QR code. You can use the QRCoder package in .NET as well, but now let's keep things simple.

I scanned the code in my Microsoft Authenticator app, and the account was added. The one-time password code is visible to me, and I can enable 2FA through the API.

2FA is now enabled. Let's log in again.

You can see the difference: earlier, login was direct without 2FA and immediately returned.

But now we have to use 2FA login.

Passkey authentication in .NET 10

.NET allows passkey implementation for WebAuthn/FIDO compliance. Let's go through it step by step.

Step 1: Install packages

dotnet add package Fido2
dotnet add package Microsoft.AspNetCore.Session

The Fido2 package handles passkey tasks of enabling passkey, Windows Hello, Face ID, and passwordless authentication. While AspNetCore.Session provides session storage for Fido2 challengers.

Step 2: Create models

StoredCredential

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace TwoFaNET10.Models;

public class StoredCredential
{
    public int Id { get; set; }

    [Required]
    public string UserId { get; set; } = string.Empty;

    [Required]
    public byte[] CredentialId { get; set; } = Array.Empty<byte>();

    [Required]
    public byte[] PublicKey { get; set; } = Array.Empty<byte>();

    public uint SignatureCounter { get; set; }

    public string CredType { get; set; } = string.Empty;

    public DateTime RegDate { get; set; } = DateTime.UtcNow;

    public Guid AaGuid { get; set; }

    public byte[] UserHandle { get; set; } = Array.Empty<byte>();

    public string? FriendlyName { get; set; }

    [ForeignKey(nameof(UserId))]
    public ApplicationUser User { get; set; } = null!;
}

It stores a user's passkey information. CredentialId is a unique identifier of the passkey. When a user logs in, the server looks up the incoming credential ID. The Publickey is saved on the server that verifies using the private key saved on the user's device. CredentialType is another important field specifying the type of WebAuthn credential, in our case, it is PublicKey.

CompletePasskeyRegistrationDtoInp

using Fido2NetLib;

namespace TwoFaNET10.Models.Dtos;

public class CompletePasskeyRegistrationDtoInp
{
    public AuthenticatorAttestationRawResponse AttestationResponse { get; set; } = null!;
    public string? FriendlyName { get; set; }
}

PasskeyAssertionRequest

namespace TwoFaNET10.Models.Dtos;

public record PasskeyAssertionRequest(string? Username);

A simple value type was enough here, so I used a record.

Step 3: Add a new table to the context

public DbSet<StoredCredential> StoredCredentials => Set<StoredCredential>();

Step 4: Add Fido2 configurations

Add the following object to appsettings.json.

"Fido2": {
  "ServerDomain": "localhost",
  "ServerName": "TwoFaNET10",
  "Origins": [
    "http://localhost:5220"
  ]
},

And, in Program.cs add the following code.

builder.Services.AddFido2(options =>
{
    options.ServerDomain = builder.Configuration["Fido2:ServerDomain"] ?? "localhost";
    options.ServerName = builder.Configuration["Fido2:ServerName"] ?? "TwoFactorAuth";
    options.Origins = builder.Configuration.GetSection("Fido2:Origins").Get<HashSet<string>>()
                      ?? [];
    options.TimestampDriftTolerance = builder.Configuration.GetValue<int>("Fido2:TimestampDriftTolerance", 300000);
});

Step 5: Add Distributed cache

Passkeys are set in the session cache, so injecting the cache in Program.cs. For production APIs, use an in-memory cache or Redis.

builder.Services.AddDistributedMemoryCache();
builder.Services.AddSession();
builder.Services.AddHttpContextAccessor();

app.UseSession();

The HTTP context is needed to fetch the logged-in user's session.

Step 6: Define Passkey service

Service interface

using Fido2NetLib;
using TwoFaNET10.Models;

namespace TwoFaNET10.Services;

public interface IPasskeyService
{
    Task<CredentialCreateOptions> GetAttestationOptionsAsync(ApplicationUser user);

    Task<(bool Success, string Error)> MakeCredentialAsync(
        AuthenticatorAttestationRawResponse attestationResponse,
        ApplicationUser user,
        string? friendlyName);

    Task<AssertionOptions> GetAssertionOptionsAsync(string? username);

    Task<(bool Success, string UserId, string Error)> MakeAssertionAsync(
        AuthenticatorAssertionRawResponse assertionResponse);
}

Implementation

using System.Text;
using Fido2NetLib;
using Fido2NetLib.Objects;
using Microsoft.EntityFrameworkCore;
using TwoFaNET10.Data;
using TwoFaNET10.Models;

namespace TwoFaNET10.Services;

public class PasskeyService(
    IFido2 fido2,
    ApplicationDbContext db,
    IHttpContextAccessor httpContextAccessor,
    ILogger<PasskeyService> logger) : IPasskeyService
{
    private const string AttestationKey = "fido2.attestationOptions";
    private const string AssertionKey = "fido2.assertionOptions";
    public async Task<CredentialCreateOptions> GetAttestationOptionsAsync(ApplicationUser user)
    {
        var existingCredentials = await db.StoredCredentials
            .Where(c => c.UserId == user.Id)
            .ToListAsync();

        var excludeCredentials = existingCredentials
            .Select(c => new PublicKeyCredentialDescriptor(c.CredentialId))
            .ToList();

        var fido2User = new Fido2User
        {
            Id = Encoding.UTF8.GetBytes(user.Id),
            Name = user.Email!,
            DisplayName = user.FullName
        };

        var authenticatorSelection = new AuthenticatorSelection
        {
            UserVerification = UserVerificationRequirement.Required,
            ResidentKey = ResidentKeyRequirement.Required,
            RequireResidentKey = true
        };

        var options = fido2.RequestNewCredential(new RequestNewCredentialParams
        {
            User = fido2User,
            AuthenticatorSelection = authenticatorSelection,
            ExcludeCredentials = excludeCredentials,
            AttestationPreference = AttestationConveyancePreference.None
        });

        // DO NOT rely on session in production (still ok for dev)
        Session.SetString(AttestationKey, options.ToJson());

        return options;
    }
    
    public async Task<(bool Success, string Error)> MakeCredentialAsync(
        AuthenticatorAttestationRawResponse attestationResponse,
        ApplicationUser user,
        string? friendlyName)
    {
        try
        {
            var json = Session.GetString(AttestationKey);
            if (string.IsNullOrEmpty(json))
                return (false, "Session expired. Please try again.");

            var options = CredentialCreateOptions.FromJson(json);

            var existingCredIds = await db.StoredCredentials
                .Select(x => x.CredentialId)
                .ToListAsync();

            var credential = await fido2.MakeNewCredentialAsync(new MakeNewCredentialParams
            {
                AttestationResponse = attestationResponse,
                OriginalOptions = options,

                IsCredentialIdUniqueToUserCallback = (args, ct) =>
                {
                    return Task.FromResult(
                        !existingCredIds.Any(id => id.SequenceEqual(args.CredentialId))
                    );
                }
            });

            db.StoredCredentials.Add(new StoredCredential
            {
                UserId = user.Id,
                CredentialId = credential.Id,
                PublicKey = credential.PublicKey,
                SignatureCounter = credential.SignCount,
                CredType = credential.Type.ToString(),
                RegDate = DateTime.UtcNow,
                AaGuid = credential.AaGuid,
                UserHandle = credential.User.Id,
                FriendlyName = string.IsNullOrWhiteSpace(friendlyName)
                    ? "My Passkey"
                    : friendlyName
            });

            await db.SaveChangesAsync();

            return (true, string.Empty);
        }
        catch (Exception ex)
        {
            logger.LogError(ex, "Error making credential for user {UserId}", user.Id);
            return (false, ex.Message);
        }
    }
    
    public async Task<AssertionOptions> GetAssertionOptionsAsync(string? username)
    {
        var allowedCredentials = new List<PublicKeyCredentialDescriptor>();

        if (!string.IsNullOrWhiteSpace(username))
        {
            var user = await db.Users.FirstOrDefaultAsync(u => u.Email == username);
            if (user is not null)
            {
                var userCredentials = await db.StoredCredentials
                    .Where(c => c.UserId == user.Id)
                    .ToListAsync();

                allowedCredentials = userCredentials
                    .Select(c => new PublicKeyCredentialDescriptor(c.CredentialId))
                    .ToList();
            }
        }
        var assertionOpt = new GetAssertionOptionsParams
        {
            AllowedCredentials = allowedCredentials,
            UserVerification = UserVerificationRequirement.Preferred
        };
        var options = fido2.GetAssertionOptions(assertionOpt);

        Session.SetString(AssertionKey, options.ToJson());
        return options;
    }
    
    public async Task<(bool Success, string UserId, string Error)> MakeAssertionAsync(
        AuthenticatorAssertionRawResponse assertionResponse)
    {
        try
        {
            var json = Session.GetString(AssertionKey);
            if (string.IsNullOrEmpty(json))
                return (false, string.Empty, "Session expired. Please try again.");

            var options = AssertionOptions.FromJson(json);

            var storedCred = await db.StoredCredentials
                .FirstOrDefaultAsync(c => c.CredentialId == assertionResponse.RawId);

            if (storedCred is null)
                return (false, string.Empty, "Passkey not found.");

            var result = await fido2.MakeAssertionAsync(new MakeAssertionParams
            {
                AssertionResponse = assertionResponse,
                OriginalOptions = options,
                StoredPublicKey = storedCred.PublicKey,
                StoredSignatureCounter = storedCred.SignatureCounter,

                IsUserHandleOwnerOfCredentialIdCallback = (args, ct) =>
                {
                    return Task.FromResult(
                        storedCred.UserHandle.SequenceEqual(args.UserHandle)
                    );
                }
            });

            storedCred.SignatureCounter = result.SignCount;
            await db.SaveChangesAsync();

            return (true, storedCred.UserId, string.Empty);
        }
        catch (Exception ex)
        {
            logger.LogError(ex, "Error verifying passkey assertion");
            return (false, string.Empty, ex.Message);
        }
    }
    
    private ISession Session =>
        httpContextAccessor.HttpContext?.Session
        ?? throw new InvalidOperationException("HttpContext session is not available.");
}

GetAttestationOptionsAsync generates the WebAuthn registration challenge for a logged-in user. The method converts their credential IDs into excludeCredentials after fetching the user's existing passkeys. Then it builds Fido2User from user ID, email, and display name into the format Fido2NetLib needs. Setting UserVerification and ResidentKey to Required will enforce a biometric PIN and enable passwordless login later. fido2.RequestNewCredential builds an CredentialCreateOptions object that is saved in the session and also returned to the API response. The response object contains the Fido user, challenge, timeout milliseconds, and other fields.

MakeCredentialAsync verifies and stores a newly created passkey. First, it gets CredentialCreateOptions from the session. Now it loads all the credential IDs from the database for a uniqueness check. fido2.MakeNewCredentialAsync validates challenges. origin matches, public key, and verifies CredentialId's uniqueness. IsCredentialIdUniqueToUserCallback ensures the credential isn't already registered, though it is very unlikely, Fido2NetLib requires its implementation. Upon success, passkey details such as credential ID, public key, initial sign counter, and AAGUID (which identifies the authenticator model) are saved in the database.

GetAssertionOptionsAsync starts the login process by generating a WebAuthn challenge. If a username is provided, we first retrieve the user's details and allowed credentials. Then the server sends the list of allowed credentials to the browser, which it uses for authentication. If there is no username, the list will be empty, and we will move towards passwordless login. With a username, the server tells the authenticator which credential(s) to use. Without a username, the authenticator lets the user choose a passkey for the website and signs the server's challenge. The server then verifies the signed response using the stored public key.

MakeAssertionAsync verifies the login attempt and identifies the user. It retrieves AssertionOptions from the session and looks up the StoredCredential row by matching CredentialId == assertionResponse.RawId. If no matching credential exists, login fails immediately. fido2.MakeAssertionAsync validates the challenge and origin, verifies the signature using the stored public key, and checks the sign counter for clone detection. IsUserHandleOwnerOfCredentialIdCallback verifies if the credentials belong to the correct user. Finally, after updating the SignatureCounter, I save the changes to the database.

A lambda expression is setting the session from the HTTP context.

Step 7: Add Controller methods

[Authorize]
[HttpPost("passkey/attestation-options")]
public async Task<IActionResult> GetPasskeyAttestationOptions()
{
    var user = await userManager.GetUserAsync(User);

    if (user is null)
        return Unauthorized(new { Success = false, Error = "User not found." });

    var options = await passkeyService.GetAttestationOptionsAsync(user);

    return Ok(options);
}

[Authorize]
[HttpPost("passkey/attestation-verify")]
public async Task<IActionResult> VerifyPasskeyAttestation(
    [FromBody] CompletePasskeyRegistrationDtoInp request)
{
    var user = await userManager.GetUserAsync(User);

    if (user is null)
        return Unauthorized(new { Success = false, Error = "User not found." });

    var (success, error) = await passkeyService.MakeCredentialAsync(
        request.AttestationResponse,
        user,
        request.FriendlyName);

    if (!success)
        return BadRequest(new { Success = false, Error = error });

    return Ok(new
    {
        Success = true,
        Message = "Passkey registered successfully."
    });
}

[HttpPost("passkey/assertion-options")]
public async Task<IActionResult> GetPasskeyAssertionOptions(
    [FromBody] PasskeyAssertionRequest request)
{
    var options = await passkeyService.GetAssertionOptionsAsync(request.Username);
    return Ok(options);
}

[HttpPost("passkey/assertion-verify")]
public async Task<IActionResult> PasskeyAssertionVerify(
    [FromBody] AuthenticatorAssertionRawResponse assertionResponse)
{
    var (success, userId, error) =
        await passkeyService.MakeAssertionAsync(assertionResponse);

    if (!success)
        return BadRequest(new { Success = false, Error = error });

    var user = await userManager.FindByIdAsync(userId);

    if (user is null)
        return NotFound(new { Success = false, Error = "User not found." });

    await signInManager.SignInAsync(user, false);

    return Ok(new { Success = true });
}

attestation-options starts passkey registration. It is an authenticated endpoint. Registering a passkey is an account management action, user must login before adding a new credential to that account. Calls GetAttestationOptionsAsync and returns the options JSON directly to the browser, which feeds it into navigator.credentials.create().

attestation-verify ties the new credential to the currently logged-in user. It calls MakeCredentialAsync with the browser's attestation response, the current user, and an optional friendly name (e.g. "My Phone"). Validates credentials with Fido2, saves it in the database, and returns success or failure. As a result, the passkey is now registered for the user and stored in the database. The device also stores the private key.

assertion-options is the actual passkey login endpoint. The user logs in here; hence, it is unauthenticated. It calls the underlying GetAssertionOptionsAsync method and returns the challenge options to the browser for navigator.credentials.get().

assertion-verify is the final step in the login process. It relies on the service's MakeAssertionAsync method to retrieve the userId and success. After confirming the success in the service layer, it fetches the user and signs them in.

Step 8: Run migrations

dotnet ef migrations add PasskeyInitial

To reflect the migration.

dotnet ef database update

A glance at the database schema.

Step 9: Run the project

dotnet run

Let's run the project again and be unauthorized.

Now the passkey.

navigator.credentials.create() and navigator.credentials.get() are browser WebAuthn APIs, not HTTP calls. Swagger can hit your attestation-options/assertion-options endpoints fine as they're just JSON, but the actual "talk to the authenticator/fingerprint/phone/security key" step only exists in a browser's JS engine, and you cannot trigger that from Swagger's UI or curl.

Run the following in the browser console.

function base64UrlToBase64(input) {
  input = input.replace(/-/g, '+').replace(/_/g, '/');
  const pad = input.length % 4;
  if (pad) input += '='.repeat(4 - pad);
  return input;
}

function bufferToBase64url(buffer) {
  const bytes = new Uint8Array(buffer);
  let str = '';
  for (const b of bytes) str += String.fromCharCode(b);
  return btoa(str).replace(/\+/g, '-').replace(/\//g, '_').replace(/=+$/, '');
}

// 1. Get attestation options (must be logged in via session/cookie)
const options = await fetch("/api/Account/passkey/attestation-options", {
  method: "POST",
  headers: { "Content-Type": "application/json" }
}).then(r => r.json());

// 2. Convert challenge and user.id to ArrayBuffer
options.challenge = Uint8Array.from(
  atob(base64UrlToBase64(options.challenge)),
  c => c.charCodeAt(0)
);
options.user.id = Uint8Array.from(
  atob(base64UrlToBase64(options.user.id)),
  c => c.charCodeAt(0)
);

if (options.excludeCredentials) {
  options.excludeCredentials = options.excludeCredentials.map(c => ({
    ...c,
    id: Uint8Array.from(atob(base64UrlToBase64(c.id)), c => c.charCodeAt(0))
  }));
}

// 3. Create credential - THIS is what triggers the "create a passkey" prompt
const credential = await navigator.credentials.create({ publicKey: options });

// 4. Build payload and send to attestation-verify
const attestationResponse = {
  id: credential.id,
  rawId: bufferToBase64url(credential.rawId),
  type: credential.type,
  clientExtensionResults: credential.getClientExtensionResults(),
  response: {
    clientDataJSON: bufferToBase64url(credential.response.clientDataJSON),
    attestationObject: bufferToBase64url(credential.response.attestationObject),
    transports: credential.response.getTransports ? credential.response.getTransports() : []
  }
};

const verifyResult = await fetch("/api/Account/passkey/attestation-verify", {
  method: "POST",
  headers: { "Content-Type": "application/json" },
  body: JSON.stringify({
    attestationResponse: attestationResponse,
    friendlyName: "My Phone"
  })
}).then(r => r.json());

console.log(verifyResult);

The script first calls attestation-options with the logged-in user and gets the user details and challenge. The server has stored the challenge and sent it to the browser. For further use, convert the received Base64Url strings to Uint8Array, as WebAuthn APIs expect ArrayBuffer. navigator.credentials.create() cannot work with strings, so we had to go the extra mile. In the said function, the browser inputs the options containing challenge, user, and RP information. The browser calls Windows Hello or Face ID (Windows Hello in our case) to prompt for a passkey. It stores the private key safely and returns a credential object to the browser. Then I created attestationResponse by converting the binary credential data into JSON using bufferToBase64url before sending it to attestation-verify. The server will validate the challenge and return a success or failure message. The user's passkey is registered now.

A Window Hello appeared where I added my passkey.

Now the passkey is saved, and the browser console shows.

Now assertion.

const options = await fetch("/api/Account/passkey/assertion-options", {
  method: "POST",
  headers: { "Content-Type": "application/json" },
  body: JSON.stringify({ username: "ali@gmaail.com" })
}).then(r => r.json());

options.challenge = Uint8Array.from(atob(base64UrlToBase64(options.challenge)), c => c.charCodeAt(0));
options.allowCredentials = options.allowCredentials.map(c => ({
  ...c,
  id: Uint8Array.from(atob(base64UrlToBase64(c.id)), c => c.charCodeAt(0))
}));

const assertion = await navigator.credentials.get({ publicKey: options });

const assertionResponse = {
  id: assertion.id,
  rawId: bufferToBase64url(assertion.rawId),
  type: assertion.type,
  clientExtensionResults: assertion.getClientExtensionResults(),
  response: {
    clientDataJSON: bufferToBase64url(assertion.response.clientDataJSON),
    authenticatorData: bufferToBase64url(assertion.response.authenticatorData),
    signature: bufferToBase64url(assertion.response.signature),
    userHandle: assertion.response.userHandle ? bufferToBase64url(assertion.response.userHandle) : null
  }
};

const verifyResult = await fetch("/api/Account/passkey/assertion-verify", {
  method: "POST",
  headers: { "Content-Type": "application/json" },
  body: JSON.stringify(assertionResponse)
}).then(r => r.json());

console.log(verifyResult);

assertion-options will do its job of loading the user's passkeys from StoredCredentials and stores a newly generated challenge in the session. The browser gets an object containing the challenge and allowed credentials. Similar to the earlier script, convert the received data into binary in the input of navigator.credentials.get that is responsible for getting PIN, passkey, or touch finger sensor from user prompt. Windows Hello finds the credential specified in allowCredentials verifies user input, retrieves the private key, and signs the challenge. Note that the private key never leaves Windows Hello and is used to sign the challenge. assertionResponse is prepared and passed as a parameter in assertion-verify by which the server verifies the signature using the public key and user data. Finally, it creates an authentication cookie, and the user is now logged in.

Once the passkey was inserted, the follow appeared in the console.

Now our authorized endpoint will reply with a success.

Conclusion

The advent of 2FA and passkeys significantly improves both security and user experience. Both play a brilliant role in reducing password vulnerabilities in modern applications. .NET provides extensive support for integrating these security measures into projects. In this post, I discussed 2FA and passkeys, followed by a step-by-step implementation in an ASP.NET Core API.

Code: https://github.com/elmahio-blog/TwoFaNET10

Imagine a bug suddenly arises in the frontend from an endpoint. The endpoint has multiple layers and involves dozens of files. Now, you are tasked to resolve that bug, those piles of code make it difficult to pinpoint the cause of the error. Some exceptions, like 'Object reference not set to an instance of an object" are vague and hard to detect. You need a detailed log of API operations to identify the exact code and reason for the error. Serilog is one fine choice for logging that we will use to implement file logging in ASP .NET Core API.

Why use Serilog?

Serilog is a powerful logging library for .NET applications. It generates structured logs that are both human- and machine-readable, and you can easily filter and query them. Besides, it provides an array of sink options, including Console, File, SQL, Elasticsearch, and elmah.io.

Implementing Serilog file logging in ASP .NET Core API

I will start with a basic ASP.NET 10 API with simple logging code. Later, I will try to cover important use cases for real-world applications.

Step 1: Create an API project

dotnet new web -n SerilogFileLog 

Step 2: Install required packages

dotnet add package Serilog.AspNetCore
dotnet add package Serilog.Sinks.File

They are Serilog packages, and later on, logs will be written to files.

I am fond of Swagger, but .NET 10 does not provide Swagger Ui by default, hence I am using the package. To keep the work similar to the readers, I will use Swagger, as most developers are already familiar with it.

dotnet add package Swashbuckle.AspNetCore 

Step 3: Define Controller

using Microsoft.AspNetCore.Mvc;

namespace SerilogFileLog.Controllers;

[ApiController]
[Route("api/[controller]")]
public class WeatherController : ControllerBase
{
    private readonly ILogger<WeatherController> _logger;

    public WeatherController(ILogger<WeatherController> logger)
    {
        _logger = logger;
    }

    [HttpGet]
    public IActionResult Get()
    {
        _logger.LogInformation("Weather endpoint called");

        return Ok("Hello Serilog");
    }
}

The controller is simple, so we can focus on our main topic for now. ILogger object _logger is responsible for writing messages to logs. A naive way would be Log.Information("Weather endpoint called");using Serilog's global logger, which does not require injection. However, it does not give the context of the class where the error originates. Injecting a logger in classes is recommended for production environments.

Step 4: Configure Program.cs

using Serilog;
var builder = WebApplication.CreateBuilder(args);

Log.Logger = new LoggerConfiguration()
    .WriteTo.File("logs/log.txt")
    .CreateLogger();

builder.Host.UseSerilog();

builder.Services.AddOpenApi();

// Services
builder.Services.AddControllers();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.MapOpenApi();
    app.UseSwaggerUI(options =>
        {
            options.SwaggerEndpoint("/openapi/v1.json", "v1");
        }
    );
}

app.UseHttpsRedirection();

app.MapControllers();
app.Run();

So, I am adding a file logs/log.txt for the log, followed by builder.Host.UseSerilog(); injecting Serilog. I used app.UseSwaggerUI for Swagger UI here.

Step 5: run the project

dotnet run

Result

A new log is created in the solution explorer.

Rolling day logs

A good practice in logging is to keep daily logs in a separate file. Serilog allows us to do so. Just change the logging snippet.

Log.Logger = new LoggerConfiguration()
    .WriteTo.File(
        "logs/log-.txt",
        rollingInterval: RollingInterval.Day
        )
    .CreateLogger();

A new daily file will be created.

Logging configurations from appsettings.json

Let's move to a production-level example. I will add the log details in appsettings.

{
  "Serilog": {
    "MinimumLevel": "Information",
    "WriteTo": [
      {
        "Name": "File",
        "Args": {
          "path": "logs/log-.log",
          "rollingInterval": "Day"
        }
      }
    ]
  },
 //Other appsetting fields
}

MinimumLevel controls the lowest log level that will be written. Also, I moved the rolling interval here. Other options for rolling are

• Minute
• Hour
• Day 
• Month
• Year
• Infinite

and a slight change in the code.

Log.Logger = new LoggerConfiguration()
    .ReadFrom.Configuration(builder.Configuration)
    .CreateLogger();

Levels of Serilog logs

Serilog defines logs in different severity levels as follows, from lowest to highest

Verbose: Detailed diagnostic logs
Debug: Developer troubleshooting information
Information: Normal application events
Warning: Potential issue detected
Error: Operation failed unexpectedly
Fatal: Application cannot continue

By setting MinimalLevel to Information, we will ignore Verbose and Debug messages and will keep the higher ones.

Our new log file is.

And its content.

Why .log is recommended over .txt?

Log is an industry standard for application logs, and it is easy to identify log files. Many log viewers and monitoring tools automatically recognize .log.

Logging on Errors

Now, define a method in the controller that may throw an error.

[HttpGet("GetDivision")]
    public IActionResult GetDivision(int number)
    {
        try
        {
            _logger.LogInformation("GetDivision endpoint called with number: {Number}", number);

            int result = 10 / number;

            _logger.LogInformation(
                "Division completed successfully. Result: {Result}",
                result);

            return Ok(new
            {
                Success = true,
                Result = result
            });
        }
        catch (Exception ex)
        {
            _logger.LogError(
                ex,
                "An error occurred in GetDivision. Input Number: {Number}",
                number);

            return StatusCode(StatusCodes.Status500InternalServerError, new
            {
                Success = false,
                Message = ex.Message
            });
        }
    }

LogError in the catch block will write as an error message.

Erroneous condition.

Well, let's check the logs now.

Or the complete message is.

2026-06-06 18:30:39.729 +05:00 [ERR] An error occurred in GetDivision. Input Number: 0
System.DivideByZeroException: Attempted to divide by zero.
   at SerilogFileLog.Controllers.WeatherController.GetDivision(Int32 number) in D:\Elmah.io\SerilogFileLog\SerilogFileLog\Controllers\WeatherController.cs:line 30

We can get the exact line number along with the error description.

Add Enrichers and Output templates.

Serilog allows capturing metadata, such as the requester's device and username. For that, use the package.

dotnet add package Serilog.Enrichers.Environment                                    

There are two ways to include metadata in the logs, Either you can define in the appsettings.json will include an Enrich object and a template that defines the log message.

{
  "Serilog": {
    "MinimumLevel": "Information",
    "Enrich": [
      "WithMachineName",
      "WithEnvironmentUserName",
      "FromLogContext"
    ],
    "WriteTo": [
      {
        "Name": "File",
        "Args": {
          "path": "logs/log-.log",
          "outputTemplate": "{Timestamp:yyyy-MM-dd HH:mm:ss.fff zzz} [{Level:u3}] {MachineName} {EnvironmentUserName} {Message:lj}{NewLine}{Exception}",
          "rollingInterval": "Day"
        }
      }
    ]
  },
  //Other
}

Or allowing the enrichment in the code.

Log.Logger = new LoggerConfiguration()
    .ReadFrom.Configuration(builder.Configuration)
    .Enrich.WithMachineName()
    .Enrich.WithEnvironmentUserName()
    .Enrich.FromLogContext()
        .CreateLogger();

Finally, it looks like.

My PC and user name are added to the logs.

Logging Application Startup Events

Apart from operations errors, you can leverage Serilog's static methods to log errors that arise during startup. As there is no dependency when the application starts, you may not get the idea what happened, then comes the following.

using Serilog;

var builder = WebApplication.CreateBuilder(args);

// Configure and Enrich
Log.Logger = new LoggerConfiguration()
    .ReadFrom.Configuration(builder.Configuration)
    .Enrich.WithMachineName()
    .Enrich.WithEnvironmentUserName()
    .Enrich.FromLogContext()
        .CreateLogger();

try
{
    Log.Information("Starting up!");
    
    builder.Host.UseSerilog();
    
    builder.Services.AddOpenApi();
    
    // Services
    builder.Services.AddControllers();
    
    var app = builder.Build();
    
    // Configure the HTTP request pipeline.
    if (app.Environment.IsDevelopment())
    {
        app.MapOpenApi();
        app.UseSwaggerUI(options =>
            {
                options.SwaggerEndpoint("/openapi/v1.json", "v1");
            }
        );
    }
    
    app.UseHttpsRedirection();
    throw new Exception("a startup error");
    app.MapControllers();
    app.Run();
    Log.Information("Stopped cleanly");
    return 0;
}
catch (Exception ex)
{
    Log.Fatal(ex, "An unhandled exception occurred during startup");
    return 1;
}
finally
{
    Log.CloseAndFlush();
}

The CloseAndFlush method writes any pending log entries and releases resources by the Serilog. It is recommended to use the method to not to loose last logs before the application shuts down or crashes. I manually threw an error in between.

Conclusion

Logging is important for debugging an application and ensuring a seamless experience. Also, you can use logs to make critical business decisions. Today, I shared how to use Serilog file logging in .NET APIs. I began with basic file logging, progressed to scenarios of real applications, including daily rolling logs, and moved all configuration to appsettings.json standard practices. Additionally, I highlighted enrichers like WithMachineName() and WithEnvironmentUserName() to automatically attach valuable metadata to every log entry.

While file logging is perfect for testing Serilog locally, text files often aren't ideal in production. For live apps, streaming Serilog events to a cloud-based system like elmah.io centralizes your logs and provides real-time alerts before bugs impact your users.

Code: https://github.com/elmahio-blog/SerilogFileLog.git

EF Core (Entity Framework Core) is a fast ORM. However, once dataset entries increase by another 0, things start to break down. Fetching and filtering of data adds to processing time. Looking at this pitfall, I came up with a few EF Core methods that are specifically designed to work with bulk data. With 5 extension methods in EF Core, I will show how novelty can meet performance and clean code goals.

Why use other methods than EF Core's .Contains?

EF Core provides .Contains a method for filtering and retrieval. However, it brings some issues. Firstly, only 2,100 parameters can be passed. What to do when you need to filter a large number of parameters? Splitting the query is one option here, but splitting queries into batches in SQL can be problematic in some complex situations. Also, it is not recommended to filter a single table with multiple database round-trips, as it consumes more memory and keeps database connections open longer. Hence, you will need a single way to fulfil the task. All the methods we will discuss today were aimed at solving these shortcomings.

Implementation of EF Core methods for large data retrieval

To showcase each method, I will use a console application that connects to a PostgreSQL database containing up to 1 million records. Each method is suitable in its own arena.

Step 1: Create the project

dotnet new console -n EfCoreBulkFetchBenchmark
cd EfCoreBulkFetchBenchmark

Step 2: Install required packages

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Z.EntityFramework.Extensions.EFCore
dotnet add package Microsoft.Extensions.Configuration
dotnet add package Microsoft.Extensions.Configuration.Json

All the 5 methods are extensions of EF Core provided by the library Z.EntityFramework.Extensions.EFCore.

Step 3: Create appsettings.json

Adding appsettings.json with a database connection string.

{
  "ConnectionStrings": {
    "PostgresConnection": "Host=localhost;Port=5432;Database=shopDb;Username=postgres;Password=1234"
  }
}

To add appsettings.json to the project, we need to specify it in .proj

<ItemGroup>
    <None Update="appsettings.json">
        <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
</ItemGroup>

Step 4: Create models

Customer

namespace EfCoreBulkFetchBenchmark.Models;

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public ICollection<Order> Orders {get;set;} = null!;
}

Order

namespace EfCoreBulkFetchBenchmark.Models;

public class Order
{
    public int Id { get; set; }
    public int? CustomerId { get; set; }
    public decimal Amount { get; set; }
    public string Status { get; set; }
    public DateTime CreatedAt { get; set; }
    public Customer Customer { get; set; }
}

Step 5: Configure ApplicationDbContext

using EfCoreBulkFetchBenchmark.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

namespace EfCoreBulkFetchBenchmark.Data;

public class ApplicationDbContext: DbContext
{
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Customer> Customers => Set<Customer>();

    private readonly string _connectionString;

    public ApplicationDbContext()
    {
        var config = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json")
            .Build();

        _connectionString = config.GetConnectionString("PostgresConnection");
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql(_connectionString);
    }
}

Here, I am getting the connection string from the appsettings.json

Step 6: Run migrations

dotnet ef migrations add InitialCreate

To reflect the migration

dotnet ef database update

Step 7: Seed data

In a separate file, I have added code to insert a considerable amount of data

using EfCoreBulkFetchBenchmark.Models;

namespace EfCoreBulkFetchBenchmark.Data;

public class SeedData
{
    public static async Task Initialize(ApplicationDbContext context)
    {
        if (context.Orders.Any()) return;

        var random = new Random();
        var countries = new[] { "USA", "UK", "France", "Germany", "Pakistan" };
    
        var customers = Enumerable.Range(1, 100000)
            .Select(i => new Customer
            {
                Name = $"Customer {i}",
                Country = countries[random.Next(countries.Length)],
                CreatedAt = DateTime.UtcNow.AddDays(-random.Next(365)) // Added random creation date
            }).ToList();

        context.Customers.AddRange(customers);
        await context.SaveChangesAsync();

        var orders = new List<Order>();
        var rand = new Random();

        for (int i = 1; i <= 1_000_000; i++)
        {
            orders.Add(new Order
            {
                CustomerId = customers[rand.Next(customers.Count)].Id,
                Amount = rand.Next(100, 10000),
                Status = i % 2 == 0 ? "Completed" : "Pending",
                CreatedAt = DateTime.UtcNow.AddDays(-rand.Next(100))
            });
        }

        context.Orders.AddRange(orders);
        await context.SaveChangesAsync();
    }
}

In Program.cs

using EfCoreBulkFetchBenchmark.Data;
using EfCoreBulkFetchBenchmark.Models;
using Microsoft.EntityFrameworkCore;

var context = new ApplicationDbContext();

await context.Database.EnsureCreatedAsync();

await SeedData.Initialize(context);

Step 8: Implement advanced EF Core methods

Our database and code are ready, now we can see 5 ways to fetch data with a large dataset.

var orderIds = await context.Orders
    .OrderBy(x => x.Id)
    .Take(50000)
    .Select(x => x.Id)
    .ToListAsync();

// SMALL list for naive test (safe)
var smallOrderIds = await context.Orders
    .OrderBy(x => x.Id)
    .Take(2000)
    .Select(x => x.Id)
    .ToListAsync();

To filter, I initialized some IDs. The first sample is large, while the smallOrderIds will be used in a naive approach where EF Core does not allow a large number of parameters.

The naive way using .Contains will be

var result = await context.Orders
    .Where(x => smallOrderIds.Contains(x.Id))
    .ToListAsync();

Console.WriteLine($"Total records : {result.Count}");

WhereBulkContains

var result2 = await context.Orders
    .WhereBulkContains(orderIds, x => x.Id)
    .ToListAsync();

Console.WriteLine($"Total records : {result2.Count}");

WhereBulkContains takes a list as a parameter and filters records based on all items in the input list.

The first parameter is the collection of values to filter, and the second parameter is a lambda expression that specifies the property to match. In our case, it is the ID of the orders as specified in the lambda expression.

We can do the same thing without a lambda expression. The method will by default filter over the primary key field

result2 = await context.Orders
    .WhereBulkContains(orderIds)
    .ToListAsync();

You can filter in a one-to-one relationship with .Include as well like this

result2 = await context.Orders
.Include(x => x.Customer)
    .WhereBulkContains(x=>
        x.Customer,  
        new List<string>()
        {
            "Pakistan",
            "France"
        },
        x => x.Name
    )
    .ToListAsync();

The filter now populates customers' data along with the orders. However, all orders are fetched, as there is no filter on orders, while only customers' data will be included whose countries match the given list.

For a one-to-many relation

var customers = await context.Customers
    .Include(x => x.Orders)
    .WhereBulkContains(x=>
            x.Orders,  
        smallOrderIds,
        x => x.Id
    )
    .ToListAsync();

WhereBulkContains returns IQueryable<T>, which can be further chained with LINQ methods like Where and Select, or materialized with ToListAsync. Best suited for filtering by multiple values.

WhereBulkContains supports basic types like int, string or guid not complex or composite types as the list parameters.

WhereBulkNotContains 

var result3 = await context.Orders
    .WhereBulkNotContains(orderIds, x => x.Id)
    .ToListAsync();

Console.WriteLine($"Total records : {result3.Count}");

You can think of an inverted method of WhereBulkContains. WhereBulkNotContains filters data that excludes the parameter list.

The result excluded all 2000 records that matched the input list. The same thing can be achieved by

result3 = await context.Orders
    .WhereBulkNotContains(orderIds)
    .ToListAsync();

Unlike its inverse partner, input params allows any list type, including basic, entity, anonymous, and expando types. Also, it allows a one-to-many relation

result3 = await context.Orders
    .Include(x => x.Customer)
    .WhereBulkNotContains(x=>
            x.Customer,  
        new List<string>()
        {
            "Pakistan",
            "France"
        },
        x => x.Name
    )
    .ToListAsync();

While for one-to-many

var customers = await context.Customers
    .Include(x => x.Orders)
    .WhereBulkNotContains(x=>
            x.Orders,  
        smallOrderIds,
        x => x.Id
    )
    .ToListAsync();

BulkRead 

var inputOrders = orderIds
    .Select(id => new Order { Id = id }).ToList();
    
var result4 = await context.Orders
    .BulkReadAsync(inputOrders, x=>x.Id);

Console.WriteLine($"Total records : {result4.Count}");

BulkRead returns data that matches the given list. You can pass a list of any type as input and specify the column for filtering as a lambda expression. The final result is immediately returned. Think of it as WhereBulkContains but with immediate execution internally using  ToList or ToListAsync method.

Similarly, we can take the default behaviour of using the primary key for filtering

var result4 = await context.Orders
    .BulkReadAsync(inputOrders);

WhereBulkContainsFilterList 

var result5 = context.Orders
    .WhereBulkContainsFilterList(smallOrderIds, x => x.Id)
    .ToList();
    
Console.WriteLine($"Total records : {result5.Count}");

It returns items from the list that already exist in the database. It filters just like WhereBulkContains, but it filters out items in the input list rather than the database.

We filtered over 2000 IDs, and they are available in the database, so it simply returned all of them

result5 = context.Orders
    .WhereBulkContainsFilterList(smallOrderIds)
    .ToList();

A useful application of this method is when we need to filter an input list while updating.

WhereBulkNotContainsFilterList 

var result6 = context.Orders
    .WhereBulkNotContainsFilterList(
            new List<int>()
            {
                10000,
                100000,
                1000001,
                1000002,
                1000003
            }
        , x => x.Id)
    .ToList();
Console.WriteLine($"Total records : {result6.Count}");

WhereBulkNotContainsFilterList is the inverse of WhereBulkContainsFilterList, which returns items of the input list that do not exist in the table.

The table contains 1 million data and 3 items in the input Ids list did not match any of those IDs. A concise version using ids by default is

result6 = context.Orders
    .WhereBulkNotContainsFilterList(
        new List<int>()
        {
            10000,
            100000,
            1000001,
            1000002,
            1000003
        }
        )
    .ToList();

WhereBulkNotContainsFilterList has a key difference with WhereBulkNotContains where the prior one returns a filtered record from the input list, while the latter one returns filtered records from the database. While for the input list parameter, all types are allowed.

Conclusion

We discovered 5 EF Core methods for data retrieval with their implementation on a real dataset. WhereBulkContains, WhereBulkNotContains BulkRead, WhereBulkContainsFilterList, WhereBulkNotContainsFilterList each are designed to handle fetching and filtering large data. Their generated queries perform the job efficiently, while spanning a few readable lines, making the efficiency easy to read. Z.EntityFramework.Extensions.EFCore offers a trial version for a limited time that can be extended with their plans.

Code: https://github.com/elmahio-blog/EfCoreBulkFetchBenchmark

Scaling horizontally is great… until the bill comes. Hence, as a startup or a big firm, you always need to be careful about billing while maintaining competitiveness. Every user who lands on your application needs high throughput. In today's post, I will share ways to revolutionize the performance of your .NET application. We will observe the benchmark after applying those techniques to the response time.

High-throughput .NET API implementation

For the rest of this post, I'll use an ASP.NET Core Minimal API and measure performance using BenchmarkDotNet. Let's get started.

Step 1: Create the solution

dotnet new sln -n OrdersPerformanceDemo
cd OrdersPerformanceDemo

Step 2: Add projects to the solution

dotnet new web -n Orders.Api
dotnet new console -n Orders.Benchmark

dotnet sln add Orders.Api
dotnet sln add Orders.Benchmark

Step 3: Install packages

cd Orders.Api

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Dapper
dotnet add package Microsoft.Extensions.Configuration
dotnet add package Microsoft.Extensions.Configuration.Json
dotnet add package Microsoft.Extensions.Configuration.FileExtensions

Also, in the benchmark project, we will add the required packages:

cd ../Orders.Benchmark

dotnet add package BenchmarkDotNet
dotnet add package Npgsql
dotnet add package Dapper
dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

Step 4: Add Appsettings

In the appsettings.json file add a connection string:

"ConnectionStrings": {
  "PostgresConnection": "Host=localhost;Port=5433;Database=shopDb;Username=postgres;Password=1234"
}

And similarly, in Program.cs of the API project, include the following code:

var connectionString = builder.Configuration.GetConnectionString("PostgresConnection");

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseNpgsql(connectionString));

builder.Services.AddScoped<NpgsqlConnection>(_ =>
    new NpgsqlConnection(connectionString));

Step 5: Add models

Customer model.

namespace Orders.Api.Models;

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
}

Order model.

namespace Orders.Api.Models;

public class Order
{
    public int Id { get; set; }
    public int? CustomerId { get; set; }
    public decimal Amount { get; set; }
    public string Status { get; set; }
    public DateTime CreatedAt { get; set; }

    public Customer Customer { get; set; }
}

Step 6: Set up the DB context

using Microsoft.EntityFrameworkCore;
using Orders.Api.Models;

namespace Orders.Api.Data;

public class ApplicationDbContext: DbContext
{
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Customer> Customers => Set<Customer>();

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options) { }
}

Step 7: Run migrations

dotnet ef migrations add InitialCreate

And reflect on it.

dotnet ef database update

I already seeded a few thousand rows of data in the table.

Step 8: Add naive API

app.MapGet("/orders/naive", async (ApplicationDbContext db) =>
{
    var data = await db.Orders
        .Include(o => o.Customer)
        .Take(50000)
        .ToListAsync();

    return Results.Ok(data);
});

This is one of the simplest and least efficient ways of fetching data as a worst-case threshold. Well, to save the huge pressure of a million records on the system and to save time, I took 50000 records.

Step 9: Create a dapper endpoint

app.MapGet("/orders/dapper", async (
    int? customerId,
    int page,
    int pageSize,
    NpgsqlConnection conn) =>
{
    var sql = @"
        SELECT o.""Id"", o.""Amount"", o.""CreatedAt"", c.""Name""
        FROM ""Orders"" o
        JOIN ""Customers"" c ON o.""CustomerId"" = c.""Id""
        WHERE o.""Status"" = 'Completed'
        AND (@customerId IS NULL OR o.""CustomerId"" = @customerId)
        ORDER BY o.""CreatedAt"" DESC
        LIMIT @pageSize OFFSET @offset";

    var result = await conn.QueryAsync(sql, new
    {
        customerId,
        pageSize,
        offset = (page - 1) * pageSize
    });

    return Results.Ok(result);
});

Now, we are ready to start implementing optimizations.

Database indexing

CREATE INDEX IF NOT EXISTS idx_completed_orders
    ON public."Orders" USING btree
    ("CustomerId" ASC NULLS LAST)
    TABLESPACE pg_default
    WHERE "Status" = 'Completed'::text;

At the time of fetching, use the index to filter fast.

var query = db.Orders
    .AsNoTracking() 
    .Where(o => o.Status == "Completed");

Use pagination

Pagination is a win-win situation in database fetching. In most cases, APIs do not need to return large amounts of data in the response. Rather, there is a limit, usually 20 to 100, that mobile and web applications can display at once. Leverage this fact and add pagination to the fetches, significantly reducing the performance penalties. You only get what you need and do not burden yourself with extra.

var result = await query
    .OrderByDescending(o => o.CreatedAt)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .Select(o => new 
    {
        o.Id,
        o.Amount,
        o.CreatedAt,
        CustomerName = o.Customer.Name
    })
    .ToListAsync();

Add Pagination Limits

To prevent users from fetching large amounts of data at once, add a pagesize limit. When you are sure about the API's use cases.

pageSize = Math.Min(pageSize, 100);

Use Dapper for Hot Endpoints Only

app.MapGet("/orders/dapper", async (
    int? customerId,
    int page,
    int pageSize,
    NpgsqlConnection conn) =>
{
    var sql = @"
        SELECT o.""Id"", o.""Amount"", o.""CreatedAt"", c.""Name""
        FROM ""Orders"" o
        JOIN ""Customers"" c ON o.""CustomerId"" = c.""Id""
        WHERE o.""Status"" = 'Completed'
        AND (@customerId IS NULL OR o.""CustomerId"" = @customerId)
        ORDER BY o.""CreatedAt"" DESC
        LIMIT @pageSize OFFSET @offset";

    var result = await conn.QueryAsync(sql, new
    {
        customerId,
        pageSize,
        offset = (page - 1) * pageSize
    });

    return Results.Ok(result);
});

Dapper uses a query that is less abstracted than EF Core. This offers a performance edge, and you can use Dapper for high traffic operations.

Response Compression

builder.Services.AddResponseCompression();

app.UseResponseCompression();

It compresses server responses before sending them to clients, reducing bandwidth usage and improving load times.

Avoid Over-Fetching

EF Core entities contain several navigation properties to other entities. In real cases, a single entity has a cluster of dependent entities, such as a User that has navigation with userRoles, languages, and approvals. Loading all of them can be a serious drain on memory and processing power. We have customers and an order table. However, if we need order data, then no need to involve other tables with code like this: .Include(o => o.Customer). If you need to send OrderId and Status over the API, then avoid including the customer unnecessarily.

No tracking for faster reads with EF Core

var query = db.Orders
    .AsNoTracking() 
    .Where(o => o.Status == "Completed");

EF Core tracks fetched data for change tracking. To remove this behavior, use AsNoTracking when you only fetch data and don't need to modify it.

Projection to reduce response fields

Reduce the fetched field to only the required ones. Fetching and sending unnecessary fields costs CPU time and network bandwidth. Use projection to get what you need.

var result = await query
    .OrderByDescending(o => o.CreatedAt) 
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .Select(o => new 
    {
        o.Id,
        o.Amount,
        o.CreatedAt,
        CustomerName = o.Customer.Name
    })
    .ToListAsync();

Cache for frequently accessed data

Cache is another breakthrough in our journey. Caching frequently accessed data in memory reduces database round-trip. I will show two types of caching.

Output Cache

Output caching stores the whole web response in memory, the serialized JSON, headers, and status code next time you request the route with the same parameters. AddOutputCache registers output caching services in the DI container. UseOutputCache inserts the output-caching middleware into the request-processing pipeline. When a request comes in, this middleware intercepts it, checks if a valid cached response exists, and if so, returns it immediately. That way, the output caching reduces the whole cycle of hitting the database and serializing the response. In the program.cs inject the service.

builder.Services.AddOutputCache();

app.UseOutputCache();

The endpoint now looks like this:

app.MapGet("/orders/optimized_output", async (
    int? customerId,
    int page,
    int pageSize,
    ApplicationDbContext db,
    IMemoryCache cache) =>
{
    // Technique: Limit page size (protect API)
    pageSize = Math.Min(pageSize, 100);

    var query = db.Orders
        .AsNoTracking() // Technique: No tracking (faster reads)
        .Where(o => o.Status == "Completed"); //Technique: Uses partial index

    if (customerId.HasValue)
        query = query.Where(o => o.CustomerId == customerId); // Technique: Uses index

    var result = await query
        .OrderByDescending(o => o.CreatedAt) //Technique: Sorting (optimize via index)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .Select(o => new // Technique: Projection (avoid over-fetching)
        {
            o.Id,
            o.Amount,
            o.CreatedAt,
            CustomerName = o.Customer.Name
        })
        .ToListAsync();

    return Results.Ok(result);
})
.CacheOutput(p => 
    p.Expire(TimeSpan.FromSeconds(600))); // Technique: Output caching

.CacheOutput contains a lambda expression that specifies the time to retain cached data.

In-memory cache

In most cases, output caching should be sufficient. But sometimes, you may want to look into creating manual, private caches in your code.

To start creating a cache, import the following namespace in the Program.cs file:

using Microsoft.Extensions.Caching.Memory;

Inject caching:

builder.Services.AddMemoryCache();

In our endpoint method, you inject a IMemoryCache object and set the cache key:

var cacheKey = $"orders:{customerId}:{page}:{pageSize}";

upon every request. First, we check if the required data is in the in-memory cache. If found, we return it without going to the database.

if (cache.TryGetValue(cacheKey, out List<object> cached))
    return Results.Ok(cached);

Otherwise, set the fetched result in cache.

cache.Set(cacheKey, result, TimeSpan.FromSeconds(30));

The endpoint now looks like this:

app.MapGet("/orders/optimized_inmemory", async (
    int? customerId,
    int page,
    int pageSize,
    ApplicationDbContext db,
    IMemoryCache cache) =>
{
    // Technique: Limit page size (protect API)
    pageSize = Math.Min(pageSize, 100);

    // Technique: Cache key
    var cacheKey = $"orders:{customerId}:{page}:{pageSize}";

    // Technique: Memory cache (ultra fast)
    if (cache.TryGetValue(cacheKey, out List<object> cached))
        return Results.Ok(cached);

    var query = db.Orders
        .AsNoTracking() // Technique: No tracking (faster reads)
        .Where(o => o.Status == "Completed"); //Technique: Uses partial index

    if (customerId.HasValue)
        query = query.Where(o => o.CustomerId == customerId); //Technique: Uses index

    var result = await query
        .OrderByDescending(o => o.CreatedAt) //Technique: Sorting (optimize via index)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .Select(o => new // Technique: Projection (avoid over-fetching)
        {
            o.Id,
            o.Amount,
            o.CreatedAt,
            CustomerName = o.Customer.Name
        })
        .ToListAsync();

    // Technique : Cache result
    cache.Set(cacheKey, result, TimeSpan.FromSeconds(600));

    return Results.Ok(result);
});

Although I have used both output and memory caches in the app, the output cache is better suited to APIs, dashboards, and repetitive data. In-memory is optimal for expensive calculations, permissions, and non-HTTP scenarios.

Know the caching strategy in detail to better utilize it according to your application.

Step 10: Set up code in the Benchmark project

using BenchmarkDotNet.Attributes;
using Microsoft.EntityFrameworkCore;
using Dapper;
using Microsoft.Extensions.Configuration;
using Npgsql;
using Orders.Api.Data;
using Orders.Benchmark.Config;

namespace Orders.Benchmark;

[MemoryDiagnoser]
public class OrdersBenchmark
{
    private HttpClient _client;
    private string _baseUrl;

    [GlobalSetup]
    public void Setup()
    {
        var config = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json")
            .Build();

        _baseUrl = "http://localhost:5102";

        _client = new HttpClient
        {
            BaseAddress = new Uri(_baseUrl)
        };
    }

    [Benchmark]
    public async Task Naive()
    {
        var response = await _client.GetAsync("/orders/naive");
        response.EnsureSuccessStatusCode();

        var data = await response.Content.ReadAsStringAsync();
    }

    [Benchmark]
    public async Task OptimizedInmemory()
    {
        var response = await _client.GetAsync("/orders/optimized_inmemory?page=1&pageSize=50&customerId=223");
        response.EnsureSuccessStatusCode();

        var data = await response.Content.ReadAsStringAsync();
    }

    [Benchmark]
    public async Task OptimizedOutput()
    {
        var response = await _client.GetAsync("/orders/optimized_output?page=1&pageSize=50&customerId=223");
        response.EnsureSuccessStatusCode();

        var data = await response.Content.ReadAsStringAsync();
    }

    [Benchmark]
    public async Task Dapper()
    {
        var response = await _client.GetAsync("/orders/dapper?page=1&pageSize=50");
        response.EnsureSuccessStatusCode();

        var data = await response.Content.ReadAsStringAsync();
    }
}

We are setting up the client for the API project. Later, defined methods for each API endpoint.

Step 11: Call the benchmark in the Program.cs

using BenchmarkDotNet.Running;
using Orders.Benchmark;

BenchmarkRunner.Run<OrdersBenchmark>();

Step 12: Run and test

One by one, running the project in different terminals.

cd Orders.Api
dotnet run

And the benchmark in the release mode.

cd Orders.Benchmark
dotnet run -c Release

Result

Running again to check caching.

The results speak about output caching performance in both cases. After accessing cached data, the results are even faster.

External factors to achieve High-Throughput API

The greatest soldier with a weak sword cannot stand in the battle. Similarly, a very good code cannot perform well if it is not hosted properly. To achieve high throughput, you need to take care of the following external factors.

Choosing the Right infrastructure and hosting

When hosting your application, choose the right server with a few things in mind. Go with an option that offers more parallel request handling, high-performing RAM, and a location close to your users' geographic area. If your code takes 30 ms for the job but the server responds in 200 ms due to the user's geographic distance, it will be counted as slow. Besides, SSDs are faster than HDDs, so choose accordingly. High-speed storage has a significant impact on the request-response cycle.

Using a reverse Proxy / Load balancer

Load balancing is necessary for scalable systems. Never expose the API directly; instead, use HAProxy and NGINX for load balancing, SSL termination, and request buffering.

Background Processing

Tasks such as analytics, email, and logging should be separate from main API calls to avoid blocking the request-response pipeline.

Rate limit API

Rate limiting is a performance and precautionary measure that limits a single user or tenant from overusing or abusing the application. Overuse can spike the server, slowing APIs for other users. With .NET 10, rate limiting is even improved.

Conclusion

Every system needs to achieve optimal performance. In this regard, I shared a few important techniques that a .NET API can use to achieve high throughput. However, testing with 1 million requests is difficult on a personal computer. However, we observed remarkable success with all the tactics, and you can improve the application by following them as well. I also discuss external factors that contribute to user experience and system response time.

Code: https://github.com/elmahio-blog/OrdersPerformanceDemo.git

Files are an integrated part of an application. From a social app to an ERP, some form of media exists in the ecosystem. .NET Core APIs provide built-in support for uploading and fetching documents. In today's post, I will show you how to cleanly and efficiently use .NET Core's file system classes to receive file uploads.

Uploading files in ASP.NET Core API

I am creating an API with .NET 10 to accomplish our goal.

Step 1: Create a project

dotnet new webapi -n fileUploadDemo
cd fileUploadDemo

Step 2: Create a controller

using Microsoft.AspNetCore.Mvc;

namespace fileUploadDemo.Controllers;

[ApiController]
[Route("api/[controller]")]
public class FileController: ControllerBase
{
    [HttpPost("upload")]
    public async Task<IActionResult> Upload(IFormFile file)
    {
        if (file == null || file.Length == 0)
            return BadRequest("No file uploaded.");
    
        var uploadsFolder = Path.Combine(Directory.GetCurrentDirectory(), "Uploads");
    
        if (!Directory.Exists(uploadsFolder))
            Directory.CreateDirectory(uploadsFolder);
    
        var filePath = Path.Combine(uploadsFolder, file.FileName);
    
        using (var stream = new FileStream(filePath, FileMode.Create))
        {
            await file.CopyToAsync(stream);
        }
    
        return Ok(new { file.FileName, file.Length });
    }
}

The endpoint upload will save the file to the "Uploads" directory and return the filename and length.

Step 3: Configure Program.cs

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();

var app = builder.Build();

app.UseStaticFiles();
app.MapControllers();

app.UseHttpsRedirection();

app.Run();

app.UseStaticFiles(); enables the app to serve static files directly over HTTP

Make sure your Uploads folder is accessible.

Step 4: Run and test

dotnet run

Hence, our file is saved:

So far, we have seen a naive, minimal approach to saving a file in an ASP.NET Core API. However, in real applications, you may split it up into layers, services, or however you prefer to structure applications.

Adding OpenApi/Swagger documentation

.NET 10 does not create OpenAPI (formerly Swagger) documentation like previous versions. So we have to add them. First, install the NuGet package:

dotnet add package Swashbuckle.AspNetCore

Next, change Program.cs:

using Microsoft.AspNetCore.Http.Features;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddOpenApi();
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

app.UseSwagger();
app.UseSwaggerUI(c =>
{
    c.SwaggerEndpoint("/swagger/v1/swagger.json", "Upload V1");
});

if (app.Environment.IsDevelopment())
{
    app.MapOpenApi();
}

app.UseStaticFiles();
app.MapControllers();
app.UseHttpsRedirection();

app.Run();

The AddOpenApi() method registers with the OpenAPI document generation services. Then AddEndpointsApiExplorer() adds an API explorer service that discovers endpoints and provides metadata about routes, parameters, and return types. Without this, Swagger would show no APIs. AddSwaggerGen() configures Swashbuckle (Swagger for .NET) and generates a Swagger document. Besides, it adds schema generation for your DTOs. UseSwagger() registers middleware to serve the Swagger JSON document. You must register middleware before using SwaggerUI. The UseSwaggerUI configures the Swagger UI web interface and navigates the UI to the JSON document. It basically adds UI elements, such as the page and Try out buttons.

Result

Restrict the file size limit

It is recommended to set a maximum file size limit for uploaded files:

var builder = WebApplication.CreateBuilder(args);

builder.Services.Configure<FormOptions>(options =>
{
    options.MultipartBodyLengthLimit = 104857600; 
});

With the property MultipartBodyLengthLimit, I set the maximum size of the multipart body to 100 MBs.

Allowing formats for different file types

Each document type has a set of extensions: images are usually in JPG or PNG, and documents are usually in PDF or docx. For different purposes, we should validate the input. Like for a profile picture, users may not upload a docx or a PDF, while for a report, they should avoid JPG. To enforce it in our API, I will separate the image and document endpoints.

UploadImage specified for uploading image files:

[HttpPost("UploadImage")]
public async Task<IActionResult> UploadImageAsync(IFormFile file)
{
    if (file == null || file.Length == 0)
        return BadRequest("No file uploaded.");

    var allowedExtensions = new[] { ".jpg", ".png" };
    var extension = Path.GetExtension(file.FileName);
    
    if (!allowedExtensions.Contains(extension))
    {
        var extensionsWithoutDots = allowedExtensions.Select(ext => ext.TrimStart('.'));
        return BadRequest($"Invalid file type. Allowed types are: {string.Join(", ", extensionsWithoutDots)}");
    }
    
    var uploadsFolder = Path.Combine(Directory.GetCurrentDirectory(), "Uploads");

    if (!Directory.Exists(uploadsFolder))
        Directory.CreateDirectory(uploadsFolder);

    var filePath = Path.Combine(uploadsFolder, file.FileName);

    using (var stream = new FileStream(filePath, FileMode.Create))
    {
        await file.CopyToAsync(stream);
    }

    return Ok(new { file.FileName, file.Length });
}

UploadDocument method for uploading document files:

    [HttpPost("UploadDocument")]
    public async Task<IActionResult> UploadDocumentAsync(IFormFile file)
    {
        if (file == null || file.Length == 0)
            return BadRequest("No file uploaded.");

        var allowedExtensions = new[] { ".pdf", ".txt" };
        var extension = Path.GetExtension(file.FileName);
        
        if (!allowedExtensions.Contains(extension))
        {
            var extensionsWithoutDots = allowedExtensions.Select(ext => ext.TrimStart('.'));
            return BadRequest($"Invalid file type. Allowed types are: {string.Join(", ", extensionsWithoutDots)}");
        }
        
        var uploadsFolder = Path.Combine(Directory.GetCurrentDirectory(), "Uploads");
    
        if (!Directory.Exists(uploadsFolder))
            Directory.CreateDirectory(uploadsFolder);
    
        var filePath = Path.Combine(uploadsFolder, file.FileName);
    
        using (var stream = new FileStream(filePath, FileMode.Create))
        {
            await file.CopyToAsync(stream);
        }
    
        return Ok(new { file.FileName, file.Length });
    }

The prior method only supports JPG and PNG, while the latter supports PDF and TXT. Lets test it from SwaggerUI:

On a correct input:

If we inspect the Uploads folder, we can see the uploaded file:

While the other method:

And for the allowed document:

Renaming the file to standard naming

We have now successfully uploaded files. However, there is a problem: you may notice that file names are arbitrary and random.

For real applications, we often want to control the names of the files to avoid someone uploading a Snapchat image of themselves with a weird-looking filename. Let's fix that.

Step 1: Add model

First, introduce a model to input images. It will contain a type specifying what the file is for.

Add File type enum:

namespace fileUploadDemo.Models.Enums;

public enum FileTypeEnum
{
    ProfilePicture = 1,
    PostImage = 2,
    Resume = 3
}

File input:

using fileUploadDemo.Models.Enums;

namespace fileUploadDemo.Models.Dtos;

public class FileDtoInp
{
    public FileTypeEnum Type { get; set; }
    public IFormFile File { get; set; }
}

To return, we will use a model instead of an anonymous object:

namespace fileUploadDemo.Models.Dtos;

public class FileDto
{
    public string FileName { get; set; } = string.Empty;
    public long Length { get; set; } 
}

Step 2: Add service layer

So far, I have added everything to the controller, which is a bad practice. I'll add a service layer, but this can be implemented in whatever way you prefer.

Add a IFileService interface:

using fileUploadDemo.Models.Dtos;

namespace fileUploadDemo.Services.IServices;

public interface IFileService
{
    Task<FileDto> UploadImageAsync(FileDtoInp input);
    Task<FileDto> UploadDocumentAsync(FileDtoInp input);
}

And a FileService implementation:

using fileUploadDemo.Models.Dtos;
using fileUploadDemo.Models.Enums;
using fileUploadDemo.Services.IServices;

namespace fileUploadDemo.Services;

public class FileService: IFileService
{
    private readonly string _uploadsFolder;

    public FileService(IWebHostEnvironment env)
    {
        _uploadsFolder = Path.Combine(env.ContentRootPath, "Uploads");
    }
    public async Task<FileDto> UploadImageAsync(FileDtoInp input)
    {
        if (input.File == null || input.File.Length == 0)
            throw new Exception("No file uploaded.");
        
        var allowedExtensions = new[] { ".jpg", ".png" };
        var extension = Path.GetExtension(input.File.FileName);
        
        if (!allowedExtensions.Contains(extension))
        {
            var extensionsWithoutDots = allowedExtensions.Select(ext => ext.TrimStart('.'));
            throw new Exception($"Invalid file type. Allowed types are: {string.Join(", ", extensionsWithoutDots)}");
        }
        
        if (!Directory.Exists(_uploadsFolder))
            Directory.CreateDirectory(_uploadsFolder);

        var fileName = $"{ GetFileName(input.Type) }{extension}";
        
        var filePath = Path.Combine(_uploadsFolder, fileName);
    
        using (var stream = new FileStream(filePath, FileMode.Create))
        {
            await input.File.CopyToAsync(stream);
        }

        return new 
            FileDto()
            {
                FileName = fileName, 
                Length = input.File.Length
            };
    }

    public async Task<FileDto> UploadDocumentAsync(FileDtoInp input)
    {
        if (input.File == null || input.File.Length == 0)
            throw new Exception("No file uploaded.");

        var allowedExtensions = new[] { ".pdf", ".txt" };
        var extension = Path.GetExtension(input.File.FileName);
        
        if (!allowedExtensions.Contains(extension))
        {
            var extensionsWithoutDots = allowedExtensions.Select(ext => ext.TrimStart('.'));
            throw new Exception($"Invalid file type. Allowed types are: {string.Join(", ", extensionsWithoutDots)}");
        }
        
        if (!Directory.Exists(_uploadsFolder))
            Directory.CreateDirectory(_uploadsFolder);
    
        var fileName = $"{ GetFileName(input.Type) }{extension}";
        var filePath = Path.Combine(_uploadsFolder, fileName);
    
        using (var stream = new FileStream(filePath, FileMode.Create))
        {
            await input.File.CopyToAsync(stream);
        }

        return new 
            FileDto()
            {
                FileName = fileName, 
                Length = input.File.Length
            };
    }
    
    private string GetFileName(FileTypeEnum input)
        => input switch
        {
            FileTypeEnum.ProfilePicture => $"PRF-{DateTime.UtcNow:yyMMddHHmmss}",
            FileTypeEnum.PostImage => $"PST-{DateTime.UtcNow:yyMMddHHmmss}",
            FileTypeEnum.Resume => $"RSM-{DateTime.UtcNow:yyMMddHHmmss}",
            _ => throw new ArgumentOutOfRangeException(nameof(input), input, null)
        };
}

Let's understand what is happening here. We have methods for uploading images and documents, which first validate the file types. They actually abstracted the logic from the earlier upgrade of our application from controllers to services, which is always recommended. The main update here is the GetFileName method that returns a standardized name for each type. To make it collision-proof, I concatenated the current datetime into the name. Besides, the upload directory address is initialized in the constructor and used throughout the service. An even better way is to use it in appsettings.json and use the option pattern. Lets keep it simple for now. At last, I returned the FileDto model as output. You may also want to switch to not using exceptions for input validation, but I will leave that part up to you.

Step 3: Register the new service

builder.Services.AddScoped<IFileService, FileService>();

In Program.csI have registered the service dependency.

Step 4: Update controller

Now, the controller will inject and use the IFileService we just created:

using fileUploadDemo.Models.Dtos;
using fileUploadDemo.Services.IServices;
using Microsoft.AspNetCore.Mvc;

namespace fileUploadDemo.Controllers;

[ApiController]
[Route("api/[controller]")]
public class FileController: ControllerBase
{
    private readonly IFileService _fileService;

    public FileController(IFileService fileService)
    {
        _fileService = fileService;
    }
    
    [HttpPost("UploadImage")]
    public async Task<IActionResult> UploadImageAsync([FromForm] FileDtoInp input)
    {
        var result = await _fileService.UploadImageAsync(input);
        return Ok(result);
    }
    
    [HttpPost("UploadDocument")]
    public async Task<IActionResult> UploadDocumentAsync([FromForm] FileDtoInp input)
    {
        var result = await _fileService.UploadDocumentAsync(input);
        return Ok(result);
    }
}

[FromForm] specifies the input as form-data, not a JSON body, ensuring the endpoint receives files.

Step 4: Run and test

dotnet run

Ensure the file is named according to our standard, regardless of its original name.

The same happens with the documents:

Apart from naming files with date and time, using a unique GUID or the username is also a common practice. Consider the following ways:

var fileName = $"{ Guid.NewGuid().ToString() }{extension}";

or:

var fileName = $"{ _userContext.Username }{extension}";

Creating a GET endpoint to fetch a file

We have covered several ways to upload a file in an ASP.NET Core API. Next requirement naturally arises: how to fetch the saved file? Let's design an endpoint for it.

A new model to return the file from the service to the controller could be implemented like this:

namespace fileUploadDemo.Models.Dtos;

public class FileResultDto
{
    public byte[] Content { get; set; } = default!;
    public string ContentType { get; set; } = string.Empty;
    public string FileName { get; set; } = string.Empty;
}

Our service implementation should include a new method for fetching a file:

public async Task<FileResultDto?> GetFileAsync(string key)
{
    if (string.IsNullOrWhiteSpace(key) || key.Contains(".."))
        return null;

    var filePath = Path.Combine(_uploadsFolder, key);

    if (!System.IO.File.Exists(filePath))
        return null;

    var extension = Path.GetExtension(filePath).ToLower();

    var contentType = extension switch
    {
        ".jpg" or ".jpeg" => "image/jpeg",
        ".png" => "image/png",
        ".pdf" => "application/pdf",
        _ => "application/octet-stream"
    };

    var bytes = await System.IO.File.ReadAllBytesAsync(filePath);

    return new FileResultDto
    {
        Content = bytes,
        ContentType = contentType,
        FileName = key
    };
}

After some initial string checks and combining the filename with the directory, we check whether the file exists. In the latter part, we convert the file into a MIME type based on the extension. var bytes = await System.IO.File.ReadAllBytesAsync(filePath); loads the entire file into memory and returns byte[]. If large files are uploaded, consider streaming instead. Finally, it packs the content and metadata into the response model and returns it.

Use it in the controller:

[HttpGet("GetFile")]
public async Task<IActionResult> GetFileAsync([FromQuery] string key)
{
    var result = await _fileService.GetFileAsync(key);

    if (result == null)
        return NotFound("File not found");

    return File(result.Content, result.ContentType, result.FileName);
}

[FromQuery] will prompt for the filename as a key param and pass it to the service. Once the data is received, the endpoint will return the actual file.

Test

Simply, the user can download the file.

Tips for using file uploading in ASP.NET Core

For production environments and scalable systems, consider the following aspects.

• For the production environment, use a cloud service such as Azure Blob or AWS S3 to save files for better reliability and speed.
• Use the path in appsettings for security and good design.
• Organize upload paths into separate directories for document- and module-wise uploads. Even separating directories for users is also a common practice, such as "images/Profile", "images/Posts", "documents/Resumes" or "images/john/".
• Store metadata in a database for referencing and persistence. Creating an image-and-document table with a Type key is a great way to reference files to corresponding entities, such as User and Building, via ImageId and DocumentId. The tables can store other important information, such as file size, creation date, uploader user, file name, etc.
• If your system requires a very large file upload, use chunk uploading.
• Use file streaming for large file instead of buffer that can result in high RAM usage.
• Enable progress tracking via SignalR.

Conclusion

The long story comes to an end, and we learned how to handle files in ASP.NET Core APIs. We started with minimal code and applied best practices when using images and documents. By following these tips, you can design a file structure securely and efficiently in a production-grade system.

For developers who want to taste ORM but don't want to leave SQL either, Dapper is a perfect choice. Dapper runs SQL queries like ADO.NET but returns results as C# objects, like Entity Framework Core. Apart from its abstracting nature, you leverage high-speed data access. The feature set of Dapper is quite large and covers key areas of database work, such as JOINs, aggregate functions, database procedures and functions, transactions, etc. In today's post, I will walk through everything needed to get you started with Dapper.

What is Dapper?

Dapper is a micro-ORM (Object Relational Mapper) for .NET. Developed by Stack Overflow, Dapper offers higher performance database operations faster than full ORMs like Entity Framework Core (EF Core). It suits developers who work with ADO.NET because of its query commands, unlike EF Core, which operates on objects rather than SQL. SQL Queries are directly mapped to strongly typed objects like any ORM.

Why Use Dapper?

As Dapper is a micro-ORM that offers a thin abstraction between the database and applications, it introduces very little overhead. For these reasons, it is an optimal choice for high-performing scenarios. Similar to ADO .NET, you can get full control over SQL queries. You can think of it as the middle ground between ADO.NET and an ORM like EF Core, which uses data as strongly typed C# objects. One can view the SQL generated by EF Core, but can't granularly control it. Dapper uses minimal dependencies, keeping the architecture lightweight. That is one of the reasons Dapper best suits microservices, financial systems, web APIs, and modular APIs.

Commonly used Dapper methods

To summarize the most commonly used Dapper methods, let's go ahead and create a new project.

Step 1: Create a project

dotnet new console -n UserDapperDemo
cd UserDapperDemo

Step 2: Install the required packages

dotnet add package Dapper
dotnet add package Npgsql
dotnet add package Microsoft.Extensions.Configuration
dotnet add package Microsoft.Extensions.Configuration.Json

Npgsql provides a NpgsqlConnection class for connecting to PostgreSQL. The Configuration* packages are useful when we create and include appsettings.json in the project.

Step 3: Create models

namespace UserDapperDemo.Models;

public class Movie
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public int DirectorId { get; set; }
    public double Rating { get; set; }
}

namespace UserDapperDemo.Models;

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

Step 4: Add appsettings.json and its configuration

{
  "ConnectionStrings": {
    "PostgresConnection": "Host=localhost;Port=5432;Database=movieDb;Username=postgres;Password=1234"
  }
}

In the .proj file add the following item group:

    <ItemGroup>
        <None Update="appsettings.json">
            <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
        </None>
    </ItemGroup>

Step 5: Configure connection factory

using System.Data;
using Microsoft.Extensions.Configuration;
using Npgsql;

namespace UserDapperDemo.Data;

public class DbConnectionFactory
{
    
    private readonly string _connectionString;

    public DbConnectionFactory()
    {
        
        var config = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json")
            .Build();

        _connectionString = config.GetConnectionString("PostgresConnection");
    }

    public IDbConnection CreateConnection()
        => new NpgsqlConnection(_connectionString);
}

It makes a centralized database connection point.

Step 6: Prepare the database

Run the following query for the database

CREATE TABLE IF NOT EXISTS public."Director"
(
    "Id" integer NOT NULL DEFAULT nextval('"Director_Id_seq"'::regclass),
    "Name" text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "Director_pkey" PRIMARY KEY ("Id")
)
CREATE TABLE IF NOT EXISTS public."Movie"
(
    "Id" integer NOT NULL DEFAULT nextval('"Movie_Id_seq"'::regclass),
    "Title" text COLLATE pg_catalog."default" NOT NULL,
    "DirectorId" integer,
    "Rating" numeric,
    CONSTRAINT "Movie_pkey" PRIMARY KEY ("Id"),
    CONSTRAINT "Movie_DirectorId_fkey" FOREIGN KEY ("DirectorId")
        REFERENCES public."Director" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

So, our database tables look like:

Step 7: Implement Dapper methods

I will follow a repository pattern to test some of Dapper's most common methods. This is just for the sake of demoing common methods. Whether or not you want to implement repositories in your code is entirely up to you. Some people love repositories while others absolutely hate them.

The movie repository will be:

public class MovieRepo
{
    private readonly DbConnectionFactory _factory;
    public MovieRepo(DbConnectionFactory factory)
    {
        _factory = factory;
    }
    
    //methods
}
    

QueryAsync<T>()

public async Task<IEnumerable<Movie>> GetAllMoviesAsync()
{
    using var connection = _factory.CreateConnection();

    return await connection.QueryAsync<Movie>(
        "SELECT * FROM \"Movie\""
    );

QueryAsync returns multiple rows of data asynchronously. It is used when we need to fetch multiple rows of data. Its synchronous counterpart is Query<>().

QueryFirstAsync<T>()

public async Task<Movie> GetFirstMovie()
{
    using var connection = _factory.CreateConnection();

    return await connection.QueryFirstAsync<Movie>(
        "SELECT * FROM \"Movie\" ORDER BY \"Id\" LIMIT 1"
    );
}

A single row-returning operation that returns the first row that matches the given condition. It expects the record to exist, otherwise, it throws an exception. QueryFirst<T>() is for synchronous operation.

QueryFirstOrDefaultAsync<T>()

public async Task<Movie?> GetMovieByTitle(string title)
{
    using var connection = _factory.CreateConnection();

    return await connection.QueryFirstOrDefaultAsync<Movie>(
        "SELECT * FROM \"Movie\" WHERE \"Title\" = @Title",
        new { Title = title }
    );
}

As the name suggests, it returns the first row that matches the condition, or otherwise returns the default value. A similar sync method is QueryFirstOrDefault.

QuerySingleAsync<T>()

public  async Task<Movie> GetMovieById(int id)
{
    using var connection = _factory.CreateConnection();

    return await connection.QuerySingleAsync<Movie>(
        "SELECT * FROM \"Movie\" WHERE \"Id\" = @Id",
        new { Id = id }
    );
}

QuerySingleAsync or QuerySingle (sync version) expects exactly one row to match the condition. If fewer or more are found, it throws an exception. QuerySingle is ideal when filtering a record by ID, as ID does not duplicate.

QuerySingleOrDefaultAsync<T>()

public  async Task<Movie?> GetMovieSafe(int id)
{
    using var connection = _factory.CreateConnection();

    return await connection.QuerySingleOrDefaultAsync<Movie>(
        "SELECT * FROM \"Movie\" WHERE \"Id\" = @Id",
        new { Id = id }
    );
}

Returns one record matched or null. Use QuerySingleOrDefault if you want non-async.

ExecuteAsync

public  async Task<int> InsertMovie(Movie movie)
{
    using var connection = _factory.CreateConnection();

    return await connection.ExecuteAsync(
        @"INSERT INTO ""Movie"" (""Title"", ""director_id"", ""rating"")
  VALUES (@Title, @DirectorId, @Rating)",
        movie
    );
}

public async Task<int> UpdateMovieAsync(Movie movie)
{
    using var connection = _factory.CreateConnection();

    return await connection.ExecuteAsync(
        @"UPDATE ""Movie"" 
      SET ""Title"" = @Title, ""Rating"" = @Rating
      WHERE ""Id"" = @Id",
        movie
    );
}

ExecuteAsync runs the command and returns the number of rows affected. Specifically, this and its Execute methods are used for Insert, Update, and Delete operations.

ExecuteScalarAsync

public async Task<int> GetMovieCount()
{
    using var connection = _factory.CreateConnection();

    return await connection.ExecuteScalarAsync<int>(
        "SELECT COUNT(*) FROM \"Movie\""
    );
}

ExecuteScalarAsync or ExecuteScalar returns a single value as a result of SQL aggregation functions such as COUNT(), AVG(), and SUM().

QueryMultipleAsync

public async Task<(IEnumerable<Movie>, IEnumerable<Director>)> GetDashboard()
{
    using var connection = _factory.CreateConnection();

    var sql = @"
        SELECT * FROM ""Movie"";
        SELECT * FROM ""Director"";
    ";

    using var multi = await connection.QueryMultipleAsync(sql);

    var movies = multi.Read<Movie>();
    var directors = multi.Read<Director>();

    return (movies, directors);
}

The method is complex, running multiple commands from a single command. QueryMultiple is the sync version of it. Instead of hitting the database multiple times, QueryMultiple lets you fetch multiple datasets in a single round-trip.

Step 8: Set up Program.cs

using UserDapperDemo.Models;
using UserDapperDemo.Data;
using UserDapperDemo.Data.Repos;

var factory = new DbConnectionFactory();

var repo = new MovieRepo(factory);

// INSERT
repo.InsertMovie(new Movie
{
    Title = "Inception",
    DirectorId = 1,
    Rating = 9.0
});

Console.WriteLine("=== ALL MOVIES ===");
// QUERY
var movies = await repo.GetAllMoviesAsync();

foreach (var m in movies)
{
    Console.WriteLine($"Id: {m.Id}, Title: {m.Title}, Rating: {m.Rating}");
}

Console.WriteLine("\n=== SINGLE MOVIE (QueryFirst) ===");
// SINGLE
var movie1 = await repo.GetFirstMovie();

Console.WriteLine($"Id: {movie1.Id}, Title: {movie1.Title}, Rating: {movie1.Rating}");

Console.WriteLine("\n=== SINGLE MOVIE (QueryFirstOrDefault) ===");
// SINGLE
var movie2 = await repo.GetMovieByTitle("Memento");

Console.WriteLine($"Id: {movie2.Id}, Title: {movie2.Title}, Rating: {movie2.Rating}");

Console.WriteLine("\n=== SINGLE MOVIE (QuerySingle) ===");
// SINGLE
var movie3 = await repo.GetMovieById(1);

Console.WriteLine($"Id: {movie3.Id}, Title: {movie3.Title}, Rating: {movie3.Rating}");

Console.WriteLine("\n=== SINGLE MOVIE (QuerySingleOrDefault) ===");
// SINGLE
var movie4 = await repo.GetMovieSafe(1);

Console.WriteLine($"Id: {movie4.Id}, Title: {movie4.Title}, Rating: {movie4.Rating}");

Console.WriteLine("\n=== MOVIE COUNT (ExecuteScalar) ===");
// COUNT
var count = await repo.GetMovieCount();
Console.WriteLine($"Total Movies: {count}");

Console.WriteLine("\n=== DASHBOARD (QueryMultiple) ===");
// MULTIPLE
var (allMovies, directors) = await repo.GetDashboard();
foreach (var m in allMovies)
{
    Console.WriteLine($"Id: {m.Id}, Title: {m.Title}, Rating: {m.Rating}");
}

foreach (var d in directors)
{
    Console.WriteLine($"Id: {d.Id}, Name: {d.Name}");
}

Here we are using movieRepo to call all of its methods.

Step 9: Run and test

dotnet run

Advanced Dapper Features

We have seen some of the most common dapper methods till now. However, Dapper offers ways to tackle them as well for complex scenarios.

Working with transactions

When inserting or updating data, an exception in between the operation can leave the database inconsistent. Transactions rescue and atomize the data manipulation operations as one, either the whole block executes or fails and rolls back. Let's see how we can do it in Dapper.

public async Task CreateMovieWithDirector(Movie movie, string directorName)
{
    using var connection = _factory.CreateConnection();
    connection.Open();

    using var transaction = connection.BeginTransaction();

    try
    {
        var directorId = await connection.ExecuteScalarAsync<int>(
            @"INSERT INTO ""Director"" (""Name"")
          VALUES (@Name)
          RETURNING ""Id"";",
            new { Name = directorName },
            transaction
        );

        movie.DirectorId = directorId;

        await connection.ExecuteAsync(
            @"INSERT INTO ""Movie"" (""Title"", ""DirectorId"", ""Rating"")
          VALUES (@Title, @DirectorId, @Rating)",
            movie,
            transaction
        );

        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

I opened a transaction on the connection, then we first created the director before the movie, as the movie depends on the directorId. At last transaction.Commit() commits the transaction to write everything in the database, while the catch block rolls back the transaction using transaction.Rollback(). Note that I am using a parameterized query to keep the database safe from SQL injection attacks and unwanted data.

In Program.cs:

Console.WriteLine("\n=== Transaction ===");
await repo.CreateMovieWithDirector(
    new Movie()
    {
        Title = null,
        Rating = 9,
    },
    "Martin Scorsese"
);

I intentionally kept the title null, as per the repo method, the director will be added successfully, but during Movie creation, there is an error. The transaction will roll back the director insertion when the second half fails as well.

Upon correct values it will be done:

new Movie()
    {
        Title = "The Departed",
        Rating = 9
    },

Using Stored Procedures/Functions

Stored procedures and functions encapsulate complex logic and provide reusable calls. For such detailed operations, you should not write a raw query spanning dozens of lines in the code that can be difficult to maintain and organize. The best way is to abstract into functions or procedures. Let's consider a function

CREATE FUNCTION public.get_movies_by_director(
	p_director_id integer)
    RETURNS TABLE("Id" integer, "Title" text, "Rating" numeric) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
    RETURN QUERY
    SELECT m."Id", m."Title", m."Rating"
    FROM "Movie" m
    WHERE m."DirectorId" = p_director_id;
END;
$BODY$;

repo method that calls the function

public async Task<IEnumerable<Movie>> GetMoviesByDirector(int directorId)
{
    using var connection = _factory.CreateConnection();

    return await connection.QueryAsync<Movie>(
        @"SELECT * FROM get_movies_by_director(@DirectorId)",
        new { DirectorId = directorId }
    );
}

var moviesByDirector = await repo.GetMoviesByDirector(1);

foreach (var m in moviesByDirector)
{
    Console.WriteLine($"{m.Id} - {m.Title} - {m.Rating}");
}

Result:

Bulk Operations with Dapper

Bulk operations are important features supported by Dapper. When inserting a large amount of data, you do not want to hit the database separately for each record, nor should you. SQL allows bulk insertion or update at once, and you can run the query with ExecuteAsync.

public async Task BulkInsertMovies(IEnumerable<Movie> movies)
{
    using var connection = _factory.CreateConnection();

    await connection.ExecuteAsync(
        @"INSERT INTO ""Movie"" (""Title"", ""DirectorId"", ""Rating"")
          VALUES (@Title, @DirectorId, @Rating)",
        movies
    );
}

Program.cs call

await repo.BulkInsertMovies(new List<Movie>
{
    new Movie { Title = "Batman Begins", DirectorId = 1, Rating = 8.2 },
    new Movie { Title = "The Dark Knight", DirectorId = 1, Rating = 9.0 }
});

Opt between Buffered and Unbuffered Query

By default, Dapper loads everything into the buffer memory. The behavior can be problematic with large datasets, so choose unbuffered queries that stream results rather than storing them in memory.

var movies = await connection.QueryAsync<Movie>(
    sql,
    buffered: false
);

Multi-Mapping (Handling JOIN Queries)

JOINs are a common way to get data from multiple tables in SQL. That common feature is workable using Dapper as well. With the same QueryAsync you can run queries with JOIN commands.

public async Task<IEnumerable<MovieWithDirector>> GetMoviesWithDirectors()
{
    using var connection = _factory.CreateConnection();

    var sql = @"
    SELECT 
        m.""Id"", 
        m.""Title"", 
        m.""Rating"",
        d.""Name"" AS ""DirectorName""
    FROM ""Movie"" m
    JOIN ""Director"" d ON m.""DirectorId"" = d.""Id"";
";

    var result = await connection.QueryAsync<MovieWithDirector>(sql);

    return result;
}

We need a model to get the results

namespace UserDapperDemo.Models;

public class MovieWithDirector
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public double Rating { get; set; }
    public string DirectorName { get; set; } = string.Empty;
}

Simply printing them all after calling the repo method

var moviesWithDirectors = await repo.GetMoviesWithDirectors();

foreach (var m in moviesWithDirectors)
{
    Console.WriteLine($"{m.Id} - {m.Title} - {m.Rating} by {m.DirectorName}");
}

Result

Best Practices When Using Dapper

Following some practices can make Dapper work better than usual. These ways make the application fast, maintainable, and secure.

Use Parameterized Queries

That is general advice to always use parameters to prevent SQL injection attacks. Apart from security, some special characters, such as "," and")", can cause errors when inserting or updating string values.

Prefer Async Methods

To avoid thread block and support asynchronous, go with async versions like QueryAsync and ExecuteAsync. Applications usually run different threads in parallel, preferring async methods in Dapper.

Organize SQL Queries

Good architecture makes a project maintainable. You should handle data access as well when using Dapper. Repository pattern is a preferable choice however choose as per your requirement. Keeping long queries in files will also save mess in the code.

Manage Connections Efficiently

Database connections are like portals to the database. Open and close them the right way with using statements or connection factories to ensure that connections open only once and are properly closed after each query.

Avoid Business logic in the SQL queries

Validate the inputs and other business rules before passing them to SQL queries. This avoids unnecessary checks on SQL and keeps the single responsibility in the data access layer.on SQL and keep the single responsibility on the data access layer.

Avoid checks like

CASE WHEN rating > 5 THEN 'Good' ELSE 'Bad'

It is generally good to keep the repository or other data access separate from business rules.

Cache frequently-accessed data

Database operations are always costly, try to reduce them as much as possible. Caching is one of the best ways to reduce database processing and delays. First, identify which data are most requested or don't change frequently. Set up caching and save them to provide quick access to users, rather than requesting the same data from the dataset every time.

You can follow some generic recommendations for the application apart from Dapper.

Conclusion

We rediscovered Dapper from its basic methods to its advanced features. Later, we reviewed some recommendations for using Dapper. Like any other feature, Dapper has its own world and definitions. I defined its commonly used methods and some of the advanced features. I shared a real example of how those methods and features address most database requirements. By following the tips in the article, you can scale up the use of the high-performance library.

Code: https://github.com/elmahio-blog/MovieDapperDemo.git

Pattern matching is not just condition checking. It reflects how you think as a developer. Matching and validation can be achieved in a naive, descriptive way. However, a cleaner approach stands out in terms of readability and sometimes performance. Pattern matching combines patterns to express complex logic in a single, readable line. In today's post, I will cover some advanced pattern-matching solutions that developers often miss.

Implementation of pattern-matching scenarios

To show pattern matching in code, I will start by creating a console application to test out different examples.

Step 1: Create the project

dotnet new console -n PatternMatchingDemo
cd PatternMatchingDemo

Step 2: Create records

For models, I will use records because we need value types here.

namespace PatternMatchingDemo.Records;
public record Address(string City, string Country);
public record User(string Name, int Age, Address Address, List<string> Roles);
public record Request(string Source, int Priority);
public record Point(int X, int Y);

Step 3: Define collections

To use pattern matching, we need a few collections. Defining an in-memory collection, while it will mimic any real data.

var users = new List<User>
{
    new("Ali", 25, new Address("Karachi", "Pakistan"), new List<string> { "Admin", "User" }),
    new("Sara", 17, new Address("Chicago", "United States"), new List<string> { "User" }),
    new("Kennedy", 65, new Address("London", "UK"), new List<string> { "Guest" })
};

var requests = new List<Request>
{
    new("System", 10),
    new("User", 3),
    new("System", 2)
};

Step 4: Use pattern matching for different requirements

That's it for the setup. In the next sections, I will showcase pattern matching for different requirements and purposes.

Property pattern (nested matching)

foreach (var user in users)
{
    if (user is { Address.City: "Karachi" })
    {
        Console.WriteLine($"{user.Name} is from Karachi");
    }
}

Property pattern provides a clean way to match an object's properties, even when they are nested. It helps in JSON matching and DTO validation without verbosity. A traditional way without a property pattern would be:

if (user != null && user.Address != null && user.Address.City == "Karachi" && user.Age > 18)

Pattern matching with not

foreach (var user in users)
{
    if (user is not { Address.City: "Karachi" })
    {
        Console.WriteLine($"{user.Name} is NOT from Karachi");
    }
}

This one is just the opposite of the previous pattern. It simply excludes the given condition and fetches all other records.

Matching multiple cases in one pattern

foreach (var user in users)
{
    if (user is { Address.City: "Karachi" or "Lahore" })
    {
        Console.WriteLine($"{user.Name} is from a major city");
    }
}

The same property matching can be extended to multiple cases. Well, the pattern is very much descriptive itself, referring to what it actually does.

Pattern matching inside LINQ

var adultsFromPakistan = users
    .Where(u => u is { Age: > 18, Address.Country: "Pakistan" })
    .ToList();

foreach (var user in adultsFromPakistan)
{
    Console.WriteLine($"{user.Name} is adult from Pakistan");
}

One of the most usable scenarios is pattern matching inside LINQ. It filters collections based on object shape and conditions.

Matching partial objects

foreach (var user in users)
{
    if (user is { Name: "Ali" })
    {
        Console.WriteLine("Found Ali");
    }
}

To match a condition, you don't even need to know the structure completely. As the example shows, you can check on a field as well.

Relational + logical patterns

foreach (var user in users)
{
    if (user.Age is > 18 and < 60)
    {
        Console.WriteLine($"{user.Name} is Adult");
    }
    else if (user.Age is < 18 or > 60)
    {
        Console.WriteLine($"{user.Name} is Special Age Group");
    }
}

The relational pattern has made the comparison easier to read. Rather than just mathematical logical operators, you can use readable keywords. Apart from readability, it offers applications like comparing ages, checking a threshold, or checking a range.

Switch expression

foreach (var user in users)
{
    var category = user.Age switch
    {
        < 13 => "Child",
        < 20 => "Teen",
        < 60 => "Adult",
        _ => "Senior"
    };

    Console.WriteLine($"{user.Name} => {category}");
}

A switch case is a well-known way to compare multiple conditions. It is an ideal way of handling multiple cases instead of using a cluster of else-if. If you have some complex code to execute, then the following version is workable:

foreach (var user in users)
{
    string category;
    
    // Traditional switch statement with cases
    switch (user.Age)
    {
        case < 13:
            category = "Child";
            break;
        case < 20:
            category = "Teen";
            break;
        case < 60:
            category = "Adult";
            break;
        default:
            category = "Senior";
            break;
    }
    
    Console.WriteLine($"{user.Name} => {category}");
}

Type + condition pattern

object value = 150;

if (value is int number && number > 100)
{
    Console.WriteLine("Large number (old way)");
}

if (value is int and > 100)
{
    Console.WriteLine("Large number (pattern matching way)");
}

The pattern checks type and condition simultaneously, liberating you from separate casting and checking logic. It is useful for object or dynamic data.

List pattern

int[] nums = { 1, 2, 3 };

if (nums is [1, 2, 3])
{
    Console.WriteLine("Exact match");
}

if (nums is [1, .., 3])
{
    Console.WriteLine("Starts with 1 and ends with 3");
}

List matching is available in C# 11 and later versions. You can match the array/list structure and content and validate without a loop. List patterns can be handy for validating sequences, checking API payload lists, and detecting start/end patterns.

Positional pattern

var point = new Point(10, 20);

if (point is (10, 20))
{
    Console.WriteLine("Point matched (10,20)");
}

Another value comparison pattern matches objects based on their constructor/deconstructed values. Positional patterns are ideal for value types, such as records, because their comparisons are lightweight.

Combined pattern

foreach (var user in users)
{
    if (user is
        {
            Age: > 18,
            Address.City: "Lahore",
            Roles: ["User", ..]
        })
    {
        Console.WriteLine($"{user.Name} is eligible Lahore user");
    }
}

This code combines different patterns. Actually, this is one of the most realistic scenarios in which complex objects and requirements are combined to implement different patterns.

Null pattern

User? maybeUser = null;

if (maybeUser is not null)
{
    Console.WriteLine("User exists");
}
else
{
    Console.WriteLine("User is null");
}

Null pattern is a cleaner and more readable alternative to the traditional != null. Its usage spans large applications, input checks, and condition matching.

Guard clause

number = 7;

var result = number switch
{
    int n when n % 2 == 0 => "Even",
    int n when n % 2 != 0 => "Odd",
    _ => "Unknown"
};

Console.WriteLine(result);

A guard clause allows an additional condition inside a switch case. It tackles complex branching logic and mathematical conditions, giving flexibility when patterns alone aren't enough.

Request handling

foreach (var request in requests)
{
    var response = request switch
    {
        { Source: "System", Priority: > 5 } => "Critical System Request",
        { Source: "User", Priority: <= 5 } => "Normal User Request",
        _ => "Fallback"
    };

    Console.WriteLine($"{request.Source} ({request.Priority}) => {response}");
}

Request handling is a remarkable way to implement business logic while keeping it readable within a switch statement. It has a ton of use cases, like event processing, request routing, and validating business rules.

Step 5: Run and test

dotnet run

The results are expected and correct. However, we did the job more cleanly by combining patterns as needed.

Conclusion

Pattern matching has tons of usage. From validation to condition check, it is prominent. Using the right pattern at the right place can save a lot of code. In today's post, I shared some scenarios and their pattern solutions. In real scenarios, you have to know the patterns and use a combination when needed. This is real art.

Code: https://github.com/elmahio-blog/PatternMatchingDemo.git

A database is the root of most applications. Anything displayed or any operations performed often rely on the database. So, to build a loyal client base, you have to think in terms of a database. In today's post, I will go through some key strategies you can apply to optimize your PostgreSQL database and win the performance war.

Create indexes on frequently queried columns

Index data structures allow a row to be looked up quickly without scanning the entire table.

Without Indexes:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 500;

So, the execution time is 240 milliseconds.

After Index:

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

Normalize the database strategically

Normalization is a crucial step in database design to reduce anomalies and redundancy. Simply break your table into maintainable tables with logically coherent columns.

Instead of keeping role information in the user table, introduce a separate Role table and map it to User to keep data integral and non-redundant.

Keep in mind that over-normalization can also lead to performance penalties. Joining tables is more expensive than querying a single table, so you should analyze which columns should be split into new, normalized tables.

Avoid SELECT *

Fetch only the required columns from the query.

Bad query with SELECT *:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status = 'Completed';

Now, with selective columns, let's say I only need id and amount, so I'm fetching only those columns:

EXPLAIN ANALYZE
SELECT id, amount
FROM orders
WHERE status = 'Completed';

In this example, the execution time is identical, but it reduces I/O and memory usage, especially as the database schema becomes more complex. If the columns you select are all part of an index, PostgreSQL can return the data directly from the index without touching the heap (the actual table file). In that case, this can improve performance as well as I/O.

Order JOINs properly

Ordering JOINs properly can also affect performance. Modern PostgreSQL uses a cost-based optimizer. It analyzes table statistics to determine the most efficient join order and method regardless of the order you write them in the query. But when dealing with older versions or if settings like join_collapse_limit is set manually, this still applies.

Consider this query:

EXPLAIN ANALYZE
SELECT *
FROM orders o
JOIN customers c
ON o.customer_id = c.id
WHERE c.country = 'USA';

Now calling the customers table before:

EXPLAIN ANALYZE
SELECT *
FROM customers c
JOIN orders o
ON c.id = o.customer_id
WHERE c.country = 'USA';

The orders table has 1 million rows, while the clusters have 100k. When we fetch orders before then, we have a larger set to JOIN with customers. If we use customers first, then join with orders, fewer rows are filtered in the first stage. Filtering earlier reduces the rows participating in the join.

Use LIMIT When Exploring Data

In most cases, you will not use thousands of data points at once. Usually, you fetch a chunk of data to display at once, which is practical for UI display. Using that information, only fetch the required amount of data:

EXPLAIN ANALYZE
SELECT *
FROM orders
ORDER BY created_at DESC;

While the efficient way:

EXPLAIN ANALYZE
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 50;

Hence, fetching limited rows saves you in all ways.

Use partial indexes

A naive way for the query would be:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status = 'Completed';

Creating a partial index:

CREATE INDEX idx_completed_orders
ON orders(customer_id)
WHERE status = 'Completed';

Now the same query gives:

Hence, indexing reduced the execution time to half that of the prior query. Simply create an index for frequently filtered values to speed up.

Use Proper Data Types

Each data type has its own storage size and comparison time. Columns like primary keys should be handled with care due to these differences. Ints are faster than text, so use int as the primary key column for faster comparisons.

Good advice is to use the smallest sufficient data type. For example, use bigint instead of int if you expect more than 2.1 billion rows, as changing a PK type later is a high-risk operation.

Avoid Functions on Indexed Columns

Functions on indexed columns prevent index usage:

EXPLAIN ANALYZE
SELECT *
FROM customers
WHERE LOWER(name) = 'customer 100';

Creating an index :

CREATE INDEX idx_customers_lower_name
ON customers(LOWER(name));

Now, the same query gives:

We included the function in the index. Index can now support the function.

Partition large tables

Partitioning is another healthy way to handle large data sets. It divides a table into smaller ones:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-06-30';

Create a copy table where we will use partitioning:

CREATE TABLE public.orders_partitioned(
    id          serial NOT NULL ,
    customer_id integer,
    amount      numeric,
    status      text,
    created_at  timestamp without time zone NOT NULL,
    CONSTRAINT orders_partitioned_pkey PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

Create yearly partitions:

CREATE TABLE public.orders_2024
    PARTITION OF public.orders_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE public.orders_2025
    PARTITION OF public.orders_partitioned
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE public.orders_2026
    PARTITION OF public.orders_partitioned
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
    
-- Default partition — catches ANY row that doesn't fit the ranges above
CREATE TABLE public.orders_default
    PARTITION OF public.orders_partitioned
    DEFAULT;

Copy the data into our partitioned table:

INSERT INTO public.orders_partitioned SELECT * FROM public.orders;

Just to check if everything is well:

SELECT
    inhrelid::regclass AS partition
FROM pg_inherits
WHERE inhparent = 'orders_partitioned'::regclass;

We can see 4 partitions created. Now the same query after partition:

EXPLAIN ANALYZE
SELECT *
FROM orders_partitioned
WHERE created_at BETWEEN '2025-01-01' AND '2025-06-30';

Use a transaction for bulk operations.

Operations like inserting or updating multiple rows at once use transactions. Transaction groups multiple SQL operations into a single unit of work. Instead of committing after every statement, PostgreSQL commits only once at the end of the transaction:

INSERT INTO orders(customer_id, amount, status)
VALUES (1,100,'Completed');

INSERT INTO orders(customer_id, amount, status)
VALUES (2,200,'Completed');

INSERT INTO orders(customer_id, amount, status)
VALUES (3,300,'Completed');

The above one writes each row one by one. Each insertion triggers a WAL write, a disk sync, and a commit operation:

BEGIN;

INSERT INTO orders(customer_id, amount, status)
VALUES (1,100,'Completed');

INSERT INTO orders(customer_id, amount, status)
VALUES (2,200,'Completed');

INSERT INTO orders(customer_id, amount, status)
VALUES (3,300,'Completed');

COMMIT;

The transaction was completed in less than half the time. That is the only difference, with only 3 inserts. Once you scale up to more rows, this difference becomes more pronounced. transaction commits only once, awakening the expensive operations once for all the rows.

Avoid Long-Running Transactions

Long-running transactions keep old row versions alive underp PostgreSQL's MVCC. This prevents VACUUM from cleaning dead rows, causing table bloat:

BEGIN;

SELECT *
FROM orders;

COMMIT;

BEGIN;

UPDATE orders
SET status = 'Completed'
WHERE id = 100;

COMMIT;

Short, focused transactions don't last as long as the first one in our example did. Besides, VACUUM can reclaim space and shorter transactions lower lock contention.

Clean the table and index bloats

Postgres table bloat occurs when a table contains dead rows that are no longer visible to any transaction but still occupy disk space. Postgres DELETE or UPDATE Operations create dead rows in the tables they modify. For example, if we run the following on an order row with a 400 amount:

UPDATE orders
SET amount = 1000
WHERE id = 55;

Postgres keeps the older row but marks it as dead, and adds a new one. That means the deleted row will be there along with the new one. Such dead rows pile up, overwhelming the storage. PostgreSQL provides VACUUM to reclaim or reuse the storage.

Index bloat occurs when indexes contain references to dead rows that still occupy space. Every update also updates indexes, so PostgreSQL keeps dead row indexes.

Bloated tables and indexes can significantly slow down queries and unnecessarily consume additional disk space, pressuring I/O.

We can simply fix table bloat manually:

VACUUM orders;

This reclaims storage occupied by dead rows and ensures that the space can be reused:

VACUUM FULL orders;

The above is an intensive counterpart that reclaims all storage occupied by dead rows, including the space at the end of the table. Be aware that a full vacuum will block all reads and writes to that table until it finishes. For large datasets, this can cause hours of downtime.

Usually, an AUTOVACUUM is by default enabled, which we can check

SHOW autovacuum;

You can configure the auto vacuum. Make sure it remains on, otherwise, leaving dead rows can eat up space, which is dangerous for the production database. You can simply do it by running:

ALTER SYSTEM SET autovacuum = 'on';
SELECT pg_reload_conf();

How often should it run:

-- Set the naptime to 30 seconds for more aggressive cleanup
ALTER SYSTEM SET autovacuum_naptime = '30s';
-- Reload the configuration to apply changes without restarting the service
SELECT pg_reload_conf();

When vacuum triggers:

autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2

The above configuration means running the vacuum when the number of dead rows exceeds 50 and when 20% of the table size is reached.

Apply changes by running:

SELECT pg_reload_conf();

For a table level:

ALTER TABLE orders
SET (
    autovacuum_vacuum_threshold = 50
    autovacuum_vacuum_scale_factor = 0.2
);

To view how much storage your tables are taking, run:

SELECT
    relname,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Conclusion

As a popular database choice for scalable applications, PostgreSQL requires several performance optimization techniques to keep the system fast. In this post, I presented the 12 most insightful ways to speed up your database. From indexing and partitioning to projection and handling bloat. I shared them in detail and explained how to implement them.

We know that every Entity Framework Core (EF Core) LINQ query has a corresponding SQL query. That equivalent SQL is actually executed under the hood. Some LINQ expressions involve. NET-specific code, such as calling a method, using a reflection filter, or accessing files. You may have found several code blocks of your query that could not be translated. Why does it happen? In today's post, I will cover the reasons along with on-ground examples.

LINQ Queries Translation

We will write different LINQ queries in a real project and examine what results they yield. I will use a Console application with a PostgreSQL database as an example.

Step 1: Create a project

dotnet new console -n EfTranslationDemo
cd EfTranslationDemo

Step 2: Install NuGet packages

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

Step 3: Model for the table

This will be a single-table application consisting of the Perfume model.

public class Perfume
{
    public int Id { get; set; }

    public string Name { get; set; } = string.Empty;

    public string Brand { get; set; } = string.Empty;

    public decimal Price { get; set; }

    public int Rating { get; set; }

    public DateTime ReleaseDate { get; set; }
}

Step 4: Create a database

To speed up the process, I have already created the database and the Perfumes table.

CREATE DATABASE perfumedb;

CREATE TABLE public."Perfumes" (
    "Id" serial PRIMARY KEY,
    "Name" varchar(255) NOT NULL DEFAULT '',
    "Brand" varchar(255) NOT NULL DEFAULT '',
    "Price" decimal(10,2) NOT NULL,
    "Rating" integer NOT NULL,
    "ReleaseDate" timestamp NOT NULL DEFAULT (NOW() AT TIME ZONE 'UTC')
);

Step 5: Set up the DB context

using EfTranslationDemo.Models;
using Microsoft.EntityFrameworkCore;

namespace EfTranslationDemo.Data;

public class ApplicationDbContext: DbContext
{
    public DbSet<Perfume> Perfumes => Set<Perfume>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options
            .UseNpgsql("Your Connection string with db name perfumedb")
            .LogTo(Console.WriteLine);
    }
}

Step 6: Seed data

To begin with, I will seed some data programmatically.

using EfTranslationDemo.Data;
using EfTranslationDemo.Models;
using Microsoft.EntityFrameworkCore;

using var context = new ApplicationDbContext();

context.Database.EnsureCreated();

SeedData(context);

static void SeedData(ApplicationDbContext context)
{
    if (context.Perfumes.Any())
        return;

    context.Perfumes.AddRange(
        new Perfume { Name="Sauvage", Brand="Dior", Price=120, Rating=9, ReleaseDate=new DateTime(2018,1,1,0,0,0, DateTimeKind.Utc)},
        new Perfume { Name="Bleu De Chanel", Brand="Chanel", Price=150, Rating=10, ReleaseDate=new DateTime(2017,1,1,0,0,0, DateTimeKind.Utc)},
        new Perfume { Name="Aventus", Brand="Creed", Price=300, Rating=10, ReleaseDate=new DateTime(2015,1,1,0,0,0, DateTimeKind.Utc)},
        new Perfume { Name="F Black", Brand="Ferragamo", Price=60, Rating=7, ReleaseDate=new DateTime(2019,1,1,0,0,0, DateTimeKind.Utc)}
    );

    context.SaveChanges();
}

Fast forward, the data is seeded to the database

Step 7: LINQ queries testing

1. Query with price filter

A simple query to fetch perfumes with a price of more than 100.

Console.WriteLine("Full translable");
var query1 = context
    .Perfumes
    .Where(p => p.Price > 100)
    .Select(p => new { p.Name, p.Price });

Console.WriteLine(query1.ToQueryString());

2. Filter with Name

We will filter the data with the name starting with 'B'.

var query2 = context
    .Perfumes
    .Where(p => p.Name.StartsWith("B"));

Console.WriteLine(query2.ToQueryString());

StartsWith() translates to SQL's LIKE . Similarly, EndsWith and Contains are also translated.

3. Client Side Execution

Another translatable but performance-heavy solution is

var perfumes = context
    .Perfumes
    .ToList()
    .Where(p => p.Price > 100);

Console.WriteLine(perfumes.Count());

It loads all the data in memory first, then applies a filter on the stored data. Such a solution can cause significant problems, especially when the data is large.

4. External list filter

We can write a translatable filter with an external list

var brands = new List<string> { "Dior", "Creed" };

var query = context
    .Perfumes
    .Where(p => brands.Contains(p.Brand));

Console.WriteLine(query.ToQueryString());

EF Core converts the C# in-memory list into SQL constants.

5. DateTime filter

Any filter with parsing datetime will work the same as any other.

var query = context
    .Perfumes
    .Where(p => p.ReleaseDate.Year > 2016);

Console.WriteLine(query.ToQueryString());

Common DateTime properties are supported.

6. Custom method filter

What you cannot do in a LINQ query is to filter by a method. LINQ can't find an SQL equivalent of it.

static bool IsLuxury(Perfume perfume)
{
    return perfume.Price > 200;
}

var query = context
    .Perfumes
    .Where(p => IsLuxury(p));

Console.WriteLine(query.ToQueryString());

EF Core cannot inspect the body of arbitrary C# methods.

7. Regex matching

We can simply put a regex pattern in the filter as well.

var query = context
    .Perfumes
    .Where(p => Regex.IsMatch(p.Name, "^A"));

Console.WriteLine(query.ToQueryString());

var result = query.ToList();
Console.WriteLine(result.Count());

8. Non-Translatable Projection

string GetCategory(decimal price)
{
    return price > 150 ? "Luxury" : "Regular";
}

var query = context
    .Perfumes
    .Select(p => new
    {
        p.Name,
        Category = GetCategory(p.Price)
    });

Console.WriteLine(query.ToQueryString());

The right way

var query = context
    .Perfumes
    .Select(p => new
    {
        p.Name,
        Category = p.Price > 150 ? "Luxury" : "Regular"
    });

Console.WriteLine(query.ToQueryString());

9. Reflection

.NET does not have an equivalent in SQL.

var query = context
    .Perfumes
    .Where(p => p.GetType().GetProperty("Price") != null);

Console.WriteLine(query.ToQueryString());

10. File System Access

var query = context
    .Perfumes
    .Where(p => File.Exists($"brands/{p.Brand}.txt"));

Console.WriteLine(query.ToQueryString());

11. Random number generation

var query = context
    .Perfumes
    .Where(p => Random.Shared.Next(0, 10) > 5);

Console.WriteLine(query.ToQueryString());

A filter using random number generation did not translate.

12. string.Compare method

One of the cool methods that we often use for string comparison is string.Comparison. Unfortunately, you cannot use it in a LINQ query.

var query = context
    .Perfumes
    .Where(p => 
        string.Compare(p.Name, "Sauvage", 
            StringComparison.OrdinalIgnoreCase) == 0
        );
var result  = await query.ToListAsync();
Console.WriteLine(query.ToQueryString());

Why does EF Core have limitations in translating LINQ queries?

One question arises that even after years of improvement and enhancement, EF Core does not translate some common C# expressions in LINQ. Actually, LINQ does not merely read and execute any query. It first creates an expression tree of the query. Next, these trees are converted to SQL. SQL queries are a declarative language that describes what data you want, not how to compute it step by step. So, methods' bodies are not parsed, as in our case with the IsLuxury method, where expressions do not analyze arbitrary method bodies for translation. Also, C# is a full programming language, whereas SQL lacks many of the features of a programming language. Neither can it read its logic, as SQL servers cannot execute .NET runtime code.

EF Core is responsible for translating an expression tree to database-specific SQL. We know that every database has its own SQL dialect. For example, DATE_PART('year', column) in a PostgreSQL expression, while the SQL Server equivalent is YEAR(column). So EF must maintain different translators for each database. It does not rely on a single translator, so there are limitations in translating each and every query across diverse database providers. Finally, some .NET runtime features are specific to the program, such as random number generation, ordinal string comparison, and reflection, which are impossible to translate.

Conclusion

EF Core is a popular ORM and the default choice for many developers for database operations. EF Core uses LINQ for querying and enables you to use databases as if they were in C#. However, the underlying database cannot fully replicate a complete programming language like C#. Many programming expressions and filtering logic cannot be translated into a database query. Calling a method in a LINQ query, using reflection, and ordinal string comparison are examples of limitations in EF Core. SQL is specifically designed to fetch data and is not a programming language. It does not have the .NET runtime, so it cannot execute .NET features. In this post, I have gone through many examples that can be translated into SQL and some common queries that cannot.

Code: https://github.com/elmahio-blog/EfTranslationDemo.git

Entity Framework Core (EF Core) is working fine in your project. But the moment you use views, the migration gets messy. As a developer, I know any problem in the migration is haunting. You have to update and take care of other migrations so they don't get disturbed. If not done well, database views can throw you into a pitfall. In today's post, I will walk through how to map views in EF Core without breaking migrations.

What is a database view?

A view is a named query that acts as a virtual table. It is defined using base tables or previously defined views and provides an abstraction of a complex query. Views do not store any data, but they execute the enclosed query. Views reduce client-side code by encapsulating logic and adding reusability. Although it supports data modifications like create and update in some cases, it is primarily used for read-only operations.

Mapping database views with EF Core

Let's see, with the glasses from the actual project, how we can utilize views. I will use a console project with a PostgreSQL database.

Step 1: Create the project

dotnet new console -n EfCoreViewDemo
cd EfCoreViewDemo

Step 2: Install necessary NuGet packages

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.Extensions.Configuration
dotnet add package Microsoft.Extensions.Configuration.Json

Configuration packages will be used to load appsettings.

Step 3: Create appsettings.json

Adding appsettings.json with a database connection string.

{
  "ConnectionStrings": {
    "PostgresConnection": "Host=localhost;Port=5432;Database=productVDb;Username=postgres;Password=1234"
  }
}

Step 4: Define Models

Add the Product class:

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

The ProductSummary class will catch the results of the view:

namespace EfCoreViewDemo.Models;

public class ProductSummary
{
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
}

Step 5: Configure DbContext

using EfCoreViewDemo.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

namespace EfCoreViewDemo.Data;

public class ApplicationDbContext: DbContext
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<ProductSummary> ProductSummaries => Set<ProductSummary>();

    private readonly string _connectionString;

    public ApplicationDbContext()
    {
        var config = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json")
            .Build();

        _connectionString = config.GetConnectionString("PostgresConnection");
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql(_connectionString);
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<ProductSummary>()
            .ToView("vw_product_summary")   
            .HasNoKey();                  
    }
}

One important configuration is ToView(...) that specifies the DbSet map as a database view. Calling ProductSummaries will fetch data from the view named vw_product_summary. HasNoKey() specifies that views are read-only structures in the database and do not contain any primary key. If not specified, EF Core expects a primary key on the entity and throws an exception.

Step 6:  Set up appsettings.json in the project

By default, a console app will expect the file in the bin directory. To read newly added appsettings from the root directory, add the following inside the <Project> tag of the application's project file:

<ItemGroup>
  <None Update="appsettings.json">
    <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
  </None>
</ItemGroup>

Step 7: Run migrations

dotnet ef migrations add InitialCreate

So the migration will look like

using Microsoft.EntityFrameworkCore.Migrations;
using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata;

#nullable disable

namespace EfCoreViewDemo.Migrations
{
    /// <inheritdoc />
    public partial class InitialCreate : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Products",
                columns: table => new
                {
                    Id = table.Column<int>(type: "integer", nullable: false)
                        .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
                    Name = table.Column<string>(type: "text", nullable: false),
                    Price = table.Column<decimal>(type: "numeric", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Products", x => x.Id);
                });            
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Products");
        }
    }
}

But we skipped views from EF Core's tracking, so we need to add it manually in the Up method.

migrationBuilder.Sql(@"
  CREATE VIEW vw_product_summary AS
  SELECT ""Name"", ""Price""
  FROM ""Products"";
");

While in the Down method:

migrationBuilder
    .Sql(@"DROP VIEW IF EXISTS vw_product_summary;");

To reflect the migration

dotnet ef database update

Now, the database looks like

Step 8: Define program

using EfCoreViewDemo.Data;
using EfCoreViewDemo.Models;

using var context = new ApplicationDbContext();

context.Products.Add(new Product { Name = "Laptop", Price = 1000 });
context.Products.Add(new Product { Name = "Keyboard", Price = 100 });
context.Products.Add(new Product { Name = "Headphone", Price = 150 });
context.Products.Add(new Product { Name = "Web cam", Price = 200 });
context.Products.Add(new Product { Name = "Mouse", Price = 50 });

context.SaveChanges();

var summaries = context.ProductSummaries.ToList();

foreach (var item in summaries)
{
    Console.WriteLine($"{item.Name} - {item.Price}");
}

To summarize, I added 5 records manually. Later, I am calling ProductSummaries, which is a view.

Step 9: Run the project

Manual migration allows you to automate view definition without breaking migration functionality. A naive approach is to write views directly in the database.

Step 10: Update the view

Let's say a requirement came, and we need to update the view.

Create an empty migration:

dotnet ef migrations add UpdateProductSummaryView

Manual query in migration will look like this:

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace EfCoreViewDemo.Migrations
{
    /// <inheritdoc />
    public partial class UpdateProductSummaryView : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"
                DROP VIEW IF EXISTS vw_product_summary;

                CREATE VIEW vw_product_summary AS
                SELECT ""Name"", ""Price"", ""Price"" * 0.9 AS ""DiscountedPrice""
                FROM ""Products"";
            ");
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"
                DROP VIEW IF EXISTS vw_product_summary;

                CREATE VIEW vw_product_summary AS
                SELECT ""Name"", ""Price""
                FROM ""Products"";
            ");
        }
    }
}

Updating the model accordingly:

namespace EfCoreViewDemo.Models;

public class ProductSummary
{
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public decimal DiscountedPrice { get; set; }
}

In Program.cs, the view call will become:

var summaries = context.ProductSummaries.ToList();

foreach (var item in summaries)
{
    Console.WriteLine($"{item.Name} - {item.Price} - {item.DiscountedPrice}");
}

Hence, the result

Conclusion

Database Views are great for enclosing complex logic as virtual tables. Developers often use it extensively in the application. However, views can make EF Core migration tracking tedious if not managed properly. I provided a solution in today's blog on how you can design views without harming migration.

Code: https://github.com/elmahio-blog/EfCoreViewDemo.git

What really happens when you write throw new Exception() in .NET? Microsoft guidelines state that

When a member throws an exception, its performance can be orders of magnitude slower. 

It's not just a simple jump to a catch block, but a lot goes in CLR (Common Language Runtime). Expensive operations such as stack trace capture, heap allocations, and method unwinding occur each time. You will not want to use them in any hot paths. Today, In today's post, I will help you decide when exceptions are appropriate and when a simple alternative type might be better.

What is an Exception?

An exception is an error condition or unexpected behaviour during the execution of a program. Exceptions can occur at runtime for various reasons, such as accessing a null object, dividing by zero, or requesting a file that is not found. A C# exception contains several properties, including a Message describing the cause of the exception. StackTrace contains the sequence of method calls that led to the exception in reverse call order to trace the exception source.

How does an exception work?

The try block encloses the code prone to exceptions. try/catch protects the application from blowing up. Use the throw keyword to signal the error and throw an Exception object containing detailed information, such as a message and a stack trace. The caught exception allows the program to continue gracefully and notify the user where and what error occurred. When an error occurs, the CLR searches for a compatible catch block in the current method. If not found, it moves up the call stack to the calling method, and so on. Once a matching catch is found based on the exception type, control jumps to that block. In an unhandled exception situation where no compatible catch block is found, the application can terminate. Exception handling uses a heap to store the message. To look for a catch body, the CLR unwinds the stack by removing intermediate stack frames. The JIT must generate EH tables and add hidden control-flow metadata.

What is OneOf<T> in .NET?

OneOf<T> or OneOf<T1, T2, T...> represents a discriminated union containing all possible returns of an operation or a method. It contains an array of types, allowing a method to return one of several defined possibilities. The OneOf pattern provides you with fine-grained control and type safety.

Examine Exceptions with the benchmark.

To truly understand it, let's create an application. I will use a console application.

Step 1: Create the project

dotnet new console -n ExceptionBenchmark
cd ExceptionBenchmark

Step 2: Add necessary packages

I am adding the Benchmark library along with OneOf, which is used for the OneOf return type.

dotnet add package BenchmarkDotNet
dotnet add package OneOf

Step 3: Set up the program.cs

All the code is in the Program.cs

using System;
using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Running;
using OneOf;

BenchmarkRunner.Run<ExceptionBenchmarks>();

[MemoryDiagnoser] 
public class ExceptionBenchmarks
{
    private const int Iterations = 100_000;
    private const int FailureEvery = 10;

    [Benchmark]
    public int NoException()
    {
        int failures = 0;

        for (int i = 1; i <= Iterations; i++)
        {
            if (!DoWork_NoException(i))
                failures++;
        }

        return failures;
    }

    private bool DoWork_NoException(int i)
    {
        return i % FailureEvery != 0;
    }

    [Benchmark]
    public int WithException()
    {
        int failures = 0;

        for (int i = 1; i <= Iterations; i++)
        {
            try
            {
                DoWork_WithException(i);
            }
            catch
            {
                failures++;
            }
        }

        return failures;
    }

    private void DoWork_WithException(int i)
    {
        if (i % FailureEvery == 0)
            throw new InvalidOperationException();
    }

    [Benchmark]
    public int WithOneOf()
    {
        int failures = 0;

        for (int i = 1; i <= Iterations; i++)
        {
            var result = DoWork_WithOneOf(i);

            if (result.IsT1)
                failures++;
        }

        return failures;
    }

    private OneOf<Success, Error> DoWork_WithOneOf(int i)
    {
        if (i % FailureEvery == 0)
            return new Error("Error");

        return new Success("Passed");
    }

    private readonly struct Success
    {
        public string Message { get; }

        public Success(string message)
        {
            Message = message;
        }
    }

    private readonly struct Error
    {
        public string Message { get; }

        public Error(string message)
        {
            Message = message;
        }
    }
}

The first method is simple with no exception. Then it throws an exception, and in subsequent methods, it finally returns an error object from OneOf. To make it realistic, each method will observe with 10% error and 90% success rate, as FailureEvery is set to 10. Success and Error are value types to avoid allocations, since they only return the value from the method.

Step 4: Run and test

dotnet run -c Release

The best performer is the NoException. But that is not practical, you have to identify unexpected behaviour and report it in the code flow. Firstly, a naive approach is to use an exception. Using it adds a time cost and increases the Garbage collector's Gen 0 pressure. So, our alternative to exception is OneOf, which significantly saved time and memory. We can further add Objects to the OneOf, considering the possible return values of the method.

In exceptions, Stack tracing is very expensive, as it propagates stacks, captures method names, stores IL offsets, and inspects frames. Also, the JIT inlining is limited during exceptions. With OneOfI used a struct value type, so Gen 0 utilization is minimized. Neither does it fall for stack trace nor unwind it. Hence, the execution remains linear.

When can I use an exception alternative?

In the following cases, exceptions can be replaced with Result or OneOf in normal application flows.

• Business rule rejection, such as the customers cannot order out-of-stock items. You can return an error in response.
• API validation, where you can simply return 400 with a custom message after figuring out all possible error cases.
• High-throughput paths where you cannot afford an exception mechanism.
• Validation failure, such as invalid email or mobile number input.
• Data not found scenarios where you know either the request data will be available or will not be found. Simply, you can deal with both cases.

When is an exception the optimal choice?

You don't remove fire alarms from a building because they're loud. You just don't pull them every time someone burns toast. We have some situations where exceptions stand out even if they are expensive.

• Exceptions occur when the program falls into an impossible state, such as when a null database connection is used. You cannot proceed anywhere because the connection is not even initialized for some reason.
• For environmental failures, you will opt for exceptions such as timeout failures, disk I/O failures, or database connection losses.
• Programming bugs where your code falls into a dead end, and it cannot handle further. Conditions where your input case exhaust, such as you have order statuses of pending, cancelled, and confirmed, are enumerated with 1,2 and 3, respectively. There is no case apart from that, so you can simply throw an ArgumentOutOfRangeException or a custom exception in the default case.
• If developing a library, use an exception to signal to the user what went wrong and halt normal execution. Here, you cannot force consumers to handle result types.

Conclusion

In high-performance systems, every allocation matters. Exceptions aim to provide a safeguard against anomalous conditions, but they can sometimes be a burden on memory and CPU. I put light on the exception of how much resource they can use for simple operations compared to their counterparts. We explored where it is suitable and where it can be replaced. In short, use exceptions for Unexpected, impossible, and environmental failures. While you can simply use Result/OneOf As an alternative, when conditions are expected, it is useful for business validation, user-driven errors, and high-frequency failures.

Code: https://github.com/elmahio-blog/ExceptionBenchmarks.git

APIs are the engine of modern applications. Your product belongs to any domain, either medical, banking, or IoT, APIs are most probable bricks in it. Good, maintainable, and reusable code promises a functional system. While a bad one makes maintenance and testing tedious. There is so much to care about in your application. In today's post, I will dig into writing a clean controller that fulfils the Single Responsibility Principle. We will see why exposing any business logic can harm the application and what exactly a controller should include.

Example with Fat Controller (bad design)

Consider the following ASP.NET Core controller implementation:

using System;
using Microsoft.AspNetCore.Mvc;

namespace EcCommerce.Controllers;

[ApiController]
[Route("api/[controller]")]
public class OrdersController : ControllerBase
{
    private readonly ApplicationDbContext _context;

    public OrdersController(ApplicationDbContext context)
    {
        _context = context;
    }
        
    [HttpPost]
    public async Task<IActionResult> CreateOrder(CreateOrderRequest request)
    {
        var user = await _context.Users
            .Include(u => u.Orders)
            .FirstOrDefaultAsync(u => u.Id == request.UserId);
    
        if (user == null)
            return NotFound("User not found");
    
        if (!user.IsActive)
            return BadRequest("User is not active");
    
        if (request.TotalAmount <= 0)
            return BadRequest("Invalid order amount");
    
        var todayOrdersCount = user.Orders
            .Count(o => o.CreatedAt.Date == DateTime.UtcNow.Date);
    
        if (todayOrdersCount >= 5)
            return BadRequest("Daily order limit exceeded");
    
        var order = new Order
        {
            UserId = user.Id,
            TotalAmount = request.TotalAmount,
            CreatedAt = DateTime.UtcNow
        };
    
        _context.Orders.Add(order);
        await _context.SaveChangesAsync();
    
        return Ok(order);
    }
}

The first thing in the code is that it is piercing to the eyes. Also, the controller knows too much of business logic and is really fat. If any business logic changes are required, we need to update the controller. Let's say we have to increase the daily order limit from 5 to 50, then we will need to update it here. Testing is difficult too, any test, even for business logic, will be done on the controller. All of that violates the DRY (Don't Repeat Yourself) and Single Responsibility Principles, as the controller is not inherently dedicated to these tasks. The example may look like I have added too much logic here, which people usually avoid, but you still need to be clear about exactly what the controllers should have. Many developers get confused and write a few lines of business logic where they shouldn't. The controllers should handle HTTP requests, validate the mapping, and return responses.

Business Rules Don't Leak Into Controllers

So, correcting the faulty controller here.

Step 1: Create a Domain Service

public interface IOrderService
{
    Task<Order> CreateOrderAsync(int userId, decimal totalAmount);
}

A very good way to encapsulate business logic is to introduce a service layer with an interface and its implementation, and inject them into the controller.

Step 2: Implementation of the domain service

public class OrderService : IOrderService
{
    private readonly ApplicationDbContext _context;

    public OrderService(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<Order> CreateOrderAsync(int userId, decimal totalAmount)
    {
        var user = await _context.Users
            .Include(u => u.Orders)
            .FirstOrDefaultAsync(u => u.Id == userId);

        if (user == null)
            throw new Exception("User not found");

        if (!user.IsActive)
            throw new Exception("User is not active");

        if (totalAmount <= 0)
            throw new Exception("Invalid order amount");

        var todayOrdersCount = user.Orders
            .Count(o => o.CreatedAt.Date == DateTime.UtcNow.Date);

        if (todayOrdersCount >= 5)
            throw new Exception("Daily order limit exceeded");

        var order = new Order
        {
            UserId = user.Id,
            TotalAmount = totalAmount,
            CreatedAt = DateTime.UtcNow
        };

        _context.Orders.Add(order);
        await _context.SaveChangesAsync();

        return order;
    }
}

Sometimes, you can add a repository layer below the services and inject it instead of using ApplicationDbContext directly.

Step 3: Thin Controller

using System;
using Microsoft.AspNetCore.Mvc;

namespace EcCommerce.Controllers;

[ApiController]
[Route("api/[controller]")]
public class OrdersController : ControllerBase
{
    private readonly IOrderService _orderService;

    public OrdersController(IOrderService orderService)
    {
        _orderService = orderService;
    }
        
    [HttpPost]
    public async Task<IActionResult> CreateOrder(CreateOrderRequest request)
    {
      
        var order = await _orderService
            .CreateOrderAsync(request.UserId, request.TotalAmount);
    
        return Ok(order);
    }
}

Now, we have a soothing code. Not to forget dependency injection in your Program.cs:

var builder = WebApplication.CreateBuilder(args);

// Other injections

builder.Services.AddScoped<IOrderService, OrderService>();

Clean Architecture Style (Domain-Driven)

One more way you can deal is to define the logic in the domain itself.

User Model

public class User
{
    public bool IsActive { get; private set; }
    public List<Order> Orders { get; private set; } = new();

    public void CanPlaceOrder(decimal totalAmount)
    {
        if (!IsActive)
            throw new Exception("User is not active");

        if (totalAmount <= 0)
            throw new Exception("Invalid order amount");

        var todayOrders = Orders
            .Count(o => o.CreatedAt.Date == DateTime.UtcNow.Date);

        if (todayOrders >= 5)
            throw new Exception("Daily limit exceeded");
    }
}

Service

public class OrderService : IOrderService
{
    private readonly ApplicationDbContext _context;

    public OrderService(ApplicationDbContext context)
    {
        _context = context;
    }
    
    public async Task<Order> CreateOrderAsync(int userId, decimal totalAmount)
    {
        var user = await _context.Users
            .Include(u => u.Orders)
            .FirstOrDefaultAsync(u => u.Id == userId);
    
        if (user == null)
            throw new Exception("User not found");
    
        user.CanPlaceOrder(totalAmount);
    
        var order = new Order(userId, totalAmount);
    
        _context.Orders.Add(order);
        await _context.SaveChangesAsync();
    
        return order;
    }
}

Dependency injection

var builder = WebApplication.CreateBuilder(args);

// Other injections

builder.Services.AddScoped<IOrderService, OrderService>();

Now the service becomes cleaner. In our corrected versions, we addressed all issues seen in the first code. If a new requirement asks to change the daily limit, we will go to the service or the domain model in a later example, and the controller will remain unaffected in both cases. Testing units are also made easy with the exposed method in the service. If any endpoint requires similar CreateOrder operations, then we can simply use it from OrderService, complying with the DRY principle. Our controller is now clean, and it adheres to the Single Responsibility Principle. The controller can only focus on validating requests and generating responses, while other tasks are handled in the underlying layers.

Conclusion

API controllers are responsible for exposing endpoints that your clients extensively rely on. They should validate the request, call the underlying layer like the service layer, and return the data. Keeping things in their place can help a lot with testing and maintenance, especially if your code is considerably large. One overlooked aspect is understanding exactly what a controller should contain. People often mistakenly add logic inside the controller method and fail to identify where to draw the line. I made it easy with this article and showed what potential problems can arise if business logic leaks inside the controller.

If you design an application with a data source, the repository pattern often comes to mind as a prominent choice. In fact, many developers see it as the default choice. However, the pattern is not helping every time. In this post, I will pinpoint some cases where the repository pattern is not the best choice.

What is a repository pattern?

The repository pattern is a design pattern that acts as an intermediate layer between data access and business logic. It abstracts the data source and implements the details, providing a clean representation of data manipulation as objects and lists.

Let us start by looking at how a repository pattern can be implemented with EF Core.

Start by adding a new model named Movie:

public class Movie
{
    public Guid Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public string Director { get; set; } = string.Empty;
    public int ReleaseYear { get; set; }
    public double ImdbRating { get; set; }
    public DateTime CreatedAtUtc { get; set; } = DateTime.UtcNow;
}

Next, add a IMovieRepository interface with the basic methods for adding, getting, and saving movies:

public interface IMovieRepository
{
    Task AddAsync(Movie movie);
    Task<Movie?> GetByIdAsync(Guid id);
    Task<List<Movie>> GetTopRatedAsync(double minRating);
    Task SaveChangesAsync();
}

Add an implementation of that interface using EF Core:

public class MovieRepository : IMovieRepository
{
    private readonly AppDbContext _context;

    public MovieRepository(AppDbContext context)
    {
        _context = context;
    }

    public async Task AddAsync(Movie movie)
    {
        await _context.Movies.AddAsync(movie);
    }

    public async Task<Movie?> GetByIdAsync(Guid id)
    {
        return await _context.Movies.FindAsync(id);
    }

    public async Task<List<Movie>> GetTopRatedAsync(double minRating)
    {
        return await _context.Movies
            .Where(m => m.ImdbRating >= minRating)
            .OrderByDescending(m => m.ImdbRating)
            .ToListAsync();
    }

    public async Task SaveChangesAsync()
    {
        await _context.SaveChangesAsync();
    }
}

Finally, I'll add a service class that shows how to use the movie repository:

public class MovieService
{
    private readonly IMovieRepository _repository;

    public MovieService(IMovieRepository repository)
    {
        _repository = repository;
    }

    public async Task<Guid> CreateMovieAsync(
        string title,
        string director,
        int releaseYear,
        double rating)
    {
        var movie = new Movie
        {
            Id = Guid.NewGuid(),
            Title = title,
            Director = director,
            ReleaseYear = releaseYear,
            ImdbRating = rating
        };

        await _repository.AddAsync(movie);
        await _repository.SaveChangesAsync();

        return movie.Id;
    }

    public async Task<List<Movie>> GetHighlyRatedMoviesAsync()
    {
        return await _repository.GetTopRatedAsync(8.0);
    }
}

If you are writing CRUD applications, implementing a data layer like this probably looks very familiar.

What are the advantages of the Repository pattern?

The repository pattern promises several key advantages.

• A clean separation of concerns where data access logic is centralized.
• Reusability, where the same repo methods can be used without copying the same logic again.

When to use the Repository pattern

Like any tool, it offers leverage only when in the right place. If you smell any scent in your code, go for the repository pattern.

• When your application does not rely on simple data storage or fetching but requires enquiring logic such as validation, projection, object preparation, or calculations. Domains such as insurance, banking, healthcare, and IoT require calculations, so the repository pattern can be helpful.
• The repository pattern can win for you if you are aggregating multiple data sources but presenting them as a single source to the upper layers. Usage of different data sources, such as MSSQL, Postgres, and external APIs, is kept hidden from the business logic layer.
• The repository layer can be handy if an application demands sophisticated caching strategies and you don't want to pollute the business layers. Hence, the service layer can be unaware of how the cache is configured, or even of whether the data comes from the cache or another source.
• For unit testing, you can employ the repository pattern, especially in error-critical systems such as financial systems, medical devices, and safety systems. Repositories enable you to test business logic in isolation by swapping real data access with test doubles. You can verify complex business rules, edge cases, and error handling without the overhead, unpredictability, and slowness of database tests.

When to avoid the Repository pattern

Well, we have seen the usefulness of the repository pattern. Now, rejoining our original question, "In what conditions can you avoid the repository pattern?"

• If your app is just basic Create, Read, Update, Delete operations without complex business logic, you simply go without it. A simple creation or fetch will not require verbose code, and adding a new layer will overengineer it.

For example, in the code, a repository pattern has simple operations:

public class UserRepository : IUserRepository 
{
    public User GetById(int id) => _context.Users.Find(id);
    public void Add(User user) => _context.Users.Add(user);
}

• With an ORM, you can avoid the abstraction layer. Most ORMs, such as Entity Framework Core, NHibernate, and Doctrine, already implement the repository pattern using DbSet and AppDbContext. You can simply deal with entities like collections and objects. If you don't have to add conditions, validation, and projections in the operations, you can choose simplicity. When wrapping an ORM in repositories, you are often hiding powerful features (like IQueryable for deferred execution or Include for eager loading) behind a more restrictive interface.
• Smaller projects also don't need to be tedious. If your project requires simple queries and consists of 10-15 tables, you are good to go without bombarding a small project with more code.
• Any abstraction comes with overhead. In a performance-critical system, a repository may not be the best choice for the same reason. Repository layers can require memory allocation, additional method calls, or complex query translation, which may slow down the software. Repositories often lead to the N+1 query problem or over-fetching data because the repository method returns a generic object rather than a specific projection (Select) tailored to the view.
• One more scenario where you can skip the repository pattern is in a microservice architecture. If a service is simple enough to have a small database and minimal operations, you don't need to trade off the repository pattern for maintenance and performance.
• While preparing reporting and analytics data, the repository pattern can be unnecessary. Mostly, the stored procedures, raw SQL queries, and database-specific optimizations do the whole job for us. The code only calls those underlying queries and returns. To keep things maintainable and, of course, speedy, you can avoid one layer.

Conclusion

The repository pattern is something you have probably used on your development journey. Why not? It is one of the popular choices for abstracting data access. However, abstractions have a hidden cost that I highlighted in the blog. I identified a few scenarios where you can escape it and barely lose anything. If you still want to use the repository pattern without losing its limitation, the specification pattern is another player that can work. It allows for reusable query logic without the bloat of a traditional repository.

For F1 racing, choosing the right car is as important as your expertise. No matter how skilled you are, if you race in an ordinary car, you can't stand out. You need to understand the race and use the F1 racing car. The same goes for programming. Going with the wrong option can hurt your application. If your application contains high-throughput junctions prone to bottlenecks, you are left with choices for different collections. Today, I will explore popular data collection options, including IEnumerable and List, and examine how IEnumerable can hinder hot paths.

IEnumerable in C#

IEnumerable is an interface that represents a forward-only sequence of elements. It represents a behaviour that can be deferred or materialized based on implementation. It exposes a single method, GetEnumerator() that returns an IEnumerator<T> object you can iterate sequentially. However, it does not support indexing.

List in C#

A list is a dynamic collection that resizes automatically and allows access to elements by index. The List class provides methods such as Add, Remove, and AddRange. A List is materialized, loading all data into memory. In fact, List implements IEnumerable.

IEnumerable analysis with benchmark

To observe actual ground effects, let's create a console application where we can benchmark IEnumerable with a concrete List.

Step 1: Create the project

dotnet new console -n IEnumerableBenchmark
cd IEnumerableBenchmark

Step 2: Install the BenchmarkDotNet library

dotnet add package BenchmarkDotNet

Step 3: Prepare the benchmarking code

Here, we will define everything in the Program.cs file:

using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Running;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;

BenchmarkRunner.Run<EnumerationBenchmarks>();

[MemoryDiagnoser]
public class EnumerationBenchmarks
{
    private List<int> _list = null!;
    private IEnumerable<int> _interfaceEnumerable = null!;
    private IEnumerable<int> _linqEnumerable = null!;
    private IEnumerable<int> _yieldEnumerable = null!;
    private int[] _array = null!;

    [Params(1000, 100_000)]
    public int N;

    [GlobalSetup]
    public void Setup()
    {
        // Independent data sources
        _list = Enumerable.Range(1, N).ToList();

        _array = Enumerable.Range(1, N).ToArray();

        _interfaceEnumerable = Enumerable.Range(1, N);

        _linqEnumerable = Enumerable.Range(1, N)
                                    .Where(x => x % 2 == 0)
                                    .Select(x => x * 2);

        _yieldEnumerable = CreateYieldSequence(N);
    }

    private IEnumerable<int> CreateYieldSequence(int count)
    {
        for (int i = 1; i <= count; i++)
        {
            if (i % 2 == 0)
                yield return i * 2;
        }
    }

    // -------------------------------
    // List - for (optimized)
    // -------------------------------
    [Benchmark(Baseline = true)]
    public int List_For()
    {
        int sum = 0;
        var list = _list;
        int count = list.Count;

        for (int i = 0; i < count; i++)
        {
            var x = list[i];
            if (x % 2 == 0)
                sum += x * 2;
        }

        return sum;
    }

    // -------------------------------
    // List - foreach
    // -------------------------------
    [Benchmark]
    public int List_Foreach()
    {
        int sum = 0;

        foreach (var x in _list)
        {
            if (x % 2 == 0)
                sum += x * 2;
        }

        return sum;
    }

    // -------------------------------
    // IEnumerable - foreach
    // -------------------------------
    [Benchmark]
    public int IEnumerable_Foreach()
    {
        int sum = 0;

        foreach (var x in _interfaceEnumerable)
        {
            if (x % 2 == 0)
                sum += x * 2;
        }

        return sum;
    }

    // -------------------------------
    // Array - foreach
    // -------------------------------
    [Benchmark]
    public int Array_Foreach()
    {
        int sum = 0;

        foreach (var x in _array)
        {
            if (x % 2 == 0)
                sum += x * 2;
        }

        return sum;
    }

    // -------------------------------
    // LINQ Deferred Execution
    // -------------------------------
    [Benchmark]
    public int Linq_Deferred()
    {
        return _linqEnumerable.Sum();
    }

    // -------------------------------
    // Yield State Machine
    // -------------------------------
    [Benchmark]
    public int Yield_Enumeration()
    {
        int sum = 0;

        foreach (var x in _yieldEnumerable)
        {
            sum += x;
        }

        return sum;
    }

    // -------------------------------
    // Multiple Enumeration
    // -------------------------------
    [Benchmark]
    public int Multiple_Enumeration()
    {
        int sum = 0;

        if (_linqEnumerable.Any())
        {
            foreach (var x in _linqEnumerable)
            {
                sum += x;
            }
        }

        return sum;
    }

    // -------------------------------
    // Span<T>
    // -------------------------------
    [Benchmark]
    public int Span_For()
    {
        int sum = 0;
        var span = CollectionsMarshal.AsSpan(_list);

        for (int i = 0; i < span.Length; i++)
        {
            var x = span[i];
            if (x % 2 == 0)
                sum += x * 2;
        }

        return sum;
    }
}

I defined a list and an IEnumerable. In the Setup, we will initialize each one with 1000 and 100000 elements. There are several other testers to get how IEnumerable performs as deferred and as materialized collections, as shown in the methods.

Step 4: Run the project

Let's run our project in release mode

dotnet run -c Release

So, the results show the cost of IEnumerable.

As per the results, List outperformed IEnumerable by up to 7x. Thanks to contiguous memory allocation and JITs array optimization, arrays outclass the list, too. But flexibility and numerous handy methods make the list more usable in most scenarios. The single test does not prove that IEnumerable should be abandoned. In fact, you will not want to overload memory by loading the entire dataset if it is extremely large. Then theIEnumerable's behaviour took effect. The List is a help for small to medium-sized collections and requires frequent manipulation, such as adding and removing items. As immediate execution loads data immediately, lists are ideal when you need data to be readily available and need frequent manipulation.

Using IEnumerable in hot paths slows performance and does not support data manipulation. Usually, applications do not load all data at once; instead, they use filtering or pagination when fetching data. In such cases, lists leverage immediate execution.

Conclusion

We may not need to emphasize the importance of performance in any application. We already know the importance of keeping the operations as fast as possible. Identifying and mitigating the hot path is one link in this chain. I shared one underrated point about using the right collection in such bottleneck areas. Being cautious about IEnumerable in hot paths, can be good for the application and your peace of mind.

Code: https://github.com/elmahio-blog/IEnumerableBenchmark