Data access with Entity Framework Core

Model entities, migrate the schema, query with LINQ, control loading, and use transactions and raw SQL when they are the right tool.

DbContext and entities

using Microsoft.EntityFrameworkCore;

public sealed class Order
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public string Sku { get; set; } = "";
    public int Quantity { get; set; }
    public DateTimeOffset CreatedAt { get; set; }
    public Guid CustomerId { get; set; }
    public Customer Customer { get; set; } = null!;    // required navigation
    public List<OrderLine> Lines { get; set; } = [];
}

public sealed class Customer
{
    public Guid Id { get; set; }
    public string Name { get; set; } = "";
    public List<Order> Orders { get; set; } = [];
}

public sealed class OrderLine
{
    public int Id { get; set; }
    public Guid OrderId { get; set; }
    public string Sku { get; set; } = "";
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
}

public sealed class ShopContext : DbContext
{
    public ShopContext(DbContextOptions<ShopContext> options) : base(options) { }

    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<OrderLine> Lines => Set<OrderLine>();

    protected override void OnModelCreating(ModelBuilder b)
    {
        b.Entity<Order>(e =>
        {
            e.HasKey(o => o.Id);
            e.Property(o => o.Sku).HasMaxLength(32).IsRequired();
            e.HasIndex(o => new { o.CustomerId, o.CreatedAt });   // covers the common filter
            e.HasOne(o => o.Customer).WithMany(c => c.Orders).HasForeignKey(o => o.CustomerId);
            e.HasMany(o => o.Lines).WithOne().HasForeignKey(l => l.OrderId).OnDelete(DeleteBehavior.Cascade);
        });

        b.Entity<OrderLine>().Property(l => l.UnitPrice).HasPrecision(18, 2);   // never use double for money
    }
}

dotnet tool install --global dotnet-ef
dotnet ef migrations add InitialSchema
dotnet ef database update
dotnet ef migrations script --idempotent -o migrate.sql   # inspect before applying
dotnet ef migrations remove                                 # undo an unapplied migration

Always read the generated migration before applying it. A rename that EF models as a drop and an add silently deletes a column's data; fix it with migrationBuilder.RenameColumn instead.

Querying, projections and loading

// project to a DTO in SQL: only the needed columns come back
var summaries = await db.Orders
    .Where(o => o.CreatedAt >= since && o.Quantity > 0)
    .OrderByDescending(o => o.CreatedAt)
    .Select(o => new OrderSummary(o.Id, o.Sku, o.Quantity, o.Customer.Name))
    .AsNoTracking()                       // read-only: no change tracking overhead
    .Take(50)
    .ToListAsync(ct);

// joins and grouping translate to SQL
var perCustomer = await db.Orders
    .GroupBy(o => o.Customer.Name)
    .Select(g => new { Customer = g.Key, Orders = g.Count(), Total = g.Sum(o => o.Quantity) })
    .ToListAsync(ct);

// explicit loading strategy: include only what you need
var withLines = await db.Orders
    .Include(o => o.Lines)
    .AsSplitQuery()                       // avoids the cartesian explosion of two Includes
    .FirstOrDefaultAsync(o => o.Id == id, ct);

// AsNoTrackingWithIdentityResolution keeps one instance per key without tracking
var graph = await db.Orders
    .AsNoTrackingWithIdentityResolution()
    .Include(o => o.Lines)
    .ToListAsync(ct);

• N+1 is the most common EF performance bug: a query inside a loop issues one round trip per iteration. Project or include the data in the original query instead.
• AsNoTracking is safe and worthwhile for read-only queries; it skips snapshotting every entity.
• Only the expression tree is translated to SQL. A method EF cannot translate throws at run time, so keep client-side logic after AsEnumerable.
• ExecuteUpdateAsync and ExecuteDeleteAsync issue a single statement without loading entities, which is far cheaper for bulk changes.
• A DbContext is not thread safe. One instance per request (scoped) and never shared across parallel tasks.

// bulk operations without loading anything
int changed = await db.Orders
    .Where(o => o.CreatedAt < cutoff)
    .ExecuteUpdateAsync(s => s.SetProperty(o => o.Archived, true), ct);

int removed = await db.Orders
    .Where(o => o.Archived && o.CreatedAt < older)
    .ExecuteDeleteAsync(ct);

Transactions and raw SQL

// a transaction around several operations
await using var tx = await db.Database.BeginTransactionAsync(ct);
try
{
    var order = new Order { Sku = sku, Quantity = qty };
    db.Orders.Add(order);
    await db.SaveChangesAsync(ct);

    await db.Database.ExecuteSqlInterpolatedAsync(
        $"UPDATE Stock SET Reserved = Reserved + {qty} WHERE Sku = {sku}", ct);

    await tx.CommitAsync(ct);
}
catch
{
    await tx.RollbackAsync(ct);
    throw;
}

// raw SQL with parameters: SqlInterpolated parameterises the holes
var rows = await db.Orders
    .FromSql($"SELECT * FROM Orders WHERE Quantity > {min}")
    .ToListAsync(ct);

// a keyless type for a stored procedure or a view
public sealed class SalesByMonth
{
    public string Month { get; set; } = "";
    public decimal Total { get; set; }
}

// db.Database.SqlQuery<SalesByMonth>($"EXEC SalesByMonth {year}")

FAQ

Related

Building a web API with ASP.NET Core Testing with xUnit, mocking and integration tests

Last refreshed 2026-09-18.