When EF Core Isn't Enough — Raw SQL, Dapper & Stored Procs

// Raw ADO.NET — maximum control, maximum verbosity
public async Task<List<OrderSummary>> GetOrderSummariesAsync(int customerId)
{
    await using var conn = new SqlConnection(_connectionString);
    await conn.OpenAsync();

    await using var cmd = conn.CreateCommand();
    cmd.CommandText = """
        SELECT o.Id, o.ReferenceNumber, o.TotalAmount, c.Name AS CustomerName
        FROM Orders o
        INNER JOIN Customers c ON c.Id = o.CustomerId
        WHERE o.CustomerId = @CustomerId
        ORDER BY o.CreatedAt DESC
        """;

    cmd.Parameters.AddWithValue("@CustomerId", customerId);

    var results = new List<OrderSummary>();
    await using var reader = await cmd.ExecuteReaderAsync();

    while (await reader.ReadAsync())
    {
        results.Add(new OrderSummary(
            Id:           reader.GetInt32(0),
            Reference:    reader.GetString(1),
            Total:        reader.GetDecimal(2),
            CustomerName: reader.GetString(3)));
    }

    return results;
}

dotnet add package Dapper

// Register the connection factory
builder.Services.AddScoped<IDbConnection>(sp =>
    new SqlConnection(sp.GetRequiredService<IConfiguration>()
        .GetConnectionString("DefaultConnection")));

// Query<T> — map a SELECT to a list of objects
public async Task<IEnumerable<Order>> GetOrdersAsync(int customerId)
{
    const string sql = """
        SELECT Id, ReferenceNumber, TotalAmount, Status, CreatedAt
        FROM Orders
        WHERE CustomerId = @CustomerId
        ORDER BY CreatedAt DESC
        """;

    return await _db.QueryAsync<Order>(sql, new { CustomerId = customerId });
}

// QueryFirstOrDefaultAsync — single result
public async Task<Order?> GetByIdAsync(int id)
{
    const string sql = "SELECT * FROM Orders WHERE Id = @Id";
    return await _db.QueryFirstOrDefaultAsync<Order>(sql, new { Id = id });
}

// ExecuteAsync — INSERT, UPDATE, DELETE
public async Task<int> UpdateStatusAsync(int orderId, string status)
{
    const string sql = "UPDATE Orders SET Status = @Status WHERE Id = @Id";
    return await _db.ExecuteAsync(sql, new { Id = orderId, Status = status });
}

// ExecuteScalarAsync — single value result
public async Task<int> GetOrderCountAsync(int customerId)
{
    const string sql = "SELECT COUNT(*) FROM Orders WHERE CustomerId = @CustomerId";
    return await _db.ExecuteScalarAsync<int>(sql, new { CustomerId = customerId });
}

// Map a JOIN result to nested objects
public async Task<IEnumerable<OrderWithCustomer>> GetOrdersWithCustomersAsync()
{
    const string sql = """
        SELECT o.Id, o.ReferenceNumber, o.TotalAmount,
               c.Id, c.Name, c.Email
        FROM Orders o
        INNER JOIN Customers c ON c.Id = o.CustomerId
        """;

    return await _db.QueryAsync<Order, Customer, OrderWithCustomer>(
        sql,
        (order, customer) => new OrderWithCustomer(order, customer),
        splitOn: "Id"   // second "Id" column is where Customer starts
    );
}

public record OrderWithCustomer(Order Order, Customer Customer);

// One round-trip, multiple result sets
public async Task<(IEnumerable<Order> Orders, int TotalCount)> GetPagedAsync(
    int page, int pageSize)
{
    const string sql = """
        SELECT * FROM Orders ORDER BY CreatedAt DESC
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;

        SELECT COUNT(*) FROM Orders;
        """;

    using var multi = await _db.QueryMultipleAsync(sql,
        new { Offset = (page - 1) * pageSize, PageSize = pageSize });

    var orders = await multi.ReadAsync<Order>();
    var total  = await multi.ReadFirstAsync<int>();

    return (orders, total);
}

// Call a stored procedure with output parameters
public async Task<int> CreateOrderSpAsync(CreateOrderParams parameters)
{
    var dynamicParams = new DynamicParameters(parameters);
    dynamicParams.Add("@NewOrderId", dbType: DbType.Int32, direction: ParameterDirection.Output);

    await _db.ExecuteAsync(
        "dbo.usp_CreateOrder",
        dynamicParams,
        commandType: CommandType.StoredProcedure);

    return dynamicParams.Get<int>("@NewOrderId");
}

// ❌ EF Core bulk insert — 10,000 rows = very slow (change tracker overhead)
_db.Products.AddRange(products);
await _db.SaveChangesAsync();   // fires 10,000 individual INSERT statements

// ✅ Option 1: EF Core Bulk Extensions
dotnet add package EFCore.BulkExtensions
await _db.BulkInsertAsync(products);            // one SQL statement
await _db.BulkUpdateAsync(products);
await _db.BulkDeleteAsync(products);

// ✅ Option 2: SqlBulkCopy (raw ADO.NET) — fastest possible
public async Task BulkInsertOrdersAsync(IEnumerable<Order> orders)
{
    using var dataTable = ToDataTable(orders);
    await using var bulkCopy = new SqlBulkCopy(_connectionString)
    {
        DestinationTableName = "Orders",
        BatchSize            = 1000,
    };
    await bulkCopy.WriteToServerAsync(dataTable);
}

// ✅ Option 3: EF Core ExecuteUpdate/ExecuteDelete (EF 7+) — set-based, no loading
// Update all pending orders older than 7 days to Cancelled — one SQL UPDATE
await _db.Orders
    .Where(o => o.Status == "Pending" && o.CreatedAt < DateTime.UtcNow.AddDays(-7))
    .ExecuteUpdateAsync(s => s.SetProperty(o => o.Status, "Cancelled"));

// Delete all archived records — one SQL DELETE
await _db.AuditLogs
    .Where(l => l.CreatedAt < DateTime.UtcNow.AddMonths(-6))
    .ExecuteDeleteAsync();

Use EF Core when:
✅ Standard CRUD (Create, Read, Update, Delete)
✅ You want change tracking + SaveChanges
✅ Relationships and navigation properties (Include)
✅ Migrations to manage schema
✅ Most of your queries are simple WHERE + JOIN

Use Dapper when:
✅ Complex reporting queries (GROUP BY, window functions, CTEs)
✅ You need full SQL control
✅ The EF Core-generated SQL isn't performing well
✅ Stored procedures with output parameters
✅ You need the absolute minimum overhead on a hot read path

Use Raw ADO.NET when:
✅ Bulk inserts (SqlBulkCopy)
✅ Custom connection management
✅ You're building your own data access library
✅ You need streaming large result sets without buffering

Mix all three in the same project:
✅ Use EF Core for commands (write operations)
✅ Use Dapper for complex query reads
✅ Use SqlBulkCopy for imports/batch operations

// Register EF Core for writes
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString));

// Register Dapper's IDbConnection for reads
builder.Services.AddScoped<IDbConnection>(_ =>
    new SqlConnection(connectionString));

// Command handler — uses EF Core (change tracking, domain events)
public class ConfirmOrderCommandHandler : IRequestHandler<ConfirmOrderCommand>
{
    private readonly AppDbContext _db;

    public async Task Handle(ConfirmOrderCommand cmd, CancellationToken ct)
    {
        var order = await _db.Orders.FindAsync(cmd.OrderId, ct)
            ?? throw new NotFoundException(cmd.OrderId);

        order.Confirm();               // domain logic, raises events
        await _db.SaveChangesAsync(ct); // persists + dispatches events
    }
}

// Query handler — uses Dapper (no tracking, complex SQL, fast)
public class GetOrderReportQueryHandler : IRequestHandler<GetOrderReportQuery, OrderReportDto>
{
    private readonly IDbConnection _db;

    public async Task<OrderReportDto> Handle(GetOrderReportQuery query, CancellationToken ct)
    {
        const string sql = """
            WITH MonthlyRevenue AS (
                SELECT
                    YEAR(CreatedAt)  AS Year,
                    MONTH(CreatedAt) AS Month,
                    SUM(TotalAmount) AS Revenue,
                    COUNT(*)         AS OrderCount
                FROM Orders
                WHERE Status = 'Confirmed'
                  AND CreatedAt >= @StartDate
                GROUP BY YEAR(CreatedAt), MONTH(CreatedAt)
            )
            SELECT Year, Month, Revenue, OrderCount,
                   SUM(Revenue) OVER (ORDER BY Year, Month) AS CumulativeRevenue
            FROM MonthlyRevenue
            ORDER BY Year, Month
            """;

        var rows = await _db.QueryAsync<MonthlyRevenueRow>(
            sql, new { StartDate = query.StartDate });

        return new OrderReportDto(rows.ToList());
    }
}