database-performance

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Database Performance Patterns

数据库性能优化模式

When to Use This Skill

何时使用该技能

Use this skill when:

Designing data access layers
Optimizing slow database queries
Choosing between EF Core and Dapper
Avoiding common performance pitfalls

在以下场景使用该技能：

设计数据访问层
优化缓慢的数据库查询
在EF Core和Dapper之间做选择
规避常见的性能陷阱

Core Principles

核心原则

Separate read and write models - Don't use the same types for both
Think in batches - Avoid N+1 queries
Only retrieve what you need - No SELECT *
Apply row limits - Always have a configurable Take/Limit
Do joins in SQL - Never in application code
AsNoTracking for reads - EF Core change tracking is expensive

分离读写模型 - 不要为读写操作使用同一类型
批量思维 - 避免N+1查询
仅获取所需数据 - 不要使用SELECT *
应用行限制 - 始终配置Take/Limit参数
在SQL中进行关联 - 绝不在应用代码中关联
读取操作使用AsNoTracking - EF Core的变更追踪开销很高

Read/Write Model Separation (CQRS Pattern)

读写模型分离（CQRS模式）

Read and write models are fundamentally different - they have different shapes, columns, and purposes. Don't create a single "User" entity and reuse it everywhere.

Read models are denormalized, optimized for query efficiency, and return multiple projection types (UserProfile, UserSummary, UserDetailForAdmin)
Write models are normalized, validation-focused, and accept strongly-typed commands (CreateUserCommand, UpdateUserCommand)

**读写模型本质上完全不同——它们的结构、列和用途都不一样。**不要创建单一的「User」实体并在所有场景复用。

读模型是非规范化的，为查询效率优化，返回多种投影类型（UserProfile、UserSummary、UserDetailForAdmin）
写模型是规范化的，聚焦于验证，接收强类型命令（CreateUserCommand、UpdateUserCommand）

Architecture

架构

src/
  MyApp.Data/
    Users/
      # Read side - multiple optimized projections
      IUserReadStore.cs
      PostgresUserReadStore.cs

      # Write side - command handlers
      IUserWriteStore.cs
      PostgresUserWriteStore.cs

      # Read DTOs - lightweight, denormalized
      UserProfile.cs
      UserSummary.cs

      # Write commands - validation-focused
      CreateUserCommand.cs
      UpdateUserCommand.cs
    Orders/
      IOrderReadStore.cs
      IOrderWriteStore.cs
      (similar structure...)

src/
  MyApp.Data/
    Users/
      # 读端 - 多个优化后的投影
      IUserReadStore.cs
      PostgresUserReadStore.cs

      # 写端 - 命令处理器
      IUserWriteStore.cs
      PostgresUserWriteStore.cs

      # 读DTO - 轻量、非规范化
      UserProfile.cs
      UserSummary.cs

      # 写命令 - 聚焦验证
      CreateUserCommand.cs
      UpdateUserCommand.cs
    Orders/
      IOrderReadStore.cs
      IOrderWriteStore.cs
      (类似结构...)

Read Store Interface

读存储接口

csharp

// Read models: Multiple specialized projections optimized for different use cases
public interface IUserReadStore
{
    // Returns detailed profile for single-user view
    Task<UserProfile?> GetByIdAsync(UserId id, CancellationToken ct = default);

    // Returns lightweight info for lookups
    Task<UserProfile?> GetByEmailAsync(EmailAddress email, CancellationToken ct = default);

    // Returns paginated summaries - only what the list view needs
    Task<IReadOnlyList<UserSummary>> GetAllAsync(int limit, UserId? cursor = null, CancellationToken ct = default);

    // Boolean query - no entity needed
    Task<bool> EmailExistsAsync(EmailAddress email, CancellationToken ct = default);
}

csharp

// 读模型：为不同场景优化的多种专用投影
public interface IUserReadStore
{
    // 返回单用户视图的详细信息
    Task<UserProfile?> GetByIdAsync(UserId id, CancellationToken ct = default);

    // 返回用于查找的轻量信息
    Task<UserProfile?> GetByEmailAsync(EmailAddress email, CancellationToken ct = default);

    // 返回分页摘要 - 仅包含列表视图所需数据
    Task<IReadOnlyList<UserSummary>> GetAllAsync(int limit, UserId? cursor = null, CancellationToken ct = default);

    // 布尔查询 - 无需返回实体
    Task<bool> EmailExistsAsync(EmailAddress email, CancellationToken ct = default);
}

Write Store Interface

写存储接口

csharp

// Write model: Accepts strongly-typed commands, minimal return values
public interface IUserWriteStore
{
    // Returns only the created ID - caller doesn't need the full entity
    Task<UserId> CreateAsync(CreateUserCommand command, CancellationToken ct = default);

    // Update validates command, returns void (success or throws)
    Task UpdateAsync(UserId id, UpdateUserCommand command, CancellationToken ct = default);

    // Delete is simple and explicit
    Task DeleteAsync(UserId id, CancellationToken ct = default);
}

Key structural differences illustrated:

Read store returns multiple different DTOs (UserProfile, UserSummary, bool flag)
Write store returns minimal data (just UserId on create) or void
Read queries are stateless projections - no tracking needed
Write operations focus on command validation, not retrieving data afterwards
Different databases/tables can back read vs write (eventual consistency pattern)

csharp

// 写模型：接收强类型命令，返回最少数据
public interface IUserWriteStore
{
    // 仅返回创建后的ID - 调用方无需完整实体
    Task<UserId> CreateAsync(CreateUserCommand command, CancellationToken ct = default);

    // 更新操作验证命令，返回void（成功或抛出异常）
    Task UpdateAsync(UserId id, UpdateUserCommand command, CancellationToken ct = default);

    // 删除操作简洁明确
    Task DeleteAsync(UserId id, CancellationToken ct = default);
}

核心结构差异说明：

读存储返回多种不同的DTO（UserProfile、UserSummary、布尔标识）
写存储返回最少数据（仅创建时返回UserId）或void
读查询是无状态投影 - 无需追踪
写操作聚焦于命令验证，而非事后获取数据
读端和写端可使用不同的数据库/表（最终一致性模式）

Always Apply Row Limits

始终应用行限制

Never return unbounded result sets. Every read method should have a configurable limit.

**绝不要返回无边界的结果集。**每个读取方法都应配置限制参数。

Pattern: Limit Parameter

模式：限制参数

csharp

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

// Implementation
public async Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(
    CustomerId customerId,
    int limit,
    OrderId? cursor = null,
    CancellationToken ct = default)
{
    await using var connection = await _dataSource.OpenConnectionAsync(ct);

    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
        """;

    var rows = await connection.QueryAsync<OrderRow>(sql, new
    {
        CustomerId = customerId.Value,
        Cursor = cursor?.Value,
        Limit = limit
    });

    return rows.Select(r => r.ToOrderSummary()).ToList();
}

csharp

public interface IOrderReadStore
{
    // 限制参数为必填项，而非可选
    Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(
        CustomerId customerId,
        int limit,
        OrderId? cursor = null,
        CancellationToken ct = default);
}

// 实现
public async Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(
    CustomerId customerId,
    int limit,
    OrderId? cursor = null,
    CancellationToken ct = default)
{
    await using var connection = await _dataSource.OpenConnectionAsync(ct);

    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
        """;

    var rows = await connection.QueryAsync<OrderRow>(sql, new
    {
        CustomerId = customerId.Value,
        Cursor = cursor?.Value,
        Limit = limit
    });

    return rows.Select(r => r.ToOrderSummary()).ToList();
}

EF Core with Pagination

EF Core 分页实现

csharp

public async Task<PaginatedList<OrderSummary>> GetOrdersAsync(
    CustomerId customerId,
    Paginator paginator,
    CancellationToken ct = default)
{
    var query = _context.Orders
        .AsNoTracking()
        .Where(o => o.CustomerId == customerId.Value)
        .OrderByDescending(o => o.CreatedAt);

    var totalCount = await query.CountAsync(ct);

    var orders = await query
        .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);

    return new PaginatedList<OrderSummary>(
        orders,
        totalCount,
        paginator.PageSize,
        paginator.PageNumber);
}

csharp

public async Task<PaginatedList<OrderSummary>> GetOrdersAsync(
    CustomerId customerId,
    Paginator paginator,
    CancellationToken ct = default)
{
    var query = _context.Orders
        .AsNoTracking()
        .Where(o => o.CustomerId == customerId.Value)
        .OrderByDescending(o => o.CreatedAt);

    var totalCount = await query.CountAsync(ct);

    var orders = await query
        .Skip((paginator.PageNumber - 1) * paginator.PageSize)
        .Take(paginator.PageSize)  // 始终添加限制！
        .Select(o => new OrderSummary(
            new OrderId(o.Id),
            o.Total,
            o.Status,
            o.CreatedAt))
        .ToListAsync(ct);

    return new PaginatedList<OrderSummary>(
        orders,
        totalCount,
        paginator.PageSize,
        paginator.PageNumber);
}

AsNoTracking for Read Queries

读取查询使用AsNoTracking

EF Core's change tracking is expensive. Disable it for read-only queries.

csharp

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

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

EF Core的变更追踪开销很高，对只读查询禁用该功能。

csharp

// 推荐：对读取操作禁用追踪
var users = await _context.Users
    .AsNoTracking()
    .Where(u => u.IsActive)
    .ToListAsync();

// 不推荐：对无需修改的实体启用追踪
var users = await _context.Users
    .Where(u => u.IsActive)
    .ToListAsync();  // 启用了变更追踪 - 造成资源浪费

Configure Default Behavior

配置默认行为

csharp

// For read-heavy applications, consider this in DbContext
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}

Then explicitly enable tracking when needed:

csharp

var user = await _context.Users
    .AsTracking()  // Explicit - we intend to modify
    .FirstOrDefaultAsync(u => u.Id == userId);

csharp

// 对于读密集型应用，可在DbContext中配置
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}

需要时再显式启用追踪：

csharp

var user = await _context.Users
    .AsTracking()  // 显式启用 - 表明我们要修改该实体
    .FirstOrDefaultAsync(u => u.Id == userId);

Avoid N+1 Queries

避免N+1查询

The N+1 problem: fetching a list, then querying for each item's related data.

N+1问题：先获取列表，再为每个项查询关联数据。

The Problem

问题示例

csharp

// BAD: N+1 queries
var orders = await _context.Orders.ToListAsync();

foreach (var order in orders)
{
    // Each iteration hits the database!
    var items = await _context.OrderItems
        .Where(i => i.OrderId == order.Id)
        .ToListAsync();
}

csharp

// 错误：N+1查询
var orders = await _context.Orders.ToListAsync();

foreach (var order in orders)
{
    // 每次循环都会访问数据库！
    var items = await _context.OrderItems
        .Where(i => i.OrderId == order.Id)
        .ToListAsync();
}

Solution 1: Include (EF Core)

解决方案1：Include（EF Core）

csharp

// GOOD: Single query with join
var orders = await _context.Orders
    .AsNoTracking()
    .Include(o => o.Items)
    .ToListAsync();

csharp

// 正确：单查询关联
var orders = await _context.Orders
    .AsNoTracking()
    .Include(o => o.Items)
    .ToListAsync();

Solution 2: Batch Query (Dapper)

解决方案2：批量查询（Dapper）

csharp

// GOOD: 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();

// Join in memory (acceptable - data already fetched)
foreach (var order in orders)
{
    order.Items = items.Where(i => i.OrderId == order.Id).ToList();
}

csharp

// 正确：两次查询，无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();

// 内存中关联（可接受 - 数据已全部获取）
foreach (var order in orders)
{
    order.Items = items.Where(i => i.OrderId == order.Id).ToList();
}

Never Do Application-Side Joins

绝不进行应用层关联

Joins must happen in SQL, not in C#.

csharp

// BAD: Application join - two queries, memory waste
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()  // O(n*m) in memory!
});

// GOOD: SQL join - single query
var result = await _context.Customers
    .AsNoTracking()
    .Include(c => c.Orders)
    .ToListAsync();

// GOOD: Explicit join (Dapper)
const string sql = """
    SELECT c.id, c.name, COUNT(o.id) as order_count
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.name
    """;

关联操作必须在SQL中完成，而非C#代码中。

csharp

// 错误：应用层关联 - 两次查询，内存浪费
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()  // 内存中O(n*m)复杂度！
});

// 正确：SQL关联 - 单查询
var result = await _context.Customers
    .AsNoTracking()
    .Include(c => c.Orders)
    .ToListAsync();

// 正确：显式关联（Dapper）
const string sql = """
    SELECT c.id, c.name, COUNT(o.id) as order_count
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.name
    """;

Avoid Cartesian Explosions

避免笛卡尔积爆炸

Multiple

Include

calls can cause Cartesian products.

csharp

// DANGEROUS: Can explode into millions of rows
var product = await _context.Products
    .Include(p => p.Reviews)      // 100 reviews
    .Include(p => p.Images)       // 20 images
    .Include(p => p.Categories)   // 5 categories
    .FirstOrDefaultAsync(p => p.Id == id);
// Result: 100 * 20 * 5 = 10,000 rows transferred!

多次使用

Include

可能导致笛卡尔积。

csharp

// 危险：可能生成数百万行数据
var product = await _context.Products
    .Include(p => p.Reviews)      // 100条评论
    .Include(p => p.Images)       // 20张图片
    .Include(p => p.Categories)   // 5个分类
    .FirstOrDefaultAsync(p => p.Id == id);
// 结果：100 * 20 * 5 = 10,000行数据被传输！

Solution: Split Queries

解决方案：拆分查询

csharp

// GOOD: Multiple queries, no Cartesian explosion
var product = await _context.Products
    .AsSplitQuery()
    .Include(p => p.Reviews)
    .Include(p => p.Images)
    .Include(p => p.Categories)
    .FirstOrDefaultAsync(p => p.Id == id);
// Result: 4 separate queries, ~125 rows total

csharp

// 正确：多查询，无笛卡尔积爆炸
var product = await _context.Products
    .AsSplitQuery()
    .Include(p => p.Reviews)
    .Include(p => p.Images)
    .Include(p => p.Categories)
    .FirstOrDefaultAsync(p => p.Id == id);
// 结果：4次独立查询，共传输约125行数据

Solution: Explicit Projection

解决方案：显式投影

csharp

// BEST: 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.Description,
        p.Reviews.OrderByDescending(r => r.CreatedAt).Take(10).ToList(),
        p.Images.Take(5).ToList(),
        p.Categories.Select(c => c.Name).ToList()))
    .FirstOrDefaultAsync();

csharp

// 最佳：仅获取所需数据
var product = await _context.Products
    .AsNoTracking()
    .Where(p => p.Id == id)
    .Select(p => new ProductDetail(
        p.Id,
        p.Name,
        p.Description,
        p.Reviews.OrderByDescending(r => r.CreatedAt).Take(10).ToList(),
        p.Images.Take(5).ToList(),
        p.Categories.Select(c => c.Name).ToList()))
    .FirstOrDefaultAsync();

Constrain Column Sizes

限制列大小

Define maximum lengths in your EF Core model to prevent oversized data.

csharp

public class UserConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.Property(u => u.Email)
            .HasMaxLength(254)  // RFC 5321 limit
            .IsRequired();

        builder.Property(u => u.Name)
            .HasMaxLength(100)
            .IsRequired();

        builder.Property(u => u.Bio)
            .HasMaxLength(500);

        // For truly large content, use text type explicitly
        builder.Property(u => u.Notes)
            .HasColumnType("text");
    }
}

在EF Core模型中定义最大长度，防止数据过大。

csharp

public class UserConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.Property(u => u.Email)
            .HasMaxLength(254)  // RFC 5321标准限制
            .IsRequired();

        builder.Property(u => u.Name)
            .HasMaxLength(100)
            .IsRequired();

        builder.Property(u => u.Bio)
            .HasMaxLength(500);

        // 对于超大内容，显式使用text类型
        builder.Property(u => u.Notes)
            .HasColumnType("text");
    }
}

Don't Build Generic Repositories

不要构建通用仓储

Generic repositories hide query complexity and make optimization difficult.

csharp

// BAD: Generic repository
public interface IRepository<T>
{
    Task<T?> GetByIdAsync(int id);
    Task<IEnumerable<T>> GetAllAsync();  // No limit!
    Task<IEnumerable<T>> FindAsync(Expression<Func<T, bool>> predicate);  // Can't optimize
}

// GOOD: Purpose-built read stores
public interface IOrderReadStore
{
    Task<OrderDetail?> GetByIdAsync(OrderId id, CancellationToken ct = default);
    Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(CustomerId id, int limit, CancellationToken ct = default);
    Task<IReadOnlyList<OrderSummary>> GetPendingAsync(int limit, CancellationToken ct = default);
}

Problems with generic repositories:

Can't optimize specific queries
No way to enforce limits
Hide N+1 problems
Make it easy to fetch too much data
Encourage lazy thinking about data access

通用仓储会隐藏查询复杂度，难以优化。

csharp

// 错误：通用仓储
public interface IRepository<T>
{
    Task<T?> GetByIdAsync(int id);
    Task<IEnumerable<T>> GetAllAsync();  // 无限制！
    Task<IEnumerable<T>> FindAsync(Expression<Func<T, bool>> predicate);  // 无法优化
}

// 正确：专用读存储
public interface IOrderReadStore
{
    Task<OrderDetail?> GetByIdAsync(OrderId id, CancellationToken ct = default);
    Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(CustomerId id, int limit, CancellationToken ct = default);
    Task<IReadOnlyList<OrderSummary>> GetPendingAsync(int limit, CancellationToken ct = default);
}

通用仓储的问题：

无法优化特定查询
无法强制限制结果集
隐藏N+1问题
容易获取过多数据
弱化对数据访问的思考

Dapper for Read-Heavy Workloads

读密集型工作负载使用Dapper

For complex read queries, Dapper with explicit SQL is often cleaner and faster.

csharp

public sealed class PostgresUserReadStore : IUserReadStore
{
    private readonly NpgsqlDataSource _dataSource;

    public PostgresUserReadStore(NpgsqlDataSource dataSource)
    {
        _dataSource = dataSource;
    }

    public async Task<UserProfile?> GetByIdAsync(UserId id, CancellationToken ct = default)
    {
        await using var connection = await _dataSource.OpenConnectionAsync(ct);

        const string sql = """
            SELECT id, email, name, bio, created_at
            FROM users
            WHERE id = @Id
            """;

        var row = await connection.QuerySingleOrDefaultAsync<UserRow>(
            sql, new { Id = id.Value });

        return row?.ToUserProfile();
    }

    // Internal row type for Dapper mapping
    private sealed class UserRow
    {
        public Guid id { get; set; }
        public string email { get; set; } = null!;
        public string name { get; set; } = null!;
        public string? bio { get; set; }
        public DateTime created_at { get; set; }

        public UserProfile ToUserProfile() => new(
            Id: new UserId(id),
            Email: new EmailAddress(email),
            Name: new PersonName(name),
            Bio: bio,
            CreatedAt: new DateTimeOffset(created_at, TimeSpan.Zero));
    }
}

对于复杂的读查询，使用Dapper配合显式SQL通常更简洁高效。

csharp

public sealed class PostgresUserReadStore : IUserReadStore
{
    private readonly NpgsqlDataSource _dataSource;

    public PostgresUserReadStore(NpgsqlDataSource dataSource)
    {
        _dataSource = dataSource;
    }

    public async Task<UserProfile?> GetByIdAsync(UserId id, CancellationToken ct = default)
    {
        await using var connection = await _dataSource.OpenConnectionAsync(ct);

        const string sql = """
            SELECT id, email, name, bio, created_at
            FROM users
            WHERE id = @Id
            """;

        var row = await connection.QuerySingleOrDefaultAsync<UserRow>(
            sql, new { Id = id.Value });

        return row?.ToUserProfile();
    }

    // Dapper映射用的内部行类型
    private sealed class UserRow
    {
        public Guid id { get; set; }
        public string email { get; set; } = null!;
        public string name { get; set; } = null!;
        public string? bio { get; set; }
        public DateTime created_at { get; set; }

        public UserProfile ToUserProfile() => new(
            Id: new UserId(id),
            Email: new EmailAddress(email),
            Name: new PersonName(name),
            Bio: bio,
            CreatedAt: new DateTimeOffset(created_at, TimeSpan.Zero));
    }
}

When to Use EF Core vs Dapper

EF Core与Dapper的适用场景

Scenario	Recommendation
Simple CRUD	EF Core
Complex read queries	Dapper
Writes with validation	EF Core
Bulk operations	Dapper or raw SQL
Reporting/analytics	Dapper
Domain-heavy writes	EF Core

You can use both in the same project - EF Core for writes, Dapper for reads.

场景	推荐方案
简单CRUD	EF Core
复杂读查询	Dapper
带验证的写操作	EF Core
批量操作	Dapper或原生SQL
报表/分析	Dapper
领域密集型写操作	EF Core

可以在同一项目中混用两者——用EF Core处理写操作，用Dapper处理读操作。

Quick Reference

速查参考

Anti-Pattern	Solution
No row limit	Add `limit` parameter to every read method
SELECT *	Project only needed columns
N+1 queries	Use Include or batch queries
Application joins	Do joins in SQL
Cartesian explosion	Use AsSplitQuery or projection
Tracking read-only data	Use AsNoTracking
Generic repository	Purpose-built read/write stores
Unbounded strings	Configure MaxLength in model

反模式	解决方案
无行限制	为每个读取方法添加limit参数
SELECT *	仅投影所需列
N+1查询	使用Include或批量查询
应用层关联	在SQL中进行关联
笛卡尔积爆炸	使用AsSplitQuery或显式投影
对只读数据启用追踪	使用AsNoTracking
通用仓储	使用专用读写存储
无边界字符串	在模型中配置MaxLength

Resources

参考资源

EF Core Performance: https://learn.microsoft.com/en-us/ef/core/performance/
Dapper: https://github.com/DapperLib/Dapper
AsSplitQuery: https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries

EF Core性能优化：https://learn.microsoft.com/en-us/ef/core/performance/
Dapper：https://github.com/DapperLib/Dapper
AsSplitQuery：https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries