From dotnet-skills
Entity Framework Core best practices including NoTracking by default, query splitting for navigation collections, migration management, dedicated migration services, interceptors, compiled queries, and connection resiliency. Use when setting up EF Core in a new project, optimizing query performance, managing database migrations, integrating EF Core with .NET Aspire, or debugging change tracking issues.
npx claudepluginhub wshaddix/dotnet-skillsThis skill uses the workspace's default tool permissions.
Searches, retrieves, and installs Agent Skills from prompts.chat registry using MCP tools like search_skills and get_skill. Activates for finding skills, browsing catalogs, or extending Claude.
Searches prompts.chat for AI prompt templates by keyword or category, retrieves by ID with variable handling, and improves prompts via AI. Use for discovering or enhancing prompts.
Compares coding agents like Claude Code and Aider on custom YAML-defined codebase tasks using git worktrees, measuring pass rate, cost, time, and consistency.
Use this skill when:
DbContext is a unit of work and should be short-lived. In ASP.NET Core, register it as scoped (one per request):
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));
| Scenario | Lifetime | Registration |
|---|---|---|
| Web API / MVC request | Scoped (default) | AddDbContext<T>() |
| Background service | Scoped via factory | AddDbContextFactory<T>() |
| Blazor Server | Scoped via factory | AddDbContextFactory<T>() |
| Console app | Transient or manual | new AppDbContext(options) |
Background services and Blazor Server circuits outlive a single scope. Use IDbContextFactory<T>:
public sealed class OrderProcessor(IDbContextFactory<AppDbContext> contextFactory)
{
public async Task ProcessBatchAsync(CancellationToken ct)
{
await using var db = await contextFactory.CreateDbContextAsync(ct);
var pending = await db.Orders
.Where(o => o.Status == OrderStatus.Pending)
.ToListAsync(ct);
foreach (var order in pending)
{
order.Status = OrderStatus.Processing;
}
await db.SaveChangesAsync(ct);
}
}
builder.Services.AddDbContextFactory<AppDbContext>(options =>
options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));
AddDbContextPool<T>() reuses DbContext instances to reduce allocation overhead:
builder.Services.AddDbContextPool<AppDbContext>(options =>
options.UseNpgsql(connectionString), poolSize: 128);
Pooling constraints: Do not store per-request state on the DbContext subclass. Do not inject scoped services into the constructor.
Configure your DbContext to disable change tracking by default:
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: base(options)
{
ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
}
public DbSet<Order> Orders => Set<Order>();
public DbSet<Customer> Customers => Set<Customer>();
}
Read-only queries work normally:
var orders = await dbContext.Orders
.Where(o => o.Status == OrderStatus.Pending)
.ToListAsync();
Writes require explicit handling:
// WRONG - Entity not tracked, SaveChanges does nothing
var order = await dbContext.Orders.FirstOrDefaultAsync(o => o.Id == orderId);
order.Status = OrderStatus.Shipped;
await dbContext.SaveChangesAsync(); // Nothing happens!
// CORRECT - Explicitly mark entity for update
var order = await dbContext.Orders.FirstOrDefaultAsync(o => o.Id == orderId);
order.Status = OrderStatus.Shipped;
dbContext.Orders.Update(order);
await dbContext.SaveChangesAsync();
// ALSO CORRECT - Use AsTracking() for the query
var order = await dbContext.Orders
.AsTracking()
.FirstOrDefaultAsync(o => o.Id == orderId);
order.Status = OrderStatus.Shipped;
await dbContext.SaveChangesAsync();
| Scenario | Use Tracking? | Why |
|---|---|---|
| Display data in UI | No | Read-only, no updates |
| API GET endpoints | No | Returning data, no mutations |
| Update single entity | Yes or explicit Update() | Need to save changes |
| Complex update with navigation | Yes | Tracking handles relationships |
| Batch operations | No + ExecuteUpdate | More efficient |
var orders = await db.Orders
.AsNoTracking()
.Where(o => o.CustomerId == customerId)
.ToListAsync(ct);
var ordersWithItems = await db.Orders
.AsNoTrackingWithIdentityResolution()
.Include(o => o.Items)
.Where(o => o.Status == OrderStatus.Active)
.ToListAsync(ct);
When loading multiple navigation collections via Include(), EF Core generates a single query that can cause cartesian explosion.
// Single query: produces Cartesian product of OrderItems x Payments
var orders = await db.Orders
.Include(o => o.Items) // N items
.Include(o => o.Payments) // M payments
.ToListAsync(ct);
// Result set: N x M rows per order
var orders = await db.Orders
.Include(o => o.Items)
.Include(o => o.Payments)
.AsSplitQuery()
.ToListAsync(ct);
// Executes 3 separate queries: Orders, Items, Payments
options.UseNpgsql(connectionString, npgsql =>
npgsql.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
| Approach | Pros | Cons |
|---|---|---|
| Single query (default) | Atomic snapshot, one round-trip | Cartesian explosion with multiple Includes |
| Split query | No Cartesian explosion, less data transfer | Multiple round-trips, no atomicity guarantee |
Rule of thumb: Use AsSplitQuery() when including two or more collection navigations.
CRITICAL: Always use EF Core CLI commands to manage migrations. Never manually edit migration files (except for custom SQL in Up()/Down()).
dotnet ef migrations add AddCustomerTable \
--project src/MyApp.Infrastructure \
--startup-project src/MyApp.Api
dotnet ef migrations add AddCustomerTable \
--context ApplicationDbContext \
--project src/MyApp.Infrastructure \
--startup-project src/MyApp.Api
dotnet ef migrations remove \
--project src/MyApp.Infrastructure \
--startup-project src/MyApp.Api
dotnet ef database update \
--project src/MyApp.Infrastructure \
--startup-project src/MyApp.Api
dotnet ef database update AddCustomerTable \
--project src/MyApp.Infrastructure \
--startup-project src/MyApp.Api
dotnet ef database update PreviousMigrationName \
--project src/MyApp.Infrastructure \
--startup-project src/MyApp.Api
dotnet ef migrations script \
--project src/MyApp.Infrastructure \
--startup-project src/MyApp.Api \
--output migrations.sql
dotnet ef migrations script \
--idempotent \
--project src/MyApp.Infrastructure \
--startup-project src/MyApp.Api
dotnet ef migrations bundle \
--project src/MyApp.Infrastructure \
--startup-project src/MyApp.Api \
--output efbundle \
--self-contained
./efbundle --connection "Host=prod-db;Database=myapp;Username=deploy;Password=..."
--idempotent flag)Database.Migrate() at startup in production -- use migration bundles or scriptsprotected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<OrderStatus>().HasData(
new OrderStatus { Id = 1, Name = "Pending" },
new OrderStatus { Id = 2, Name = "Processing" },
new OrderStatus { Id = 3, Name = "Completed" },
new OrderStatus { Id = 4, Name = "Cancelled" });
}
Separate migration execution from your main application.
src/
├── MyApp.AppHost/ # Aspire orchestration
├── MyApp.Api/ # Main application
├── MyApp.Infrastructure/ # DbContext and migrations
└── MyApp.MigrationService/ # Dedicated migration runner
public class MigrationWorker : BackgroundService
{
private readonly IServiceProvider _serviceProvider;
private readonly IHostApplicationLifetime _hostApplicationLifetime;
private readonly ILogger<MigrationWorker> _logger;
protected override async Task ExecuteAsync(CancellationToken stoppingToken)
{
_logger.LogInformation("Migration service starting...");
try
{
using var scope = _serviceProvider.CreateScope();
var dbContext = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
await RunMigrationsAsync(dbContext, stoppingToken);
_logger.LogInformation("Migration service completed successfully.");
}
catch (Exception ex)
{
_logger.LogError(ex, "Migration service failed: {Error}", ex.Message);
throw;
}
finally
{
_hostApplicationLifetime.StopApplication();
}
}
private async Task RunMigrationsAsync(ApplicationDbContext dbContext, CancellationToken ct)
{
var strategy = dbContext.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
var pendingMigrations = await dbContext.Database.GetPendingMigrationsAsync(ct);
if (pendingMigrations.Any())
{
_logger.LogInformation("Applying {Count} pending migrations...",
pendingMigrations.Count());
await dbContext.Database.MigrateAsync(ct);
_logger.LogInformation("Migrations applied successfully.");
}
else
{
_logger.LogInformation("No pending migrations. Database is up to date.");
}
});
}
}
var builder = DistributedApplication.CreateBuilder(args);
var postgres = builder.AddPostgres("postgres");
var db = postgres.AddDatabase("appdb");
var migrations = builder.AddProject<Projects.MyApp_MigrationService>("migrations")
.WaitFor(db)
.WithReference(db);
var api = builder.AddProject<Projects.MyApp_Api>("api")
.WaitForCompletion(migrations)
.WithReference(db);
Always use CreateExecutionStrategy() for operations that might fail transiently:
public async Task UpdateWithRetryAsync(Guid id, Action<Order> update)
{
var strategy = _dbContext.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
var order = await _dbContext.Orders
.AsTracking()
.FirstOrDefaultAsync(o => o.Id == id);
if (order is null) return;
update(order);
await _dbContext.SaveChangesAsync();
});
}
var strategy = _dbContext.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
await using var transaction = await _dbContext.Database.BeginTransactionAsync();
try
{
await _dbContext.SaveChangesAsync();
await transaction.CommitAsync();
}
catch
{
await transaction.RollbackAsync();
throw;
}
});
// PostgreSQL
options.UseNpgsql(connectionString, npgsql =>
npgsql.EnableRetryOnFailure(
maxRetryCount: 3,
maxRetryDelay: TimeSpan.FromSeconds(30),
errorCodesToAdd: null));
// SQL Server
options.UseSqlServer(connectionString, sqlServer =>
sqlServer.EnableRetryOnFailure(
maxRetryCount: 3,
maxRetryDelay: TimeSpan.FromSeconds(30),
errorNumbersToAdd: null));
EF Core interceptors allow cross-cutting concerns without modifying entity logic.
public sealed class AuditTimestampInterceptor : SaveChangesInterceptor
{
public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
DbContextEventData eventData,
InterceptionResult<int> result,
CancellationToken ct = default)
{
if (eventData.Context is null)
return ValueTask.FromResult(result);
var now = DateTimeOffset.UtcNow;
foreach (var entry in eventData.Context.ChangeTracker.Entries<IAuditable>())
{
switch (entry.State)
{
case EntityState.Added:
entry.Entity.CreatedAt = now;
entry.Entity.UpdatedAt = now;
break;
case EntityState.Modified:
entry.Entity.UpdatedAt = now;
break;
}
}
return ValueTask.FromResult(result);
}
}
public interface IAuditable
{
DateTimeOffset CreatedAt { get; set; }
DateTimeOffset UpdatedAt { get; set; }
}
public sealed class SoftDeleteInterceptor : SaveChangesInterceptor
{
public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
DbContextEventData eventData,
InterceptionResult<int> result,
CancellationToken ct = default)
{
if (eventData.Context is null)
return ValueTask.FromResult(result);
foreach (var entry in eventData.Context.ChangeTracker.Entries<ISoftDeletable>())
{
if (entry.State == EntityState.Deleted)
{
entry.State = EntityState.Modified;
entry.Entity.IsDeleted = true;
entry.Entity.DeletedAt = DateTimeOffset.UtcNow;
}
}
return ValueTask.FromResult(result);
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Product>()
.HasQueryFilter(p => !p.IsDeleted);
}
builder.Services.AddDbContext<AppDbContext>((sp, options) =>
options.UseNpgsql(connectionString)
.AddInterceptors(
sp.GetRequiredService<AuditTimestampInterceptor>(),
sp.GetRequiredService<SoftDeleteInterceptor>()));
builder.Services.AddSingleton<AuditTimestampInterceptor>();
builder.Services.AddSingleton<SoftDeleteInterceptor>();
For queries executed very frequently:
public static class CompiledQueries
{
public static readonly Func<AppDbContext, int, Task<Order?>>
GetOrderById = EF.CompileAsyncQuery(
(AppDbContext db, int orderId) =>
db.Orders
.AsNoTracking()
.Include(o => o.Items)
.FirstOrDefault(o => o.Id == orderId));
public static readonly Func<AppDbContext, string, IAsyncEnumerable<Order>>
GetOrdersByCustomer = EF.CompileAsyncQuery(
(AppDbContext db, string customerId) =>
db.Orders
.AsNoTracking()
.Where(o => o.CustomerId == customerId)
.OrderByDescending(o => o.CreatedAt));
}
var order = await CompiledQueries.GetOrderById(db, orderId);
await foreach (var o in CompiledQueries.GetOrdersByCustomer(db, customerId)
.WithCancellation(ct))
{
}
When to use: Queries that execute thousands of times per second. For typical CRUD, standard LINQ is sufficient.
Use EF Core 7+ ExecuteUpdateAsync and ExecuteDeleteAsync:
// WRONG - Loads all entities into memory
var expiredOrders = await _db.Orders
.Where(o => o.ExpiresAt < DateTimeOffset.UtcNow)
.ToListAsync();
foreach (var order in expiredOrders)
{
order.Status = OrderStatus.Expired;
}
await _db.SaveChangesAsync();
// CORRECT - Single SQL UPDATE statement
await _db.Orders
.Where(o => o.ExpiresAt < DateTimeOffset.UtcNow)
.ExecuteUpdateAsync(setters => setters
.SetProperty(o => o.Status, OrderStatus.Expired)
.SetProperty(o => o.UpdatedAt, DateTimeOffset.UtcNow));
await _db.Orders
.Where(o => o.Status == OrderStatus.Cancelled && o.CreatedAt < cutoffDate)
.ExecuteDeleteAsync();
// Silent failure - entity not tracked
var customer = await _db.Customers.FindAsync(id);
customer.Name = "New Name";
await _db.SaveChangesAsync(); // Does nothing!
// Explicit update
var customer = await _db.Customers.FindAsync(id);
customer.Name = "New Name";
_db.Customers.Update(customer);
await _db.SaveChangesAsync();
// N+1 queries - one query per order
var customers = await _db.Customers.ToListAsync();
foreach (var customer in customers)
{
var orders = customer.Orders; // Lazy load triggers query
}
// Eager loading - single query
var customers = await _db.Customers
.Include(c => c.Orders)
.ToListAsync();
// Tracking conflict
var order1 = await _db1.Orders.AsTracking().FindAsync(id);
var order2 = await _db2.Orders.AsTracking().FindAsync(id);
order2.Status = OrderStatus.Shipped;
await _db2.SaveChangesAsync();
// Use single context or detach
_db1.Entry(order1).State = EntityState.Detached;
// Query per iteration
foreach (var orderId in orderIds)
{
var order = await _db.Orders.FindAsync(orderId);
}
// Single query
var orders = await _db.Orders
.Where(o => orderIds.Contains(o.Id))
.ToListAsync();
var options = new DbContextOptionsBuilder<ApplicationDbContext>()
.UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
.Options;
using var context = new ApplicationDbContext(options);
var container = new PostgreSqlBuilder()
.WithImage("postgres:16-alpine")
.Build();
await container.StartAsync();
var options = new DbContextOptionsBuilder<ApplicationDbContext>()
.UseNpgsql(container.GetConnectionString())
.Options;
DbContext into singleton services -- use IDbContextFactory<T> instead.CancellationToken propagation -- pass ct to all async EF Core methods.Database.EnsureCreated() alongside migrations -- use only in test scenarios.SaveChangesAsync is transactional across multiple calls -- wrap in explicit transaction.validateAllProperties: true to Validator.TryValidateObject.