Dapper & Raw SQL

Key Points

• Dapper is a tiny micro-ORM: maps SQL results to POCOs without change tracking. Faster than EF for read-heavy workloads.
• Use Dapper for: complex SQL, reports, hot paths, when EF Core's translation hurts more than it helps.
• Use EF Core's raw SQL (FromSqlRaw, ExecuteSqlRaw) when you want to mix SQL with EF tracking/projection.
• Always parameterize to prevent SQL injection. @param syntax in Dapper; {0}/{1} in EF's FromSqlInterpolated.
• Don't drop to raw SQL prematurely — EF Core's optimizer is good. Profile first.

Concepts (deep dive)

Dapper basics

using var conn = new SqlConnection(connStr);
await conn.OpenAsync();

// Single
var customer = await conn.QuerySingleOrDefaultAsync<Customer>(
    "SELECT * FROM Customers WHERE Id = @Id", new { Id = 1 });

// Many
var orders = await conn.QueryAsync<Order>(
    "SELECT TOP 100 * FROM Orders WHERE CreatedAt > @Since", new { Since = DateTime.UtcNow.AddDays(-7) });

// Multi-mapping (joined entities)
var ordersWithCustomer = await conn.QueryAsync<Order, Customer, Order>(
    @"SELECT o.*, c.* FROM Orders o JOIN Customers c ON o.CustomerId = c.Id WHERE o.Id = @Id",
    (order, cust) => { order.Customer = cust; return order; },
    new { Id = 1 },
    splitOn: "Id");

// Execute (INSERT/UPDATE/DELETE)
var rows = await conn.ExecuteAsync(
    "UPDATE Orders SET Status = @Status WHERE Id = @Id",
    new { Status = "Shipped", Id = 42 });

Dapper is allocation-light, no change tracking, no LINQ translation — just SQL → POCO. Often 2-3× faster than EF for the same query.

When to choose Dapper over EF

• Reports / aggregations — complex SQL, EF translates poorly.
• Hot read paths — minimize allocation.
• Stored procedures — conn.QueryAsync<T>("sp_GetSomething", new { ... }, commandType: CommandType.StoredProcedure).
• Dynamic SQL — query-string-driven shape.

EF Core raw SQL — when EF tracking matters

var orders = await db.Orders
    .FromSqlInterpolated($"SELECT * FROM Orders WHERE CreatedAt > {since}")
    .ToListAsync();
// Tracked entities; can SaveChanges them

FromSqlInterpolated is parameterized (the interpolation produces parameters, not literal SQL). Don't use FromSqlRaw($"... {since}") — that's SQL injection.

// Compose with LINQ:
var recent = await db.Orders
    .FromSqlInterpolated($"SELECT * FROM Orders WHERE Status = {status}")
    .Where(o => o.Total > 100)
    .ToListAsync();

EF wraps the raw SQL as a subquery; LINQ operations apply on top.

ExecuteSqlRaw / ExecuteSqlInterpolated

var rows = await db.Database.ExecuteSqlInterpolatedAsync(
    $"UPDATE Orders SET Archived = 1 WHERE CreatedAt < {cutoff}");

Direct execution, no entity loading. EF Core 7+'s ExecuteUpdateAsync is often cleaner — but for complex SQL not expressible in LINQ, raw is the answer.

Stored procedures

// Dapper:
var results = await conn.QueryAsync<Order>(
    "GetOrdersByCustomer",
    new { CustomerId = 42 },
    commandType: CommandType.StoredProcedure);

// EF:
var results = await db.Orders
    .FromSqlInterpolated($"EXEC GetOrdersByCustomer @CustomerId={customerId}")
    .ToListAsync();

SQL injection — always parameterize

// ❌ SQL injection
var sql = $"SELECT * FROM Users WHERE Name = '{name}'";

// ✅ Parameterized
var sql = "SELECT * FROM Users WHERE Name = @Name";
await conn.QueryAsync(sql, new { Name = name });

EF's FromSqlInterpolated automatically parameterizes interpolated values. Dapper's anonymous-object args also parameterize.

Dynamic parameters

var p = new DynamicParameters();
p.Add("@Status", status);
if (customerId.HasValue) p.Add("@CustomerId", customerId.Value);

var sql = "SELECT * FROM Orders WHERE Status = @Status"
       + (customerId.HasValue ? " AND CustomerId = @CustomerId" : "");

var orders = await conn.QueryAsync<Order>(sql, p);

For optional parameters, build SQL conditionally with parameterized values.

Connection lifetime

// Per-call (Dapper):
await using var conn = new SqlConnection(connStr);
await conn.OpenAsync();
/* ... */
// Disposed on scope exit; goes back to pool

// Or use EF's connection (when mixing):
await db.Database.OpenConnectionAsync();
var conn = db.Database.GetDbConnection();
var orders = await conn.QueryAsync<Order>(sql);

Connection pooling means new SqlConnection is cheap (it grabs from the pool).

TVP and table-valued parameters

var dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
foreach (var id in ids) dt.Rows.Add(id);

await conn.QueryAsync<Customer>(
    "SELECT * FROM Customers WHERE Id IN (SELECT Id FROM @Ids)",
    new { Ids = dt.AsTableValuedParameter("dbo.IntList") });

For SQL Server: TVPs let you pass arrays/lists efficiently. EF Core 10+ improves the IN story; before that, TVPs are the answer for large lists.

Code: correct vs wrong

❌ Wrong: SQL injection

var sql = $"SELECT * FROM Users WHERE Name = '{name}'";   // bad

✅ Correct: parameterize

await conn.QueryAsync<User>("SELECT * FROM Users WHERE Name = @Name", new { Name = name });

❌ Wrong: dropping to Dapper for a simple LINQ-translatable query

var orders = conn.Query<Order>("SELECT * FROM Orders WHERE IsActive = 1");

If the query translates fine in EF, EF's tracking and async story is usually clearer. Use Dapper when EF struggles.

❌ Wrong: not disposing connection

var conn = new SqlConnection(connStr);
conn.Open();
// ... query ...
// Connection leak — never returned to pool

✅ Correct

await using var conn = new SqlConnection(connStr);
await conn.OpenAsync();
/* ... */

Design patterns for this topic

Pattern 1 — "EF for writes; Dapper for reads"

• Intent: EF's tracking helps with mutations; Dapper's speed helps with reports.

Pattern 2 — "FromSqlInterpolated for raw + tracking"

• Intent: custom SQL with EF-managed entities.

Pattern 3 — "Parameterize always"

• Intent: SQL injection defense.

Pattern 4 — "Stored procs for legacy / DBA-managed schemas"

• Intent: respect existing SQL boundaries.

Pattern 5 — "TVPs for large IN lists (pre-EF 10)"

• Intent: efficient batch lookups.

Pros & cons / trade-offs

When to use / when to avoid

• Use EF Core as default.
• Use Dapper for reports, hot reads, complex SQL.
• Use FromSqlInterpolated when you want raw SQL + EF tracking.
• Avoid string concatenation for SQL — always parameterize.
• Avoid Dapper everywhere — you lose EF's developer ergonomics.

Interview Q&A

Q1. When use Dapper over EF Core? For reports, complex SQL, hot read paths, stored procs. EF for typical CRUD with tracking.

Q2. What's FromSqlInterpolated? EF method that parameterizes interpolated SQL: $"... WHERE Id = {id}" becomes WHERE Id = @p0. Returns tracked entities.

Q3. Why not FromSqlRaw($"...{x}")? Bare interpolation = SQL injection. Raw doesn't parameterize.

Q4. What's a TVP? SQL Server table-valued parameter. Pass a DataTable as a single parameter representing a list. Better than million-element IN clauses.

Q5. How does Dapper handle splits in JOINs? splitOn: "Id" tells Dapper where one entity ends and the next begins in the result set. Maps each segment to a different type.

Q6. Why is Dapper faster than EF for the same query? No LINQ translation, no change tracking, smaller object graph (just POCOs). For reads, the difference is real.

Q7. Can you mix Dapper and EF in one transaction? Yes — share IDbConnection and IDbTransaction. EF's db.Database.GetDbConnection() and BeginTransaction participate.

Q8. How does Dapper deal with multiple result sets? QueryMultipleAsync returns a reader; call ReadAsync<T>() once per result set.

Q9. What's ExecuteSqlRaw? EF method to run a non-query SQL command (UPDATE/DELETE/exec). For complex SQL not expressible in LINQ.

Q10. How do you handle nullable parameters in Dapper? new { Id = id ?? (object?)DBNull.Value }. Or use DynamicParameters with explicit nullability.

Gotchas / common mistakes

• ⚠️ String concat in SQL — injection.
• ⚠️ FromSqlRaw with interpolation — looks safe but isn't.
• ⚠️ Connection leak — forgetting to dispose.
• ⚠️ Dapper for everything — lose EF's mutation tracking.
• ⚠️ TVP type mismatch — must match the user-defined SQL type definition.
• ⚠️ Mixing query and stored proc parameter modes — easy to mix up.

Further reading

• Dapper
• EF Core raw SQL
• ExecuteSqlInterpolated