dotnet-dapper-query-builder

Dapper クエリビルダー

概要

Dapper は軽量で高パフォーマンスなデータアクセスを提供します。

• 生 SQL - クエリを完全に制御できます
• マルチマッピング - 複雑な結合に対応します
• パラメーター化クエリ - SQL インジェクションを防止します
• 最小限のオーバーヘッド - ADO.NET に近いパフォーマンスです

クイックリファレンス

メソッド	目的	使用例
QueryAsync<T>	複数の行	リスト、レポート
QueryFirstOrDefaultAsync<T>	1 行	ID による取得
QueryMultipleAsync	複数の結果セット	複雑なデータ
ExecuteAsync	結果なし	挿入／更新／削除
ExecuteScalarAsync<T>	単一の値	件数、存在確認

---

テンプレート：基本的なクエリハンドラー

// src/{name}.application/{Feature}/Get{Entity}ById/Get{Entity}ByIdQueryHandler.cs
using System.Data;
using Dapper;
using {name}.application.abstractions.data;
using {name}.application.abstractions.messaging;
using {name}.domain.abstractions;

namespace {name}.application.{feature}.Get{Entity}ById;

internal sealed class Get{Entity}ByIdQueryHandler 
    : IQueryHandler<Get{Entity}ByIdQuery, {Entity}Response>
{
    private readonly ISqlConnectionFactory _sqlConnectionFactory;

    public Get{Entity}ByIdQueryHandler(ISqlConnectionFactory sqlConnectionFactory)
    {
        _sqlConnectionFactory = sqlConnectionFactory;
    }

    public async Task<Result<{Entity}Response>> Handle(
        Get{Entity}ByIdQuery request,
        CancellationToken cancellationToken)
    {
        using IDbConnection connection = _sqlConnectionFactory.CreateConnection();

        const string sql = """
            SELECT 
                e.id AS Id,
                e.name AS Name,
                e.description AS Description,
                e.is_active AS IsActive,
                e.created_at AS CreatedAt,
                o.id AS OrganizationId,
                o.name AS OrganizationName
            FROM entity e
            INNER JOIN organization o ON e.organization_id = o.id
            WHERE e.id = @Id
            """;

        var entity = await connection.QueryFirstOrDefaultAsync<{Entity}Response>(
            sql,
            new { request.Id });

        return entity is null
            ? Result.Failure<{Entity}Response>({Entity}Errors.NotFound)
            : entity;
    }
}

---

テンプレート：マルチマッピング（一対多）

internal sealed class Get{Entity}WithDetailsQueryHandler 
    : IQueryHandler<Get{Entity}WithDetailsQuery, {Entity}DetailResponse>
{
    private readonly ISqlConnectionFactory _sqlConnectionFactory;

    public async Task<Result<{Entity}DetailResponse>> Handle(
        Get{Entity}WithDetailsQuery request,
        CancellationToken cancellationToken)
    {
        using IDbConnection connection = _sqlConnectionFactory.CreateConnection();

        const string sql = """
            SELECT 
                e.id AS Id,
                e.name AS Name,
                c.id AS ChildId,
                c.name AS ChildName,
                c.sort_order AS SortOrder
            FROM entity e
            LEFT JOIN child c ON c.entity_id = e.id
            WHERE e.id = @Id
            ORDER BY c.sort_order
            """;

        var entityDict = new Dictionary<Guid, {Entity}DetailResponse>();

        await connection.QueryAsync<{Entity}DetailResponse, ChildResponse, {Entity}DetailResponse>(
            sql,
            (entity, child) =>
            {
                if (!entityDict.TryGetValue(entity.Id, out var existingEntity))
                {
                    existingEntity = entity;
                    existingEntity.Children = new List<ChildResponse>();
                    entityDict.Add(entity.Id, existingEntity);
                }

                if (child is not null)
                {
                    existingEntity.Children.Add(child);
                }

                return existingEntity;
            },
            new { request.Id },
            splitOn: "ChildId");

        var result = entityDict.Values.FirstOrDefault();

        return result is null
            ? Result.Failure<{Entity}DetailResponse>({Entity}Errors.NotFound)
            : result;
    }
}

---

テンプレート：フィルタリングを使ったページ分割クエリ

internal sealed class Search{Entities}QueryHandler 
    : IQueryHandler<Search{Entities}Query, PagedList<{Entity}Response>>
{
    private readonly ISqlConnectionFactory _sqlConnectionFactory;

    public async Task<Result<PagedList<{Entity}Response>>> Handle(
        Search{Entities}Query request,
        CancellationToken cancellationToken)
    {
        using IDbConnection connection = _sqlConnectionFactory.CreateConnection();

        var offset = (request.PageNumber - 1) * request.PageSize;
        var searchPattern = request.SearchTerm is not null 
            ? $"%{request.SearchTerm}%" 
            : null;

        // Build dynamic WHERE clause
        var whereConditions = new List<string> { "1 = 1" };
        
        if (searchPattern is not null)
            whereConditions.Add("(e.name ILIKE @SearchPattern OR e.description ILIKE @SearchPattern)");
        
        if (request.OrganizationId.HasValue)
            whereConditions.Add("e.organization_id = @OrganizationId");
        
        if (request.IsActive.HasValue)
            whereConditions.Add("e.is_active = @IsActive");

        var whereClause = string.Join(" AND ", whereConditions);

        var countSql = $"""
            SELECT COUNT(*)
            FROM entity e
            WHERE {whereClause}
            """;

        var dataSql = $"""
            SELECT 
                e.id AS Id,
                e.name AS Name,
                e.description AS Description,
                e.is_active AS IsActive,
                e.created_at AS CreatedAt
            FROM entity e
            WHERE {whereClause}
            ORDER BY e.created_at DESC
            OFFSET @Offset ROWS
            FETCH NEXT @PageSize ROWS ONLY
            """;

        var parameters = new
        {
            SearchPattern = searchPattern,
            request.OrganizationId,
            request.IsActive,
            Offset = offset,
            request.PageSize
        };

        var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);
        var items = await connection.QueryAsync<{Entity}Response>(dataSql, parameters);

        return new PagedList<{Entity}Response>(
            items.ToList(),
            request.PageNumber,
            request.PageSize,
            totalCount);
    }
}

---

テンプレート：CTE（共通テーブル式）

const string sql = """
    WITH RankedItems AS (
        SELECT 
            e.*,
            ROW_NUMBER() OVER (PARTITION BY e.category_id ORDER BY e.score DESC) as rank
        FROM entity e
        WHERE e.organization_id = @OrganizationId
    ),
    TopItems AS (
        SELECT * FROM RankedItems WHERE rank <= 3
    )
    SELECT 
        ti.id AS Id,
        ti.name AS Name,
        ti.score AS Score,
        ti.rank AS Rank,
        c.name AS CategoryName
    FROM TopItems ti
    INNER JOIN category c ON ti.category_id = c.id
    ORDER BY c.name, ti.rank
    """;

---

テンプレート：SQL 接続ファクトリー

// src/{name}.application/Abstractions/Data/ISqlConnectionFactory.cs
using System.Data;

namespace {name}.application.abstractions.data;

public interface ISqlConnectionFactory
{
    IDbConnection CreateConnection();
}

// src/{name}.infrastructure/Data/SqlConnectionFactory.cs
using System.Data;
using Npgsql;

namespace {name}.infrastructure.data;

internal sealed class SqlConnectionFactory : ISqlConnectionFactory
{
    private readonly string _connectionString;

    public SqlConnectionFactory(string connectionString)
    {
        _connectionString = connectionString;
    }

    public IDbConnection CreateConnection()
    {
        var connection = new NpgsqlConnection(_connectionString);
        connection.Open();
        return connection;
    }
}

---

列のマッピング（スネークケース → パスカルケース）

SELECT 
    e.id AS Id,                          -- Maps to Id
    e.first_name AS FirstName,           -- Maps to FirstName
    e.created_at AS CreatedAt,           -- Maps to CreatedAt
    e.organization_id AS OrganizationId  -- Maps to OrganizationId
FROM entity e

---

重要なルール

1. 必ずパラメーターを使用する - ユーザー入力を連結しないでください
2. 接続には using を使用する - 適切に破棄します
3. DTO に合わせて列にエイリアスを設定する - AS PropertyName
4. 結合にはマルチマッピングを使用する - N+1 クエリを回避します
5. 複雑なロジックには CTE を使用する - ネストしたクエリより読みやすくなります
6. ページ分割には OFFSET/FETCH を使用する - 標準的な SQL ページ分割です
7. 大文字と小文字を区別しない検索には ILIKE を使用する - PostgreSQL 固有です
8. エンティティではなく DTO を返す - クエリのプロジェクションのみを返します

---

関連スキル

• dotnet-cqrs-query-generator - クエリハンドラーの構造
• dotnet-repository-pattern - 書き込みには EF Core
• dotnet-clean-architecture - アプリケーション層