# .NET中的数据存取:比较ORMs和绘图战略(第二部分-Dapper、Raw SQL和混合方法)

<!-- category -- .NET, Dapper, PostgreSQL, Performance, Database -->
<datetime class="hidden">2025-12-03T15:00</datetime>

欢迎使用 .NET! in [第一部分 第一部分](/blog/orm-mapping-comparison-part1)我们深入探索了实体框架核心, 包括SQL一代、共同的陷阱,

在本条中,我们将探索较轻重量的替代办法, 以及如何结合多种方法实现最佳性能:

- **[顶顶顶端](https://github.com/DapperLib/Dapper)**微-ORM甜点
- **Raw ADO.NET/[Npgsql Npgsql](https://www.npgsql.org/)**:最大性能和控制
- **目标绘图图书馆**: [地图地图师](https://github.com/MapsterMapper/Mapster) Vs 和 [自动 Mapper 自动管理器](https://automapper.org/)
- **混合办法**:将EF核心和Dapper组合起来(CQRS模式)
- **业绩基准**:现实世界比较
- **决定矩阵**:选择适合您情景的正确工具

## 目录目录目录

## 微ORM(微-ORM)

[顶顶顶端](https://github.com/DapperLib/Dapper) 是一个由Stack 溢出创建的轻量、高性能微ORM。它为 ADO.NET 提供一层薄层,用于处理对对象进行查询结果绘图的烦琐工作,同时给予您完整的 SQL 控制。

### 为什么Dappper 存在

Dapper是来自Stack Overflow对高性能数据访问的需要。团队发现,像实体框架(Cre前)这样的完整的ORMs为高流量的情景增加了过多的管理费。 Dapper给了你95%的方便,在原始 ADO.NET 上只有 5-15%的管理费。

### 密钥特征

- **Raw SQL 原 SQL**:您自己写所有 SQL - 完全控制
- **高绩效 高表现**ADO.NET:最低间接费用(5-15%)
- **多控制**:对多个相关类型的复杂查询地图
- **参数处理**:自动参数化防止 SQL 注入
- **交易支持**:对交易的全面控制
- **简单化**:没有配置,没有变化跟踪,没有魔术
- **Async/ 等待**:为现代.NET提供全面的同步支持

### 基本顶点示例

```csharp
using Npgsql;
using Dapper;

public class DapperBlogRepository
{
    private readonly string _connectionString;

    public DapperBlogRepository(string connectionString)
    {
        _connectionString = connectionString;

        // Configure Dapper to work with PostgreSQL naming conventions
        DefaultTypeMap.MatchNamesWithUnderscores = true;
    }

    // Simple query
    public async Task<IEnumerable<BlogPost>> GetRecentPostsAsync(int count)
    {
        using var connection = new NpgsqlConnection(_connectionString);

        const string sql = @"
            SELECT id, title, content, tags, published_date, category_id
            FROM blog_posts
            ORDER BY published_date DESC
            LIMIT @Count";

        return await connection.QueryAsync<BlogPost>(sql, new { Count = count });
    }

    // Query with WHERE clause
    public async Task<BlogPost> GetPostByIdAsync(int id)
    {
        using var connection = new NpgsqlConnection(_connectionString);

        const string sql = @"
            SELECT id, title, content, published_date
            FROM blog_posts
            WHERE id = @Id";

        return await connection.QueryFirstOrDefaultAsync<BlogPost>(sql, new { Id = id });
    }

    // Insert with returning ID
    public async Task<int> CreatePostAsync(BlogPost post)
    {
        using var connection = new NpgsqlConnection(_connectionString);

        const string sql = @"
            INSERT INTO blog_posts (title, content, published_date, category_id)
            VALUES (@Title, @Content, @PublishedDate, @CategoryId)
            RETURNING id";

        return await connection.ExecuteScalarAsync<int>(sql, post);
    }

    // Update
    public async Task UpdatePostAsync(BlogPost post)
    {
        using var connection = new NpgsqlConnection(_connectionString);

        const string sql = @"
            UPDATE blog_posts
            SET title = @Title,
                content = @Content,
                published_date = @PublishedDate
            WHERE id = @Id";

        await connection.ExecuteAsync(sql, post);
    }

    // Delete
    public async Task DeletePostAsync(int id)
    {
        using var connection = new NpgsqlConnection(_connectionString);

        const string sql = "DELETE FROM blog_posts WHERE id = @Id";

        await connection.ExecuteAsync(sql, new { Id = id });
    }
}
```

### 多控制: 处理合并

Dapper最强大的特征之一是多图绘制-高效处理连接和测绘多个相关物体:

```csharp
public async Task<IEnumerable<BlogPost>> GetPostsWithCategoryAsync()
{
    using var connection = new NpgsqlConnection(_connectionString);

    const string sql = @"
        SELECT
            p.id, p.title, p.content, p.published_date,
            c.id, c.name, c.description
        FROM blog_posts p
        INNER JOIN categories c ON p.category_id = c.id
        ORDER BY p.published_date DESC";

    return await connection.QueryAsync<BlogPost, Category, BlogPost>(
        sql,
        (post, category) =>
        {
            post.Category = category;
            return post;
        },
        splitOn: "id"  // Split at the second "id" column
    );
}

// More complex: Posts with comments
public async Task<IEnumerable<BlogPost>> GetPostsWithCommentsAsync()
{
    using var connection = new NpgsqlConnection(_connectionString);

    const string sql = @"
        SELECT
            p.id, p.title, p.content,
            c.id, c.author, c.content, c.created_at
        FROM blog_posts p
        LEFT JOIN comments c ON p.id = c.blog_post_id
        ORDER BY p.published_date DESC, c.created_at";

    var postDict = new Dictionary<int, BlogPost>();

    await connection.QueryAsync<BlogPost, Comment, BlogPost>(
        sql,
        (post, comment) =>
        {
            if (!postDict.TryGetValue(post.Id, out var existingPost))
            {
                existingPost = post;
                existingPost.Comments = new List<Comment>();
                postDict.Add(post.Id, existingPost);
            }

            if (comment != null)
            {
                existingPost.Comments.Add(comment);
            }

            return existingPost;
        },
        splitOn: "id"
    );

    return postDict.Values;
}
```

### 高级顶级高级技术

**复杂查询的动态参数 :**

```csharp
public async Task<IEnumerable<BlogPost>> SearchWithDynamicFiltersAsync(SearchCriteria criteria)
{
    using var connection = new NpgsqlConnection(_connectionString);

    var parameters = new DynamicParameters();
    var conditions = new List<string>();

    var sql = new StringBuilder("SELECT * FROM blog_posts");

    if (!string.IsNullOrEmpty(criteria.SearchTerm))
    {
        conditions.Add("search_vector @@ to_tsquery('english', @SearchTerm)");
        parameters.Add("SearchTerm", criteria.SearchTerm);
    }

    if (criteria.CategoryIds?.Any() == true)
    {
        conditions.Add("category_id = ANY(@CategoryIds)");
        parameters.Add("CategoryIds", criteria.CategoryIds);
    }

    if (criteria.FromDate.HasValue)
    {
        conditions.Add("published_date >= @FromDate");
        parameters.Add("FromDate", criteria.FromDate.Value);
    }

    if (criteria.Tags?.Any() == true)
    {
        conditions.Add("tags && @Tags");  // PostgreSQL array overlap
        parameters.Add("Tags", criteria.Tags);
    }

    if (conditions.Any())
    {
        sql.Append(" WHERE ");
        sql.Append(string.Join(" AND ", conditions));
    }

    sql.Append(" ORDER BY published_date DESC LIMIT @Limit");
    parameters.Add("Limit", criteria.Limit);

    return await connection.QueryAsync<BlogPost>(sql.ToString(), parameters);
}
```

**PostgreSQL 类型自定义类型手动器 :**

```csharp
// Handle PostgreSQL arrays
public class PostgresArrayTypeHandler : SqlMapper.TypeHandler<string[]>
{
    public override void SetValue(IDbDataParameter parameter, string[] value)
    {
        parameter.Value = value;
        ((NpgsqlParameter)parameter).NpgsqlDbType = NpgsqlDbType.Array | NpgsqlDbType.Text;
    }

    public override string[] Parse(object value)
    {
        return (string[])value;
    }
}

// Handle PostgreSQL JSONB
public class JsonTypeHandler<T> : SqlMapper.TypeHandler<T>
{
    public override void SetValue(IDbDataParameter parameter, T value)
    {
        parameter.Value = JsonSerializer.Serialize(value);
        ((NpgsqlParameter)parameter).NpgsqlDbType = NpgsqlDbType.Jsonb;
    }

    public override T Parse(object value)
    {
        return JsonSerializer.Deserialize<T>(value.ToString());
    }
}

// Register handlers (in startup)
SqlMapper.AddTypeHandler(new PostgresArrayTypeHandler());
SqlMapper.AddTypeHandler(new JsonTypeHandler<Dictionary<string, object>>());
```

**使用 PostgreSQL COPY 的散装操作:**

```csharp
public async Task BulkInsertPostsAsync(IEnumerable<BlogPost> posts)
{
    using var connection = new NpgsqlConnection(_connectionString);
    await connection.OpenAsync();

    using var writer = await connection.BeginBinaryImportAsync(
        "COPY blog_posts (title, content, tags, published_date) FROM STDIN (FORMAT BINARY)"
    );

    foreach (var post in posts)
    {
        await writer.StartRowAsync();
        await writer.WriteAsync(post.Title);
        await writer.WriteAsync(post.Content);
        await writer.WriteAsync(post.Tags, NpgsqlDbType.Array | NpgsqlDbType.Text);
        await writer.WriteAsync(post.PublishedDate);
    }

    await writer.CompleteAsync();
}
```

**交易支持:**

```csharp
public async Task TransferPostToCategoryAsync(int postId, int newCategoryId)
{
    using var connection = new NpgsqlConnection(_connectionString);
    await connection.OpenAsync();

    using var transaction = await connection.BeginTransactionAsync();

    try
    {
        // Update the post
        await connection.ExecuteAsync(
            "UPDATE blog_posts SET category_id = @CategoryId WHERE id = @PostId",
            new { CategoryId = newCategoryId, PostId = postId },
            transaction
        );

        // Log the change
        await connection.ExecuteAsync(
            @"INSERT INTO category_history (post_id, category_id, changed_at)
              VALUES (@PostId, @CategoryId, @ChangedAt)",
            new { PostId = postId, CategoryId = newCategoryId, ChangedAt = DateTime.UtcNow },
            transaction
        );

        await transaction.CommitAsync();
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}
```

### 何时使用 Dapper

**使用 dapper 时 :**

- 性能是关键 但你不需要绝对最高速度
- 你写SQL很舒服
- 您有复杂的查询, 无法映射到 LINQ 。
- 您需要精细控制 SQL 一代
- 你和现有的数据库计划合作
- 你想要最低的间接费用和依附关系
- 内存的使用是一个令人关切的问题
- 您需要广泛利用数据库特有的功能

**在下列情况下避免达珀:**

- 你的团队对SQL不满意
- 您需要自动更改跟踪
- 你想把计划移民作为代码
- 你有一个迅速进化的预想
- 您需要交叉数据库可移动性
- 您更喜欢 LINQ 而不是 SQL 语法

### 性能特点

- **查询性能**:原始ADO.NET的间接费用占5-15%
- **内存使用**:最低 - 无更改跟踪或代理
- **散装作业**与 PostgreSQL COPY 相比极佳
- **第一个查询**:快速 - 没有汇编步骤
- **开发者生产率**:需要SQL知识,但非常可预测

## Raw ADO.net 与 Npgsql 连接

绝对最大性能和控制最大性能和控制,您可以使用 [Npgsql Npgsql](https://www.npgsql.org/) 直接无任何ORM层。

### 当Raw ADO.

Raw ADO. NET 适合当:

- 你需要每一毫秒的表演
- 你正在建造高通量数据处理器
- 您的工作与 PostgreSQL 不受 ORMS 支持的具体功能
- 你需要准确控制记忆分配
- 您正在做大宗操作或流出大型数据集

### 示例:纯Npgsql

```csharp
public class NpgsqlBlogRepository
{
    private readonly string _connectionString;

    public async Task<List<BlogPost>> GetRecentPostsAsync(int count)
    {
        var posts = new List<BlogPost>();

        using var connection = new NpgsqlConnection(_connectionString);
        await connection.OpenAsync();

        using var command = new NpgsqlCommand(
            "SELECT id, title, content, tags, published_date FROM blog_posts ORDER BY published_date DESC LIMIT @count",
            connection
        );

        command.Parameters.AddWithValue("count", count);

        using var reader = await command.ExecuteReaderAsync();

        while (await reader.ReadAsync())
        {
            posts.Add(new BlogPost
            {
                Id = reader.GetInt32(0),
                Title = reader.GetString(1),
                Content = reader.GetString(2),
                Tags = reader.GetFieldValue<string[]>(3),
                PublishedDate = reader.GetDateTime(4)
            });
        }

        return posts;
    }

    // Using prepared statements for repeated queries
    public async Task<BlogPost> GetPostByIdAsync(int id)
    {
        using var connection = new NpgsqlConnection(_connectionString);
        await connection.OpenAsync();

        using var command = new NpgsqlCommand(
            "SELECT id, title, content FROM blog_posts WHERE id = $1",
            connection
        );

        command.Parameters.AddWithValue(id);
        await command.PrepareAsync(); // Prepared statement for performance

        using var reader = await command.ExecuteReaderAsync();

        if (await reader.ReadAsync())
        {
            return new BlogPost
            {
                Id = reader.GetInt32(0),
                Title = reader.GetString(1),
                Content = reader.GetString(2)
            };
        }

        return null;
    }

    // Working with PostgreSQL JSONB
    public async Task<Dictionary<string, object>> GetPostMetadataAsync(int id)
    {
        using var connection = new NpgsqlConnection(_connectionString);
        await connection.OpenAsync();

        using var command = new NpgsqlCommand(
            "SELECT metadata FROM blog_posts WHERE id = $1",
            connection
        );

        command.Parameters.AddWithValue(id);

        var json = await command.ExecuteScalarAsync() as string;
        return JsonSerializer.Deserialize<Dictionary<string, object>>(json);
    }

    // Streaming large result sets
    public async IAsyncEnumerable<BlogPost> StreamAllPostsAsync()
    {
        using var connection = new NpgsqlConnection(_connectionString);
        await connection.OpenAsync();

        using var command = new NpgsqlCommand(
            "SELECT id, title, content FROM blog_posts ORDER BY id",
            connection
        );

        using var reader = await command.ExecuteReaderAsync();

        while (await reader.ReadAsync())
        {
            yield return new BlogPost
            {
                Id = reader.GetInt32(0),
                Title = reader.GetString(1),
                Content = reader.GetString(2)
            };
        }
    }
}
```

### 性能特点

- **查询性能**基准 - 尽可能快
- **内存使用**:最低 -- -- 完全控制拨款
- **散装作业**与 COPY 协议的极配
- **开发者生产率**:需要大多数手工工作

## 目标绘图图书馆

当与 Dapper 或 原始 ADO.NET 合作时, 您通常需要绘制不同对象表达式( DTOs、 实体、 查看模型) 之间的地图 。 多个图书馆可以实现这个自动化 。

### 地图绘制:高性能绘图

[地图地图师](https://github.com/MapsterMapper/Mapster) 是一个快速的、基于公约的物体绘图仪,利用源生成来优化性能。

```csharp
// Install: Mapster and Mapster.Tool
using Mapster;

public class BlogPostDto
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Summary { get; set; }
    public List<string> CategoryNames { get; set; }
}

public class BlogPost
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public List<Category> Categories { get; set; }
}

// Configuration
public class MappingConfig : IRegister
{
    public void Register(TypeAdapterConfig config)
    {
        config.NewConfig<BlogPost, BlogPostDto>()
            .Map(dest => dest.Summary, src => src.Content.Substring(0, Math.Min(200, src.Content.Length)))
            .Map(dest => dest.CategoryNames, src => src.Categories.Select(c => c.Name).ToList());

        // Reverse map with ignore
        config.NewConfig<BlogPostDto, BlogPost>()
            .Ignore(dest => dest.Content);
    }
}

// Registration in Program.cs
TypeAdapterConfig.GlobalSettings.Scan(Assembly.GetExecutingAssembly());

// Usage with Dapper
public class BlogService
{
    private readonly string _connectionString;

    public async Task<List<BlogPostDto>> GetPostsAsync()
    {
        using var connection = new NpgsqlConnection(_connectionString);

        var posts = await connection.QueryAsync<BlogPost>(@"
            SELECT p.id, p.title, p.content
            FROM blog_posts p
        ");

        // Map to DTOs - very fast with Mapster
        return posts.Adapt<List<BlogPostDto>>();
    }

    // Projection mapping (compile-time)
    public async Task<List<BlogPostDto>> GetPostsDtosDirectlyAsync()
    {
        using var connection = new NpgsqlConnection(_connectionString);

        // Query directly to DTO shape
        return (await connection.QueryAsync<BlogPostDto>(@"
            SELECT
                id,
                title,
                SUBSTRING(content, 1, 200) as summary
            FROM blog_posts
        ")).ToList();
    }
}
```

### 自动 Mapper: 以公约为基础的绘图仪

[自动 Mapper 自动管理器](https://automapper.org/) 地图图书馆是最受欢迎的地图图书馆,尽管比《地图》要慢。

```csharp
// Install: AutoMapper and AutoMapper.Extensions.Microsoft.DependencyInjection
using AutoMapper;

public class MappingProfile : Profile
{
    public MappingProfile()
    {
        CreateMap<BlogPost, BlogPostDto>()
            .ForMember(d => d.Summary, opt => opt.MapFrom(s =>
                s.Content.Length > 200 ? s.Content.Substring(0, 200) : s.Content))
            .ForMember(d => d.CategoryNames, opt => opt.MapFrom(s =>
                s.Categories.Select(c => c.Name)));

        // Reverse map
        CreateMap<BlogPostDto, BlogPost>()
            .ForMember(d => d.Content, opt => opt.Ignore());
    }
}

// Registration in Program.cs
services.AddAutoMapper(typeof(MappingProfile));

// Usage
public class BlogService
{
    private readonly IMapper _mapper;
    private readonly string _connectionString;

    public BlogService(IMapper mapper, IConfiguration configuration)
    {
        _mapper = mapper;
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    public async Task<List<BlogPostDto>> GetPostsAsync()
    {
        using var connection = new NpgsqlConnection(_connectionString);

        var posts = await connection.QueryAsync<BlogPost>(@"
            SELECT id, title, content FROM blog_posts
        ");

        return _mapper.Map<List<BlogPostDto>>(posts.ToList());
    }
}
```

### 人工绘图:全面控制

有时,最好的办法是明确人工绘图:

```csharp
public static class BlogPostMapper
{
    public static BlogPostDto ToDto(this BlogPost post)
    {
        return new BlogPostDto
        {
            Id = post.Id,
            Title = post.Title,
            Summary = post.Content.Length > 200
                ? post.Content.Substring(0, 200) + "..."
                : post.Content,
            CategoryNames = post.Categories?.Select(c => c.Name).ToList() ?? new List<string>()
        };
    }

    public static List<BlogPostDto> ToDtoList(this IEnumerable<BlogPost> posts)
    {
        return posts.Select(p => p.ToDto()).ToList();
    }

    // Inline mapping for simple cases
    public static BlogPostDto MapToDto(BlogPost post) => new()
    {
        Id = post.Id,
        Title = post.Title,
        Summary = post.Content[..Math.Min(200, post.Content.Length)]
    };
}

// Usage
var posts = await _repository.GetAllPostsAsync();
var dtos = posts.ToDtoList();
```

### 绘图业绩比较

```
BenchmarkDotNet Results (mapping 1000 objects):

Method              | Mean      | Allocated
--------------------|-----------|----------
Manual Mapping      | 45.2 μs   | 78 KB
Mapster             | 52.1 μs   | 79 KB
AutoMapper          | 184.3 μs  | 156 KB
```

**密钥外卖 :**

- 手工绘图速度最快,但需要更多的代码
- 地图师的代码少了 几乎和地图师一样快
- AutoMapper 方便,但管理费较高

## 两种办法的平衡兼顾办法:最佳世界和最佳世界

在实际应用中,您往往想要对同一应用中的不同情景使用不同的方法。这是 **建议采用的方法** 对于大多数生产系统来说都是如此。

### CQRS 模式:分离读写

CQRS(Comman和查询责任隔离)模式自然适合混合数据获取方法。 [马尔坦](https://martendb.io/),见我的文章 [现代CQRS CQRS 和事件观察](/blog/moderncqrsandeventsourcing).

**Marten与这次讨论有何关联:**

Marten是一个基于 PostgreSQL 的文件数据库和事件存储库, 将混合数据存取到另一个级别。 它合并了 :

- **事件来源** 用于写入(不可移动事件流)
- **预测预测数** 改为(查询时优化的实物化观点)
- **PostgreSQL 的 JSONB 日志** 文档存储
- CQRS 内建 CQRS 模式

虽然本条侧重于传统关系数据存取(EF Core, Dapper),但Marten展示了如何利用PostgreSQL的先进功能(JSONB,事件流)来实施复杂的结构。

- 单独的写作模型(优化用于交易和一致性)
- 单独的阅读模型(优化查询和业绩)
- 每个工作使用正确的工具

```mermaid
graph TB
    Client[Client Application]

    subgraph "Write Side - Commands"
        WriteAPI[Write API / Commands]
        EFCore[EF Core Context]
        WriteDB[(PostgreSQL<br/>Write Operations)]
    end

    subgraph "Read Side - Queries"
        ReadAPI[Read API / Queries]
        Dapper[Dapper Repository]
        ReadDB[(PostgreSQL<br/>Read Operations)]
    end

    Client -->|Create/Update/Delete| WriteAPI
    WriteAPI --> EFCore
    EFCore -->|Change Tracking<br/>Validation<br/>Business Logic| WriteDB

    Client -->|Query/Search| ReadAPI
    ReadAPI --> Dapper
    Dapper -->|Optimized SQL<br/>DTOs<br/>No Tracking| ReadDB

    WriteDB -.->|Same Database| ReadDB

    style Client stroke:#6366f1,stroke-width:2px
    style WriteAPI stroke:#2563eb,stroke-width:2px
    style EFCore stroke:#2563eb,stroke-width:2px
    style WriteDB stroke:#2563eb,stroke-width:2px
    style ReadAPI stroke:#059669,stroke-width:2px
    style Dapper stroke:#059669,stroke-width:2px
    style ReadDB stroke:#059669,stroke-width:2px
```

此模式的杠杆作用 :

- **EF 核心核心** 更改追踪、验证、业务规则
- **顶顶顶端** 最大查询性能和灵活性
- 同一数据库,为每个使用案例优化的不同访问模式

### 实施:具有EF核心和Dapper的CQRS CQRS

```csharp
// Commands: Use EF Core for change tracking and validation
public class BlogCommandService
{
    private readonly BlogDbContext _context;
    private readonly ILogger<BlogCommandService> _logger;

    public BlogCommandService(BlogDbContext context, ILogger<BlogCommandService> logger)
    {
        _context = context;
        _logger = logger;
    }

    public async Task<int> CreatePostAsync(CreatePostCommand command)
    {
        // Business logic and validation
        var post = new BlogPost
        {
            Title = command.Title,
            Content = command.Content,
            CategoryId = command.CategoryId,
            PublishedDate = DateTime.UtcNow
        };

        _context.BlogPosts.Add(post);
        await _context.SaveChangesAsync();

        _logger.LogInformation("Created blog post {PostId}", post.Id);

        return post.Id;
    }

    public async Task UpdatePostAsync(UpdatePostCommand command)
    {
        var post = await _context.BlogPosts.FindAsync(command.Id);

        if (post == null)
            throw new InvalidOperationException($"Post {command.Id} not found");

        post.Title = command.Title;
        post.Content = command.Content;
        post.UpdatedAt = DateTime.UtcNow;

        await _context.SaveChangesAsync();

        _logger.LogInformation("Updated blog post {PostId}", post.Id);
    }

    public async Task DeletePostAsync(int id)
    {
        var post = await _context.BlogPosts.FindAsync(id);

        if (post != null)
        {
            _context.BlogPosts.Remove(post);
            await _context.SaveChangesAsync();

            _logger.LogInformation("Deleted blog post {PostId}", id);
        }
    }
}

// Queries: Use Dapper for read performance
public class BlogQueryService
{
    private readonly string _connectionString;
    private readonly ILogger<BlogQueryService> _logger;

    public BlogQueryService(IConfiguration configuration, ILogger<BlogQueryService> logger)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
        _logger = logger;
    }

    public async Task<BlogPostDto> GetPostBySlugAsync(string slug)
    {
        using var connection = new NpgsqlConnection(_connectionString);

        const string sql = @"
            SELECT
                p.id,
                p.title,
                p.slug,
                p.content,
                p.published_date,
                c.id as category_id,
                c.name as category_name,
                (SELECT COUNT(*) FROM comments WHERE blog_post_id = p.id) as comment_count
            FROM blog_posts p
            INNER JOIN categories c ON p.category_id = c.id
            WHERE p.slug = @Slug";

        var post = await connection.QueryFirstOrDefaultAsync<BlogPostDto>(sql, new { Slug = slug });

        if (post != null)
        {
            _logger.LogInformation("Retrieved blog post by slug {Slug}", slug);
        }

        return post;
    }

    public async Task<PagedResult<BlogPostSummaryDto>> GetRecentPostsAsync(int page, int pageSize)
    {
        using var connection = new NpgsqlConnection(_connectionString);

        const string sql = @"
            SELECT
                p.id,
                p.title,
                p.slug,
                LEFT(p.content, 200) as summary,
                p.published_date,
                c.name as category_name
            FROM blog_posts p
            INNER JOIN categories c ON p.category_id = c.id
            ORDER BY p.published_date DESC
            LIMIT @PageSize OFFSET @Offset";

        const string countSql = "SELECT COUNT(*) FROM blog_posts";

        var posts = await connection.QueryAsync<BlogPostSummaryDto>(
            sql,
            new { PageSize = pageSize, Offset = (page - 1) * pageSize }
        );

        var totalCount = await connection.ExecuteScalarAsync<int>(countSql);

        return new PagedResult<BlogPostSummaryDto>
        {
            Items = posts.ToList(),
            TotalCount = totalCount,
            Page = page,
            PageSize = pageSize
        };
    }

    public async Task<List<BlogPostDto>> SearchPostsAsync(string searchTerm)
    {
        using var connection = new NpgsqlConnection(_connectionString);

        const string sql = @"
            SELECT
                p.id,
                p.title,
                p.slug,
                p.content,
                p.published_date,
                c.name as category_name,
                ts_rank(p.search_vector, query) as relevance_score
            FROM blog_posts p
            INNER JOIN categories c ON p.category_id = c.id,
                 to_tsquery('english', @SearchTerm) query
            WHERE p.search_vector @@ query
            ORDER BY relevance_score DESC
            LIMIT 50";

        var posts = await connection.QueryAsync<BlogPostDto>(sql, new { SearchTerm = searchTerm });

        _logger.LogInformation(
            "Searched posts with term {SearchTerm}, found {Count} results",
            searchTerm,
            posts.Count()
        );

        return posts.ToList();
    }
}

// Service layer orchestrating commands and queries
public class BlogService
{
    private readonly BlogCommandService _commands;
    private readonly BlogQueryService _queries;

    public BlogService(BlogCommandService commands, BlogQueryService queries)
    {
        _commands = commands;
        _queries = queries;
    }

    // Write operations delegate to command service
    public Task<int> CreatePostAsync(CreatePostCommand command) => _commands.CreatePostAsync(command);
    public Task UpdatePostAsync(UpdatePostCommand command) => _commands.UpdatePostAsync(command);
    public Task DeletePostAsync(int id) => _commands.DeletePostAsync(id);

    // Read operations delegate to query service
    public Task<BlogPostDto> GetPostBySlugAsync(string slug) => _queries.GetPostBySlugAsync(slug);
    public Task<PagedResult<BlogPostSummaryDto>> GetRecentPostsAsync(int page, int pageSize)
        => _queries.GetRecentPostsAsync(page, pageSize);
    public Task<List<BlogPostDto>> SearchPostsAsync(string searchTerm)
        => _queries.SearchPostsAsync(searchTerm);
}
```

### EF 核心部分,不定期原始SQL

用于主要为EF核心但需要偶尔优化性能的应用:

```csharp
public class BlogService
{
    private readonly BlogDbContext _context;

    // 95% of queries: Use EF Core LINQ
    public async Task<List<BlogPost>> GetPostsByCategoryAsync(int categoryId)
    {
        return await _context.BlogPosts
            .Where(p => p.CategoryId == categoryId)
            .Include(p => p.Comments)
            .ToListAsync();
    }

    // 5% of queries: Use raw SQL for complex analytics
    public async Task<List<PostAnalytics>> GetPostAnalyticsAsync()
    {
        using var connection = _context.Database.GetDbConnection();
        await _context.Database.OpenConnectionAsync();

        using var command = connection.CreateCommand();
        command.CommandText = @"
            WITH post_metrics AS (
                SELECT
                    p.id,
                    p.title,
                    COUNT(DISTINCT c.id) as comment_count,
                    COUNT(DISTINCT v.id) as view_count,
                    AVG(c.sentiment_score) as avg_sentiment
                FROM blog_posts p
                LEFT JOIN comments c ON p.id = c.post_id
                LEFT JOIN post_views v ON p.id = v.post_id
                WHERE p.published_date >= NOW() - INTERVAL '30 days'
                GROUP BY p.id, p.title
            )
            SELECT * FROM post_metrics
            ORDER BY view_count DESC";

        var analytics = new List<PostAnalytics>();
        using var reader = await command.ExecuteReaderAsync();

        while (await reader.ReadAsync())
        {
            analytics.Add(new PostAnalytics
            {
                PostId = reader.GetInt32(0),
                Title = reader.GetString(1),
                CommentCount = reader.GetInt64(2),
                ViewCount = reader.GetInt64(3),
                AverageSentiment = reader.IsDBNull(4) ? 0 : reader.GetDouble(4)
            });
        }

        return analytics;
    }
}
```

## 业绩比较

让我们看看现实世界与PostgreSQL共同行动的业绩基准:

### 基准:阅读1000记录

```
BenchmarkDotNet Results (Lower is Better):

Method                    | Mean       | Allocated
------------------------- |----------- |-----------
EF Core (No Tracking)    | 12.34 ms   | 2.4 MB
EF Core (With Tracking)  | 15.67 ms   | 4.8 MB
Dapper                   | 8.21 ms    | 1.8 MB
Raw Npgsql               | 7.45 ms    | 1.2 MB
```

### 基准:插入1 000记录

```
Method                    | Mean       | Allocated
------------------------- |----------- |-----------
EF Core (SaveChanges)    | 245.3 ms   | 15.2 MB
EF Core (BulkInsert)     | 42.1 ms    | 8.4 MB
Dapper (Loop)            | 189.7 ms   | 2.1 MB
Npgsql COPY              | 18.3 ms    | 0.8 MB
```

### 基准:复杂联合查询

```
Method                    | Mean       | Allocated
------------------------- |----------- |-----------
EF Core (Include)        | 28.5 ms    | 5.2 MB
EF Core (Split Query)    | 24.1 ms    | 4.8 MB
Dapper (Multi-Map)       | 16.8 ms    | 3.1 MB
Raw Npgsql               | 15.2 ms    | 2.4 MB
```

### 密钥外出

1. **Raw Npgsql 最快** 需要最多代码
2. **Dapper 表现优异** 抽取成本最低(比EF核心量快60-70%)
3. **EF 核心无跟踪跟踪查询** 多数假设情况均合理
4. **散散散业务** 显示最大的性能差距( 10- 13x差异 ! )
5. **内存分配款** 遵循与执行时间类似的模式

## 决定矩阵表:采用哪种方法

### 使用 EF 核心当:

- 建立符合不断变化的需求的新应用程序
- 带有丰富实体模型的域驱动设计
- 您需要迁移和计划管理
- 团队比SQL更适应C#
- 读/写比率平衡
- 在最佳的20%至50%内查询性能是可以接受的
- 你想要改变跟踪 和单位工作模式

见见 [第一部分 第一部分](/blog/orm-mapping-comparison-part1) 全面的核心指导。

### 使用 dapper 时间 :

- 业绩很重要,但并不重要
- 您有复杂的查询, 无法向 LINQ 绘制好 。
- 你写SQL很舒服
- 您需要精细控制 SQL 一代
- 与现有数据库计划合作
- 使用简单写作的重读工作量 Name
- 你想要最小的抽象管理费

### 使用 Raw Npgsql 时 :

- 最大性能是关键
- 建立高投入数据处理器
- 与PostgreSQL具体特点广泛合作
- 批发业务和批量进口
- 每毫秒和兆字节的事情
- 你需要绝对控制

### 于 时 分 分 :

- 应用的不同部分有不同的需求
- CQRS 模式 (EF Core for write, Dapper for leep)
- 大多数查询使用EF核心,但少数需要原始SQL
- 您想要在各种途径之间逐步迁移
- 具有不同要求的大型复杂应用程序

## 最佳做法摘要

### 一般准则一般准则

1. **以 EF 核心** 用于新项目的项目,除非有具体的业绩要求
2. **优化前的配置文件** - 不要假设你需要达帕/草SQL
3. **使用混合方法** - 结合不同工具的优势
4. **保持数据存取逻辑分离** - 仓库模式有助于转换实施
5. **使用连接共用电联** - 适当配置您的工作量
6. **利用 PostgreSQL 特性** - 不要从强大的数据库能力中抽取

### 具体顶点

1. **使用参数查询** 防止 SQL 注入
2. **考虑查询结果缓存** 昂贵的反复查询
3. **使用多绘图** 以加入代替多个圆曲次
4. **重新使用连接** 从连接集合
5. **考虑达珀。** 用于简单 CRUD 操作的简单 CRUD 操作

### 绩效提示

1. **输入查询索引** 用解析分析分析缓慢的查询
2. **使用所准备的发言稿** 重复查询
3. **批次业务** 可能时
4. **使用COPY( 使用COPY)** 用于在 PostgreSQL 中 批量插入
5. **监视连接池** - 配置最小/最大池池规模
6. **考虑阅读复制** 重重工作量

## 结论 结论 结论 结论 结论

在 PostgreSQL 的.NET 应用程序中选择正确的数据访问方法, 不是为了找到“ 最佳” 工具,

- **EF 核心核心** (见 [第一部分 第一部分](/blog/orm-mapping-comparison-part1)在快速发展、领域建模和应用方面,开发者生产力优于原始业绩的开发者生产力优于原始业绩方面,优异于迅速发展、领域建模和应用
- **顶顶顶端** 提供极佳的中间地带,近于最佳性能和合理的抽象性
- **原始 Npgsql** 为数据密集型业务提供最大性能和控制
- **制图图书馆** 例如,Master and AutoMapper 和AutoMapper 在使用较低级别数据访问时减少锅炉板

在实践中,最成功的应用程序通常使用 **混合办法**酌情利用每种工具的优势:

- 使用使用 **EF 核心核心** 您的域域域逻辑并写入
- 使用使用 **顶顶顶端** 查询和报告
- 使用使用 **原始 Npgsql** 用于散装作业和分析

关键是:

1. **理解你的要求** - 业绩、发展速度、团队技能
2. **配置您的应用程序** - 查明实际的瓶颈,而不是假定的瓶颈
3. **务实选择** - 使用最简单的工具满足你的需要
4. **保持灵活** - 在同一应用程序中,您可以混合各种途径

记住: 过早优化是所有邪恶的根源, 但建设一个无法在需要时缩放的系统也是如此。 开始简单、 测量业绩, 并优化其重要性所在 。

## 参考文献和进一步阅读

**本系列第一部分:**

- [.NET 第一部分:实体框架核心](/blog/orm-mapping-comparison-part1)

**正式文件:**

- [Dapper GitHub( 达帕地虎)](https://github.com/DapperLib/Dapper)
- [Dapper 导书](https://dapper-tutorial.net/)
- [Npgsql 文档文档](https://www.npgsql.org/doc/)
- [Npgsql ADO.Net 提供者](https://www.npgsql.org/doc/basic-usage.html)
- [GitHub 地图](https://github.com/MapsterMapper/Mapster)
- [自动 Mapper 文档文档](https://docs.automapper.org/)
- [PostgreSQL 文档文档](https://www.postgresql.org/docs/)

**本博客相关文章:**

- [添加实体博客日志框架](/blog/addingentityframeworkforblogpostspt1)
- [EF 移徙的正确途径](/blog/efmigrationstherightway)
- [以 EF 核心搜索全文](/blog/textsearchingpt1)
- [现代CQRS CQRS 和事件观察](/blog/moderncqrsandeventsourcing)

---


*我们从EF核心的强力抽象到原始SQL的最大性能, 都包括了所有内容, 并附有关于整合最佳结果方法的实用指导。*