dotnet-database-performance

Optimize .NET database access using proven patterns: CQRS (Command Query Responsibility Segregation) read/write model separation, N+1 query prevention, AsNoTracking for reads, mandatory row limits, and SQL-side joins. Works with EF Core and Dapper on .NET 8+. Requires NuGet packages Microsoft.EntityFrameworkCore and/or Dapper.

Acronyms: CQRS (Command Query Responsibility Segregation), EF (Entity Framework), DTO (Data Transfer Object), DI (Dependency Injection).

When to Use This Skill

• Designing data access layers with separate read and write models for performance isolation
• Optimizing slow database queries by eliminating N+1 patterns and unnecessary tracking
• Choosing between EF Core and Dapper for specific workload types in the same project
• Implementing cursor-based or offset pagination with mandatory row limits on every query
• Reviewing existing code for Cartesian explosion risks caused by multiple Include calls
• Replacing generic repository patterns with purpose-built, query-optimized read stores

Related Skills

| Skill | Scope | |-------|-------| | dotnet-modern-csharp-coding-standards | Record types, pattern matching, Result<T> error handling | | dotnet-type-design-performance | Span<T>, Memory<T>, zero-allocation patterns | | dotnet-project-structure | Solution layout, project organization, dependency management |

Core Principles

1. Separate Read and Write Models — Use different types for queries and commands. Why: read models are denormalized projections; write models enforce validation and invariants.
2. Think in Batches — Fetch related data in bulk queries, never one-by-one. Why: N+1 queries cause linear database round-trips that destroy performance under load.
3. Only Retrieve What You Need — Project specific columns and apply row limits on every read. Why: transferring unused data wastes bandwidth, memory, and database I/O.
4. Do Joins in SQL — Never join collections in application memory. Why: the database engine optimizes joins with indexes; application-side joins are O(n×m) brute force.
5. Disable Tracking for Reads — Use AsNoTracking on all read-only queries in EF Core. Why: change tracking allocates snapshot copies of every entity, doubling memory usage.

Values: 基礎と型の追求（最小形式で最大可能性を生む設計思想）, 温故知新（SQL の基本原則を EF Core/Dapper の最新 API で活かす）

Workflow: Optimize Database Access

Step 1: Separate Read and Write Stores

Apply CQRS by creating distinct interfaces for read and write operations. Use when designing a new data access layer or refactoring a single repository into optimized query paths.

// Read models: multiple specialized projections for different use cases
public interface IUserReadStore
{
    Task<UserProfile?> GetByIdAsync(UserId id, CancellationToken ct = default);
    Task<IReadOnlyList<UserSummary>> GetAllAsync(int limit, UserId? cursor = null, CancellationToken ct = default);
    Task<bool> EmailExistsAsync(EmailAddress email, CancellationToken ct = default);
}

// Write model: accepts typed commands, returns minimal data
public interface IUserWriteStore
{
    Task<UserId> CreateAsync(CreateUserCommand command, CancellationToken ct = default);
    Task UpdateAsync(UserId id, UpdateUserCommand command, CancellationToken ct = default);
    Task DeleteAsync(UserId id, CancellationToken ct = default);
}

Key structural differences:

• Read store returns multiple DTO types (UserProfile, UserSummary, bool) — stateless projections
• Write store returns minimal data (UserId on create, void on update) — command-focused
• Different databases or tables can back read vs write for eventual consistency

See references/cqrs-patterns.md for full folder structure and implementation examples.

Values: 基礎と型の追求（読み取りと書き込みの責務を分離し、各モデルを最適化する型設計）

Step 2: Apply Row Limits and Pagination

Apply mandatory limit parameters on every read method. Use when designing any query that returns a collection — unbounded result sets are a production incident waiting to happen.

public interface IOrderReadStore
{
    // Limit is required, not optional
    Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(
        CustomerId customerId, int limit,
        OrderId? cursor = null, CancellationToken ct = default);
}

Cursor-based pagination (Dapper):

const string sql = """
    SELECT id, customer_id, total, status, created_at
    FROM orders
    WHERE customer_id = @CustomerId
    AND (@Cursor IS NULL OR created_at < (SELECT created_at FROM orders WHERE id = @Cursor))
    ORDER BY created_at DESC
    LIMIT @Limit
    """;

Offset pagination (EF Core):

var orders = await query
    .AsNoTracking()
    .Skip((paginator.PageNumber - 1) * paginator.PageSize)
    .Take(paginator.PageSize)  // Always limit!
    .Select(o => new OrderSummary(new OrderId(o.Id), o.Total, o.Status, o.CreatedAt))
    .ToListAsync(ct);

Values: 継続は力（すべてのクエリに制限を適用する習慣が、本番障害を未然に防ぐ）

Step 3: Use AsNoTracking for Read Queries

Apply AsNoTracking() on all read-only EF Core queries. Use when retrieving data you will not modify — change tracking doubles memory usage by storing entity snapshots.

// ✅ Disable tracking for reads
var users = await _context.Users
    .AsNoTracking()
    .Where(u => u.IsActive)
    .ToListAsync();

// ❌ Track entities you won't modify — wasteful
var users = await _context.Users
    .Where(u => u.IsActive)
    .ToListAsync();  // Change tracking enabled

Configure default behavior for read-heavy applications:

// In DbContext configuration
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}

// Then explicitly enable tracking when needed for writes
var user = await _context.Users
    .AsTracking()  // Explicit — we intend to modify
    .FirstOrDefaultAsync(u => u.Id == userId);

Values: ニュートラルな視点（デフォルトを NoTracking にし、追跡が必要な箇所だけ明示する偏りのない設計）

Step 4: Prevent N+1 Queries

Apply batch loading or eager loading instead of per-item queries. Use when fetching a list and its related data — the N+1 pattern causes one query per item, scaling linearly with data size.

// ❌ N+1 queries — each iteration hits the database
var orders = await _context.Orders.ToListAsync();
foreach (var order in orders)
{
    var items = await _context.OrderItems
        .Where(i => i.OrderId == order.Id).ToListAsync();
}

// ✅ EF Core: single query with join
var orders = await _context.Orders
    .AsNoTracking()
    .Include(o => o.Items)
    .ToListAsync();

Dapper batch approach (two queries, no N+1):

const string sql = """
    SELECT id, customer_id, total FROM orders WHERE customer_id = @CustomerId;
    SELECT oi.* FROM order_items oi
    INNER JOIN orders o ON oi.order_id = o.id WHERE o.customer_id = @CustomerId;
    """;

using var multi = await connection.QueryMultipleAsync(sql, new { CustomerId = customerId });
var orders = (await multi.ReadAsync<OrderRow>()).ToList();
var items = (await multi.ReadAsync<OrderItemRow>()).ToList();

Values: 成長の複利（N+1 を見抜く力が、あらゆるデータアクセス層の品質を底上げする）

Step 5: Perform Joins in SQL and Avoid Cartesian Explosions

Apply SQL-side joins and use AsSplitQuery() or explicit projection for multiple Include calls. Use when combining related entities — application-side joins waste memory and CPU.

// ❌ Application-side join — two full table scans, O(n×m) in memory
var customers = await _context.Customers.ToListAsync();
var orders = await _context.Orders.ToListAsync();
var result = customers.Select(c => new {
    Customer = c,
    Orders = orders.Where(o => o.CustomerId == c.Id).ToList()
});

// ✅ SQL join — single query, database-optimized
var result = await _context.Customers
    .AsNoTracking()
    .Include(c => c.Orders)
    .ToListAsync();

Avoid Cartesian explosions with AsSplitQuery:

// ❌ Cartesian: 100 reviews × 20 images × 5 categories = 10,000 rows
var product = await _context.Products
    .Include(p => p.Reviews).Include(p => p.Images).Include(p => p.Categories)
    .FirstOrDefaultAsync(p => p.Id == id);

// ✅ Split: 4 separate queries, ~125 rows total
var product = await _context.Products
    .AsSplitQuery()
    .Include(p => p.Reviews).Include(p => p.Images).Include(p => p.Categories)
    .FirstOrDefaultAsync(p => p.Id == id);

// ✅ Best: explicit projection — only fetch what you need
var product = await _context.Products
    .AsNoTracking()
    .Where(p => p.Id == id)
    .Select(p => new ProductDetail(
        p.Id, p.Name,
        p.Reviews.OrderByDescending(r => r.CreatedAt).Take(10).ToList(),
        p.Images.Take(5).ToList(),
        p.Categories.Select(c => c.Name).ToList()))
    .FirstOrDefaultAsync();

Values: 余白の設計（プロジェクションで必要なデータだけ取得し、将来のスケーリングに余白を残す）

Good Practices

• ✅ Use separate read/write store interfaces — never reuse the same entity for both
• ✅ Apply AsNoTracking() on every read-only query in EF Core
• ✅ Use AsSplitQuery() when multiple Include calls risk Cartesian products
• ✅ Apply mandatory limit parameter on every collection-returning read method
• ✅ Use cursor-based pagination for large datasets over offset pagination
• ✅ Use Dapper for complex read queries where explicit SQL is cleaner and faster
• ✅ Use EF Core for writes with validation and domain-heavy operations
• ✅ Constrain column sizes with HasMaxLength() in EF Core model configuration
• ✅ Avoid generic IRepository<T> — build purpose-specific read stores instead
• ✅ Accept CancellationToken in all async data access methods

Common Pitfalls

1. Unbounded result sets — Returning all rows without a LIMIT clause causes out-of-memory in production. Fix: make limit a required method parameter.
2. Tracking read-only entities — EF Core change tracking doubles memory usage. Fix: apply AsNoTracking() on every read-only query.
3. N+1 query loops — Fetching related data inside a foreach loop creates one query per item. Fix: use Include() or batch queries.
4. Application-side joins — Loading two full tables and joining in LINQ is O(n×m). Fix: use SQL JOIN or EF Core Include().
5. Cartesian explosion from multiple Includes — Three Include calls can multiply rows exponentially. Fix: use AsSplitQuery() or explicit projection.
6. Generic repository hiding complexity — IRepository<T>.GetAll() makes it impossible to enforce limits or optimize queries. Fix: design purpose-built stores.

Anti-Patterns

❌ Generic Repository → ✅ Purpose-Built Stores

// ❌ BAD — can't optimize, no limits, hides N+1
public interface IRepository<T>
{
    Task<T?> GetByIdAsync(int id);
    Task<IEnumerable<T>> GetAllAsync();  // No limit!
    Task<IEnumerable<T>> FindAsync(Expression<Func<T, bool>> predicate);
}

// ✅ GOOD — query-specific, enforces limits, optimizable
public interface IOrderReadStore
{
    Task<OrderDetail?> GetByIdAsync(OrderId id, CancellationToken ct = default);
    Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(
        CustomerId id, int limit, CancellationToken ct = default);
}

Architecture-level problems with generic repositories:

• Cannot optimize specific query patterns or enforce row limits
• Hide N+1 problems behind abstraction layers
• Encourage fetching too much data through convenience methods

❌ SELECT * → ✅ Explicit Column Projection

// ❌ BAD — fetches all columns including large text fields
var users = await _context.Users.ToListAsync();

// ✅ GOOD — project only needed columns
var users = await _context.Users
    .AsNoTracking()
    .Select(u => new UserSummary(u.Id, u.Name, u.Email))
    .ToListAsync();

❌ Unconstrained Strings → ✅ MaxLength Configuration

// ✅ Configure column sizes to prevent oversized data
builder.Property(u => u.Email).HasMaxLength(254).IsRequired();  // RFC 5321 limit
builder.Property(u => u.Name).HasMaxLength(100).IsRequired();
builder.Property(u => u.Notes).HasColumnType("text");  // Explicit large content

Quick Reference

EF Core vs Dapper Decision Table

| Scenario | Recommendation | Why | |----------|---------------|-----| | Simple CRUD operations | EF Core | Change tracking and migrations simplify writes | | Complex read queries | Dapper | Explicit SQL gives full control over performance | | Writes with domain validation | EF Core | Entity configuration enforces constraints | | Bulk insert/update operations | Dapper or raw SQL | Avoids per-entity tracking overhead | | Reporting and analytics queries | Dapper | Complex aggregations are cleaner in raw SQL | | Mixed read/write project | Both | EF Core for writes, Dapper for reads |

Anti-Pattern Quick Fix Table

| Anti-Pattern | Fix | Instead | |--------------|-----|---------| | No row limit | Add limit parameter | Every read method requires a limit | | SELECT * | Project columns | Use .Select() for specific fields | | N+1 queries | Batch or Include | Use .Include() or multi-query | | Application joins | SQL JOIN | Use .Include() or INNER JOIN | | Cartesian explosion | AsSplitQuery | Use .AsSplitQuery() or projection | | Tracking reads | AsNoTracking | Use .AsNoTracking() on read queries | | Generic repository | Purpose-built stores | Design query-specific interfaces |

Resources

• EF Core Performance — Official performance guide
• Dapper — Micro-ORM for high-performance reads
• AsSplitQuery — Split vs single query guidance
• references/cqrs-patterns.md — Full CQRS folder structure and Dapper implementation