
<Sponsorships>

Introducing [**Shesha**](https://shesha.io?utm_source=milan&utm_medium=email&utm_campaign=20240518), a brand new, open-source, low-code framework for .NET developers.
Create business applications faster and with >80% less code! [**Learn more here.**](https://shesha.io?utm_source=milan&utm_medium=email&utm_campaign=20240518)

[**Collaborate directly with your external partners**](https://learning.postman.com/docs/collaborating-in-postman/using-workspaces/partner-workspaces/) in Postman!
You can use [**Partner Workspaces**](https://learning.postman.com/docs/collaborating-in-postman/using-workspaces/partner-workspaces/)
to help your partners consume your team's APIs and work together on API projects.
[Learn more here](https://learning.postman.com/docs/collaborating-in-postman/using-workspaces/partner-workspaces/).

</Sponsorships>

Managing database schemas as your applications grow can quickly become a headache.
Manual changes are error-prone and time-consuming.
This can easily lead to inconsistencies between development and production environments.
I've seen these issues firsthand on countless projects, and it's not pretty.
How can we do better?

Enter Entity Framework (EF) Migrations, a powerful tool that lets you version your database schemas.

Imagine this: Instead of writing SQL scripts, you define your changes in code.
Need to add a column?
Rename a table?
No problem - EF Migrations has you covered.
It tracks every modification to the data model.
You can review, test, and apply changes confidently, even across different environments.

In this newsletter, we'll break down the essentials of EF Migrations:

- **Creating Migrations**: Defining and generating migrations that capture your schema changes.
- **Migration SQL Scripts**: Understanding the SQL generated by your migrations and how to use it.
- **Applying Migrations**: Different ways to apply migrations to your database.
- **Migration Tools**: Exploring additional tools and frameworks for managing database migration.
- **EF Migration Best Practices:**: I'll share my recommendations from using EF for years.

We have many examples to cover, so let's dive in.

## Creating Migrations

If you're completely new to EF migrations, I recommend checking out the [EF migrations docs](https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations)
to grasp the fundamentals.
Moving forward, I'll assume you have some prior knowledge of EF Core.

We'll need an entity and a database context before we can create migrations with EF.

Let's define a simple `Product` entity:

```csharp
public class Product
{
    public int Id { get; set; }

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

    public string? Description { get; set; }

    public decimal Price { get; set; }
}
```

We will also need a `DbContext` implementation, so let's define the `AppDbContext` class.
In the `OnModelCreating` method, we're going to configure the `Product` entity.

```csharp
public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>(builder =>
        {
            builder.ToTable("Products", tableBuilder =>
            {
                tableBuilder.HasCheckConstraint(
                    "CK_Price_NotNegative",
                    sql: $"{nameof(Product.Price)} > 0");
            });

            builder.HasKey(p => p.Id);

            builder.Property(p => p.Name).HasMaxLength(100);

            builder.Property(p => p.Description).HasMaxLength(1000);

            builder.Property(p => p.Price).HasPrecision(18, 2);

            builder.HasIndex(p => p.Name).IsUnique();
        });
    }
}
```

Let's break down a few of the methods we're using:

- `ToTable` - Configures the table name for the specific entity.
  It also allows us to provide `TableBuilder` delegate.
  We can use it to configure a check constraint using `HasCheckConstraint`.
- `HasKey` - Configures the table's primary key.
  EF will also pick up the `Id` property by convention, so this step is optional.
- `HasProperty` - Represents the entry point for configuring individual properties of the entity.
- `HasIndex` - Defines an index on the specified property (or properties).
  We can also declare that the index should be unique by calling `IsUnique`.

We're now ready to create our first migration.
I'm going to use the PowerShell syntax:

```
Add-Migration Create_Database
```

This will create the first database migration called `Create_Database`.
The migration will apply the configuration we defined in the `OnModelCreating` method.
It contains the `Up` and `Down` methods, allowing us to apply or revert changes to the database.

Note that some operations are destructive (like removing a column) and can't be easily reverted.
It's up to you to examine the generated migration and prevent any possible data loss.

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

public partial class Create_Database : Migration
{
    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: "character varying(100)", maxLength: 100, nullable: false),
                Description = table.Column<string>(type: "character varying(1000)", maxLength: 1000, nullable: true),
                Price = table.Column<decimal>(type: "numeric(18,2)", precision: 18, scale: 2, nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Products", x => x.Id);
                table.CheckConstraint("CK_Price_NotNegative", "Price > 0");
            });

        migrationBuilder.CreateIndex(
            name: "IX_Products_Name",
            table: "Products",
            column: "Name",
            unique: true);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "Products");
    }
}
```

## Customizing Migrations

We can also modify the migration files if we need to apply some custom changes.

A notable example is renaming a column.
Let's say we rename the `Description` property to `ShortDescription`.
In some EF versions, this would result in the following migration:

```csharp
migrationBuilder.DropColumn(
    name: "Description",
    table: "Customers");

migrationBuilder.AddColumn<string>(
    name: "ShortDescription",
    table: "Products",
    nullable: true);
```

What's the problem here?
By calling `DropColumn` first, we will remove the column from the database and lose valuable data.

What we actually want to do is rename the existing column.
So, we can modify the migration file to use the `RenameColumn` method:

```csharp
migrationBuilder.RenameColumn(
    name: "Description",
    table: "Products",
    newName: "ShortDescription");
```

Another example is executing custom SQL commands from your migrations.
Custom SQL commands are helpful when we can't express something through the EF fluent API.
I've used it in the past to migrate data from one column to another or define a complex index.

```csharp
public partial class Update_Products : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("<YOUR CUSTOM SQL HERE>");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // You are also responsible for reverthing any changes.
    }
}
```

## Migration SQL Scripts

You can use the `Script-Migration` command to generate SQL scripts from your migrations.
This is useful for reviewing changes before applying them to the database.
SQL scripts allow us to execute migrations in environments without direct access to the EF tooling.

Remember, you are responsible for preventing any data loss when executing EF migrations.
Review the migrations carefully before applying them to the database.

Here are a few ways you can execute the `Script-Migration` command:

```
Script-Migration

Script-Migration <FromMigration>

Script-Migration <FromMigration> <ToMigration>
```

The `<FromMigration>` argument should be the name of the last migration applied to the database.
It's your responsibility to apply the script appropriately, and only to databases in the correct migration state.

Here's what the SQL script for the `Create_Database` migration looks like:

```sql
CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" character varying(150) NOT NULL,
    "ProductVersion" character varying(32) NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);

START TRANSACTION;

CREATE TABLE "Products" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" character varying(100) NOT NULL,
    "Description" character varying(1000),
    "Price" numeric(18,2) NOT NULL,
    CONSTRAINT "PK_Products" PRIMARY KEY ("Id"),
    CONSTRAINT "CK_Price_NotNegative" CHECK (Price > 0)
);

CREATE UNIQUE INDEX "IX_Products_Name" ON "Products" ("Name");

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20240516095344_Create_Database', '8.0.5');

COMMIT;
```

You can also specify an `-Idempotent` argument to the `Script-Migration` command.
The `Script-Migration` command will generate SQL scripts that only apply migrations that haven't been applied already.
This is useful if you're not sure what the last migration applied to the database.

```
Script-Migration -Idempotent
```

Here's what the idempotent SQL script looks like:

```sql
CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" character varying(150) NOT NULL,
    "ProductVersion" character varying(32) NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);

START TRANSACTION;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240516095344_Create_Database') THEN
    CREATE TABLE "Products" (
        "Id" integer GENERATED BY DEFAULT AS IDENTITY,
        "Name" character varying(100) NOT NULL,
        "Description" character varying(1000),
        "Price" numeric(18,2) NOT NULL,
        CONSTRAINT "PK_Products" PRIMARY KEY ("Id"),
        CONSTRAINT "CK_Price_NotNegative" CHECK (Price > 0)
    );
    END IF;
END $EF$;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240516095344_Create_Database') THEN
    CREATE UNIQUE INDEX "IX_Products_Name" ON "Products" ("Name");
    END IF;
END $EF$;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240516095344_Create_Database') THEN
    INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
    VALUES ('20240516095344_Create_Database', '8.0.5');
    END IF;
END $EF$;
COMMIT;
```

## Applying Migrations

How do we apply EF migrations to the database?

We have a few options:

- SQL scripts
- Command-line tools
- Apply migrations through code
- Migration bundles

We discussed SQL scripts in the previous section, so I won't mention them again.

### Command-line Tools

The most common approach to applying database migrations is using the CLI.
You can use either the `dotnet ef` tool or the PowerShell commands.
For example, you can execute the `Update-Database` command from PowerShell to apply any pending migrations.

```
Update-Database -Migration <ToMigration> -Connection <ConnectionString>
```

Here are the documentation links if you want to learn more:

- [EF Core CLI documentation](https://learn.microsoft.com/en-us/ef/core/cli/)
- [EF Core PowerShell documentation](https://learn.microsoft.com/en-us/ef/core/cli/powershell)

### Applying Migrations through Code

Here's a helper method for applying database migrations.
It uses an `IServiceScope` to resolve a `DbContext` instance and uses it to call the `Migrate` method.

```csharp
public static void ApplyMigration<TDbContext>(IServiceScope scope)
    where TDbContext : DbContext
{
    using TDbContext context = scope.ServiceProvider
        .GetRequiredService<TDbContext>();

    context.Database.Migrate();
}
```

You can apply migrations when the application is starting.
I **don't recommend** using this approach for production environments.
Migration can fail, concurrency issues exist, and rolling back migrations is challenging.
However, this approach can be helpful in local development and when scaffolding databases for integration testing.

```csharp
var builder = WebApplication.CreateBuilder(args);

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    using IServiceScope scope = app.ApplicationServices.CreateScope();

    ApplyMigration<AppDbContext>(scope);
}

app.Run();
```

### Migration Bundles

Migration bundles are executable files that you can use to apply database migrations.
They're self-contained and can be executed from CI pipelines.

You can use the `Bundle-Migration` command to create a migration bundle:

```
Bundle-Migration -Connection <ConnectionString>
```

This will create an `efbundle.exe` file that we can run to apply any pending database migrations.

I recommend reading the [migration bundles documentation](https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/applying#bundles) to learn more.

## Additional Database Migration Tools

What can you do if you don't want to use EF Core migrations?

I wanted to mention some additional tools you can use for database schema versioning and running migrations:

- [FluentMigrator](https://github.com/fluentmigrator/fluentmigrator): A migration framework for .NET with a fluent API for defining migrations.
- [DbUp](https://github.com/DbUp/DbUp): A lightweight library for applying SQL scripts to databases.
- [Grate](https://erikbra.github.io/grate/): An automated database deployment (change management) system that relies on SQL scripts.
- [Flyway](https://flywaydb.org/): An open-source database migration tool that simplifies the management and versioning of database schema changes.

We won't do a deep dive on these tools, but I recommend you check out their documentation.

## EF Core Migrations Best Practices

I want to wrap up this issue with a few tips from my experience of working with EF Core migrations over the years:

- **Use meaningful migration names**: Don't name migrations with dates or generic descriptions.
  Use clear, descriptive names that indicate the purpose of the migration.
  Good examples: `AddProductsTable`, `RenameDescriptionToShortDescription`.
  This makes it much easier to understand your migration history and find specific changes.
- **Keep migrations small and focused**: Avoid creating massive migrations containing multiple unrelated changes.
  Smaller migrations are easier to review, test, and troubleshoot if something goes wrong.
  Aim for one migration per feature or logical change.
- **Test migrations thoroughly**: Before applying migrations to production, test them in a development or staging environment.
  Development and staging environments should mirror your production setup as closely as possible.
  This will help catch any unexpected issues or data loss risks before they affect real users.
- **Beware of destructive changes**: Some operations, like dropping columns or tables, can lead to irreversible data loss.
  Carefully consider the consequences before including such changes in migrations.
  Provide a way to migrate data or create a backup plan.
- **Avoid merge conflicts**: Solving merge conflicts for EF migration snapshots can be a real headache.
  Be mindful of this when working in a team that creates many database migrations.
  It's recommended to always be up-to-date with the latest migration before creating a new one.
  This should minimize the chance of creating merge conflicts.

My preferred approach to applying migrations is using SQL scripts.
Depending on the project scope and complexity, we could do this manually or through a tool that automates the process.
This allows me to review the migration and identify any potential problems.

I hope this was helpful!

Thanks for reading, and I'll see you next week.

---



<Sponsorships>

Build API applications visually using [**Postman Flows**](https://learning.postman.com/docs/postman-flows/gs/flows-overview/).
Postman Flows is a visual tool for building API-driven applications for the API-first world.
You can use Flows to chain requests, handle data, and create real-world workflows in your Postman workspace.
[**Get started here**](https://learning.postman.com/docs/postman-flows/gs/flows-overview/).

[**9 Best Practices for Building Blazor Web Applications**](https://www.telerik.com/blogs/blazor-basics-9-best-practices-building-blazor-web-applications?utm_medium=cpm&utm_source=milanjovanovic&utm_campaign=blazor-general-awareness):
In this article, you will learn nine best practices for building Blazor web applications by the .NET developer and YouTube influencer Claudio Bernasconi.
[**Read it here**](https://www.telerik.com/blogs/blazor-basics-9-best-practices-building-blazor-web-applications?utm_medium=cpm&utm_source=milanjovanovic&utm_campaign=blazor-general-awareness).

</Sponsorships>

From my experience working with microservices systems, things don't always go as planned.
Network requests randomly fail, application servers become overloaded, and unexpected errors appear.
That's where resilience comes in.

Resilient applications can recover from transient failures and continue to function.
Resilience is achieved by designing applications that can handle failures gracefully and recover quickly.

By designing your applications with resilience in mind, you can create robust and reliable systems, even when the going gets tough.

In this newsletter, we'll explore the tools and techniques we have in .NET to build resilient systems.

## Resilience: Why You Should Care

Sending HTTP requests is a common approach for remote communication between services.
However, HTTP requests are susceptible to failures from network or server issues.
These failures can disrupt service availability, especially as dependencies increase and the risk of cascading failures grows.

So, how can you improve the resilience of your applications and services?

Here are a few strategies you can consider to increase resilience:

- **Retries**: Retry requests that fail due to transient errors.
- **Timeouts**: Cancel requests that exceed a specified time limit.
- **Fallbacks**: Define alternative actions or results for failed operations.
- **Circuit Breakers**: Temporarily suspend communication with unavailable services.

You can use these strategies individually or in combination for optimal HTTP request resilience.

Let's see how we can introduce resilience in a .NET application.

## Resilience Pipelines

With .NET 8, integrating resilience into your applications has become much simpler.
We can use `Microsoft.Extensions.Resilience` and `Microsoft.Extensions.Http.Resilience`, which are built on top of [Polly](https://github.com/App-vNext/Polly).
Polly is a .NET resilience and transient fault-handling library.
Polly allows us to define resilience strategies such as retry, circuit breaker, timeout, rate-limiting, fallback, and hedging.

Polly received a new API surface in its latest version (V8), which was implemented in collaboration with Microsoft.
You can learn more about the [**Polly V8 API in this video**](https://youtu.be/PqVQFUCTzUM).

If you were previously using `Microsoft.Extensions.Http.Polly`, it is recommended that you switch to one of the previously mentioned packages.

Let's start by installing the required NuGet packages:

```powershell
Install-Package Microsoft.Extensions.Resilience
Install-Package Microsoft.Extensions.Http.Resilience
```

To use resilience, you must first build a pipeline consisting of resilience [strategies](https://www.pollydocs.org/strategies/).
Each strategy that we configure as part of the pipeline will execute in order of configuration.
Order is important with resilience pipelines.
Keep that in mind.

We start by creating an instance of `ResiliencePipelineBuilder`, which allows us to configure resilience strategies.

```csharp
ResiliencePipeline pipeline = new ResiliencePipelineBuilder()
    .AddRetry(new RetryStrategyOptions
    {
        ShouldHandle = new PredicateBuilder().Handle<ConflictException>(),
        Delay = TimeSpan.FromSeconds(1),
        MaxRetryAttempts = 2,
        BackoffType = DelayBackoffType.Exponential,
        UseJitter = true
    })
    .AddTimeout(new TimeoutStrategyOptions
    {
        Timeout = TimeSpan.FromSeconds(10)
    })
    .Build();

await pipeline.ExecuteAsync(
    async ct => await httpClient.GetAsync("https://modularmonolith.com", ct),
    cancellationToken);
```

Here's what we're adding to the resilience pipeline:

- `AddRetry` - Configures a retry resilience strategy, which we can further configure by passing in a `RetryStrategyOptions` instance.
  We can provide a predicate for the `ShouldHandle` property to define which exceptions the resilience strategy should handle.
  The retry strategy also comes with some sensible [default values](https://www.pollydocs.org/strategies/retry.html#defaults).
- `AddTimeout` - Configures a timeout strategy that will throw a `TimeoutRejectedException` if the delegate does not complete before the timeout.
  We can provide a custom timeout by passing in a `TimeoutStrategyOptions` instance.
  The default timeout is 30 seconds.

Finally, we can `Build` the resilience pipeline and get back a configured `ResiliencePipeline` instance that will apply the respective resilience strategies.
To use the `ResiliencePipeline`, we can call the `ExecuteAsync` method and pass in a delegate.

## Resilience Pipelines and Dependency Injection

Configuring a resilience pipeline every time we want to use it is cumbersome.
.NET 8 introduces a new extension method for the `IServiceCollection` interface that allows us to register resilience pipelines with dependency injection.

Instead of manually configuring resilience every time, you ask for a pre-made pipeline by name.

We start by calling the `AddResiliencePipeline` method, which allows us to configure the resilience pipeline.
Each resilience pipeline needs to have a unique key.
We can use this key to resolve the respective resilience pipeline instance.

In this example, we're passing in a `string` key which allows us to configure the non-generic `ResiliencePipelineBuilder`.

```csharp
services.AddResiliencePipeline("retry", builder =>
{
    builder.AddRetry(new RetryStrategyOptions
    {
        Delay = TimeSpan.FromSeconds(1),
        MaxRetryAttempts = 2,
        BackoffType = DelayBackoffType.Exponential,
        UseJitter = true
    });
})
```

However, we can also specify generic arguments when calling `AddResiliencePipeline`.
This allows us to configure a typed resilience pipeline using `ResiliencePipelineBuilder<TResult>`.
Using this approach, we can access the [hedging](https://www.pollydocs.org/strategies/hedging.html) and [fallback](https://www.pollydocs.org/strategies/fallback.html) strategies.

In the following example, we're configuring a fallback strategy by calling `AddFallback`.
This allows us to provide a fallback value that we can return in case of a failure.
The fallback could be a static value or come from another HTTP request or the database.

```csharp
services.AddResiliencePipeline<string, GitHubUser?>("gh-fallback", builder =>
{
    builder.AddFallback(new FallbackStrategyOptions<GitHubUser?>
    {
        FallbackAction = _ =>
            Outcome.FromResultAsValueTask<GitHubUser?>(GitHubUser.Empty)
    });
});
```

To use resilience pipelines configured with dependency injection, we can use the `ResiliencePipelineProvider`.
It exposes a `GetPipeline` method for obtaining the pipeline instance.
We have to provide the key used to register the resilience pipeline.

```csharp {3,6}
app.MapGet("users", async (
    HttpClient httpClient,
    ResiliencePipelineProvider<string> pipelineProvider) =>
{
    ResiliencePipeline<GitHubUser?> pipeline =
        pipelineProvider.GetPipeline<GitHubUser?>("gh-fallback");

    var user = await pipeline.ExecuteAsync(async token =>
        await httpClient.GetAsync("api/users", token),
        cancellationToken);
})
```

## Resilience Strategies and Polly

[Resilience strategies](https://www.pollydocs.org/strategies/) are the core component of Polly.
They're designed to run custom callbacks while introducing an additional layer of resilience.
We can't run these strategies directly.
Instead, we execute them through a resilience pipeline.

Polly categorizes resilience strategies into **reactive** and **proactive**.
Reactive strategies handle specific exceptions or results.
Proactive strategies decide to cancel or reject the execution of callbacks using a rate limiter or a timeout resilience strategy.

Polly has the following built-in resilience strategies:

- **Retry**: The classic "try again" approach.
  Works great for temporary network glitches.
  You can configure how many retries you have and even add some randomness (jitter) to avoid overloading the system if everyone retries at once.
- **Circuit-breaker**: Like an electrical circuit breaker, this prevents hammering a failing system.
  If errors pile up, the circuit breaker "trips" temporarily to give the system time to recover.
- **Fallback**: Provides a safe, default response if your primary call fails.
  It might be a cached result or a simple "service unavailable" message.
- **Hedging**: Makes multiple requests simultaneously, taking the first successful response.
  It is helpful if your system has numerous ways of handling something.
- **Timeout**: Prevents requests from hanging forever by terminating them if the timeout is exceeded.
- **Rate-limiter**: Throttles outgoing requests to prevent overwhelming external services.

## HTTP Request Resilience

Sending HTTP calls to external services is how your application interacts with the outside world.
These could be third-party services like payment gateways and identity providers or other services your team owns and operates.

The `Microsoft.Extensions.Http.Resilience` library comes with ready-to-use resilience pipelines for sending HTTP requests.

We can add resilience to outgoing [**HttpClient requests**](the-right-way-to-use-httpclient-in-dotnet) using the `AddStandardResilienceHandler` method.

```csharp {5}
services.AddHttpClient<GitHubService>(static (httpClient) =>
{
    httpClient.BaseAddress = new Uri("https://api.github.com/");
})
.AddStandardResilienceHandler();
```

This also means you can eliminate any [**delegating handlers**](extending-httpclient-with-delegating-handlers-in-aspnetcore) you previously used for resilience.

The standard resilience handler combines five Polly strategies to create a resilience pipeline suitable for most scenarios.
The standard pipeline contains the following strategies:

- **Rate limiter**: Limits the maximum number of concurrent requests sent to the dependency.
- **Total request timeout**: Introduces a total timeout, including any retry attempts.
- **Retry**: Retries a request if it fails because of a timeout or a transient error.
- **Circuit breaker**: Prevents sending further requests if too many failures are detected.
- **Attempt timeout**: Introduces a timeout for an individual request.

You can customize any aspect of the standard resilience pipeline by configuring the `HttpStandardResilienceOptions`.

## Takeaway

Resilience isn't just a buzzword; it's a core principle for building reliable software systems.
We're fortunate to have powerful tools like `Microsoft.Extensions.Resilience` and Polly at our disposal.
We can use them to design systems that gracefully handle any transient failures.

Good [**monitoring and observability**](introduction-to-distributed-tracing-with-opentelemetry-in-dotnet)
are essential to understand how your resilience mechanisms work in production.
Remember, the goal isn't to eliminate failures but to gracefully handle them and keep your application functioning.

Ready to dive deeper into resilient architecture?
My advanced course on [**building modular monoliths**](/modular-monolith-architecture) will equip you with the skills to design and implement robust, scalable systems.
Check out [**Modular Monolith Architecture**](/modular-monolith-architecture).

**Challenge**: Take a look at your existing .NET projects.
Are there any critical areas where a little resilience could go a long way?
Pick one and try applying some of the techniques we've discussed here.

That's all for today.

See you next week.

---



<Sponsorships>

[**Announcing Postman v11**](https://blog.postman.com/announcing-postman-v11/): supercharging development with AI and APIs.
Streamline API development with [**Postman v11's**](https://blog.postman.com/announcing-postman-v11/) new features for collaboration, AI, and security.
Try out the new features like VS Code extension, Partner Workspaces, and Postman Vault to streamline your API development workflow.

[**Product for Engineers**](https://newsletter.posthog.com/) from PostHog is a newsletter helping engineers improve their product skills.
[**Subscribe for free**](https://newsletter.posthog.com/) to get curated advice on building great products, lessons (and mistakes) from PostHog, and deep dives on top startups.

</Sponsorships>

API gateways provide clients with a single point of entry.
This streamlines their interactions with your system and ensures the security of your microservices or distributed system.

One critical aspect of API gateways is authentication - ensuring only authorized users and applications can access your valuable data and resources.

In this newsletter, we'll explore how you can implement API gateway authentication using [YARP](https://microsoft.github.io/reverse-proxy/index.html) (Yet Another Reverse Proxy),
a powerful and flexible reverse proxy library for .NET applications.

Here's what we will cover:

- The role of API gateways
- Configuring authentication with YARP
- Creating custom authorization policies

Let's dive in.

## The Role of API Gateways

An [**API gateway**](implementing-an-api-gateway-for-microservices-with-yarp) is the "front door" to your backend services and APIs.
It acts as an intermediary layer, handling client requests and routing them to the appropriate destinations.

The key benefits of API gateways are:

- **Centralized access**: All incoming requests must first pass through the gateway. This simplifies management and monitoring.
- **Service abstraction**: Clients interact only with the gateway. We can hide the complexity of the backend architecture from clients.
- **Performance enhancement**: Implement techniques like caching and [**load balancing**](horizontally-scaling-aspnetcore-apis-with-yarp-load-balancing) to optimize API performance.
- **Authentication and Authorization**: API gateways verify user and application identities, enforcing whether a request is allowed or not.

<figure>
  <Image
    src="blogs/mnw_088/api_gateway.png"
    width={2115}
    height={1591}
    alt="API Gateway diagram."
  />
  <figcaption>
    Source: [Modular Monolith Architecture](/modular-monolith-architecture)
  </figcaption>
</figure>

## Configuring Authentication With YARP

We can use the API gateway to authenticate and authorize requests before they are proxied to the destination servers.
This can reduce the load on the destination servers, and introduce a layer of security.
Implementing authentication on the API gateway ensures consistent policies are implemented across your applications.

If you're new to YARP, I recommend first reading about [**how to implement an API gateway with YARP**](implementing-an-api-gateway-for-microservices-with-yarp).

By default, YARP won't authenticate or authorize requests unless enabled in the route or application configuration.

We can start by introducing [authentication](https://docs.microsoft.com/aspnet/core/security/authentication/)
and [authorization](https://learn.microsoft.com/en-us/aspnet/core/security/authorization/introduction) middleware:

```csharp {1,2}
app.UseAuthentication();
app.UseAuthorization();

app.MapReverseProxy();
```

This allows us to configure the authorization policy by providing the `AuthorizationPolicy` value in the route configuration.

There are two special values we can specify in a route's authorization parameter:

- `default` - The route will require an authenticated user.
- `anonymous` - The route will not require authorization regardless of any other configuration.

Here's how we can enforce that all incoming requests must be authenticated:

```json {6}
{
  // This is how we define reverse proxy routes.
  "Routes": {
    "api-route": {
      "ClusterId": "api-cluster",
      "AuthorizationPolicy": "default",
      "Match": {
        "Path": "api/{**catch-all}"
      }
    }
  }
}
```

We want to authorize any incoming request as soon as it hits the API gateway.
However, the destination server may still need to know who the user is (authentication) and what they can do (authorization).

YARP will pass any credentials to the proxied request.
By default, cookies, bearer tokens, and API keys will flow to the destination server.

## Creating Custom Authentication Policies

YARP can utilize the powerful [authorization policies](https://learn.microsoft.com/en-us/aspnet/core/security/authorization/policies) feature in ASP.NET Core.
We can specify a policy per route in the proxy configuration, and the rest is handled by existing ASP.NET Core authentication and authorization components.

```json {6,13}
{
  // This is how we define auth policies for reverse proxy routes.
  "Routes": {
    "api-route1": {
      "ClusterId": "api-cluster",
      "AuthorizationPolicy": "is-vip",
      "Match": {
        "Path": "api/hello-vip"
      }
    },
    "api-route2": {
      "ClusterId": "api-cluster",
      "AuthorizationPolicy": "default",
      "Match": {
        "Path": "api/{**catch-all}"
      }
    }
  }
}
```

Here's how we can create a custom `is-vip` policy with two components.
It requires an authenticated user and `vip` claim with one of the defined allowed values to be present .
To use this policy, we can just specify it as the value for the `AuthorizationPolicy` in the route configuration.

```csharp
services.AddAuthorization(options =>
{
    options.AddPolicy("is-vip", policy =>
        policy
            .RequireAuthenticatedUser()
            .RequireClaim("vip", allowedValues: true.ToString()));
});
```

## Summary

API gateways provide a unified access point, streamlining client interactions and securing your backend services.
Authentication is an essential element of API gateway security, controlling who can access your resources.

YARP offers a versatile solution for building .NET API gateways.
By integrating with ASP.NET Core's authentication and authorization frameworks, YARP enables robust security mechanisms.

This flexibility really shines with support for custom authorization policies.
This allows you to define [**granular access control**](master-claims-transformation-for-flexible-aspnetcore-authorization)
based on user roles, claims, or other attributes.

Thanks for reading, and I'll see you next week.

**P.S.** Here's the complete [source code](https://github.com/m-jovanovic/yarp-authentication) for this article if you want to try it out.

---



<Sponsorships>

Become a Postman master with [**Postman Intergalactic sessions.**](https://www.postman.com/events/intergalactic)
Test your APIs with ease and collaborate on API development with workspaces.
[**Get Postman for FREE!**](https://www.postman.com/downloads)

Free eBook: [**How to Choose a Component Library?**](https://www.telerik.com/campaigns/devcraft/how-to-choose-a-component-library?utm_medium=cpm&utm_source=milanjovanovic&utm_campaign=dt_devcraft_wp_productivity_choose_component_library)
Practical advice for choosing the right component libraries for your business requirements.
[**Try the expert-curated solutions now!**](https://www.telerik.com/campaigns/devcraft/how-to-choose-a-component-library?utm_medium=cpm&utm_source=milanjovanovic&utm_campaign=dt_devcraft_wp_productivity_choose_component_library)

</Sponsorships>

Building distributed applications might seem simple at first. It's just servers talking to each other. Right?

However, it opens a set of potential problems you must consider.
What if the network has a hiccup?
A service unexpectedly crashes?
You try to scale, and everything crumbles under the load?
This is where the way your distributed system communicates becomes critical.

Traditional synchronous communication, where services call each other directly, is inherently fragile.
It creates tight coupling, making your whole application vulnerable to single points of failure.

To combat this, we can turn to distributed messaging
(and introduce an entirely different set of problems, but that's a story for another issue).

One powerful tool for achieving this in the .NET world is MassTransit.

In this week's issue, we'll explore MassTransit's implementation of the request-response pattern.

## Request-Response Messaging Pattern Introduction

Let's start by explaining how the request-response messaging pattern works.

The request-response pattern is just like making a traditional function call but over the network.
One service, the requester, sends a request message and waits for a corresponding response message.
This is a [**synchronous communication approach**](modular-monolith-communication-patterns) from the requester's side.

The good parts:

- **Loose Coupling**: Services don't need direct knowledge of each other, only of the message contracts.
  This makes changes and scaling easier.
- **Location Transparency**: The requester doesn't need to know _where_ the responder is located, leading to improved flexibility.

The bad parts:

- **Latency**: The overhead of messaging adds some additional latency.
- **Complexity**: Introducing a messaging system and managing the additional infrastructure can increase project complexity.

<Image
  src="blogs/mnw_087/request_response.png"
  width={1617}
  height={848}
  alt="Request response messaging pattern diagram."
/>

## Request-Response Messaging With MassTransit

[**MassTransit**](using-masstransit-with-rabbitmq-and-azure-service-bus)
supports the [request-response](https://masstransit.io/documentation/concepts/requests) messaging pattern out of the box.
We can use a **request client** to send requests and wait for a response.
The request client is asynchronous and supports the `await` keyword.
The request will also have a timeout of 30 seconds by default, to prevent waiting for the response for too long.

Let's imagine a scenario where you have an order processing system that needs to fetch an order's latest status.
We can fetch the status from an Order Management service.
With MassTransit, you'll create a request client to initiate the process.
This client will send a `GetOrderStatusRequest` message onto the bus.

```csharp
public record GetOrderStatusRequest
{
    public string OrderId { get; init; }
}
```

On the Order Management side, a responder (or consumer) will be listening for `GetOrderStatusRequest` messages.
It receives the request, potentially queries a database to get the status,
and then sends a `GetOrderStatusResponse` message back onto the bus.
The original request client will be waiting for this response and can then process it accordingly.

```csharp
public class GetOrderStatusRequestConsumer : IConsumer<GetOrderStatusRequest>
{
    public async Task Consume(ConsumeContext<GetOrderStatusRequest> context)
    {
        // Get the order status from a database.

        await context.ResponseAsync<GetOrderStatusResponse>(new
        {
            // Set the respective response properties.
        });
    }
}
```

## Getting User Permissions In a Modular Monolith

Here's a real-world scenario where my team decided to implement this pattern.
We were building a [**modular monolith**](/modular-monolith-architecture),
and one of the modules was responsible for managing user permissions.
The other modules could call out to the Users module to get the user's permissions.
And this works great while we are still inside a monolith system.

However, at one point we needed to extract one module into a separate service.
This meant that the communication with the Users module using simple method calls would no longer work.

Luckily, we were already using MassTransit and RabbitMQ for messaging inside the system.

So, we decided to use the MassTransit request-response feature to implement this.

The new service will inject an `IRequestClient<GetUserPermissions>`.
We can use it to send a `GetUserPermissions` message and await a response.

A very powerful feature of MassTransit is that you can await more than one response message.
In this example, we're waiting for a `PermissionsResponse` or an `Error` response.
This is great, because we also have a way to handle failures in the consumer.

```csharp {11,13,18}
internal sealed class PermissionService(
    IRequestClient<GetUserPermissions> client)
    : IPermissionService
{
    public async Task<Result<PermissionsResponse>> GetUserPermissionsAsync(
        string identityId)
    {
        var request = new GetUserPermissions(identityId);

        Response<PermissionsResponse, Error> response =
            await client.GetResponse<PermissionsResponse, Error>(request);

        if (response.Is(out Response<Error> errorResponse))
        {
            return Result.Failure<PermissionsResponse>(errorResponse.Message);
        }

        if (response.Is(out Response<PermissionsResponse> permissionResponse))
        {
            return permissionResponse.Message;
        }

        return Result.Failure<PermissionsResponse>(NotFound);
    }
}
```

In the Users module, we can easily implement the `GetUserPermissionsConsumer`.
It will respond with a `PermissionsResponse` if the permissions are found or an `Error` in case of a failure.

```csharp
public sealed class GetUserPermissionsConsumer(
    IPermissionService permissionService)
    : IConsumer<GetUserPermissions>
{
    public async Task Consume(ConsumeContext<GetUserPermissions> context)
    {
        Result<PermissionsResponse> result =
            await permissionService.GetUserPermissionsAsync(
                context.Message.IdentityId);

        if (result.IsSuccess)
        {
            await context.RespondAsync(result.Value);
        }
        else
        {
            await context.RespondAsync(result.Error);
        }
    }
}
```

## Closing Thoughts

By embracing messaging patterns with MassTransit, you're building on a much sturdier foundation.
Your .NET services are now less tightly coupled, giving you the flexibility to evolve them independently
and weather those inevitable network glitches or service outages.

The [request-response pattern](https://youtu.be/NjsoykEOkrk) is a powerful tool in your messaging arsenal.
MassTransit makes it remarkably easy to implement, ensuring that requests and responses are delivered reliably.

We can use request-response to implement communication between modules in a [**modular monolith**](/modular-monolith-architecture).
However, don't take this to the extreme, or your system might suffer from increased latency.

Start small, experiment, and see how the reliability and flexibility of messaging can transform your development experience.

That's all for this week. Stay awesome!

---



<Sponsorships>

Introducing [**Shesha**](https://shesha.io?utm_source=milan&utm_medium=email&utm_campaign=20240420), a brand new, open-source, low-code framework for .NET developers.
Create business applications faster and with >80% less code! [**Learn more here.**](https://shesha.io?utm_source=milan&utm_medium=email&utm_campaign=20240420)

Become a Postman master with [**Postman Intergalactic sessions.**](https://www.postman.com/events/intergalactic)
Test your APIs with ease and collaborate on API development with workspaces.
[**Get Postman for FREE!**](https://www.postman.com/downloads)

</Sponsorships>

If you're building or maintaining distributed .NET applications, understanding how they behave is key to ensuring reliability and performance.

Distributed systems offer flexibility but introduce complexity, making troubleshooting a headache.
Understanding how requests flow through your system is crucial for debugging and performance optimization.

OpenTelemetry is an open-source observability framework that makes this possible.

In this article, we'll dive into what OpenTelemetry is, how to use it in your .NET projects, and the powerful insights it provides.

## OpenTelemetry Introduction

[OpenTelemetry](https://opentelemetry.io/) (OTel) is a vendor-neutral, open-source standard for instrumenting applications to generate telemetry data.
OpenTelemetry contains APIs, SDKs, tools, and integrations for creating and managing this telemetry data (traces, metrics, and logs).

Telemetry data includes:

- **Traces**: Represent the flow of requests through distributed systems, showing timings and relationships between services.
- **Metrics**: Numerical measurements of system behavior over time (e.g., request counts, error rates, memory usage).
- **Logs**: Textual records of events with rich contextual information. Structured logs.

<figure>
  <Image
    src="blogs/mnw_086/otel.png"
    width={1500}
    height={996}
    alt="OpenTelemetry Reference Architecture."
  />
  <figcaption>
    Source: [https://opentelemetry.io/docs/](https://opentelemetry.io/docs/)
  </figcaption>
</figure>

OpenTelemetry provides a unified way to collect this data, making it easier to understand the behavior and health of complex distributed applications.

We can export the telemetry data we are collecting to a service capable of processing it and providing us with an interface to analyze it.

We're going to configure OpenTelemetry to export traces directly to [Jaeger](https://www.jaegertracing.io/).

## Adding OpenTelemetry to .NET Applications

OpenTelemetry provides libraries and SDKs to add code (instrumentation) into your .NET applications.
These instrumentations automatically capture the traces, metrics, and logs we are interested in.

We're going to install the following NuGet packages:

```powershell
# Automatic tracing, metrics
Install-Package OpenTelemetry.Extensions.Hosting

# Telemetry data exporter
Install-Package OpenTelemetry.Exporter.OpenTelemetryProtocol

# Instrumentation packages
Install-Package OpenTelemetry.Instrumentation.Http
Install-Package OpenTelemetry.Instrumentation.AspNetCore
Install-Package OpenTelemetry.Instrumentation.EntityFrameworkCore
Install-Package OpenTelemetry.Instrumentation.StackExchangeRedis
Install-Package Npgsql.OpenTelemetry
```

Once we have these NuGet packages installed, it's time to configure some services.

```csharp
services
    .AddOpenTelemetry()
    .ConfigureResource(resource => resource.AddService(serviceName))
    .WithTracing(tracing =>
    {
        tracing
            .AddAspNetCoreInstrumentation()
            .AddHttpClientInstrumentation()
            .AddEntityFrameworkCoreInstrumentation()
            .AddRedisInstrumentation()
            .AddNpgsql();

        tracing.AddOtlpExporter();
    });
```

- `AddAspNetCoreInstrumentation` - This enables ASP.NET Core instrumentation.
- `AddHttpClientInstrumentation` - This enables `HttpClient` instrumentation for outgoing requests.
- `AddEntityFrameworkCoreInstrumentation` - This enables EF Core instrumentation.
- `AddRedisInstrumentation` - This enables Redis instrumentation.
- `AddNpgsql` - This enables PostgreSQL instrumentation.

With all of these instrumentations configured, our application will start collecting a lot of valuable traces at runtime.

We also need to configure an environment variable for the exporter added with `AddOtlpExporter` to work correctly.
We can set `OTEL_EXPORTER_OTLP_ENDPOINT` through application settings.
The address specified here will point to a local Jaeger instance.

```
OTEL_EXPORTER_OTLP_ENDPOINT=http://localhost:4317
```

## Running Jaeger Locally

[Jaeger](https://www.jaegertracing.io/) is an open source, distributed tracing platform.
Jaeger maps the flow of requests and data as they travel through a distributed system.
These requests could be calling out to multiple services, and Jaeger knows how to piece all of this information together.

Here's how to run Jaeger inside a Docker container:

```
docker run -d -p 4317:4317 -p 16686:16686 jaegertracing/all-in-one:latest
```

We're using the `jaegertracing/all-in-one:latest` image, and exposing the `4317` to accept telemetry data.
The Jaeger user interface will be exposed on the `16686` port.

## Distributed Tracing

After installing the OpenTelemetry libraries and configuring tracing in our applications, we can send some requests to generate telemetry data.
We can then access Jaeger to start analyzing our distributed traces.

**Registering a new user**

Here's an example of registering a new user with the system.
We're accessing the API gateway (`Evently.Gateway`) service, which proxies the request to the `Evently.Api` service.
And you can see that the `Evently.Api` service makes a few HTTP requests before persisting a new record in the database.

<Image
  src="blogs/mnw_086/trace_1.png"
  width={1920}
  height={650}
  alt="Distributed trace."
/>

**Publishing a message with MassTransit**

Here's another distributed trace where we publish the `UserRegisteredIntegrationEvent` over a message bus.
You can see that it's being consumed by two different services that write some data to the database.

<Image
  src="blogs/mnw_086/trace_2.png"
  width={1920}
  height={596}
  alt="Distributed trace."
/>

**Examining additional trace information**

Distributed traces can include some useful contextual information.
Here's an example trace representing a database command.
This comes from the PostgreSQL instrumentation, and we can see the SQL query that we are executing.

<Image
  src="blogs/mnw_086/trace_3.png"
  width={1920}
  height={500}
  alt="Distributed trace."
/>

**Complex distributed traces**

Here's a more complex distributed trace, which includes:

- Three .NET applications
- PostgreSQL database
- Redis distributed cache

We're sending a request to get the customer's cart.
The request will first hit the API gateway, which proxies it to the `Evently.Ticketing.Api` service that owns the data.
However, the `Evently.Ticketing.Api` service needs to reach out to the `Evently.Api` service to get the authorization information.
And all of this leads to the distributed trace you can see below.

<Image
  src="blogs/mnw_086/trace_4.png"
  width={1920}
  height={850}
  alt="Distributed trace."
/>

## Summary

Understanding modern applications, especially distributed ones, can be a real mind-bender.
OpenTelemetry is like having X-ray vision into your system.

While adding OpenTelemetry takes some upfront work, consider it an investment.
That investment pays off big time when problems pop up.
Instead of frantic guesswork, you have precise data to zero in on issues fast.

Is OpenTelemetry a magic bullet for all your problems? Nope.

But it's an excellent tool to add to your troubleshooting arsenal, especially as your .NET applications grow and get more complex.

If you're curious where the distributed traces come from, it's from the application we're building in my [**Modular Monolith course**](/modular-monolith-architecture).

That's all for today. Stay awesome, and I'll see you next week.

---



<Sponsorships>

[**Xamarin Days Are Numbered**](https://www.telerik.com/blogs/xamarin-days-numbered-ready-migrate-net-maui?utm_medium=cpm&utm_source=milanjovanovich&utm_campaign=end-of-xamarin-support) -
Are You Ready to Migrate to .NET MAUI?
Microsoft's decision to halt support for Xamarin on May 1, 2024, is rooted in the evolution of technology.
What's next?
[**Find out here.**](https://www.telerik.com/blogs/xamarin-days-numbered-ready-migrate-net-maui?utm_medium=cpm&utm_source=milanjovanovich&utm_campaign=end-of-xamarin-support)

Become a Postman master with [**Postman Intergalactic sessions.**](https://www.postman.com/events/intergalactic)
Test your APIs with ease and collaborate on API development with workspaces.
[**Get Postman for FREE!**](https://www.postman.com/downloads)

</Sponsorships>

Sometimes, especially in high-traffic scenarios, you absolutely need to ensure that only one process can modify a piece of data at a time.

Imagine you're building the ticket sales system for a wildly popular concert.
Customers are eagerly grabbing tickets, and the last few could sell out simultaneously.
If you're not careful, multiple customers might think they've secured the final seat, leading to overbooking and disappointment!

Entity Framework Core is a fantastic tool, but it doesn't have a direct mechanism for pessimistic locking.
[Optimistic locking](solving-race-conditions-with-ef-core-optimistic-locking) (using versions) can work, but in high-contention scenarios, it can lead to a lot of retries.

So, how can we solve this problem with EF Core?

## The Scenario in More Detail

Here's a simplified code snippet to illustrate our ticketing challenge:

```csharp
public async Task Handle(CreateOrderCommand request)
{
    await using DbTransaction transaction = await unitOfWork
        .BeginTransactionAsync();

    Customer customer = await customerRepository.GetAsync(request.CustomerId);

    Order order = Order.Create(customer);
    Cart cart = await cartService.GetAsync(customer.Id);

    foreach (CartItem cartItem in cart.Items)
    {
        // Uh-oh... what if two requests hit this at the same time?
        TicketType ticketType = await ticketTypeRepository.GetAsync(
            cartItem.TicketTypeId);

        ticketType.UpdateQuantity(cartItem.Quantity);

        order.AddItem(ticketType, cartItem.Quantity, cartItem.Price);
    }

    orderRepository.Insert(order);

    await unitOfWork.SaveChangesAsync();

    await transaction.CommitAsync();

    await cartService.ClearAsync(customer.Id);
}
```

The example above is contrived, but it should be enough to explain the problem.
During checkout, we verify the `AvailableQuantity` for each ticket.

What will happen if we get concurrent requests trying to purchase the same ticket?

The worst-case scenario is we end up "overselling" the tickets.
Concurrent requests could see available tickets for sale and complete the checkout.

So, how do we solve this?

## Raw SQL to the Rescue!

Since EF Core doesn't offer pessimistic locking directly, we'll dip into a bit of good old-fashioned SQL.
We will replace the `GetAsync` call to fetch the ticket with `GetWithLockAsync`.

Thankfully, EF Core makes this easy with [raw SQL queries](ef-core-raw-sql-queries):

```csharp {10}
public async Task<TicketType> GetWithLockAsync(Guid id)
{
    return await context
        .TicketTypes
        .FromSql(
            $@"
            SELECT id, event_id, name, price, currency, quantity
            FROM ticketing.ticket_types
            WHERE id = {id}
            FOR UPDATE NOWAIT") // PostgreSQL: Lock or fail immediately
        .SingleAsync();
}
```

Understanding the magic:

- `FOR UPDATE NOWAIT`: This is the heart of pessimistic locking in PostgreSQL.
  It tells the database "Grab this row, lock it for me, and if it's already locked, raise an error right now."
- **Error Handling**: We'd wrap our `GetWithLockAsync` call in a `try-catch` block to gracefully handle locking failures, either retrying or notifying the user.

Since there isn't a built-in way in EF Core to add query hints, we have to write raw SQL queries.
We can use the PostgreSQL `SELECT FOR UPDATE` statement to acquire a row-level lock on the selected rows.
Any competing transactions will be blocked until the current transaction releases the lock.
This is a very simple way to implement pessimistic locking.

## Flavors of Locking and When to Use Them

To prevent the operation from waiting for other transactions to release any locked rows, you can combine `FOR UPDATE` with:

- `NO WAIT` - Reports an error if the row can't be locked instead of waiting.
- `SKIP LOCKED` - Skips any selected rows that cannot be locked.

Skipping locked rows comes with a caveat - you will get inconsistent results from the database.
However, this can be useful to avoid lock contention when multiple consumers access a queue-like table.
Implementing the [Outbox pattern](outbox-pattern-for-reliable-microservices-messaging) is a great example of this.

**SQL Server**: You'd use the `WITH (UPDLOCK, READPAST)` query hint for a similar effect.

## Pessimistic Locking vs. Serializable Transactions

**Serializable** transactions offer the highest level of data consistency.
They guarantee that all transactions are executed as if they happened in a strict, sequential order, even if they occur simultaneously.
This eliminates the possibility of anomalies like dirty reads (seeing uncommitted data) or non-repeatable reads (data changing between reads).

Here's how it works:

- When a transaction starts under the Serializable isolation level, the database locks all the data the transaction might access.
- These locks are held until the entire transaction is committed or rolled back.
- Any other transaction attempting to access the locked data will be blocked until the first transaction releases its locks.

While Serializable transactions provide the ultimate isolation, they come with a significant cost:

- **Performance Overhead**: Locking a large chunk of data can severely impact performance,
  especially in high-concurrency scenarios.
- **Deadlocks**: With so much locking happening, there's a higher risk of deadlocks.
  These occur when two or more transactions are waiting for locks held by each other, creating a stalemate.

Pessimistic locking with `SELECT FOR UPDATE` offers a more targeted approach to data isolation.
You explicitly lock the specific rows you need to modify.
Other transactions attempting to access the locked rows are blocked until the lock is released.

By locking only the necessary data, pessimistic locking avoids the performance overhead associated with locking everything.
Since you're locking fewer resources, the chances of deadlocks are lower.

## When to Use Each Approach

The best approach depends on your specific needs:

- **Serializable Transactions**: Ideal for scenarios involving highly sensitive data where even the slightest inconsistency is unacceptable.
  Examples include financial transactions and medical record updates.
- **Pessimistic Locking**: A great choice for most use cases, especially in high-traffic applications.
  It provides strong consistency while maintaining good performance and reducing deadlock risks.

## Takeaway

I hope this exploration of pessimistic locking has been helpful.
It's a powerful tool to have in your arsenal if you have scenarios where absolute data consistency is paramount.

Both **Serializable transactions** and pessimistic locking with `SELECT FOR UPDATE` are excellent options for ensuring data consistency.
Consider the level of isolation required, potential performance impact, and the likelihood of deadlocks when making your choice.

That's all for today. Stay awesome, and I'll see you next week.

---



<Sponsorships>

Introducing [**Shesha**](https://shesha.io?utm_source=milan&utm_medium=email&utm_campaign=20240406), a brand new, open-source, low-code framework for .NET developers.
Create business applications faster and with >80% less code! [**Learn more here.**](https://shesha.io?utm_source=milan&utm_medium=email&utm_campaign=20240406)

[IronXL for .NET](https://ironsoftware.com/csharp/excel/?utm_source=milan&utm_medium=email&utm_campaign=milan):
Streamline Excel management with the intuitive IronXL NET Excel library.
No Microsoft Office or Excel Interop needed.
Edit files across all .NET Core and Framework versions.
[Try it free for 30 days](https://ironsoftware.com/csharp/excel/?utm_source=milan&utm_medium=email&utm_campaign=milan).

</Sponsorships>

[Claims-based authorization](https://learn.microsoft.com/en-us/aspnet/core/security/authorization/claims) mechanisms are central to modern authorization in ASP.NET Core.
However, the access tokens issued by your Identity Provider (IDP) might not always perfectly align with your application's internal authorization needs.

External IDPs like [Microsoft Entra ID](https://www.microsoft.com/en-us/security/business/identity-access/microsoft-entra-id) (previously Azure AD) or
[Auth0](https://auth0.com) might have their own schema for claims or might not directly issue all the claims your application needs for its authorization logic.

The solution? Claims transformation.

Claims transformation allows you to modify the claims before the application uses them for authorization.

In today's issue, we will:

- Explore the concept of claims transformation in ASP.NET Core
- Explore the `IClaimsTransformation` interface with practical examples
- Address considerations for security and RBAC (Role-Based Access Control)

## How Does Claims Transformation Work?

They say a picture is worth a thousand words.
In software engineering, we have something called [UML](https://en.wikipedia.org/wiki/Unified_Modeling_Language) diagrams that we can use to paint a picture.

Here's a [sequence diagram](https://en.wikipedia.org/wiki/Sequence_diagram) showing the claims transformation flow:

1. The user authenticates with the Identity Provider
2. The user calls the backend API and provides an access token
3. The backend API performs claims transformation and authorization
4. If the user is correctly authorized, the backend API returns a response

<Image
  src="blogs/mnw_084/claims_transformation_sequence_diagram.png"
  width={1614}
  height={1357}
  alt="Claims transformation sequence diagram."
/>

Let's see how to implement this in ASP.NET Core.

## Simple Claims Transformation

Claims can be created from any user or identity data issued by a trusted identity provider.
A claim is a name-value pair that represents the subject's identity, not what the subject can do.

The core of [claims transformation](https://learn.microsoft.com/en-us/aspnet/core/security/authentication/claims#extend-or-add-custom-claims-using-iclaimstransformation)
in ASP.NET Core is the [`IClaimsTransformation`](https://learn.microsoft.com/en-us/dotnet/api/microsoft.aspnetcore.authentication.iclaimstransformation) interface.

It exposes a single method to transform claims:

```csharp
public interface IClaimsTransformation
{
    Task<ClaimsPrincipal> TransformAsync(ClaimsPrincipal principal);
}
```

Here's a simple example of using `IClaimsTransformation` to add a custom claim:

```csharp
internal static class CustomClaims
{
    internal const string CardType = "card_type";
}

internal sealed class CustomClaimsTransformation : IClaimsTransformation
{
    public Task<ClaimsPrincipal> TransformAsync(ClaimsPrincipal principal)
    {
        if (principal.HasClaim(claim => claim.Type == CustomClaims.CardType))
        {
            return Task.FromResult(principal);
        }

        ClaimsIdentity claimsIdentity = new ClaimsIdentity();

        claimsIdentity.AddClaim(new Claim(CustomClaims.CardType, "platinum"));

        principal.AddIdentity(claimsIdentity);

        return Task.FromResult(principal);
    }
}
```

The `CustomClaimsTransformation` class should be registered as a service:

```csharp
builder.Services
    .AddTransient<IClaimsTransformation, CustomClaimsTransformation>();
```

Finally, you can define a custom authorization policy that uses this claim:

```csharp
builder.Services.AddAuthorization(options =>
{
    options.AddPolicy(
        "HasPlatinumCard",
        builder => builder
            .RequireAuthenticatedUser()
            .RequireClaim(CustomClaims.CardType, "platinum"));
});
```

There are a few caveats with using `IClaimsTransformation` you should be aware of:

- **Might execute multiple times**: The `TransformAsync` method might get called multiple times.
  Claims transformation should be idempotent to avoid adding the same claim multiple times to the `ClaimsPrincipal`.
- **Potential performance impact**: Since it's executed on authentication requests, be mindful of your transformation logic's performance,
  especially if it involves external calls (database, APIs). Consider caching where appropriate.

## Implementing RBAC With Claims Transformation

[Role-Based Access Control (RBAC)](https://auth0.com/docs/manage-users/access-control/rbac) is an authorization model where permissions are assigned to roles,
and users are granted roles.
Claims transformation helps implement RBAC smoothly.
By adding role claims and potentially permission claims, authorization logic throughout your application can be simplified.
Another benefit is that you can keep the access token smaller and free of any role or permission claims.

Let's consider a scenario where your application manages resources at a granular level,
but your identity provider only provides coarse-grained roles like `Registered` or `Member`.
You could use claims transformation to map the `Member` role to specific fine-grained permissions like `SubmitOrder` and `PurchaseTicket`.

Here's a more complex `CustomClaimsTransformation` implementation.
We send a database query using `GetUserPermissionsQuery` and get the `PermissionsResponse` back.
The `PermissionsResponse` contains the user's permissions, which are added as custom claims.

```csharp
internal sealed class CustomClaimsTransformation(
    IServiceProvider serviceProvider)
    : IClaimsTransformation
{
    public async Task<ClaimsPrincipal> TransformAsync(
        ClaimsPrincipal principal)
    {
        if (principal.HasClaim(c => c.Type == CustomClaims.Sub ||
                                    c.Type == CustomClaims.Permission))
        {
            return principal;
        }

        using IServiceScope scope = serviceProvider.CreateScope();

        ISender sender = scope.ServiceProvider.GetRequiredService<ISender>();

        string identityId = principal.GetIdentityId();

        Result<PermissionsResponse> result = await sender.Send(
            new GetUserPermissionsQuery(identityId));

        if (result.IsFailure)
        {
            throw new ClaimsAuthorizationException(
                nameof(GetUserPermissionsQuery), result.Error);
        }

        var claimsIdentity = new ClaimsIdentity();

        claimsIdentity.AddClaim(
            new Claim(CustomClaims.Sub, result.Value.UserId.ToString()));

        foreach (string permission in result.Value.Permissions)
        {
            claimsIdentity.AddClaim(
                new Claim(CustomClaims.Permission, permission));
        }

        principal.AddIdentity(claimsIdentity);

        return principal;
    }
}
```

Now that the `ClaimsPrincipal` contains the permissions as custom claims, you can do some interesting things.
For example, you can implement a permission-based `AuthorizationHandler`:

```csharp {8}
internal sealed class PermissionAuthorizationHandler
    : AuthorizationHandler<PermissionRequirement>
{
    protected override Task HandleRequirementAsync(
        AuthorizationHandlerContext context,
        PermissionRequirement requirement)
    {
        HashSet<string> permissions = context.User.GetPermissions();

        if (permissions.Contains(requirement.Permission))
        {
            context.Succeed(requirement);
        }

        return Task.CompletedTask;
    }
}
```

## Takeaway

Claims transformation is an elegant way to bridge the gap between claims provided by identity providers and the needs of your ASP.NET Core application.
The `IClaimsTransformation` interface enables you to customize the claims of the current `ClaimsPrincipal`.
Whether you need to add roles, map external groups to internal permissions, or extract additional information from a user profile,
claims transformation offers the flexibility to do so.

However, it's important to use claims transformation with a few key considerations in mind:

- Claims transformations are executed on each request.
- The `IClaimsTransformation` should be idempotent. It should not add existing claims to the `ClaimsPrincipal` if executed multiple times.
- Design your transformations efficiently, and consider caching the results if you're fetching external data to enrich your claims.

If you want to see a complete implementation of RBAC in ASP.NET Core, check out this [Authentication & Authorization playlist](https://www.youtube.com/playlist?list=PLYpjLpq5ZDGtJOHUbv7KHuxtYLk1nJPw5).

Hope this was helpful.

See you next week.

---



<Sponsorships>

[**Build Interactive Blazor Apps**](https://www.telerik.com/blogs/building-interactive-blazor-apps-webassembly?utm_medium=cpm&utm_source=milanjovanovic&utm_campaign=blazor-hybrid-general-awareness) with WebAssembly:
See what's new for Blazor WebAssembly in .NET 8 and create your first interactive WASM app.
[**Learn more here.**](https://www.telerik.com/blogs/building-interactive-blazor-apps-webassembly?utm_medium=cpm&utm_source=milanjovanovic&utm_campaign=blazor-hybrid-general-awareness)

Become a Postman master with [**Postman Intergalactic sessions.**](https://www.postman.com/events/intergalactic)
Test your APIs with ease and collaborate on API development with workspaces.
[**Get Postman for FREE!**](https://www.postman.com/downloads)

</Sponsorships>

Modern web applications need to serve increasing numbers of users and handle surges in traffic.
When a single server reaches its limits, performance degrades, leading to slow response times, errors, or complete downtime.

Load balancing is a key technique to address these challenges and improve the scalability of your application.

In this article, we will explore:

- How to use [YARP (Yet Another Reverse Proxy)](implementing-an-api-gateway-for-microservices-with-yarp) to implement load balancing
- How to leverage horizontal scaling for performance gains
- How to utilize K6 as a load testing tool

We'll dive into load balancing, why it matters, and how YARP simplifies the process for .NET applications.

## Types of Software Scalability

Before exploring YARP and load balancing further, let's cover the fundamentals of scaling.

There are two main approaches:

- **Vertical Scaling**: Involves upgrading individual servers with more powerful hardware - more CPU cores, RAM, and faster storage.
  However, this has a few limitations: costs escalate quickly, and you'll still hit a performance ceiling.
- **Horizontal Scaling**: Involves adding more servers to your infrastructure and distributing the load intelligently among them.
  This approach offers greater scalability potential, as you can continue adding servers to handle more traffic.

Horizontal scaling is where load balancing comes in, and YARP shines bright in this approach.

## Adding a Reverse Proxy

[YARP](https://microsoft.github.io/reverse-proxy/index.html) is a high-performance reverse proxy library from Microsoft.
It's designed with modern microservice architectures in mind.
A reverse proxy sits in front of your backend servers, acting as a traffic director.

Setting up YARP is quite straightforward.
You'll install the YARP NuGet package, create a basic configuration to define your backend destinations, and then activate the YARP middleware.
YARP allows you to perform routing and transformation tasks on incoming requests before they reach your backend servers.

First, let's install the `Yarp.ReverseProxy` NuGet package:

```powershell
Install-Package Yarp.ReverseProxy
```

Then, we're going to configure the required application services and introduce the YARP middleware to the request pipeline:

```csharp {3,4,8}
var builder = WebApplication.CreateBuilder(args);

builder.Services.AddReverseProxy()
    .LoadFromConfig(builder.Configuration.GetSection("ReverseProxy"));

var app = builder.Build();

app.MapReverseProxy();

app.Run();
```

All that's left is adding the YARP configuration to our `appsettings.json` file.
YARP uses `Routes` to represent incoming requests to the reverse proxy and `Clusters` to define the downstream services.
The `{**catch-all}` pattern allows us to easily route all incoming requests.

```json
{
  "ReverseProxy": {
    "Routes": {
      "api-route": {
        "ClusterId": "api-cluster",
        "Match": {
          "Path": "{**catch-all}"
        },
        "Transforms": [{ "PathPattern": "{**catch-all}" }]
      }
    },
    "Clusters": {
      "api-cluster": {
        "Destinations": {
          "destination1": {
            "Address": "http://api:8080"
          }
        }
      }
    }
  }
}
```

This configures YARP as a pass-through proxy, but let's update it to support horizontal scaling.

## Scaling Out With YARP Load Balancing

The core of horizontal scaling with YARP lies in its various [load balancing strategies](https://microsoft.github.io/reverse-proxy/articles/load-balancing.html):

- `PowerOfTwoChoices`: Selects two random destinations and selects the one with the least assigned requests.
- `FirstAlphabetical`: Selects the alphabetically first available destination server.
- `LeastRequests`: Sends requests to servers with the least assigned requests.
- `RoundRobin`: Distributes requests evenly across backend servers.
- `Random`: Randomly selects a backend server for each request.

You configure these strategies within YARP's configuration file.
The load balancing strategy can be configured using the `LoadBalancingPolicy` property on the cluster.

Here's what the updated YARP configuration looks like with `RoundRobin` load balancing:

```json {14,16-23}
{
  "ReverseProxy": {
    "Routes": {
      "api-route": {
        "ClusterId": "api-cluster",
        "Match": {
          "Path": "{**catch-all}"
        },
        "Transforms": [{ "PathPattern": "{**catch-all}" }]
      }
    },
    "Clusters": {
      "api-cluster": {
        "LoadBalancingPolicy": "RoundRobin",
        "Destinations": {
          "destination1": {
            "Address": "http://api-1:8080"
          },
          "destination2": {
            "Address": "http://api-2:8080"
          },
          "destination3": {
            "Address": "http://api-3:8080"
          }
        }
      }
    }
  }
}
```

Here's a diagram of what our system could look like with a YARP load balancer and horizontally scaled application servers.

The incoming API requests will first hit YARP, distributing the traffic to the application servers based on the load balancing strategy.
In this example, there's one database serving multiple application instances.

<Image
  src="blogs/mnw_083/horizontal_scaling.png"
  width={1615}
  height={919}
  alt="Horizontal scaling with a YARP load balancer."
/>

And now, let's do some performance testing.

## Performance Testing with K6

To see the impact of our horizontal scaling efforts, we need to do some load testing.
[K6](https://k6.io/) is a modern, developer-friendly load testing tool.
We'll write K6 scripts to simulate user traffic on our application and compare metrics like average response time and the number of successful requests per second.

The application we're going to scale horizontally has two API endpoints.
The `POST /users` endpoint creates a new user, saves the user to a [PostgreSQL](https://www.postgresql.org/) database, and returns the user's identifier.
The `GET /users/id` endpoint returns a user with the given identifer if it exists.

Here's a k6 performance test that will:

- Ramp up to **20 virtual users**
- Send a `POST` request to the `/users` endpoint
- Check that the response is `201 Created`
- Send a `GET` request to the `/users/{id}` endpoint
- Check that the response is `200 OK`

Note that all API requests go through the YARP load balancer.

```js
import { check } from 'k6';
import http from 'k6/http';

export const options = {
  stages: [
    { duration: '10s', target: 20 },
    { duration: '1m40s', target: 20 },
    { duration: '10s', target: 0 }
  ]
};

export default function () {
  const proxyUrl = 'http://localhost:3000';

  const response = http.post(`${proxyUrl}/users`);

  check(response, {
    'response code was 201': (res) => res.status == 201
  });

  const userResponse = http.get(`${proxyUrl}/users/${response.body}`);

  check(userResponse, {
    'response code was 200': (res) => res.status == 200
  });
}
```

To make the performance testing results more consistent, we can limit the available resources on the [Docker](https://www.docker.com) containers to `1 CPU` and `0.5G` of RAM.

```yml {4,5}
services:
  api:
    image: ${DOCKER_REGISTRY-}loadbalancingapi
    cpus: 1
    mem_limit: '0.5G'
    ports:
      - 5000:8080
    networks:
      - proxybackend
```

Finally, here are the k6 performance testing results:

```
|    API Instances   |     Request Duration     |    Requests Per Second    |
|------------------- |------------------------- |---------------------------:
|         1          |         9.68 ms          |          2260/s           |
|--------------------|------------------------- |---------------------------|
|         2          |         6.57 ms          |          2764/s           |
|--------------------|------------------------- |---------------------------|
|         3          |         5.62 ms          |          3227/s           |
|--------------------|------------------------- |---------------------------|
|         5          |         4.65 ms          |          3881/s           |
```

## Summary

Horizontal scaling, coupled with effective load balancing, can significantly enhance the performance and scalability of your web applications.
The benefits of horizontal scaling become especially apparent in high-traffic scenarios where a single server can no longer cope with the demand.

YARP is a powerful and user-friendly reverse proxy server for .NET applications.
However, highly complex, large-scale distributed systems might benefit from specialized, standalone load-balancing solutions.
These dedicated solutions can offer more granular control and sophisticated features.

If you want to learn more, here's how to [build an API Gateway using YARP](implementing-an-api-gateway-for-microservices-with-yarp).

You can find the [source code](https://github.com/m-jovanovic/yarp-load-balancing) for this example on GitHub.

That's all for today. I'll see you next week.

---



<Sponsorships>

Become a Postman master with [**Postman Intergalactic sessions.**](https://www.postman.com/events/intergalactic)
Test your APIs with ease and collaborate on API development with workspaces.
[**Get Postman for FREE!**](https://www.postman.com/downloads)

Introducing [**Shesha**](https://shesha.io?utm_source=milan&utm_medium=email&utm_campaign=20240323), a brand new, open-source, low-code framework for .NET developers.
Create business applications faster and with >80% less code! [**Learn more here.**](https://shesha.io?utm_source=milan&utm_medium=email&utm_campaign=20240323)

</Sponsorships>

Whether you're building a data analytics platform, migrating a legacy system, or onboarding a surge of new users,
there will likely come a time when you'll need to insert a massive amount of data into your database.

Inserting the records one by one feels like watching paint dry in slow motion.
Traditional methods won't cut it.

So, understanding fast bulk insert techniques with C# and EF Core becomes essential.

In today's issue, we'll explore several options for performing bulk inserts in C#:

- Dapper
- EF Core
- EF Core Bulk Extensions
- SQL Bulk Copy

The examples are based on a `User` class with a respective `Users` table in **SQL Server**.

```csharp
public class User
{
    public int Id { get; set; }
    public string Email { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string PhoneNumber { get; set; }
}
```

This isn't a complete list of bulk insert implementations.
There are a few options I didn't explore, like manully generating SQL statements
and using [Table-Valued parameters](https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver16).

## EF Core Simple Approach

Let's start with a simple example using EF Core.
We're creating an `ApplicationDbContext` instance, adding a `User` object, and calling `SaveChangesAsync`.
This will insert each record to the database one by one.
In other words, each record requires one round trip to the database.

```csharp
using var context = new ApplicationDbContext();

foreach (var user in GetUsers())
{
    context.Users.Add(user);

    await context.SaveChangesAsync();
}
```

The results are as poor as you'd expect:

```
EF Core - Add one and save, for 100 users: 20 ms
EF Core - Add one and save, for 1,000 users: 260 ms
EF Core - Add one and save, for 10,000 users: 8,860 ms
```

I omitted the results with `100,000` and `1,000,000` records because they took too long to execute.

We'll use this as a "how not to do bulk inserts" example.

## Dapper Simple Insert

[Dapper](https://github.com/DapperLib/Dapper) is a simple SQL-to-object mapper for .NET.
It allows us to easily insert a collection of objects into the database.

I'm using Dapper's feature to unwrap a collection into a SQL `INSERT` statement.

```csharp
using var connection = new SqlConnection(connectionString);
connection.Open();

const string sql =
    @"
    INSERT INTO Users (Email, FirstName, LastName, PhoneNumber)
    VALUES (@Email, @FirstName, @LastName, @PhoneNumber);
    ";

await connection.ExecuteAsync(sql, GetUsers());
```

The results are much better than the initial example:

```
Dapper - Insert range, for 100 users: 10 ms
Dapper - Insert range, for 1,000 users: 113 ms
Dapper - Insert range, for 10,000 users: 1,028 ms
Dapper - Insert range, for 100,000 users: 10,916 ms
Dapper - Insert range, for 1,000,000 users: 109,065 ms
```

## EF Core Add and Save

However, EF Core still didn't throw in the towel.
The first example was poorly implemented on purpose.
EF Core can batch multiple SQL statements together, so let's use that.

If we make a simple change, we can get significantly better performance.
First, we're adding all the objects to the `ApplicationDbContext`.
Then, we're going to call `SaveChangesAsync` only once.

EF will create a batched SQL statement - group many `INSERT` statements together - and send them to the database together.
This reduces the number of round trips to the database, giving us improved performance.

```csharp
using var context = new ApplicationDbContext();

foreach (var user in GetUsers())
{
    context.Users.Add(user);
}

await context.SaveChangesAsync();
```

Here are the benchmark results of this implementation:

```
EF Core - Add all and save, for 100 users: 2 ms
EF Core - Add all and save, for 1,000 users: 18 ms
EF Core - Add all and save, for 10,000 users: 203 ms
EF Core - Add all and save, for 100,000 users: 2,129 ms
EF Core - Add all and save, for 1,000,000 users: 21,557 ms
```

Remember, it took Dapper **109 seconds** to insert `1,000,000` records.
We can achieve the same with EF Core batched queries in **~21 seconds**.

## EF Core AddRange and Save

This is an alternative to the previous example.
Instead of calling `Add` for all objects, we can call `AddRange` and pass in a collection.

I wanted to show this implementation because I prefer it over the previous one.

```csharp
using var context = new ApplicationDbContext();

context.Users.AddRange(GetUsers());

await context.SaveChangesAsync();
```

The results are very similar to the previous example:

```
EF Core - Add range and save, for 100 users: 2 ms
EF Core - Add range and save, for 1,000 users: 18 ms
EF Core - Add range and save, for 10,000 users: 204 ms
EF Core - Add range and save, for 100,000 users: 2,111 ms
EF Core - Add range and save, for 1,000,000 users: 21,605 ms
```

## EF Core Bulk Extensions

There's an awesome library called [EF Core Bulk Extensions](https://github.com/borisdj/EFCore.BulkExtensions) that we can use to squeeze out more performance.
You can do a lot more than bulk inserts with this library, so I recommend exploring it.
This library is open source, and has a community license if you meet the free usage criteria.
Check the [licensing section](https://github.com/borisdj/EFCore.BulkExtensions?#license) for more details.

For our use case, the `BulkInsertAsync` method is an excellent choice.
We can pass the collection of objects, and it will perform an SQL bulk insert.

```csharp
using var context = new ApplicationDbContext();

await context.BulkInsertAsync(GetUsers());
```

The performance is equally amazing:

```
EF Core - Bulk Extensions, for 100 users: 1.9 ms
EF Core - Bulk Extensions, for 1,000 users: 8 ms
EF Core - Bulk Extensions, for 10,000 users: 76 ms
EF Core - Bulk Extensions, for 100,000 users: 742 ms
EF Core - Bulk Extensions, for 1,000,000 users: 8,333 ms
```

For comparison, we needed **~21 seconds** to insert `1,000,000` records with EF Core batched queries.
We can do the same with the [Bulk Extensions](https://github.com/borisdj/EFCore.BulkExtensions) library in just **8 seconds**.

## SQL Bulk Copy

Lastly, if we can't get the desired performance from EF Core, we can try using [`SqlBulkCopy`](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy).
SQL Server supports [bulk copy operations](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/bulk-copy-operations-in-sql-server) natively, so let's use this.

This implementation is slightly more complex than the EF Core examples.
We need to configure the `SqlBulkCopy` instance and create a `DataTable` containing the objects we want to insert.

```csharp
using var bulkCopy = new SqlBulkCopy(ConnectionString);

bulkCopy.DestinationTableName = "dbo.Users";

bulkCopy.ColumnMappings.Add(nameof(User.Email), "Email");
bulkCopy.ColumnMappings.Add(nameof(User.FirstName), "FirstName");
bulkCopy.ColumnMappings.Add(nameof(User.LastName), "LastName");
bulkCopy.ColumnMappings.Add(nameof(User.PhoneNumber), "PhoneNumber");

await bulkCopy.WriteToServerAsync(GetUsersDataTable());
```

However, the performance is blazing fast:

```
SQL Bulk Copy, for 100 users: 1.7 ms
SQL Bulk Copy, for 1,000 users: 7 ms
SQL Bulk Copy, for 10,000 users: 68 ms
SQL Bulk Copy, for 100,000 users: 646 ms
SQL Bulk Copy, for 1,000,000 users: 7,339 ms
```

Here's how you can create a `DataTable` and populate it with a list of objects:

```csharp
DataTable GetUsersDataTable()
{
    var dataTable = new DataTable();

    dataTable.Columns.Add(nameof(User.Email), typeof(string));
    dataTable.Columns.Add(nameof(User.FirstName), typeof(string));
    dataTable.Columns.Add(nameof(User.LastName), typeof(string));
    dataTable.Columns.Add(nameof(User.PhoneNumber), typeof(string));

    foreach (var user in GetUsers())
    {
        dataTable.Rows.Add(
            user.Email, user.FirstName, user.LastName, user.PhoneNumber);
    }

    return dataTable;
}
```

## Results

Here are the results for all the bulk insert implementations:

```
| Method             |   Size     |      Speed
|------------------- |----------- |----------------:
| EF_OneByOne        | 100        |      19.800 ms |
| EF_OneByOne        | 1000       |     259.870 ms |
| EF_OneByOne        | 10000      |   8,860.790 ms |
| EF_OneByOne        | 100000     |            N/A |
| EF_OneByOne        | 1000000    |            N/A |

| Dapper_Insert      | 100        |      10.650 ms |
| Dapper_Insert      | 1000       |     113.137 ms |
| Dapper_Insert      | 10000      |   1,027.979 ms |
| Dapper_Insert      | 100000     |  10,916.628 ms |
| Dapper_Insert      | 1000000    | 109,064.815 ms |

| EF_AddAll          | 100        |       2.064 ms |
| EF_AddAll          | 1000       |      17.906 ms |
| EF_AddAll          | 10000      |     202.975 ms |
| EF_AddAll          | 100000     |   2,129.370 ms |
| EF_AddAll          | 1000000    |  21,557.136 ms |

| EF_AddRange        | 100        |       2.035 ms |
| EF_AddRange        | 1000       |      17.857 ms |
| EF_AddRange        | 10000      |     204.029 ms |
| EF_AddRange        | 100000     |   2,111.106 ms |
| EF_AddRange        | 1000000    |  21,605.668 ms |

| BulkExtensions     | 100        |       1.922 ms |
| BulkExtensions     | 1000       |       7.943 ms |
| BulkExtensions     | 10000      |      76.406 ms |
| BulkExtensions     | 100000     |     742.325 ms |
| BulkExtensions     | 1000000    |   8,333.950 ms |

| BulkCopy           | 100        |       1.721 ms |
| BulkCopy           | 1000       |       7.380 ms |
| BulkCopy           | 10000      |      68.364 ms |
| BulkCopy           | 100000     |     646.219 ms |
| BulkCopy           | 1000000    |   7,339.298 ms |
```

## Takeaway

`SqlBulkCopy` holds the crown for maximum raw speed.
However, [EF Core Bulk Extensions](https://github.com/borisdj/EFCore.BulkExtensions) deliver fantastic performance while maintaining the ease of use that Entity Framework Core is known for.

The best choice hinges on your project's specific demands:

- Performance is all that matters? `SqlBulkCopy` is your solution.
- Need excellent speed and streamlined development? EF Core is a smart choice.

I leave it up to you to decide which option is best for your use case.

Hope this was helpful.

See you next week.

---



<Sponsorships>

Become a Postman master with [**Postman Intergalactic sessions.**](https://www.postman.com/events/intergalactic)
Test your APIs with ease and collaborate on API development with workspaces.
[**Get Postman for FREE!**](https://www.postman.com/downloads)

[**ABP Framework**](https://commercial.abp.io/pricing?utm_source=milanw&utm_medium=w81)
is a platform for building enterprise-grade ASP.NET Core applications.
It comes with production-ready components, modular architecture, and Domain-Driven Design.
[**Learn more here.**](https://commercial.abp.io/pricing?utm_source=milanw&utm_medium=w81)

</Sponsorships>

To delete or not to delete, that is the question (pun intended).

The traditional way to remove information in a database is through a "hard delete."
A hard delete permanently erases a record from the database table.
While this seems straightforward, it presents a significant risk: once that data is gone, it's gone for good.

Instead of physically removing a record, a soft delete marks it as deleted, usually by setting a flag like `IsDeleted` to `true`.
The record remains in the database, but it's effectively hidden from regular application queries.

Today, we'll dive into the details of how to implement soft deletes using EF Core.
We'll discuss global query filters, explore efficient ways to handle soft-deleted data, and weigh the trade-offs.

## What Is a Soft Delete?

A soft delete is a data persistence strategy that prevents the permanent deletion of records from your database.
Instead of removing data from the database, a flag is set on the record, indicating it as "deleted."

This approach allows the application to ignore these records during normal queries.
However, you can restore these records if necessary.
Soft delete is also practical if you want to keep foreign key constraints in place.
Soft delete is a "non-destructive" operation in contrast with hard delete, where data is completely removed from the database.

A hard delete uses the SQL `DELETE` statement:

```sql
DELETE FROM bookings.Reviews
WHERE Id = @BookingId;
```

A soft delete, on the other hand, uses an `UPDATE` statement:

```sql
UPDATE bookings.Reviews
SET IsDeleted = 1, DeletedOnUtc = @UtcNow
WHERE Id = @BookingId;
```

The data is still present in the database, and the operation can be undone.

But you need to remember to filter out soft-deleted data when querying the database:

```sql
SELECT *
FROM bookings.Reviews
WHERE IsDeleted = 0;
```

Let's see how we can implement soft delete with [EF Core](https://learn.microsoft.com/en-us/ef/core/).

## Soft Deletes Using EF Core Interceptors

[EF Core interceptors](https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/interceptors)
provide a powerful mechanism for intercepting and modifying database operations.
For example, you can intercept the saving changes operation to implement soft delete functionality.

Let's create an `ISoftDeletable` marker interface to represent soft-deletable entities:

```csharp
public interface ISoftDeletable
{
    bool IsDeleted { get; set; }

    DateTime? DeletedOnUtc { get; set; }
}
```

The entities that should support soft delete will implement this interface.
You will need to apply the respective database migration to create these columns.

The next component we need is a `SaveChangesInterceptor`, which allows us to hook into the `SavingChangesAsync` (or `SavingChanges`) method.
We can access the `ChangeTracker` and look for entries that implement `ISoftDeletable` and are flagged for deletion.
We can figure this out by checking if the entity state is `EntityState.Deleted`.

When we find the entities flagged for deletion, we loop through them and update their state to `EntityState.Modified`.
You should also set the respective values for the `IsDeleted` and `DeletedOnUtc` properties.
This will cause EF to generate an `UPDATE` operation instead of a `DELETE` operation.

```csharp
public sealed class SoftDeleteInterceptor : SaveChangesInterceptor
{
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData,
        InterceptionResult<int> result,
        CancellationToken cancellationToken = default)
    {
        if (eventData.Context is null)
        {
            return base.SavingChangesAsync(
                eventData, result, cancellationToken);
        }

        IEnumerable<EntityEntry<ISoftDeletable>> entries =
            eventData
                .Context
                .ChangeTracker
                .Entries<ISoftDeletable>()
                .Where(e => e.State == EntityState.Deleted);

        foreach (EntityEntry<ISoftDeletable> softDeletable in entries)
        {
            softDeletable.State = EntityState.Modified;
            softDeletable.Entity.IsDeleted = true;
            softDeletable.Entity.DeletedOnUtc = DateTime.UtcNow;
        }

        return base.SavingChangesAsync(eventData, result, cancellationToken);
    }
}
```

This approach ensures that all delete operations across the application respect the soft delete policy.

You'll need to register the `SoftDeleteInterceptor` with dependency injection and configure it with the `ApplicationDbContext`.

```csharp
services.AddSingleton<SoftDeleteInterceptor>();

services.AddDbContext<ApplicationDbContext>(
    (sp, options) => options
        .UseSqlServer(connectionString)
        .AddInterceptors(
            sp.GetRequiredService<SoftDeleteInterceptor>()));
```

If you want to learn more, here's an article with a few practical use cases for [EF Core interceptors](how-to-use-ef-core-interceptors).

## Automatically Filtering Soft-Deleted Data

To ensure that soft-deleted records are automatically excluded from queries, we can use [EF Core global query filters](how-to-use-global-query-filters-in-ef-core).
We can apply query filters to entities using the `OnModelCreating` method to automatically exclude records marked as deleted.
This feature dramatically simplifies writing queries.

Here's how to configure the soft delete query filter:

```csharp {8}
public sealed class ApplicationDbContext(
    DbContextOptions<UsersDbContext> options) : DbContext(options)
{
    public DbSet<Review> Reviews { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Review>().HasQueryFilter(r => !r.IsDeleted);
    }
}
```

A limitation is you can't have more than one query filter configured per entity.

However, it's sometimes useful to explicitly include soft-deleted records.
You can achieve this using the `IgnoreQueryFilters` method.

```csharp {2}
dbContex.Reviews
    .IgnoreQueryFilters()
    .Where(r => r.ApartmentId == apartmentId)
    .ToList();
```

## Faster Queries Using Filtered Index

To enhance query performance, especially in tables with a significant number of soft-deleted records, you can use **filtered indexes**.
A filtered index only includes records that meet the specified criteria.
This reduces the index size and improves query execution times for operations that exclude filtered records.
Most popular databases support filtered indexes.

Here's how you can configure a [filtered index with EF Core](https://learn.microsoft.com/en-us/ef/core/modeling/indexes?tabs=data-annotations#index-filter):

```csharp {10-12}
public sealed class ApplicationDbContext(
    DbContextOptions<UsersDbContext> options) : DbContext(options)
{
    public DbSet<Review> Reviews { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Review>().HasQueryFilter(r => !r.IsDeleted);

        modelBuilder.Entity<Review>()
            .HasIndex(r => r.IsDeleted)
            .HasFilter("IsDeleted = 0");
    }
}
```

The `HasFilter` method accepts the SQL filter for records that will be included in the index.

You can also create a filtered index using SQL:

```sql
CREATE INDEX IX_Reviews_IsDeleted
ON bookings.Reviews (IsDeleted)
WHERE IsDeleted = 0;
```

You can learn more about filtered indexes from the documentation:

- [SQL Server filtered index](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes?view=sql-server-ver16)
- [PostgreSQL partial index](https://www.postgresql.org/docs/16/indexes-partial.html)

## Do You Really Need Soft Deletes?

It's worthwhile to think through if you even need to soft delete records.

In enterprise systems, you're typically not thinking about "deleting" data.
There are business concepts that don't involve deleting data.
A few examples are canceling an order, refunding a payment, or voiding an invoice.
These "destructive" operations return the system to a previous state.
But from a business perspective, you aren't really deleting data.

Soft deletes are helpful if there is a risk of accidental deletion.
They allow you to easily restore soft-deleted records.

In any case, consider if soft deletes make sense from a business perspective.

## Takeaway

Soft deletes offer a valuable safety net for data recovery and can enhance historical data tracking.
However, it's crucial to assess whether they truly align with your application's specific requirements.
Consider factors like the importance of deleted data recovery, any auditing needs, and your industry's regulations.
Creating a filtered index can improve query performance on tables with soft-deleted records.

If you decide that soft deletes are a good fit, EF Core provides the tools necessary for a streamlined implementation.

Thanks for reading, and I'll see you next week!

---


Previous issues of The .NET Weekly