Dapper vs EF Core

Dapper is a micro-ORM — it maps SQL query results to C# objects without generating SQL for you. It's faster than EF Core and gives you full control of your queries.

When to Use Each

| | EF Core | Dapper | |---|---|---| | CRUD with simple queries | ✅ Great | Works but verbose | | Complex SQL (CTEs, window functions) | Awkward | ✅ Natural | | Performance-critical reads | Slower | ✅ Faster | | Migrations & schema management | ✅ Built-in | Manual | | Bulk operations | Package needed | ✅ Direct SQL | | Team knows SQL well | Either | ✅ Dapper |

Many teams use both: EF Core for writes/simple reads, Dapper for complex reporting queries.

Install

Bash

dotnet add package Dapper
dotnet add package Npgsql  # or Microsoft.Data.SqlClient for SQL Server

Basic Queries

using Dapper;
using Npgsql;

public class ProductRepository
{
    private readonly string _connectionString;

    public ProductRepository(IConfiguration config)
    {
        _connectionString = config.GetConnectionString("Default")!;
    }

    private NpgsqlConnection CreateConnection()
        => new(_connectionString);

    // Query — returns IEnumerable<T>
    public async Task<IEnumerable<Product>> GetAllAsync()
    {
        using var conn = CreateConnection();
        return await conn.QueryAsync<Product>("SELECT * FROM products ORDER BY name");
    }

    // QuerySingle — expects exactly one row
    public async Task<Product?> GetByIdAsync(int id)
    {
        using var conn = CreateConnection();
        return await conn.QuerySingleOrDefaultAsync<Product>(
            "SELECT * FROM products WHERE id = @Id",
            new { Id = id });
    }

    // With filtering
    public async Task<IEnumerable<Product>> SearchAsync(string? name, decimal? maxPrice)
    {
        using var conn = CreateConnection();
        return await conn.QueryAsync<Product>(
            """
            SELECT * FROM products
            WHERE (@Name IS NULL OR name ILIKE '%' || @Name || '%')
              AND (@MaxPrice IS NULL OR price <= @MaxPrice)
            ORDER BY name
            """,
            new { Name = name, MaxPrice = maxPrice });
    }
}

Execute (INSERT, UPDATE, DELETE)

// Insert
public async Task<int> CreateAsync(CreateProductRequest req)
{
    using var conn = CreateConnection();
    return await conn.ExecuteScalarAsync<int>(
        """
        INSERT INTO products (name, price, stock)
        VALUES (@Name, @Price, @Stock)
        RETURNING id
        """,
        req);
}

// Update
public async Task<bool> UpdateAsync(int id, UpdateProductRequest req)
{
    using var conn = CreateConnection();
    var affected = await conn.ExecuteAsync(
        "UPDATE products SET name = @Name, price = @Price WHERE id = @Id",
        new { req.Name, req.Price, Id = id });
    return affected > 0;
}

// Delete
public async Task<bool> DeleteAsync(int id)
{
    using var conn = CreateConnection();
    var affected = await conn.ExecuteAsync(
        "DELETE FROM products WHERE id = @Id",
        new { Id = id });
    return affected > 0;
}

Multi-Mapping — JOIN Queries

public async Task<IEnumerable<OrderWithCustomer>> GetOrdersWithCustomersAsync()
{
    using var conn = CreateConnection();

    return await conn.QueryAsync<Order, Customer, OrderWithCustomer>(
        """
        SELECT o.*, c.*
        FROM orders o
        JOIN customers c ON c.id = o.customer_id
        ORDER BY o.created_at DESC
        """,
        (order, customer) => new OrderWithCustomer(order, customer),
        splitOn: "id"  // column that separates Order from Customer
    );
}

Transactions

public async Task TransferAsync(int fromId, int toId, decimal amount)
{
    using var conn = CreateConnection();
    await conn.OpenAsync();
    using var tx = await conn.BeginTransactionAsync();

    try
    {
        await conn.ExecuteAsync(
            "UPDATE accounts SET balance = balance - @Amount WHERE id = @Id",
            new { Amount = amount, Id = fromId },
            transaction: tx);

        await conn.ExecuteAsync(
            "UPDATE accounts SET balance = balance + @Amount WHERE id = @Id",
            new { Amount = amount, Id = toId },
            transaction: tx);

        await tx.CommitAsync();
    }
    catch
    {
        await tx.RollbackAsync();
        throw;
    }
}

Dynamic Parameters

// Build WHERE clause dynamically
public async Task<IEnumerable<Product>> FilterAsync(ProductFilter filter)
{
    var where = new List<string>();
    var p = new DynamicParameters();

    if (!string.IsNullOrEmpty(filter.Category))
    {
        where.Add("category = @Category");
        p.Add("Category", filter.Category);
    }
    if (filter.MinPrice.HasValue)
    {
        where.Add("price >= @MinPrice");
        p.Add("MinPrice", filter.MinPrice);
    }

    var sql = "SELECT * FROM products"
        + (where.Count > 0 ? " WHERE " + string.Join(" AND ", where) : "");

    using var conn = CreateConnection();
    return await conn.QueryAsync<Product>(sql, p);
}

Key Takeaways

Dapper is ~10x faster than EF Core for reads — queries go straight to the DB with minimal overhead
Parameters are always bound by name (@Name, @Id) — prevents SQL injection
Use QuerySingleOrDefaultAsync for single-row queries — returns null instead of throwing
For JOINs, use multi-mapping with splitOn to map columns to separate objects
Combine with EF Core for the best of both: EF for writes and migrations, Dapper for complex reads