# 在“.NET: 比较ORMs和绘图战略”中的数据访问@(Part @2 @{-DapperMS K5Raw SQL},和混合方法+)

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

欢迎使用“ .NET! in” 我们综合数据存取指南的 MPK1{2}部分 [部分#1](/blog/orm-mapping-comparison-part1)我们深入探索了实体框架核心 包括SQL Droups , 常见陷阱

在文章“,”中,我们'}将探讨较轻的#-}重量替代品以及如何结合多种方法实现最佳性能=:}

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

## 目录目录

## Dapper : 微小的 mosk1 ORM

[顶端](https://github.com/DapperLib/Dapper) 它是由 Stack overflow_.}创建的轻量端@,}高端 @-}性能微缩=-}ORM 它在 ADO\.}{NET>,}上提供一层薄层, 用于处理对对象进行重复式绘图查询结果的工作, 同时给予您完整的 SQL control+.}

### 为什么达霸存在

Dapper出生于Stack overflocation-性能数据访问@. 团队发现,像实体框架{(pre}-Core)这样的完整的ORMs 为他们的高端-traffic假想增加过多的间接费用>.□Dapper 给了您“95%方便度 ” 只要在原始ADOZK10NET}♪.}

### 密钥特征

- **原 SQL**: 您自己写所有 SQL {-} 完整的控制 。
- **高业绩表现优**@: @(5-15%)@ADO#.NET 最低间接费用@MSK2}
- **多关- 映射**: 地图复杂查询多个相关类型
- **参数处理**\ : 自动参数化防止 SQL 注入
- **交易支持**: 对交易的全面控制
- **简单化**:无配置, 无更改跟踪MS K2无魔法
- **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 });
    }
}
```

### 多@ - @ mapping @ MS K1 @ 处理加入

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**

- 性能是关键,但您不需要绝对最大速度
- 您在 SQL 写SQL上很舒服
- 您有复杂的查询, 且您能够向 LINQ 绘制好的地图@ ' @ t
- 您需要精细控制 SQL 一代
- 您正在使用现有数据库的图案
- 你想要最低的间接费用和依赖性
- 内存的使用是一个令人关切的问题
- 您需要广泛利用数据库@ - @ 特定特性

***❌* 避免达珀当": ***

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

### 性能特点

- **查询性能**@: @ @ 5-15%# 原始ADO#.NET的间接费用
- **内存使用**: 最小@- 无更改跟踪或代理
- **散装作业**-=YTET -伊甸园字幕组=- 翻译:
- **第一个查询**:QFast @-}没有编译步骤
- **开发者生产率**:需要SQL知识,但非常可预测

## 使用 Npgsql 的 Raw ADO.NET

用于绝对最大性能和控控域@, @% 您可以使用 [Npgsql](https://www.npgsql.org/) 直接不带任何ORM 层@. @%

### 当 Raw ADO.NET 产生感知时

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@,}工作时, 您通常需要绘制不同对象表达式之间的映射 : @ (_ DTOZMS K3}实体 @ MPK4 查看模型\ ).{ 几个图书馆可以将此%.}自动化

### Mapster:高 -绩效映射

[地图绘制器](https://github.com/MapsterMapper/Mapster) 是一个使用源生成来优化性能的快速天体映射仪@ , @ action+-}

```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 @ : @ 性质公约@ MS K1_ 基于映射器

[自动 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 方便,但管理费较高

## 两种世界最好的办法

在实际应用程序中 @ , @% 您经常想要对同一应用中的不同情景使用不同的方法@ .}\ this is **建议采用的方法** 用于大多数生产系统

### CQRS 模式@:分隔读和写

CQRS {(}Command Query Responsiblication_)}模式自然适合混合数据访问方法}.(更深潜到CQRS和事件来源 [马尔坦](https://martendb.io/)“,”见我的文章 [现代CQRS和事件观察](/blog/moderncqrsandeventsourcing).

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

Marten 是一个基于 PostgreSQL 的文件数据库和事件存储库, 它将混合数据存取到另一个关卡@ . @ it companys:}

- **事件来源** 来撰写\ (\ immontible 事件流@)\
- **预测数** 表示“(”和“为查询优化的量化视图”
- **PostgreSQL ' @%s JSONB** 文档存储
- CQRS 模式

本篇文章的重点是传统关系数据存取@(EF Core @,Dapper),Marten 展示您如何利用 PostgreSQLMS K3}高级特写 @(JsonBMSKO5事件流_)实施复杂的架构}#.}原则是相同的\:}

- 用于交易和一致性的单独写作模型 @ ( @ 优化@ optimized
- 用于查询和性能优化的单独读取模型@( @% 1#
- 每个工作使用正确的工具

```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

```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:共同行动的实际业绩基准

### 校对:Portnoy

```
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
```

### 基准:插入{1000}记录

```
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
```

### 查询( R)

```
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 Core@)相比速度更快
3. **EF 核心查询@-}跟踪查询** 多数假设情况均合理
4. **散装业务** 显示最大性能差距@(10-13_x差异 @!)}
5. **内存分配款** 遵循与执行时间类似的模式

## 决定母体-MESK0}决定 采用哪种方法

### 何时使用 EF 核心@ : @ {}

- 建立符合不断变化的要求的新应用程序
- 具有丰富实体模型的驱动设计
- ✅ 您需要迁移和计划管理
- 与SQL相比,
- ✅ 读@/ writer 比例平衡
- ✅查询在 @20-50%内的最佳性能是可以接受的
- ✅ 您想要更改跟踪和单位工作模式

见见 [部分#1](/blog/orm-mapping-comparison-part1) EF 全面核心指导

### 使用 Dapper 时@ : @ {}

- 性能很重要,但并不重要
- ✅ 您有复杂的查询, 且您能够将这些复杂查询绘制到 LINQ 。
- ✅}你写SQL很舒服
- ✅ 您需要精细控制 SQL 一代
- 与现有数据库计划合作
- @✅_read\ -}用简单写作的重工作量
- ✅ 您想要最小的抽象管理费

### 使用 Raw Npgsql 时@ : @ {}

- 最大性能是关键
- 正在建造高空的-+通量数据处理器
- 与PostgreSQL-特定特性广泛合作
- ✅批发业务和大宗进口
- 每毫秒和兆字节都重要
- ✅ 您需要绝对控制

### 当 :}% 1 k6 x1\ k5\ s

- 不同的应用部分有不同的需求
- ✅ CQRS 模式 @(EF 核心写作@,Dapper 读作{)}
- 多数查询使用EF Core @,}但少数需要原始 SQL
- ✅ 您想要在不同方法之间逐渐迁移
- 具有不同要求的复杂应用

## 最佳做法摘要

### 一般准则一般准则

1. **以 EF 核心** 用于新项目的项目,除非有具体的业绩要求
2. **优化前的配置文件** {\fn黑体\fs22\bord1\shad0\3aHBE\4aH00\fscx67\fscy66\2cHFFFFFF\3cH808080}你需要Dapper吗?
3. **使用混合方法** “-”结合了不同工具的优势
4. **保持数据存取逻辑分离** - 仓库模式有助于切换执行
5. **使用连接共用电联** - 适合您的工作量配置
6. **利用 PostgreSQL 特性** @ -_ doñQ'_T 抽象脱去强大的数据库能力@ action

### 具体顶点

1. **使用参数查询** 防止SQL注入
2. **考虑查询结果缓存** 对于昂贵的@,}重复查询
3. **使用多@ - @ mapping** 组合而不是多个圆@- @%trips
4. **重新使用连接** 从连接集合
5. **考虑 Dapper.** 用于简单的 CROUD 操作

### 绩效提示

1. **输入查询索引** “- ” 用解析性分析分析缓慢的查询
2. **使用所准备的发言稿** 重复查询
3. **批次业务** 可能时
4. **使用COPY** 用于在 PostgreSQL 中批量插入
5. **监视连接池** @ -_ 配置 min@ MS K1 max 游泳池大小
6. **考虑阅读复制** 重工作量

## 结论结论

在 PostgreSQL 中选择正确的数据访问方法@ .NET 应用程序的正确数据访问方式是 @'}关于找到“ "Best}"}工具 @ MS K4 it'有关将正确工具匹配到您的具体需求@:}

- **EF 核心** (See( MSk0_ see) [部分#1](/blog/orm-mapping-comparison-part1)) 精于快速发展 MS K1 域模型_, 以及开发商生产力优于原始性能的应用
- **顶端** 提供极佳的中间地带, 接近@- @# 最佳性能和合理的抽象化
- **原始 Npgsql** 为数据提供最大性能和控制
- **制图图书馆** 就像地图师和自动Mapper 在使用更低的 @ - {} 级别数据访问时减少锅炉板

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

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

密钥是“:”

1. **理解你的要求** -=YTET -伊甸园字幕组=- 翻译:
2. **配置您的应用程序** @- 辨别实际的瓶颈 ,}*不是假设的
3. **务实选择** - 使用最简单的工具满足您的需要
4. **保持灵活** - 您可以在同一应用程序中混合方针

请记住 : 过早优化是所有邪恶的根@,,但建设一个系统也是一样。 如果需要的话, 这个系统可以 MS K2t 比例 *\ . 启动简单 @, 测量性能* MSKO5 并且优化它重要的地方=.

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

**本系列的1 部分**

- [数据存取于 @ .NET Part {1:} 实体框架核心](/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和事件观察](/blog/moderncqrsandeventsourcing)

---


*最后,我们在“.NET!We{'}中关于数据存取的系列丛书覆盖了从EF Core_'}强力抽象到原始SQL='}最高性能*,>的所有内容。*