dapper

name	dapper
description	Use when writing read-optimized queries with Dapper alongside EF Core.
metadata	{"category":"data","agent":"ef-specialist","when-to-use":"When writing read-optimized SQL queries using Dapper alongside EF Core"}

Dapper Queries

When to Use Dapper vs EF Core

Scenario	Use
Complex read queries with joins	Dapper
Reporting and aggregation	Dapper
Bulk insert/update	Dapper
CRUD operations	EF Core
Change tracking	EF Core
Migrations	EF Core

Basic Query

namespace {Company}.{Domain}.Infrastructure.Queries;

public sealed class OrderQueryService(IDbConnectionFactory connectionFactory)
{
    public async Task<OrderDetailDto?> GetByIdAsync(Guid id, CancellationToken ct)
    {
        using var connection = connectionFactory.Create();
        const string sql = """
            SELECT o.Id, o.CustomerName, o.Total, o.Status, o.CreatedAt
            FROM Orders o
            WHERE o.Id = @Id
            """;
        return await connection.QuerySingleOrDefaultAsync<OrderDetailDto>(
            new CommandDefinition(sql, new { Id = id }, cancellationToken: ct));
    }
}

Multi-Mapping (Joins)

public async Task<IEnumerable<OrderWithItemsDto>> GetOrdersWithItemsAsync(
    CancellationToken ct)
{
    using var connection = connectionFactory.Create();
    const string sql = """
        SELECT o.Id, o.CustomerName, o.Total,
               i.Id AS ItemId, i.ProductName, i.Quantity, i.UnitPrice
        FROM Orders o
        LEFT JOIN OrderItems i ON i.OrderId = o.Id
        ORDER BY o.CreatedAt DESC
        """;

    var lookup = new Dictionary<Guid, OrderWithItemsDto>();

    await connection.QueryAsync<OrderWithItemsDto, OrderItemDto, OrderWithItemsDto>(
        new CommandDefinition(sql, cancellationToken: ct),
        (order, item) =>
        {
            if (!lookup.TryGetValue(order.Id, out var existing))
            {
                existing = order;
                existing.Items = [];
                lookup[order.Id] = existing;
            }
            if (item is not null)
                existing.Items.Add(item);
            return existing;
        },
        splitOn: "ItemId");

    return lookup.Values;
}

Paginated Query with Dynamic Filtering

public async Task<PaginatedResult<OrderSummaryDto>> GetPaginatedAsync(
    string? search, string? status, int page, int pageSize, CancellationToken ct)
{
    using var connection = connectionFactory.Create();

    var builder = new SqlBuilder();
    var countTemplate = builder.AddTemplate(
        "SELECT COUNT(*) FROM Orders o /**where**/");
    var selectTemplate = builder.AddTemplate("""
        SELECT o.Id, o.CustomerName, o.Total, o.Status, o.CreatedAt
        FROM Orders o /**where**/ /**orderby**/
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY
        """);

    if (!string.IsNullOrEmpty(search))
        builder.Where("o.CustomerName LIKE @Search", new { Search = $"%{search}%" });

    if (!string.IsNullOrEmpty(status))
        builder.Where("o.Status = @Status", new { Status = status });

    builder.OrderBy("o.CreatedAt DESC");

    var total = await connection.ExecuteScalarAsync<int>(
        new CommandDefinition(countTemplate.RawSql, countTemplate.Parameters,
            cancellationToken: ct));

    var items = await connection.QueryAsync<OrderSummaryDto>(
        new CommandDefinition(selectTemplate.RawSql,
            new DynamicParameters(selectTemplate.Parameters)
            {
                { "Offset", (page - 1) * pageSize },
                { "PageSize", pageSize }
            }, cancellationToken: ct));

    return new PaginatedResult<OrderSummaryDto>(items.ToList(), total, page, pageSize);
}

Connection Factory

public interface IDbConnectionFactory
{
    IDbConnection Create();
}

public sealed class SqlConnectionFactory(IOptions<DatabaseOptions> options)
    : IDbConnectionFactory
{
    public IDbConnection Create() =>
        new SqlConnection(options.Value.ConnectionString);
}

DI Registration

services.AddSingleton<IDbConnectionFactory, SqlConnectionFactory>();
services.AddScoped<OrderQueryService>();

Anti-Patterns

Anti-Pattern	Correct Approach
String concatenation for SQL	Use SqlBuilder or parameterized queries
Not disposing connections	using var connection = ...
Using Dapper for simple CRUD	Use EF Core for CRUD
Forgetting CancellationToken	Pass via CommandDefinition

Detect Existing Patterns

grep -r "Dapper\|SqlMapper\|QueryAsync\|SqlBuilder" --include="*.cs"
grep -r "IDbConnection\|SqlConnection" --include="*.cs"

Adding to Existing Project

1. Add Dapper and Dapper.SqlBuilder NuGet packages
2. Create IDbConnectionFactory if not already present
3. Use alongside EF Core — Dapper for reads, EF Core for writes