Dapper

using Dapper;
using Npgsql;

// Column-to-member matching assumes DefaultTypeMap.MatchNamesWithUnderscores = true at start-up.
public sealed class InvoiceReader(NpgsqlDataSource dataSource)
{
    public async Task<IReadOnlyList<Invoice>> ForCustomerAsync(long customerId, CancellationToken ct)
    {
        const string sql = """
            SELECT i.id, i.number, i.issued_on, i.total,
                   l.id, l.sku, l.quantity, l.unit_price
            FROM invoices i
            JOIN invoice_lines l ON l.invoice_id = i.id
            WHERE i.customer_id = @customerId
            ORDER BY i.issued_on DESC, l.id
            """;

        var byId = new Dictionary<long, Invoice>();
        await using var connection = await dataSource.OpenConnectionAsync(ct);
        await connection.QueryAsync<Invoice, InvoiceLine, Invoice>(
            new CommandDefinition(sql, new { customerId }, cancellationToken: ct),
            (invoice, line) =>
            {
                // Each row carries a fresh copy of the invoice columns; keep the first one.
                if (!byId.TryGetValue(invoice.Id, out var existing))
                {
                    existing = invoice;
                    byId.Add(invoice.Id, existing);
                }
                existing.Lines.Add(line);
                return existing;
            },
            splitOn: "id");

        return byId.Values.ToList();
    }
}

public sealed class Invoice
{
    public long Id { get; init; }
    public string Number { get; init; } = "";
    public DateTime IssuedOn { get; init; }
    public decimal Total { get; init; }
    public List<InvoiceLine> Lines { get; } = [];
}

public sealed class InvoiceLine
{
    public long Id { get; init; }
    public string Sku { get; init; } = "";
    public int Quantity { get; init; }
    public decimal UnitPrice { get; init; }
}

using Dapper;
using Npgsql;

// Column-to-member matching assumes DefaultTypeMap.MatchNamesWithUnderscores = true at start-up.
public sealed record StockLevel(string Sku, int OnHand);

public sealed class StockQueries(NpgsqlDataSource dataSource)
{
    // Pitfall: Dapper rewrites IN @skus into IN (@skus1, @skus2, ...). Every list length
    // is a different SQL text, so the server plans it again and Dapper caches another entry.
    public async Task<IReadOnlyList<StockLevel>> SlowAsync(string[] skus, CancellationToken ct)
    {
        await using var connection = await dataSource.OpenConnectionAsync(ct);
        var rows = await connection.QueryAsync<StockLevel>(new CommandDefinition(
            "SELECT sku, on_hand FROM inventory WHERE sku IN @skus", new { skus }, cancellationToken: ct));
        return rows.AsList();
    }

    // Fix on PostgreSQL: one text[] parameter, one statement for any list length.
    public async Task<IReadOnlyList<StockLevel>> FastAsync(string[] skus, CancellationToken ct)
    {
        await using var connection = await dataSource.OpenConnectionAsync(ct);
        var rows = await connection.QueryAsync<StockLevel>(new CommandDefinition(
            "SELECT sku, on_hand FROM inventory WHERE sku = ANY(@skus)", new { skus }, cancellationToken: ct));
        return rows.AsList();
    }
}

using Dapper;
using Npgsql;

// Column-to-member matching assumes DefaultTypeMap.MatchNamesWithUnderscores = true at start-up.
public sealed record OrderHeader(long Id, string Status, decimal TotalAmount);
public sealed record OrderLineRow(string Sku, int Quantity, decimal UnitPrice);
public sealed record PaymentRow(string Provider, decimal Amount, string State);
public sealed record OrderPage(OrderHeader Header, IReadOnlyList<OrderLineRow> Lines, IReadOnlyList<PaymentRow> Payments);

public sealed class OrderPageQuery(NpgsqlDataSource dataSource)
{
    public async Task<OrderPage?> LoadAsync(long orderId, CancellationToken ct)
    {
        const string sql = """
            SELECT id, status, total_amount FROM orders WHERE id = @orderId;
            SELECT sku, quantity, unit_price FROM order_lines WHERE order_id = @orderId ORDER BY id;
            SELECT provider, amount, state FROM payments WHERE order_id = @orderId ORDER BY created_at;
            """;
        await using var connection = await dataSource.OpenConnectionAsync(ct);
        await using var grid = await connection.QueryMultipleAsync(
            new CommandDefinition(sql, new { orderId }, cancellationToken: ct));

        var header = await grid.ReadSingleOrDefaultAsync<OrderHeader>();
        if (header is null) return null;
        var lines = (await grid.ReadAsync<OrderLineRow>()).AsList();
        var payments = (await grid.ReadAsync<PaymentRow>()).AsList();
        return new OrderPage(header, lines, payments);
    }
}