EF Core is my default. Dapper is my scalpel.
When I reach for Dapper: real-world lessons and examples — this phrase captures something we've learned across hundreds of production systems. Entity Framework Core handles 95% of our data access beautifully, but there are moments when reaching for raw SQL through Dapper makes the difference between a query that takes 500ms and one that takes 5ms. This article walks through when, why, and how we use Dapper alongside EF Core, with patterns we've battle-tested in e-commerce, trading, and SaaS applications.
Complex reporting queries with CTEs and window functions
EF Core's LINQ is expressive, but when you're building a financial dashboard or an analytics report, you often need window functions, recursive CTEs, or dense ranking that feels awkward in LINQ. We've found that writing the SQL directly and mapping results to a simple DTO is cleaner and more maintainable than fighting the ORM.
Consider a real scenario: you're building a sales performance report where you need the running total for each salesperson, their rank within their region, and how they compare to the regional average. This requires SUM() OVER (PARTITION BY...) and RANK() OVER (ORDER BY...). In LINQ, this feels forced. In SQL, it's clear.
public class SalesPerformanceRow
{
public int SalesPersonId { get; set; }
public string Name { get; set; }
public string Region { get; set; }
public decimal DailyTotal { get; set; }
public decimal RunningTotal { get; set; }
public int RankInRegion { get; set; }
public decimal RegionalAverage { get; set; }
}
public async Task<List<SalesPerformanceRow>> GetSalesPerformanceReport(int regionId)
{
using var conn = new SqlConnection(_connectionString);
await conn.OpenAsync();
var query = @"
WITH regional_stats AS (
SELECT
SalesPersonId,
Name,
Region,
DATE(OrderDate) as OrderDay,
SUM(Amount) as DailyTotal,
SUM(SUM(Amount)) OVER (
PARTITION BY SalesPersonId
ORDER BY DATE(OrderDate)
) as RunningTotal,
RANK() OVER (
PARTITION BY Region
ORDER BY SUM(Amount) DESC
) as RankInRegion
FROM Orders o
JOIN SalesPeople sp ON o.SalesPersonId = sp.Id
WHERE sp.RegionId = @regionId
GROUP BY SalesPersonId, Name, Region, DATE(OrderDate)
),
region_average AS (
SELECT RegionId, AVG(Amount) as AvgAmount
FROM Orders o
JOIN SalesPeople sp ON o.SalesPersonId = sp.Id
GROUP BY RegionId
)
SELECT
rs.SalesPersonId,
rs.Name,
rs.Region,
rs.DailyTotal,
rs.RunningTotal,
rs.RankInRegion,
ra.AvgAmount as RegionalAverage
FROM regional_stats rs
JOIN region_average ra ON rs.Region = ra.RegionId
ORDER BY OrderDay DESC, RankInRegion ASC";
return (await conn.QueryAsync<SalesPerformanceRow>(query, new { regionId })).ToList();
}
This query would be significantly more complex in LINQ. By using Dapper, we keep the SQL readable and maintainable. The mapping from query results to the DTO is automatic and type-safe.
Hot read paths where every millisecond counts
In a ride-hailing system, when a driver opens the app and we need to fetch available rides within their radius, latency matters. In a trading platform, fetching live market data for a dashboard must be fast. In e-commerce, the product listing page needs to render in under 100ms. These are hot paths, and here's where Dapper shines.
Dapper's overhead is minimal because it does one thing: execute SQL and map rows to objects. There's no query compilation, no change tracking, no identity map. We've seen performance improvements of 40-60% on hot paths by switching from EF Core to Dapper, not because EF Core is slow, but because we don't need what it provides.
public class AvailableRideDto
{
public int RideId { get; set; }
public decimal PickupLat { get; set; }
public decimal PickupLng { get; set; }
public decimal DropoffLat { get; set; }
public decimal DropoffLng { get; set; }
public decimal EstimatedFare { get; set; }
public int PassengerCount { get; set; }
public DateTime CreatedAt { get; set; }
}
public async Task<List<AvailableRideDto>> GetNearbyRides(decimal driverLat, decimal driverLng, int radiusKm)
{
using var conn = new SqlConnection(_connectionString);
await conn.OpenAsync();
var query = @"
SELECT TOP 50
RideId,
PickupLat,
PickupLng,
DropoffLat,
DropoffLng,
EstimatedFare,
PassengerCount,
CreatedAt
FROM Rides
WHERE Status = 'Pending'
AND GEOGRAPHY::Point(@driverLat, @driverLng, 4326).STDistance(
GEOGRAPHY::Point(PickupLat, PickupLng, 4326)
) <= @radiusMeters
ORDER BY CreatedAt DESC";
return (await conn.QueryAsync<AvailableRideDto>(
query,
new {
driverLat,
driverLng,
radiusMeters = radiusKm * 1000
}
)).ToList();
}
This query runs multiple times per second. The difference between 10ms and 15ms per query is the difference between a smooth user experience and one that feels sluggish. Dapper's lightweight overhead matters here.
Both can share the same connection and transaction
A common misconception is that you must choose: either use EF Core everywhere or Dapper everywhere. In reality, they can coexist in the same business transaction. You can use EF Core for your domain logic, then hand off to Dapper for a specific reporting query, all within one transaction.
Here's a practical example from an e-commerce platform where we process an order:
public async Task<OrderConfirmation> ProcessOrder(CreateOrderRequest request, int customerId)
{
using var conn = new SqlConnection(_connectionString);
await conn.OpenAsync();
using var tx = conn.BeginTransaction();
try
{
// Use EF Core for domain logic
var customer = await _dbContext.Customers.FindAsync(customerId);
var orderEntity = new Order
{
CustomerId = customerId,
OrderDate = DateTime.UtcNow,
Status = OrderStatus.Processing
};
foreach (var item in request.Items)
{
var product = await _dbContext.Products.FindAsync(item.ProductId);
orderEntity.Items.Add(new OrderItem
{
ProductId = item.ProductId,
Quantity = item.Quantity,
UnitPrice = product.Price
});
}
_dbContext.Orders.Add(orderEntity);
await _dbContext.SaveChangesAsync();
// Use Dapper for the complex reporting insert
var inventoryCheckQuery = @"
INSERT INTO OrderInventoryLog (OrderId, Timestamp, InventoryStatus)
SELECT @orderId, GETUTCDATE(),
CASE
WHEN SUM(oi.Quantity) > SUM(p.StockLevel) THEN 'Insufficient'
ELSE 'Available'
END
FROM OrderItems oi
JOIN Products p ON oi.ProductId = p.Id
WHERE oi.OrderId = @orderId";
await conn.ExecuteAsync(inventoryCheckQuery, new { orderId = orderEntity.Id }, tx);
tx.Commit();
return new OrderConfirmation { OrderId = orderEntity.Id, Status = "Created" };
}
catch
{
tx.Rollback();
throw;
}
}
Notice how EF Core and Dapper share the same connection and transaction. The order is created and tracked by EF Core, then we use Dapper to insert into an audit table based on a complex calculation. Both operations are atomic — if either fails, the whole transaction rolls back.
Raw SQL is fine — as long as it is parameterized
The rule is absolute: never concatenate user input into SQL strings. Always parameterize. Dapper makes this frictionless with its parameter syntax.
When you write:
var rows = await conn.QueryAsync<SalesRow>(
"select day, sum(total) from orders " +
"where tenant_id = @t group by day",
new { t = tenantId });
The @t placeholder is replaced with a parameterized query. The database receives the SQL structure separately from the data. SQL injection is impossible.
Common safe patterns:
- Simple parameters:
new { tenantId, status = "Active" } - Lists: Use
IN (@ids)with a parameter that's a collection, and Dapper expands it correctly - Dynamic conditions: Build the parameter object conditionally, never the SQL string
// Safe: parameterized even with dynamic filters
public async Task<List<Order>> SearchOrders(int tenantId, string status = null, DateTime? after = null)
{
using var conn = new SqlConnection(_connectionString);
var query = "SELECT * FROM Orders WHERE TenantId = @tenantId";
var parameters = new DynamicParameters(new { tenantId });
if (!string.IsNullOrEmpty(status))
{
query += " AND Status = @status";
parameters.Add("status", status);
}
if (after.HasValue)
{
query += " AND CreatedAt >= @after";
parameters.Add("after", after.Value);
}
return (await conn.QueryAsync<Order>(query, parameters)).ToList();
}
Common mistakes
- Forgetting to parameterize: String concatenation with user input is the most dangerous mistake. Always use
@paramNamesyntax. - Not disposing connections: Always use
usingstatements or async patterns to ensure connections return to the pool. - Mixing queries and commands without consistency: If you're querying with Dapper, consider whether writes should use Dapper too for consistency, or if EF Core makes sense for your domain model.
- Ignoring NULL handling in DTOs: SQL NULLs map to nullable types. Make sure your DTO properties are nullable if the column can be NULL.
- Over-using Dapper: Just because you can write raw SQL doesn't mean you should everywhere. EF Core's tracking and lazy loading are valuable for transactional workloads.
Key takeaways
- Use EF Core by default for transactional workloads; it handles domain logic, change tracking, and relationships elegantly.
- Reach for Dapper when you need complex window functions, CTEs, or aggregations that are cleaner in SQL than in LINQ.
- Use Dapper on hot read paths where minimal overhead and maximum throughput matter, especially dashboards and real-time feeds.
- EF Core and Dapper can coexist in the same transaction on the same connection — they're not exclusive choices.
- Always parameterize SQL, never concatenate user input. Dapper's syntax makes this natural and painless.
- Dapper is a micro-ORM; use it for specific queries or scenarios, not as a wholesale replacement for EF Core.
Comments
Loading comments…