Entity Framework Core

#EF Core and DbContext as a unit of work

Repeated mapping and CRUD SQL cost development time. EF Core is an ORM that translates supported LINQ, materializes objects, tracks changes and persists them. It does not replace SQL knowledge or provider-specific behavior.

using Microsoft.EntityFrameworkCore;

public sealed class StoreDb(DbContextOptions<StoreDb> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();       // (1)
}
public sealed class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public decimal Price { get; set; }
}

// In Program.cs, with the SQL Server provider installed:
builder.Services.AddDbContext<StoreDb>(o => o.UseSqlServer(connectionString)); // (2)
// In a scoped handler receiving StoreDb db:
db.Products.Add(new Product { Name = "Mug", Price = 120m });
await db.SaveChangesAsync(ct);                             // (3)

A generic repository duplicating DbSet and SaveChanges may add little. Introduce abstractions when they protect a boundary or meaningful business queries.

#Fluent API and schema ownership

Conventions are a useful start, but precision, relationships and indexes need deliberate design. Fluent API separates persistence configuration from entity definitions.

using Microsoft.EntityFrameworkCore.Metadata.Builders;

public sealed class ProductConfig : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> e)
    {
        e.ToTable("Products");
        e.HasKey(p => p.Id);                              // (1)
        e.Property(p => p.Name).HasMaxLength(200).IsRequired();
        e.Property(p => p.Price).HasPrecision(18, 2);       // (2)
        e.HasIndex(p => p.Name);                          // (3)
    }
}
// Inside StoreDb:
// protected override void OnModelCreating(ModelBuilder b) =>
//     b.ApplyConfigurationsFromAssembly(typeof(StoreDb).Assembly);

Configure relationships with HasMany, WithOne and HasForeignKey, choosing DeleteBehavior deliberately. Database-first scaffolds from an existing schema; code-first evolves a model through migrations. Choose one schema authority and plan how customizations survive repeated scaffolding.

#Loading strategies and projection

An order summary does not need every product and payment. Fetch the smallest useful shape and keep database round trips visible.

// These snippets assume an Order model with Lines and Customer navigations.
var entity = await db.Orders.Include(o => o.Lines)
    .SingleAsync(o => o.Id == id, ct);                     // (1)

await db.Entry(entity).Reference(o => o.Customer).LoadAsync(ct); // (2)

var summary = await db.Orders.Where(o => o.Id == id)
    .Select(o => new { o.Id, o.Total, LineCount = o.Lines.Count })
    .SingleAsync(ct);                                     // (3)

Lazy loading queries when a navigation is accessed. Proxies require their package, configuration and overridable navigations; ILazyLoader is an alternative. Both can hide I/O and cause N+1. A projection containing an entity, such as new { Order = o }, can still track that entity.

#Tracking, no-tracking and identity resolution

When editing an entity, EF needs its original and current state. A read-only catalog usually does not need that bookkeeping.

var product = await db.Products.SingleAsync(p => p.Id == id, ct); // (1)
product.Price = newPrice;
await db.SaveChangesAsync(ct);

var catalog = await db.Products.AsNoTracking()
    .OrderBy(p => p.Id).Take(20).ToListAsync(ct);             // (2)

var graph = await db.Orders.AsNoTrackingWithIdentityResolution()
    .Include(o => o.Customer).Take(20).ToListAsync(ct);       // (3)

A tracking query may return an existing context instance with its current values; it is not a guaranteed refresh. For detached updates, attach carefully and specify allowed fields and original concurrency tokens. Calling Update on a client-provided graph can mark unwanted fields and enable over-posting.

#N+1, cartesian explosion and split queries

An endpoint can be slow because of too many round trips or too much data in one query. Those are different problems.

var orders = await db.Orders.ToListAsync(ct);
foreach (var order in orders)
    Console.WriteLine(order.Lines.Count);

var counts = await db.Orders
    .Select(o => new { o.Id, Count = o.Lines.Count })
    .ToListAsync(ct);

var orders = await db.Orders
    .Include(o => o.Lines)
    .Include(o => o.Payments)
    .AsSplitQuery()                                       // (1)
    .OrderBy(o => o.Id).Take(20).ToListAsync(ct);

Splitting reduces duplication but adds round trips and buffering. A Read Committed transaction alone does not create a shared snapshot; if statements must agree, consider suitable snapshot or serializable isolation and its cost. Inspect SQL logs, query counts and actual plans. Do not enable sensitive data logging in production merely to investigate performance.

#Set-based changes in EF Core 10

Loading thousands of entities just to modify them costs memory, tracking and SQL work. Set-based operations change matching rows directly in the database.

int changed = await db.Orders.Where(o => o.Status == "Pending")
    .ExecuteUpdateAsync(setters =>                         // (1)
    {
        setters.SetProperty(o => o.Status, "Expired");
        if (stampTime)
            setters.SetProperty(o => o.UpdatedAt, now);
    }, ct);

int deleted = await db.Orders
    .Where(o => o.Status == "Draft" && o.CreatedAt < cutoff)
    .ExecuteDeleteAsync(ct);                              // (2)

#Production migrations and expand-contract

A schema change can lock a table or break old instances during a rolling deployment. A migration is reviewable deployment input, not an instruction for every instance to alter production automatically.

# Requires matching dotnet-ef tools and EF Core Design package.
dotnet ef migrations add AddMobileNumber
dotnet ef migrations script --idempotent --output migrate.sql
# Alternative deployment artifact; self-contained requires an explicit option.
dotnet ef migrations bundle --self-contained -r linux-x64 -o efbundle

Review SQL, test production-like volumes, and plan backups, forward repair and lock duration. Execute a script or bundle in one controlled deployment step with separate privileges. Modern EF migration locking exists for some providers, but cannot solve compatibility, application privileges or every DDL risk.

1. Expand: add nullable MobileNumber while retaining Phone.
2. Deploy compatible code. While old writers remain, keep the old field authoritative or maintain reliable synchronization.
3. After old writers retire, dual-write and backfill in batches while handling concurrent writes.
4. Verify completeness, switch reads and monitor.
5. Contract: remove the old field in a later release after rollback paths and old consumers no longer need it.

Code-first compares the model with its migration snapshot, not the entire live database. Define ownership and detect drift. Database-first suits externally managed schemas. Do not mix EnsureCreated with migrations for the same database.

#Optimistic and pessimistic concurrency

Two users edit the same product; the last save should not silently erase the first change. Optimistic concurrency detects a changed version when writing.

// On an order entity, add: public byte[] Version { get; set; } = [];
// SQL Server configuration:
modelBuilder.Entity<Order>().Property(o => o.Version).IsRowVersion(); // (1)

try
{
    order.Status = "Confirmed";
    await db.SaveChangesAsync(ct);                         // (2)
}
catch (DbUpdateConcurrencyException)
{
    return Results.Conflict(new { code = "order.changed" }); // (3)
}

-- PostgreSQL: inside a short transaction, lock before read-modify-write.
SELECT id, available FROM products WHERE id = @id FOR UPDATE;

-- For a simple reservation, an atomic statement can be enough.
UPDATE products SET available = available - @qty
WHERE id = @id AND available >= @qty;

Pessimistic locking blocks competing changes until the transaction ends, at the cost of blocking and deadlocks. EF has no universal LINQ lock hint across providers. For conditional updates, validate positive quantity and inspect affected rows; zero can mean either a missing product or insufficient stock.

#Transactions and execution strategies

One SaveChanges call often suffices. Multiple saves or bulk statements may need a shared transaction; a retry strategy must replay the entire unit.

// probe and factory use identical provider options with retries configured.
await using var probe = await factory.CreateDbContextAsync(ct);
var strategy = probe.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>                    // (1)
{
    await using var db = await factory.CreateDbContextAsync(ct);
    await using var tx = await db.Database.BeginTransactionAsync(ct); // (2)
    var order = await db.Orders.SingleAsync(o => o.Id == orderId, ct);
    order.Status = "Confirmed";
    await db.SaveChangesAsync(ct);
    await db.OrderLines.Where(l => l.OrderId == orderId)
        .ExecuteUpdateAsync(s => s.SetProperty(l => l.IsReserved, true), ct);
    await tx.CommitAsync(ct);                              // (3)
});

Never send email or charge a gateway inside the retry delegate; persist an outbox entry in the transaction. SaveChanges inside an existing transaction may use savepoints; SQL Server MARS prevents automatic savepoints. Cancellation does not prove that commit failed. Do not retry every exception, especially validation and business conflicts.

#Safe raw SQL and named query filters

A complex query may need direct SQL. That escape hatch must preserve parameterization and tenant isolation.

var products = await db.Products
    .FromSql($"SELECT * FROM Products WHERE Price > {minimum}") // (1)
    .AsNoTracking().ToListAsync(ct);

var prices = await db.Database
    .SqlQuery<decimal>($"SELECT Price AS Value FROM Products WHERE Id = {id}")
    .ToListAsync(ct);                                     // (2)

// In OnModelCreating; tenantId is current context state.
modelBuilder.Entity<Order>()
    .HasQueryFilter("SoftDelete", o => !o.IsDeleted)
    .HasQueryFilter("Tenant", o => o.TenantId == tenantId);  // (3)
var archived = await db.Orders.IgnoreQueryFilters(["SoftDelete"])
    .ToListAsync(ct);                                     // (4)

#Value mapping and choosing a data tool

A shipping address is a value within an order and need not be an independent entity. Choose mapping by identity and query requirements, then choose tooling for the workload.

// EF Core 10, with a relational provider supporting complex JSON mapping.
modelBuilder.Entity<Order>()
    .ComplexProperty(o => o.ShippingAddress, address => address.ToJson()); // (1)

// Earlier owned-entity approach, as an ALTERNATIVE mapping:
// modelBuilder.Entity<Order>().OwnsOne(o => o.ShippingAddress);

JSON can reduce tables for data read together. Query translation, indexing, constraints and partial updates depend on provider and database version. Avoid giant documents that are constantly rewritten or need extensive relational reporting.

Dapper is not universally faster; SQL, round trips and payload often dominate. Mixing tools requires a shared connection and transaction when changes must commit atomically.