Dapper: Same SQL, a Fraction of the Code – Data Access | Lesson 58
Introduction
In Lesson 57, copying one student row into a C# object took about 25 lines of ADO.NET. Multiply that by 22 tables, and most of your data code would be copying columns by hand.
Dapper is a small library that does that copying for you. You still write the SQL; Dapper sends your parameters, runs the query, and turns each row into an object. It was built by the team behind a very busy question-and-answer website, where every millisecond mattered.
By the end of this lesson, you will be able to:
- Add Dapper to a project and run queries with
QueryAsyncandExecuteScalarAsync. - Pass parameters as objects, with the right SQL types.
- Map rows to classes, records, and DTOs, and know Dapper's mapping rules.
- Call stored procedures with output parameters using
DynamicParameters. - Map joins with multi-mapping, and run several queries in one round trip.
Why this matters
Dapper sits between ADO.NET and EF Core. It keeps the control and speed of hand-written SQL, and removes the boring, error-prone copying.
That is why many teams use it for reports and dashboards, even when the rest of the application uses EF Core. In this lesson, Dapper was as fast as hand-written ADO.NET on SchoolDb, with far less code to write and test.
The concept
Example: what a micro-ORM is
An ORM (object-relational mapper) connects database tables to C# objects. A full ORM, such as EF Core, writes the SQL, tracks changes, and saves them. A micro-ORM, such as Dapper, only maps: you write every SQL statement yourself.
Think of the school office answering a request. With ADO.NET, you read the answer aloud and copy it into your form, field by field.
With Dapper, a helper fills in the form for you, but you still write the question. With EF Core, you only say what you want, and someone else writes the question too.
| Tool | Who writes the SQL | Who copies rows into objects | Tracks changes |
|---|---|---|---|
| ADO.NET | You | You | No |
| Dapper | You | Dapper | No |
| EF Core | EF Core (you can still write it) | EF Core | Yes |
Example: the main methods
Dapper adds extension methods (Lesson 45) to every connection, so you call them on a
SqlConnection. Each has an Async version:
| Method | Returns | Use for |
|---|---|---|
QueryAsync<T> | Many T objects | Lists and searches |
QuerySingleOrDefaultAsync<T> | One T, or null if no row; throws if two | Find by a unique key |
QueryFirstOrDefaultAsync<T> | The first T, or null | "Any one row" queries |
ExecuteScalarAsync<T> | One value | COUNT(*), MAX(...) |
ExecuteAsync | Rows changed | INSERT, UPDATE, DELETE, procedures |
QueryMultipleAsync | A reader over several results | Several SELECTs in one round trip |
Dapper also opens a closed connection for you, and closes it again when the call ends.
Example: Dapper's mapping rules
Dapper matches each column to a property with the same name, ignoring upper and lower case. A
NULL becomes null, so there are no DBNull checks.
Columns with no matching property are ignored, and properties with no column keep their default value. Neither causes an error, which is convenient and also dangerous, as the common mistakes show.
A class needs a constructor with no parameters and property setters. Records and other classes without one are filled through a constructor whose parameters match the columns by name, type, and order.
Apply it to the SMS
Step 1: add Dapper
dotnet add src/SchoolApp.Data package Dapper
<Project Sdk="Microsoft.NET.Sdk">
<!-- Lesson 57: the data layer. It knows about SchoolApp.Core; Core never knows about it. -->
<ItemGroup>
<ProjectReference Include="..\SchoolApp.Core\SchoolApp.Core.csproj" />
</ItemGroup>
<!-- Lesson 57: the SQL Server driver for .NET. 6.1.x is the version EF Core 10 uses (Lesson 59).
Lesson 58: Dapper, which maps query results to objects. -->
<ItemGroup>
<PackageReference Include="Dapper" Version="2.1.89" />
<PackageReference Include="Microsoft.Data.SqlClient" Version="6.1.7" />
</ItemGroup>
<PropertyGroup>
<TargetFramework>net10.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
</Project>
Step 2: the same repository, with Dapper
DapperStudentRepository has the same four operations as the ADO.NET version, plus one new one.
The SQL is unchanged; the code around it shrinks.
Show src/SchoolApp.Data/Dapper/DapperStudentRepository.cs
using System.Data;
using Dapper;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;
namespace SchoolApp.Data.Dapper;
// Lesson 58: the same operations as AdoNetStudentRepository (Lesson 57), written with Dapper.
// The SQL is the same; Dapper adds the parameters and copies each row into an object.
public class DapperStudentRepository
{
private const string SelectStudent = """
SELECT Id, AdmissionNumber, FirstName, LastName, DateOfBirth, Gender,
AdmissionDate, Address, Status, CreatedAt, CreatedBy, UpdatedAt, UpdatedBy
FROM dbo.Students
""";
private readonly SqlConnectionFactory _connections;
public DapperStudentRepository(SqlConnectionFactory connections)
{
_connections = connections;
}
// Dapper opens the connection if it is closed, and closes it again afterwards.
public async Task<int> CountActiveAsync(CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
return await connection.ExecuteScalarAsync<int>(new CommandDefinition(
"SELECT COUNT(*) FROM dbo.Students WHERE Status = 'Active' AND IsDeleted = 0;",
cancellationToken: cancellationToken));
}
// Parameters come from an object: @AdmissionNumber gets the AdmissionNumber property's value.
// DbString with IsAnsi = true sends VARCHAR(15), the column's real type (Lesson 57).
public async Task<Student?> GetByAdmissionNumberAsync(string admissionNumber, CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
StudentRow? row = await connection.QuerySingleOrDefaultAsync<StudentRow>(new CommandDefinition(
SelectStudent + " WHERE AdmissionNumber = @AdmissionNumber AND IsDeleted = 0;",
new { AdmissionNumber = new DbString { Value = admissionNumber, IsAnsi = true, Length = 15 } },
cancellationToken: cancellationToken));
return row?.ToStudent();
}
public async Task<List<Student>> SearchByLastNameAsync(string lastName, CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
IEnumerable<StudentRow> rows = await connection.QueryAsync<StudentRow>(new CommandDefinition(
SelectStudent + " WHERE LastName = @LastName AND IsDeleted = 0 ORDER BY Id;",
new { LastName = lastName }, // a C# string is sent as NVARCHAR, which matches this column
cancellationToken: cancellationToken));
return rows.Select(row => row.ToStudent()).ToList();
}
// A stored procedure: commandType tells Dapper the text is a procedure name.
// ClassListEntryDto is a record; Dapper fills it through its constructor.
public async Task<List<ClassListEntryDto>> GetClassListAsync(int classId, string sectionName, CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
IEnumerable<ClassListEntryDto> entries = await connection.QueryAsync<ClassListEntryDto>(new CommandDefinition(
"dbo.usp_GetClassList",
new { ClassId = classId, SectionName = new DbString { Value = sectionName, IsAnsi = true, Length = 2 } },
commandType: CommandType.StoredProcedure,
cancellationToken: cancellationToken));
return entries.ToList();
}
// Multi-mapping: each row holds an enrollment AND its student. splitOn names the column
// where the second object starts; the map function joins the two objects together.
public async Task<List<Enrollment>> GetSectionEnrollmentsAsync(int sectionId, CancellationToken cancellationToken = default)
{
const string sql = """
SELECT e.Id, e.StudentId, e.AcademicYearId, e.SectionId, e.RollNumber, e.EnrolledOn,
s.Id, s.AdmissionNumber, s.FirstName, s.LastName, s.DateOfBirth, s.Gender,
s.AdmissionDate, s.Address, s.Status, s.CreatedAt, s.CreatedBy, s.UpdatedAt, s.UpdatedBy
FROM dbo.Enrollments AS e
JOIN dbo.AcademicYears AS y ON y.Id = e.AcademicYearId AND y.IsCurrent = 1
JOIN dbo.Students AS s ON s.Id = e.StudentId AND s.IsDeleted = 0
WHERE e.SectionId = @SectionId
ORDER BY e.RollNumber;
""";
await using SqlConnection connection = _connections.Create();
IEnumerable<Enrollment> enrollments = await connection.QueryAsync<Enrollment, StudentRow, Enrollment>(
new CommandDefinition(sql, new { SectionId = sectionId }, cancellationToken: cancellationToken),
(enrollment, student) =>
{
enrollment.Student = student.ToStudent();
return enrollment;
},
splitOn: "Id"); // the second Id column starts the student
return enrollments.ToList();
}
// Dapper fills objects through a parameterless constructor and property setters. Student needs
// its constructor (it checks the names), so rows land in this simple class first, then convert.
private sealed class StudentRow
{
public int Id { get; set; }
public string AdmissionNumber { get; set; } = "";
public string FirstName { get; set; } = "";
public string LastName { get; set; } = "";
public DateOnly DateOfBirth { get; set; }
public string Gender { get; set; } = "";
public DateOnly AdmissionDate { get; set; }
public string? Address { get; set; } // NULL becomes null: no DBNull checks
public string Status { get; set; } = "";
public DateTime CreatedAt { get; set; }
public int? CreatedBy { get; set; }
public DateTime? UpdatedAt { get; set; }
public int? UpdatedBy { get; set; }
public Student ToStudent()
{
return new Student(AdmissionNumber, FirstName, LastName, DateOfBirth, (Gender)Gender[0])
{
Id = Id,
AdmissionDate = AdmissionDate,
Address = Address,
Status = Enum.Parse<StudentStatus>(Status),
CreatedAt = CreatedAt,
CreatedBy = CreatedBy,
UpdatedAt = UpdatedAt,
UpdatedBy = UpdatedBy
};
}
}
}
Each call takes a CommandDefinition. It bundles the SQL, the parameters, and the
CancellationToken, which the short form of the methods cannot pass.
Example: a row class for entities
Student has no constructor without parameters, because its constructor checks the names (Lesson
38). So Dapper cannot create a Student directly.
The repository reads each row into a private StudentRow class with plain properties. Then
ToStudent() calls the real constructor. The mapping rules are still checked, and the code that
reads columns is gone: DateOnly and nullable columns work with no extra code.
The two files are about the same length, but compare what they contain. The ADO.NET file is mostly
GetOrdinal and IsDBNull calls; the Dapper file is mostly SQL and plain properties.
Example: parameters from an object
new { LastName = lastName } is an anonymous object: a quick object with one property. Dapper
turns each property into a parameter with the same name, so @LastName gets lastName.
They are real parameters, so SQL injection is impossible, exactly as in Lesson 57. But Dapper sends
every C# string as NVARCHAR(4000). For VARCHAR columns, such as AdmissionNumber, wrap the
value in a DbString with IsAnsi = true and a Length, so the index is used.
Example: output parameters with DynamicParameters
An anonymous object cannot describe an output parameter. DynamicParameters can: each Add call sets
a name, a value, a type, a size, and a direction.
After ExecuteAsync, payment.Get<string>("@ReceiptNumber") reads the value the procedure filled
in. The call runs inside a transaction that is rolled back, as in Lesson 57.
Example: multi-mapping a join
GetSectionEnrollmentsAsync joins Enrollments and Students. Each row holds two objects, so
QueryAsync<Enrollment, StudentRow, Enrollment> reads both and calls your function to connect them.
splitOn: "Id" tells Dapper where the second object starts: at the second Id column, s.Id. The
function sets enrollment.Student and returns the enrollment. The result is a list of enrollments,
each holding its student.
Step 3: reports straight into DTOs
Reports do not need entities at all. DapperFeeQueries selects exactly the columns a screen shows
and maps them to DTOs.
Show src/SchoolApp.Data/Dapper/DapperFeeQueries.cs
using Dapper;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Dtos;
namespace SchoolApp.Data.Dapper;
// Lesson 58: read-only fee and dashboard queries. Dapper shines here: write the exact SQL a report
// needs, and get DTOs back, with no entity in between.
public class DapperFeeQueries
{
private readonly SqlConnectionFactory _connections;
public DapperFeeQueries(SqlConnectionFactory connections)
{
_connections = connections;
}
// Straight into the Lesson 45 DTO. The columns come in the same order as the record's
// constructor parameters, with the same names, so Dapper can call that constructor.
public async Task<List<FeeInvoiceSummaryDto>> GetInvoicesWithBalanceAsync(CancellationToken cancellationToken = default)
{
const string sql = """
SELECT i.InvoiceNumber,
s.FirstName + ' ' + s.LastName AS StudentName,
i.TotalAmount - i.DiscountAmount AS Total,
ISNULL(p.Paid, 0) AS Paid,
i.TotalAmount - i.DiscountAmount - ISNULL(p.Paid, 0) AS Balance,
i.Status
FROM dbo.FeeInvoices AS i
JOIN dbo.Enrollments AS e ON e.Id = i.EnrollmentId
JOIN dbo.Students AS s ON s.Id = e.StudentId
OUTER APPLY (SELECT SUM(fp.Amount) AS Paid
FROM dbo.FeePayments AS fp
WHERE fp.FeeInvoiceId = i.Id AND fp.IsDeleted = 0) AS p
WHERE i.Status IN ('Unpaid', 'PartiallyPaid')
ORDER BY Balance DESC, i.InvoiceNumber;
""";
await using SqlConnection connection = _connections.Create();
IEnumerable<FeeInvoiceSummaryDto> invoices = await connection.QueryAsync<FeeInvoiceSummaryDto>(
new CommandDefinition(sql, cancellationToken: cancellationToken));
return invoices.ToList();
}
// QueryMultiple: three SELECTs in one round trip to the server, read back in order.
public async Task<SchoolSummary> GetSummaryAsync(CancellationToken cancellationToken = default)
{
const string sql = """
SELECT COUNT(*) FROM dbo.Students WHERE Status = 'Active' AND IsDeleted = 0;
SELECT COUNT(*) FROM dbo.Teachers WHERE IsActive = 1 AND IsDeleted = 0;
SELECT Status, COUNT(*) AS Invoices FROM dbo.FeeInvoices GROUP BY Status ORDER BY Status;
""";
await using SqlConnection connection = _connections.Create();
await using SqlMapper.GridReader results = await connection.QueryMultipleAsync(
new CommandDefinition(sql, cancellationToken: cancellationToken));
int students = await results.ReadSingleAsync<int>();
int teachers = await results.ReadSingleAsync<int>();
IEnumerable<(string Status, int Invoices)> invoices = await results.ReadAsync<(string Status, int Invoices)>();
return new SchoolSummary(students, teachers, invoices.ToDictionary(i => i.Status, i => i.Invoices));
}
}
// Lesson 58: the numbers for the office dashboard (the full dashboard API comes in Module 11).
public record SchoolSummary(int ActiveStudents, int ActiveTeachers, IReadOnlyDictionary<string, int> InvoicesByStatus);
FeeInvoiceSummaryDto is the record from Lesson 45. The SQL names its columns InvoiceNumber,
StudentName, Total, Paid, Balance, and Status, in the same order as the record's
constructor, so Dapper calls that constructor.
Example: several queries, one round trip
A dashboard needs several numbers. Sending three queries means three trips to the server and back.
QueryMultipleAsync sends all three at once and returns a grid reader.
You read the results in the same order as the SELECTs: ReadSingleAsync<int>() twice, then
ReadAsync for the status counts. The status counts arrive as a value tuple, (string Status, int Invoices), with no class needed.
Step 4: the program
Show src/SchoolApp.Console/Program.cs
// Lesson 58: Dapper. The same SchoolDb queries as Lesson 57, with far less code.
using System.Data;
using System.Diagnostics;
using Dapper;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Data;
using SchoolApp.Data.AdoNet;
using SchoolApp.Data.Dapper;
SqlConnectionFactory factory = SqlConnectionFactory.FromEnvironment();
AdoNetStudentRepository adoNet = new AdoNetStudentRepository(factory);
DapperStudentRepository dapper = new DapperStudentRepository(factory);
DapperFeeQueries fees = new DapperFeeQueries(factory);
// 1. The same answers as Lesson 57.
Console.WriteLine($"Active students: {await dapper.CountActiveAsync()}");
Student? kabir = await dapper.GetByAdmissionNumberAsync("ADM-2017-0003");
Console.WriteLine($"Found: {kabir?.Describe()}, born {kabir?.DateOfBirth:yyyy-MM-dd}, {kabir?.Gender}");
Console.WriteLine($"Students named Singh: {(await dapper.SearchByLastNameAsync("Singh")).Count}");
Console.WriteLine($"10-A class list: {string.Join(", ", (await dapper.GetClassListAsync(10, "A")).Select(e => e.FullName))}");
// 2. Multi-mapping: enrollments of section 10-A (Id 19), each with its Student object.
foreach (Enrollment enrollment in await dapper.GetSectionEnrollmentsAsync(19))
{
Console.WriteLine($" Roll {enrollment.RollNumber}: {enrollment.Student?.FullName}, " +
$"enrolled {enrollment.EnrolledOn:yyyy-MM-dd}, age {enrollment.Student?.GetAgeOn(new DateOnly(2026, 10, 1))}");
}
// 3. A report straight into a DTO: every invoice with money still due.
List<FeeInvoiceSummaryDto> due = await fees.GetInvoicesWithBalanceAsync();
Console.WriteLine($"Invoices with a balance: {due.Count}, total due {due.Sum(i => i.Balance)}");
foreach (FeeInvoiceSummaryDto invoice in due.Take(3))
{
Console.WriteLine($" {invoice.InvoiceNumber} {invoice.StudentName}: paid {invoice.Paid}, balance {invoice.Balance}, {invoice.Status}");
}
// 4. QueryMultiple: three results, one round trip.
SchoolSummary summary = await fees.GetSummaryAsync();
string byStatus = string.Join(", ", summary.InvoicesByStatus.Select(s => $"{s.Key} {s.Value}"));
Console.WriteLine($"Summary: {summary.ActiveStudents} students, {summary.ActiveTeachers} teachers, invoices: {byStatus}");
// 5. A procedure with an output parameter, using DynamicParameters. Rolled back, as in Lesson 57.
await using (SqlConnection connection = factory.Create())
{
await connection.OpenAsync();
await using SqlTransaction transaction = connection.BeginTransaction();
DynamicParameters payment = new DynamicParameters();
payment.Add("@FeeInvoiceId", 3);
payment.Add("@Amount", 5000m);
payment.Add("@PaymentMode", "Online", DbType.AnsiString, size: 12);
payment.Add("@ReceiptNumber", dbType: DbType.AnsiString, size: 20, direction: ParameterDirection.Output);
await connection.ExecuteAsync("dbo.usp_RecordFeePayment", payment, transaction, commandType: CommandType.StoredProcedure);
Console.WriteLine($"Payment recorded, receipt {payment.Get<string>("@ReceiptNumber")}");
await transaction.RollbackAsync();
}
// 6. Speed: 50 searches for the 668 Singh students with each tool, after one warm-up each.
await adoNet.SearchByLastNameAsync("Singh");
await dapper.SearchByLastNameAsync("Singh");
Stopwatch timer = Stopwatch.StartNew();
for (int i = 0; i < 50; i++)
{
await adoNet.SearchByLastNameAsync("Singh");
}
long adoNetMs = timer.ElapsedMilliseconds;
timer.Restart();
for (int i = 0; i < 50; i++)
{
await dapper.SearchByLastNameAsync("Singh");
}
Console.WriteLine($"50 searches: ADO.NET {adoNetMs} ms, Dapper {timer.ElapsedMilliseconds} ms");
Active students: 10012
Found: Kabir Singh (student ADM-2017-0003), born 2011-01-30, Male
Students named Singh: 668
10-A class list: Aarav Sharma, Diya Patel, Kabir Singh, Meera Iyer
Roll 1: Aarav Sharma, enrolled 2026-06-01, age 15
Roll 2: Diya Patel, enrolled 2026-06-01, age 15
Roll 3: Kabir Singh, enrolled 2026-06-01, age 15
Roll 4: Meera Iyer, enrolled 2026-06-01, age 14
Invoices with a balance: 10, total due 405000.00
INV-2026-000003 Kabir Singh: paid 0.00, balance 45500.00, Unpaid
INV-2026-000004 Meera Iyer: paid 0.00, balance 45500.00, Unpaid
INV-2026-000005 Rohan Das: paid 0.00, balance 45500.00, Unpaid
Summary: 10012 students, 8 teachers, invoices: Paid 2, PartiallyPaid 1, Unpaid 9
Payment recorded, receipt RCP-2026-000006
50 searches: ADO.NET 221 ms, Dapper 186 ms
The answers match Lesson 57 exactly. The total due, 405,000, is Diya's 25,500 plus four balances of 45,500 and five of 39,500.
Example: is Dapper slower?
The last line loads the 668 Singh students 50 times with each tool. Three runs gave 221, 211, and 209 ms for ADO.NET, and 186, 181, and 209 ms for Dapper.
The differences are noise. Dapper generates its mapping code once per query, at run time, and then it runs as fast as hand-written code. The real time is spent in SQL Server and on the network.
Example: what Dapper does not do
Dapper is deliberately small. It does not:
- Write SQL for you. A new column means editing every query that needs it.
- Track changes. To save an edited student, you write the
UPDATEyourself. - Load related data on its own. Every join is a query you write and map.
- Convert
DATEtoDateOnlythrough a constructor. Property setters convert it; record constructors fail, so use classes with properties for those rows.
Dapper also generates code while the program runs. A .NET 10 single-file app turns that off by
default, so the homework adds #:property PublishAot=false.
Common mistakes
- A column name that does not match the property. Dapper silently leaves the property at its
default, such as
0ornull. Fix: useASaliases, and test every query once. - Forgetting
splitOnwhen the second object's key is not calledId. Dapper throws, asking forsplitOn. Fix: name the first column of each object insplitOn. - Plain strings for
VARCHARcolumns. They are sent asNVARCHARand can force scans. Fix:DbStringwithIsAnsi = true. - Loading huge results into memory.
QueryAsyncreads every row into a list first. Fix: page the query, or useQueryUnbufferedAsyncto stream rows. - Joining user input into the SQL text. Dapper does not make that safe. Fix: parameters only.
In real projects
On a school system I worked on, the admin screens used EF Core, and that was fine. The principal's dashboard was not: it took eight seconds, because EF Core produced a very large query from a long LINQ chain.
We rewrote that one screen with Dapper and a hand-tuned SQL query, with three result sets read through
QueryMultiple. It loaded in under half a second.
We kept EF Core for everything else. Using both in one project is common and perfectly fine: EF Core for saving entities, Dapper for heavy reads.
Best practices
- Write explicit column lists, never
SELECT *, with aliases that match property names. - Use
DbStringforVARCHARparameters, andDynamicParametersfor output parameters. - Map reports straight to DTOs; use a row class when an entity needs its constructor.
- Pass a
CancellationTokenthroughCommandDefinition. - Use
QueryMultiplewhen one screen needs several small queries.
Recap
- Dapper is a micro-ORM: you write the SQL, it maps the rows.
- It adds methods such as
QueryAsync<T>andExecuteAsyncto every connection. - Parameters come from objects;
DbStringsendsVARCHAR;DynamicParametershandles output. - Multi-mapping turns one joined row into several connected objects.
QueryMultiplereads several results in one round trip.- On SchoolDb, Dapper ran as fast as hand-written ADO.NET.
Homework
Library module task: With Dapper, list every book with all its copies, using multi-mapping and a
dictionary so each book appears once. Then use QueryMultiple to get the number of open issues and
the full issue history.
A correct result looks like:
Malgudi Days (R. K. Narayan): #1 Available
The Jungle Book (Rudyard Kipling): #1 Issued
Wings of Fire (A. P. J. Abdul Kalam): #1 Available, #2 Available
Open issues: 1; history: 2 issues
2026-07-01 Wings of Fire to Aarav Sharma, returned 2026-07-14
2026-07-06 The Jungle Book to Advika Sharma, not returned
Show the solution
// Library homework, Lesson 58: the library tables with Dapper: records, one-to-many multi-mapping,
// and QueryMultiple.
// Run with: dotnet run library-assignment/058-library-dapper.cs
#:project ../src/SchoolApp.Data/SchoolApp.Data.csproj
#:property PublishAot=false
using Dapper;
using Microsoft.Data.SqlClient;
using SchoolApp.Data;
SqlConnectionFactory factory = SqlConnectionFactory.FromEnvironment();
await using SqlConnection connection = factory.Create();
// 1. One book has many copies. Each row repeats the book, so a dictionary keeps one Book per Id
// and adds each copy to its list.
Dictionary<int, Book> books = new Dictionary<int, Book>();
await connection.QueryAsync<Book, BookCopy, Book>(
"""
SELECT b.Id, b.Title, b.Author, c.Id, c.CopyNumber, c.Status
FROM dbo.Books AS b
JOIN dbo.BookCopies AS c ON c.BookId = b.Id
WHERE b.IsDeleted = 0
ORDER BY b.Title, c.CopyNumber;
""",
(book, copy) =>
{
if (!books.TryGetValue(book.Id, out Book? existing))
{
existing = book;
books.Add(existing.Id, existing);
}
existing.Copies.Add(copy);
return existing;
},
splitOn: "Id");
foreach (Book book in books.Values)
{
string copies = string.Join(", ", book.Copies.Select(c => $"#{c.CopyNumber} {c.Status}"));
Console.WriteLine($"{book.Title} ({book.Author}): {copies}");
}
// 2. Two results in one round trip: a count, then a list of records.
await using SqlMapper.GridReader results = await connection.QueryMultipleAsync("""
SELECT COUNT(*) FROM dbo.BookIssues WHERE ReturnedOn IS NULL;
SELECT b.Title, s.FirstName + ' ' + s.LastName AS StudentName, i.IssuedOn, i.ReturnedOn
FROM dbo.BookIssues AS i
JOIN dbo.BookCopies AS c ON c.Id = i.BookCopyId
JOIN dbo.Books AS b ON b.Id = c.BookId
JOIN dbo.Students AS s ON s.Id = i.StudentId
ORDER BY i.IssuedOn;
""");
int open = await results.ReadSingleAsync<int>();
List<IssueLine> history = (await results.ReadAsync<IssueLine>()).ToList();
Console.WriteLine($"Open issues: {open}; history: {history.Count} issues");
foreach (IssueLine line in history)
{
string returned = line.ReturnedOn is null ? "not returned" : $"returned {line.ReturnedOn:yyyy-MM-dd}";
Console.WriteLine($" {line.IssuedOn:yyyy-MM-dd} {line.Title} to {line.StudentName}, {returned}");
}
class Book
{
public int Id { get; set; }
public string Title { get; set; } = "";
public string Author { get; set; } = "";
public List<BookCopy> Copies { get; } = new List<BookCopy>();
}
class BookCopy
{
public int Id { get; set; }
public short CopyNumber { get; set; }
public string Status { get; set; } = "";
}
// Properties, not a positional record: Dapper converts DATE to DateOnly when it sets a property,
// but not when it calls a constructor.
class IssueLine
{
public string Title { get; set; } = "";
public string StudentName { get; set; } = "";
public DateOnly IssuedOn { get; set; }
public DateOnly? ReturnedOn { get; set; }
}
Wings of Fire has two copies, so its book row appears twice in the results. The dictionary keeps
the first Book object and adds both copies to it; this is the standard way to map one-to-many
results with Dapper.
Quiz
What does Dapper do for you?
Show answer
Answer: B. Maps result rows to objects and adds parameters You still write every SQL statement; Dapper does the copying.
A column has no matching property. What happens?
Show answer
Answer: B. The column is ignored silently That is why mismatched names cause silent bugs: test every query.
How does Dapper send a plain C# string parameter?
Show answer
Answer: B. NVARCHAR(4000) Use DbString with IsAnsi = true for VARCHAR columns.
What does splitOn tell Dapper in multi-mapping?
Show answer
Answer: B. The column where the next object starts With splitOn set to Id, the second Id column starts the Student.
Why use QueryMultipleAsync for a dashboard?
Show answer
Answer: B. Several queries run in one round trip Three SELECTs, one trip to the server and back.
Interview questions
Questions are grouped by experience level. Click a question to see a model answer. All questions: Data Access interview questions.
Fresher (0-2 yrs)
F What is Dapper, and how is it different from EF Core? concept
Dapper is a micro-ORM: you write the SQL, and it adds parameters and maps result rows to objects. EF Core is a full ORM: it also writes the SQL from LINQ, tracks changes to entities, and saves them. Dapper gives more control with less magic; EF Core gives more productivity for everyday CRUD.
F Which Dapper methods would you use to get a list, a single row, and a count? coding
QueryAsync<T> for a list, QuerySingleOrDefaultAsync<T> for one row by a unique key (null if
none, an exception if two), and ExecuteScalarAsync<int> for a count. ExecuteAsync runs INSERT,
UPDATE, DELETE, or a procedure and returns the rows changed.
F How do you pass parameters to a Dapper query, and is it safe from SQL injection? concept
Pass an object, often anonymous: new { LastName = lastName } fills @LastName. Dapper creates
real parameters, so the values never become part of the SQL text and injection is not possible.
Joining user input into the SQL string yourself is still unsafe.
Mid (3-5 yrs)
M How does Dapper match columns to properties, and what happens when a name does not match? concept
By name, ignoring case. A column with no matching property is ignored, and a property with no column keeps its default value; neither raises an error. That makes renamed columns or missing aliases a silent bug, so use explicit column lists with aliases and test each query.
M What is multi-mapping in Dapper, and what does splitOn do? coding
Multi-mapping reads one joined row into several objects, for example an Enrollment and its
Student, and a function you supply connects them. splitOn names the column where each next
object starts; it defaults to Id. For one-to-many results, a dictionary keeps one parent per key.
M How do you read an output parameter from a stored procedure with Dapper? coding
Use DynamicParameters: add the inputs, and add the output with a type, a size, and
direction: ParameterDirection.Output. Call ExecuteAsync with
commandType: CommandType.StoredProcedure, then read it with Get<string>("@ReceiptNumber").
Senior (6-9 yrs)
S Your Dapper search by admission number is slow, although the column has a unique index. What could be wrong? scenario
Dapper sends C# strings as NVARCHAR(4000). Against the VARCHAR column, SQL Server converts the
column and may scan the index instead of seeking. Pass new DbString { Value = ..., IsAnsi = true, Length = 15 } so the parameter matches the column type, then confirm the seek in the plan.
How to answer: Start from the symptom, explain the implicit conversion, show the DbString fix, and say how you would prove it: the execution plan or logical reads before and after.
S A dashboard makes six small queries per page load. How would you reduce the cost with Dapper? scenario
Send them together with QueryMultipleAsync and read each result in order from the grid reader:
one round trip instead of six. Select only the columns the dashboard shows, mapped to DTOs or
tuples. If it is still slow, look at the queries' plans and consider caching (Module 12).
How to answer: Name round trips as the cost, show QueryMultiple, mention the order-dependent reading, and add what you would measure before and after.
Architect / Lead (10+ yrs)
A Would you use EF Core and Dapper in the same application? How would you organize it? design
Yes, commonly: EF Core for writes and ordinary reads of entities, where change tracking and LINQ save time, and Dapper for heavy reports and dashboards, where hand-tuned SQL matters. Both use the same connection string and can share a transaction. Keep each behind a repository or query interface so callers do not know which tool answers.
How to answer: Give the split by workload, mention sharing connections and transactions, and explain how the abstraction keeps the choice reversible. A brief CQRS mention fits at this level.
Resources
- Code for this lesson: git tag
lesson-58-dapper(code repository coming soon) - Files:
src/SchoolApp.Data/Dapper/DapperStudentRepository.cs,src/SchoolApp.Data/Dapper/DapperFeeQueries.cs,src/SchoolApp.Console/Program.cs,library-assignment/058-library-dapper.cs - Video: coming soon
What's next
With ADO.NET and Dapper, you write every query. Next: EF Core, which reads the existing SchoolDb, maps it to our classes, and writes the SQL for you. Lesson 59: EF Core database-first
Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.