Question 1

What is Dapper and why use it?

Accepted Answer

In short: A micro-ORM: extension methods on IDbConnection that execute SQL you write and map result rows to objects automatically. It s almost as fast as raw ADO.NET with far less code, gives full control over SQL (stored procedures, CTEs, window functions), and has no change tracking or migrations. Ideal for read-heavy and performance-critical data access and for teams comfortable with SQL.

Question 2

Dapper vs Entity Framework Core?

Accepted Answer

In short: Dapper: you write SQL; minimal overhead; no change tracking, relationships, migrations or LINQ translation — perfect for reports and tuned queries. EF Core: LINQ queries translated to SQL, change tracking and SaveChanges , relationships, migrations, interceptors — faster to build CRUD and domain models, some overhead. Many projects use both: EF Core for writes, Dapper for complex reads.

Question 3

How does Dapper prevent SQL injection?

Accepted Answer

In short: When you pass values in the parameter object ( new { Id = id } ) or DynamicParameters , Dapper creates real DbParameter s, so the database treats them as data, never as SQL. Injection is only possible if you concatenate user input into the SQL string — never do that; whitelist identifiers like sort columns.

Question 4

Difference between Query, QueryFirst, QuerySingle and Execute?

Accepted Answer

In short: Query returns all rows ( IEnumerable T ). QueryFirst returns the first row and throws if none ( OrDefault returns default). QuerySingle expects exactly one row and throws on zero or multiple ( OrDefault allows zero). Execute runs a command and returns the number of affected rows; ExecuteScalar returns the first column of the first row.

Question 5

How do you call a stored procedure with output parameters in Dapper?

Accepted Answer

Create DynamicParameters , add inputs and add outputs with direction: ParameterDirection.Output (and ReturnValue if needed), call Query / Execute with commandType: CommandType.StoredProcedure , then read outputs with p.Get T ( @Name ) after the results have been consumed.

Question 6

What is multi-mapping and what does splitOn do?

Accepted Answer

In short: Mapping one row from a JOIN into several related objects: Query Order, Customer, Order (sql, (o, c) = { o.Customer = c; return o; }, splitOn: Id ) . splitOn tells Dapper at which column the next object s columns begin (default Id ). For one-to-many, use a dictionary to de-duplicate parent objects and add children.

Question 7

How do you use transactions with Dapper?

Accepted Answer

In short: Open the connection, BeginTransaction , pass the transaction to every Dapper call ( transaction: tx or positional argument), Commit on success and Rollback in a catch (or let Dispose roll back). For multiple repositories in one operation, share the connection and transaction through a unit-of-work object. TransactionScope also works (with async flow enabled).

Question 8

How do you insert thousands of rows efficiently?

Accepted Answer

In short: Not by passing a list to Execute (one round trip per row). Use SqlBulkCopy for raw speed, a table-valued parameter to a stored procedure (or MERGE ) for set-based logic, or JSON + OPENJSON in one statement. Batch very large loads and use transactions appropriately.

Question 9

What is buffered vs unbuffered querying in Dapper?

Accepted Answer

In short: Buffered (default) reads all rows into a list before returning and closes the reader — simple and safe. Unbuffered ( buffered: false or QueryUnbufferedAsync ) yields rows while reading, keeping the reader and connection open until enumeration finishes — lower memory for huge results, but you must finish iterating before using the connection again.

Question 10

How does Dapper map columns to properties and how do you handle mismatched names?

Accepted Answer

In short: By name, case-insensitive, to public properties/fields or constructor parameters. For mismatches use SQL aliases ( SELECT product_name AS Name ), DefaultTypeMap.MatchNamesWithUnderscores = true , or a custom type map ( SqlMapper.SetTypeMap with CustomPropertyTypeMap ). Custom value conversions use SqlMapper.TypeHandler T .

Question 11

How does Dapper achieve its performance?

Accepted Answer

In short: It generates and caches mapping code per (SQL, parameter type, result type) the first time a query runs — IL-emitted delegates that read the data reader directly into objects and write parameters — so later calls avoid reflection. It adds very little on top of ADO.NET: no change tracking, no expression translation. Dapper.AOT moves this generation to compile time with source generators.

Question 12

How do you manage connections with Dapper in ASP.NET Core?

Accepted Answer

In short: Register a connection factory (singleton) that creates new SqlConnection s; create, use and dispose a connection per operation ( await using ), relying on connection pooling. For multi-step transactions use a scoped unit of work holding one connection and transaction. Avoid keeping connections open for the whole request or sharing one connection across threads.

Question 13

A Dapper search endpoint is slow even though the same SQL is fast in SSMS. What could cause it?

Accepted Answer

In short: Different parameter types: Dapper sends string as NVARCHAR , and if the column is VARCHAR SQL Server converts the column, preventing an index seek — use DbString { IsAnsi = true } . Parameter sniffing: SSMS runs with literal values while the app reuses a plan compiled for different values — check the cached plan; consider OPTION (RECOMPILE) or better indexes. Also different SET options (ARITHABORT) produce different plans, and the app may be returning far more rows/columns or doing N+1 calls.

Question 14

Your team builds SQL with string interpolation for filters in Dapper. What problems does this cause and how do you fix it?

Accepted Answer

In short: SQL injection risk, no plan reuse in SQL Server and an ever-growing Dapper query cache (each distinct SQL string is cached) → memory growth. Fix: parameterise all values, build optional filters with WHERE (@x IS NULL OR Col = @x) or a safe builder (DapperQueryBuilder / SqlBuilder ) that appends parameterised fragments, and whitelist dynamic identifiers.

Question 15

How would you migrate a large ADO.NET DataReader-based data layer to Dapper?

Accepted Answer

In short: Keep the SQL and stored procedures; replace manual reader loops with Query T mapped to DTOs, DynamicParameters for procedures with outputs, and QueryMultiple for multi-result procedures. Do it repository by repository behind existing interfaces, with integration tests comparing outputs before and after. Watch column-name mismatches and type differences (DBNull handling, decimal/money, dates).

Dapper — The Micro-ORM from Basics to Advanced