Efcore Patterns

aaronontheweb/dotnet-skills/skills/efcore-patterns

作者 aarononthewebe426ed93a9f3無授權條款1.2K 個星標收錄於 2026年10月8日更新於 2026年10月8日儲存庫3 週前更新

Entity Framework Core best practices including NoTracking by default, query splitting for navigation collections, migration management, dedicated migration services, and common pitfalls to avoid.

僅含說明Software Development
AI 產生的概覽

Entity Framework Core 模式指引：預設不追蹤查詢、移轉、查詢分割與大量作業。

功能
此技能提供在 .NET 專案中使用 Entity Framework Core 的參考指引與程式碼範例。內容涵蓋預設設定為不追蹤、透過 CLI 命令管理移轉、在專用服務中執行移轉、使用執行策略處理暫時性失敗、大量更新與刪除、查詢分割、DbContext 生命週期，以及常見陷阱。它產出的是說明性模式與 C# 程式碼片段，而非可執行的成品。
適用情境
適用於在新專案中設定 EF Core、最佳化查詢效能、管理資料庫移轉、將 EF Core 與 .NET Aspire 整合、偵錯變更追蹤問題，或有效率地載入多個導覽集合的情境。
執行需求
不包含指令碼，僅有指示與程式碼範例。套用這些範例需要具備 Entity Framework Core 的 .NET 專案、dotnet ef CLI，以及 PostgreSQL 等資料庫提供者。

Entity Framework Core Patterns

When to Use This Skill

Use this skill when:

  • Setting up EF Core in a new project
  • Optimizing query performance
  • Managing database migrations
  • Integrating EF Core with .NET Aspire
  • Debugging change tracking issues
  • Loading multiple navigation collections efficiently (query splitting)

Core Principles

  1. NoTracking by Default - Most queries are read-only; opt-in to tracking
  2. Never Edit Migrations Manually - Always use CLI commands
  3. Dedicated Migration Service - Separate migration execution from application startup
  4. ExecutionStrategy for Retries - Handle transient database failures
  5. Explicit Updates - When NoTracking, explicitly mark entities for update

Pattern 1: NoTracking by Default

Configure your DbContext to disable change tracking by default. This improves performance for read-heavy workloads.

csharp
public class ApplicationDbContext : DbContext{    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)        : base(options)    {        // Disable change tracking by default for better performance on read-only queries        // Use .AsTracking() explicitly for queries that need to track changes        ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;    }
    public DbSet<Order> Orders => Set<Order>();    public DbSet<Customer> Customers => Set<Customer>();}

When NoTracking is Active

Read-only queries work normally:

csharp
// ✅ Fast read - no tracking overheadvar orders = await dbContext.Orders    .Where(o => o.Status == OrderStatus.Pending)    .ToListAsync();

Writes require explicit handling:

csharp
// ❌ WRONG - Entity not tracked, SaveChanges does nothingvar order = await dbContext.Orders.FirstOrDefaultAsync(o => o.Id == orderId);order.Status = OrderStatus.Shipped;await dbContext.SaveChangesAsync(); // Nothing happens!
// ✅ CORRECT - Explicitly mark entity for updatevar order = await dbContext.Orders.FirstOrDefaultAsync(o => o.Id == orderId);order.Status = OrderStatus.Shipped;dbContext.Orders.Update(order); // Marks entire entity as modifiedawait dbContext.SaveChangesAsync();
// ✅ ALSO CORRECT - Use AsTracking() for the queryvar order = await dbContext.Orders    .AsTracking()    .FirstOrDefaultAsync(o => o.Id == orderId);order.Status = OrderStatus.Shipped;await dbContext.SaveChangesAsync(); // Works!

When to Use Tracking

ScenarioUse Tracking?Why
Display data in UINoRead-only, no updates
API GET endpointsNoReturning data, no mutations
Update single entityYes or explicit Update()Need to save changes
Complex update with navigationYesTracking handles relationships
Batch operationsNo + ExecuteUpdateMore efficient

Explicit Add/Update Pattern

csharp
public class OrderService{    private readonly ApplicationDbContext _db;
    // CREATE - Always use Add (works regardless of tracking)    public async Task<Order> CreateOrderAsync(Order order)    {        _db.Orders.Add(order);        await _db.SaveChangesAsync();        return order;    }
    // UPDATE - Explicitly mark as modified    public async Task UpdateOrderStatusAsync(Guid orderId, OrderStatus newStatus)    {        var order = await _db.Orders.FirstOrDefaultAsync(o => o.Id == orderId)            ?? throw new NotFoundException($"Order {orderId} not found");
        order.Status = newStatus;        order.UpdatedAt = DateTimeOffset.UtcNow;
        // Explicitly mark as modified since DbContext uses NoTracking by default        _db.Orders.Update(order);        await _db.SaveChangesAsync();    }
    // DELETE - Attach and remove    public async Task DeleteOrderAsync(Guid orderId)    {        var order = new Order { Id = orderId };        _db.Orders.Remove(order);        await _db.SaveChangesAsync();    }}

Pattern 2: Never Edit Migrations Manually

CRITICAL: Always use EF Core CLI commands to manage migrations. Never:

  • Manually edit migration files (except for custom SQL in Up()/Down())
  • Delete migration files directly
  • Rename migration files
  • Copy migrations between projects

Creating Migrations

bash
# Create a new migrationdotnet ef migrations add AddCustomerTable \    --project src/MyApp.Infrastructure \    --startup-project src/MyApp.Api
# With a specific DbContext (if you have multiple)dotnet ef migrations add AddCustomerTable \    --context ApplicationDbContext \    --project src/MyApp.Infrastructure \    --startup-project src/MyApp.Api

Removing Migrations

bash
# Remove the last migration (if not yet applied)dotnet ef migrations remove \    --project src/MyApp.Infrastructure \    --startup-project src/MyApp.Api
# NEVER do this:# rm Migrations/20240101_AddCustomerTable.cs  # ❌ BAD!

Applying Migrations

bash
# Apply all pending migrationsdotnet ef database update \    --project src/MyApp.Infrastructure \    --startup-project src/MyApp.Api
# Apply to a specific migrationdotnet ef database update AddCustomerTable \    --project src/MyApp.Infrastructure \    --startup-project src/MyApp.Api
# Rollback to a previous migrationdotnet ef database update PreviousMigrationName \    --project src/MyApp.Infrastructure \    --startup-project src/MyApp.Api

Generating SQL Scripts

bash
# Generate SQL script for all migrationsdotnet ef migrations script \    --project src/MyApp.Infrastructure \    --startup-project src/MyApp.Api \    --output migrations.sql
# Generate idempotent script (safe to run multiple times)dotnet ef migrations script \    --idempotent \    --project src/MyApp.Infrastructure \    --startup-project src/MyApp.Api

Pattern 3: Dedicated Migration Service with Aspire

Separate migration execution from your main application using a dedicated migration service. This ensures:

  • Migrations complete before the app starts
  • Clean separation of concerns
  • Controlled seeding in test environments

Project Structure

src/├── MyApp.AppHost/           # Aspire orchestration├── MyApp.Api/               # Main application├── MyApp.Infrastructure/    # DbContext and migrations└── MyApp.MigrationService/  # Dedicated migration runner

MigrationService Program.cs

csharp
using MyApp.Infrastructure.Data;using MyApp.MigrationService;using Microsoft.EntityFrameworkCore;
var builder = Host.CreateApplicationBuilder(args);
// Add Aspire service defaultsbuilder.AddServiceDefaults();
// Add PostgreSQL DbContextvar connectionString = builder.Configuration.GetConnectionString("appdb")    ?? throw new InvalidOperationException("Connection string 'appdb' not found.");
builder.Services.AddDbContext<ApplicationDbContext>(options =>    options.UseNpgsql(connectionString, npgsqlOptions =>        npgsqlOptions.MigrationsAssembly("MyApp.Infrastructure")));
// Add the migration workerbuilder.Services.AddHostedService<MigrationWorker>();
var host = builder.Build();host.Run();

MigrationWorker.cs

csharp
public class MigrationWorker : BackgroundService{    private readonly IServiceProvider _serviceProvider;    private readonly IHostApplicationLifetime _hostApplicationLifetime;    private readonly ILogger<MigrationWorker> _logger;
    public MigrationWorker(        IServiceProvider serviceProvider,        IHostApplicationLifetime hostApplicationLifetime,        ILogger<MigrationWorker> logger)    {        _serviceProvider = serviceProvider;        _hostApplicationLifetime = hostApplicationLifetime;        _logger = 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        {            // Stop the application after migrations complete            _hostApplicationLifetime.StopApplication();        }    }
    private async Task RunMigrationsAsync(ApplicationDbContext dbContext, CancellationToken ct)    {        // Use execution strategy for transient failure handling        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.");            }        });    }}

AppHost Configuration

csharp
var builder = DistributedApplication.CreateBuilder(args);
var postgres = builder.AddPostgres("postgres");var db = postgres.AddDatabase("appdb");
// Migrations run first, then exitvar migrations = builder.AddProject<Projects.MyApp_MigrationService>("migrations")    .WaitFor(db)    .WithReference(db);
// API waits for migrations to completevar api = builder.AddProject<Projects.MyApp_Api>("api")    .WaitForCompletion(migrations)  // Key: waits for migrations to finish    .WithReference(db);

Pattern 4: ExecutionStrategy for Transient Failures

Always use CreateExecutionStrategy() for operations that might fail transiently:

csharp
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();    });}

Important: You cannot use CreateExecutionStrategy() with user-initiated transactions. If you need transactions with retry:

csharp
var strategy = _dbContext.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>{    // Transaction must be INSIDE the strategy callback    await using var transaction = await _dbContext.Database.BeginTransactionAsync();
    try    {        // ... your operations ...        await _dbContext.SaveChangesAsync();        await transaction.CommitAsync();    }    catch    {        await transaction.RollbackAsync();        throw;    }});

Pattern 5: Bulk Operations with ExecuteUpdate/ExecuteDelete

For bulk operations, use EF Core 7+ ExecuteUpdateAsync and ExecuteDeleteAsync instead of loading entities:

csharp
// ❌ SLOW - Loads all entities into memoryvar expiredOrders = await _db.Orders    .Where(o => o.ExpiresAt < DateTimeOffset.UtcNow)    .ToListAsync();
foreach (var order in expiredOrders){    order.Status = OrderStatus.Expired;}await _db.SaveChangesAsync();
// ✅ FAST - Single SQL UPDATE statementawait _db.Orders    .Where(o => o.ExpiresAt < DateTimeOffset.UtcNow)    .ExecuteUpdateAsync(setters => setters        .SetProperty(o => o.Status, OrderStatus.Expired)        .SetProperty(o => o.UpdatedAt, DateTimeOffset.UtcNow));
// ✅ FAST - Single SQL DELETE statementawait _db.Orders    .Where(o => o.Status == OrderStatus.Cancelled && o.CreatedAt < cutoffDate)    .ExecuteDeleteAsync();

Common Pitfalls

1. Forgetting to Update When NoTracking

csharp
// ❌ Silent failure - entity not trackedvar customer = await _db.Customers.FindAsync(id);customer.Name = "New Name";await _db.SaveChangesAsync(); // Does nothing!
// ✅ Explicit updatevar customer = await _db.Customers.FindAsync(id);customer.Name = "New Name";_db.Customers.Update(customer);await _db.SaveChangesAsync();

2. N+1 Query Problem

csharp
// ❌ N+1 queries - one query per ordervar customers = await _db.Customers.ToListAsync();foreach (var customer in customers){    var orders = customer.Orders; // Lazy load triggers query}
// ✅ Eager loading - single queryvar customers = await _db.Customers    .Include(c => c.Orders)    .ToListAsync();

3. Tracking Conflicts with Multiple DbContext Instances

csharp
// ❌ Tracking conflict - entity tracked by different contextvar order1 = await _db1.Orders.AsTracking().FindAsync(id);var order2 = await _db2.Orders.AsTracking().FindAsync(id);order2.Status = OrderStatus.Shipped;await _db2.SaveChangesAsync(); // May throw or behave unexpectedly
// ✅ Use single context or detach first_db1.Entry(order1).State = EntityState.Detached;

4. Not Using Async Consistently

csharp
// ❌ Blocking call in async contextvar orders = _db.Orders.ToList(); // Blocks thread
// ✅ Async all the wayvar orders = await _db.Orders.ToListAsync();

5. Querying Inside Loops

csharp
// ❌ Query per iterationforeach (var orderId in orderIds){    var order = await _db.Orders.FindAsync(orderId);    // process order}
// ✅ Single queryvar orders = await _db.Orders    .Where(o => orderIds.Contains(o.Id))    .ToListAsync();

DbContext Lifetime in DI

ASP.NET Core (Scoped by Default)

csharp
// Scoped = one instance per HTTP requestbuilder.Services.AddDbContext<ApplicationDbContext>(options =>    options.UseNpgsql(connectionString));

Background Services (Create Scope)

csharp
public class MyBackgroundService : BackgroundService{    private readonly IServiceProvider _serviceProvider;
    protected override async Task ExecuteAsync(CancellationToken stoppingToken)    {        // ✅ Create scope for each unit of work        using var scope = _serviceProvider.CreateScope();        var dbContext = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
        // ... use dbContext ...    }}

Actors / Long-Lived Objects (Factory Pattern)

csharp
public class OrderActor : ReceiveActor{    private readonly IDbContextFactory<ApplicationDbContext> _dbFactory;
    public OrderActor(IDbContextFactory<ApplicationDbContext> dbFactory)    {        _dbFactory = dbFactory;
        ReceiveAsync<GetOrder>(async msg =>        {            // Create fresh context for each operation            await using var db = await _dbFactory.CreateDbContextAsync();            var order = await db.Orders.FindAsync(msg.OrderId);            Sender.Tell(order);        });    }}
// Registrationbuilder.Services.AddDbContextFactory<ApplicationDbContext>(options =>    options.UseNpgsql(connectionString));

Pattern 6: Query Splitting to Prevent Cartesian Explosion

When you load multiple navigation collections via Include(), EF Core generates a single query that can cause cartesian explosion. If you have 10 orders with 10 items each, you get 100 rows instead of 10 + 10.

Global Configuration (Recommended for Most Cases)

Enable query splitting globally in your DbContext configuration:

csharp
services.AddDbContext<ApplicationDbContext>(options =>    options.UseNpgsql(connectionString, npgsqlOptions =>        {            npgsqlOptions.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);        }));

Per-Query Override

Use single query when you know it's more efficient:

csharp
// Use single query when you know the structure is well-understoodvar orders = await dbContext.Orders    .Include(o => o.Items)    .Include(o => o.Payments)    .AsSingleQuery()  // Override global split behavior    .ToListAsync();

Trade-offs

BehaviorProsCons
SplitQueryNo cartesian explosion, better for large collectionsMultiple round-trips, potential consistency issues
SingleQuerySingle round-trip, transactional consistencyCartesian explosion with multiple collections

Recommendation: Default to SplitQuery globally, override with AsSingleQuery() for specific queries where single-query is known to be better.

When to Prefer SingleQuery

  • Small, well-understood navigation graphs (2-3 levels)
  • Queries where all related data is always needed
  • Performance-critical paths where round-trip cost is lower than cartesian explosion

When to Prefer SplitQuery

  • Large or unpredictable navigation graphs
  • Many-to-many relationships
  • Queries loading collections that may not all be needed

Testing with EF Core

In-Memory Provider (Unit Tests Only)

csharp
// Only for simple unit tests - doesn't match real database behaviorvar options = new DbContextOptionsBuilder<ApplicationDbContext>()    .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())    .Options;
using var context = new ApplicationDbContext(options);

Real Database with TestContainers (Integration Tests)

See the testcontainers-integration-tests skill for proper database testing.

csharp
// Use real PostgreSQL in containervar container = new PostgreSqlBuilder()    .WithImage("postgres:16-alpine")    .Build();
await container.StartAsync();
var options = new DbContextOptionsBuilder<ApplicationDbContext>()    .UseNpgsql(container.GetConnectionString())    .Options;

來源與署名

來源：aaronontheweb/dotnet-skills位於skills/efcore-patterns提交e426ed9

授權條款： 無授權條款

內容歸原作者所有。SourceWeft 從公開儲存庫中收錄這些內容。

檢舉或申請下架

更多來自 aaronontheweb/dotnet-skills 的技能

Snapshot Testing

aaronontheweb

說明如何在 .NET 中使用 Verify 進行快照測試，用來審核 API 介面、HTTP 回應、郵件與序列化輸出。

Software Development1.2K3 週前更新

Serialization

aaronontheweb

指導 .NET 開發者選擇序列化格式，並移轉到以結構描述為基礎、相容於 AOT 的序列化方式。

Software Development1.2K3 週前更新

Microsoft Extensions Configuration

aaronontheweb

介紹 Microsoft.Extensions.Options 設定模式：強型別設定繫結、啟動時驗證，以及選項生命週期。

Software Development1.2K3 週前更新

Package Management

aaronontheweb

指導使用 dotnet CLI 命令與集中套件管理（CPM）來管理 .NET 專案中的 NuGet 套件。

Software Development1.2K3 週前更新

Mjml Email Templates

aaronontheweb

建置可編譯為跨用戶端 HTML 的響應式 MJML 郵件範本，包含 .NET 渲染器與變數替換。

Communication1.2K3 週前更新

Csharp Concurrency Patterns

aaronontheweb

指導 .NET 開發者選擇並行抽象，從 async/await 到 Channel 與 Akka.NET actor。

Software Development1.2K3 週前更新

更多Software Development技能

Playground

anthropics

精選

建立自成一體的互動式 HTML 遊樂場，包含控制項、即時預覽與可複製的提示詞輸出。

Software Development37K今天更新

M5 Onboard

anthropics

精選

透過 USB 偵測 M5Stack ESP32 開發板，燒錄 UIFlow 2.0 韌體並安裝 MicroPython 應用程式套件。

Software Development37K今天更新

Cardputer Buddy

anthropics

精選

指導迭代 Cardputer-Adv 的 MicroPython 應用程式套件：新增應用程式、透過 USB 序列埠推送檔案、查看日誌並執行 REPL 指令。

Software Development37K今天更新

Web Design Guidelines

vercel-labs

精選

依據從遠端來源取得的 Web Interface Guidelines 審查 UI 程式碼，並以 file:line 形式回報問題。

Software Development32K5 週前更新

Vercel React Native Skills

vercel-labs

精選

React Native 與 Expo 最佳實務規則，涵蓋清單效能、動畫、導覽、UI 模式、狀態與 monorepo 設定。

Software Development32K5 週前更新

Vercel React Best Practices

vercel-labs

精選

Vercel 的 React 與 Next.js 效能指南，以 70 條規則的形式用於撰寫、審查與重構程式碼。

Software Development32K5 週前更新