Migrations & Transactions

The EF Core CLI workflow for evolving the schema safely, plus explicit transactions for the operations that touch more than one table and need to succeed or fail together.

The migration workflow

shell

# one-time: install the CLI tool
dotnet tool install --global dotnet-ef

# after changing an entity or a Configuration class:
dotnet ef migrations add AddedResourceTable \
  --project YourApp.Infrastructure \
  --startup-project YourApp.API

# review the generated file before applying it — see below
dotnet ef database update \
  --project YourApp.Infrastructure \
  --startup-project YourApp.API

# undo the last migration that hasn't been applied yet
dotnet ef migrations remove --project YourApp.Infrastructure --startup-project YourApp.API

Always read the generated migration before running it

EF Core's diff is usually right, but it can't know your intent — renaming a column looks identical to EF as "drop old column, add new column", which silently loses data. Catch this in the generated file, not in production.

Migrations/…_RenamedColumn.cs — wrong (data loss) vs right

// EF's default guess for renaming Name -> FullName:
migrationBuilder.DropColumn(name: "Name", table: "Users");
migrationBuilder.AddColumn<string>(name: "FullName", table: "Users");
// ^ every existing row loses its name

// what you actually want — edit the generated file to say this instead:
migrationBuilder.RenameColumn(
    name: "Name",
    table: "Users",
    newName: "FullName");

Applying migrations at startup vs. as a separate step

The earlier Layered Architecture page's Program.cs calls context.Database.Migrate() on every boot — convenient for a small app or a single-instance deployment, since the schema is always in sync with the code. It's the wrong call once you're running multiple API instances behind a load balancer (they'd race to migrate) or want a deploy to fail loudly on a bad migration before traffic hits the new code.

CI/CD: migrate as its own pipeline step instead

# in the deploy workflow, before the new app version starts serving traffic
dotnet ef database update \
  --project YourApp.Infrastructure \
  --startup-project YourApp.API \
  --connection "$PRODUCTION_CONNECTION_STRING"

Explicit transactions

A single SaveChangesAsync() call is already atomic. You need an explicit transaction when an operation spans multiple SaveChangesAsync() calls, or mixes EF Core with a raw SQL command, and all of it needs to commit or roll back together.

TransferService.cs

public async Task TransferStockAsync(Guid fromWarehouseId, Guid toWarehouseId, Guid productId, int quantity)
{
    await using var transaction = await _context.Database.BeginTransactionAsync();

    try
    {
        var source = await _stockRepository.GetAsync(fromWarehouseId, productId);
        if (source.Quantity < quantity)
            throw new ArgumentException("Not enough stock at source warehouse.");

        source.Quantity -= quantity;
        await _context.SaveChangesAsync();

        var destination = await _stockRepository.GetOrCreateAsync(toWarehouseId, productId);
        destination.Quantity += quantity;
        await _context.SaveChangesAsync();

        await transaction.CommitAsync();
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

Connection resiliency

A transient network blip shouldn't fail a request outright. EnableRetryOnFailure makes EF Core retry a failed connection attempt automatically — but it changes how you must write explicit transactions, since a retry has to redo the whole transaction, not resume it.

Infrastructure/Dependencies.cs

services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString, sql =>
    {
        sql.MigrationsHistoryTable("Migrations");
        sql.EnableRetryOnFailure(maxRetryCount: 3, maxRetryDelay: TimeSpan.FromSeconds(5), errorNumbersToAdd: null);
    });
});

explicit transaction + retry — use an execution strategy

var strategy = _context.Database.CreateExecutionStrategy();

await strategy.ExecuteAsync(async () =>
{
    await using var transaction = await _context.Database.BeginTransactionAsync();

    source.Quantity -= quantity;
    await _context.SaveChangesAsync();
    destination.Quantity += quantity;
    await _context.SaveChangesAsync();

    await transaction.CommitAsync();
});

With EnableRetryOnFailure on, EF Core throws at startup if you begin a transaction manually without wrapping it in CreateExecutionStrategy().ExecuteAsync(...) — the retry logic needs to own the whole unit of work so it can replay it cleanly from the start on failure.