Published / updated29 Aug 2026

DTO Mapping, Async and Transactions

Before you start

You need: Dapper parameters (Article 08) and async/await (Track 03 Article 08).

Time: about 50 minutes, plus the practice.

Learning objective

Return exactly the shape each caller needs from joined data, without extra round trips, and run it asynchronously inside a transaction.

Topics

Entities versus DTOs
Multi-mapping with splitOn
One-to-many with multi-mapping
QueryMultiple
Async methods and CancellationToken
Transactions with Dapper
The N+1 problem

Entities versus DTOs

An entity mirrors a table. A DTO carries what one screen needs.

// Entity — mirrors dbo.Student
public class Student
{
    public int Id { get; set; }
    public Guid PublicId { get; set; }
    public int SchoolId { get; set; }
    public string Name { get; set; } = string.Empty;
    public string RollNumber { get; set; } = string.Empty;
    public string ClassName { get; set; } = string.Empty;
    public string Section { get; set; } = string.Empty;
    public DateTime DateOfBirth { get; set; }
    public string ParentName { get; set; } = string.Empty;
    public string ParentPhone { get; set; } = string.Empty;
    public string? Address { get; set; }
    public StudentStatus Status { get; set; }
}

// DTO — exactly what the fee report shows
public class StudentFeeSummaryDto
{
    public Guid PublicId { get; set; }
    public string RollNumber { get; set; } = string.Empty;
    public string Name { get; set; } = string.Empty;
    public string ClassSection { get; set; } = string.Empty;
    public decimal TotalFees { get; set; }
    public decimal TotalPaid { get; set; }
    public decimal Outstanding { get; set; }
    public DateTime DueDate { get; set; }
}

public List<StudentFeeSummaryDto> GetOutstandingFees(int schoolId, string academicYear)
{
    const string sql = @"
        WITH PaidByAccount AS
        (
            SELECT   p.FeeAccountId, SUM(p.Amount) AS TotalPaid
            FROM     dbo.FeePayment AS p
            WHERE    p.SchoolId = @SchoolId AND p.IsCancelled = 0
            GROUP BY p.FeeAccountId
        )
        SELECT   s.PublicId,
                 s.RollNumber,
                 s.Name,
                 s.ClassName + ' - ' + s.Section              AS ClassSection,
                 fa.TotalFees,
                 ISNULL(pba.TotalPaid, 0)                     AS TotalPaid,
                 fa.TotalFees - fa.DiscountAmount
                   - ISNULL(pba.TotalPaid, 0)                 AS Outstanding,
                 fa.DueDate
        FROM     dbo.FeeAccount AS fa
        JOIN     dbo.Student    AS s   ON s.Id = fa.StudentId
        LEFT JOIN PaidByAccount AS pba ON pba.FeeAccountId = fa.Id
        WHERE    fa.SchoolId = @SchoolId
          AND    fa.AcademicYear = @AcademicYear
          AND    s.Status <> 1
          AND    fa.TotalFees - fa.DiscountAmount - ISNULL(pba.TotalPaid, 0) > 0
        ORDER BY Outstanding DESC, s.Name;";

    using (IDbConnection connection = new SqlConnection(_connectionString))
    {
        return connection.Query<StudentFeeSummaryDto>(
            sql, new { SchoolId = schoolId, AcademicYear = academicYear }).ToList();
    }
}

Why a DTO rather than the entity:

The report needs a computed Outstanding that exists on no table
It needs ClassSection as one string
It must not carry ParentPhone or Address to a screen that does not display them

Every column in the DTO must be aliased to match a property. ClassName + ' - ' + Section has no name of its own; without AS ClassSection it maps to nothing and the property stays empty — silently.

Multi-mapping

For a join that fills two objects:

public class ExamResultDetail
{
    public int Id { get; set; }
    public decimal? MarksObtained { get; set; }
    public bool IsAbsent { get; set; }
    public Student Student { get; set; } = new Student();
    public Subject Subject { get; set; } = new Subject();
}

public List<ExamResultDetail> GetResults(int schoolId, int examId)
{
    const string sql = @"
        SELECT   r.Id, r.MarksObtained, r.IsAbsent,
                 s.Id, s.PublicId, s.Name, s.RollNumber, s.ClassName, s.Section,
                 sub.Id, sub.Name, sub.Code, sub.MaxMarks, sub.PassingMarks
        FROM     dbo.ExamResult AS r
        JOIN     dbo.Student    AS s   ON s.Id   = r.StudentId
        JOIN     dbo.Exam       AS e   ON e.Id   = r.ExamId
        JOIN     dbo.Subject    AS sub ON sub.Id = e.SubjectId
        WHERE    r.SchoolId = @SchoolId AND r.ExamId = @ExamId
        ORDER BY s.Name;";

    using (IDbConnection connection = new SqlConnection(_connectionString))
    {
        return connection.Query<ExamResultDetail, Student, Subject, ExamResultDetail>(
            sql,
            (result, student, subject) =>
            {
                result.Student = student;
                result.Subject = subject;
                return result;
            },
            new { SchoolId = schoolId, ExamId = examId },
            splitOn: "Id,Id").ToList();
    }
}

splitOn is the key idea. It names the columns where a new object begins. The default is "Id"; with three types you need two split points, hence "Id,Id".

The column order in the SELECT must match the type order in the generic arguments. Dapper reads left to right, starting a new object each time it meets a splitOn column.

Two failure modes:

Wrong splitOn — the first object absorbs columns belonging to the second, and the second comes back with defaults.
A splitOn column that is NULL — from a LEFT JOIN with no match — still creates an object with all-default values, not null. Check a key property to detect the "no match" case:

(result, student, teacher) =>
{
    result.Student = student;
    result.Teacher = teacher.Id == 0 ? null : teacher;
    return result;
}

One-to-many

Multi-mapping returns one row per joined row, so a student with three payments yields three rows. Collapse them in the lambda:

public List<StudentWithPayments> GetStudentsWithPayments(int schoolId, string academicYear)
{
    const string sql = @"
        SELECT   s.Id, s.PublicId, s.Name, s.RollNumber,
                 p.Id, p.Amount, p.PaidOn, p.PaymentMode, p.ReceiptNumber
        FROM     dbo.Student    AS s
        JOIN     dbo.FeeAccount AS fa ON fa.StudentId = s.Id
        LEFT JOIN dbo.FeePayment AS p ON p.FeeAccountId = fa.Id AND p.IsCancelled = 0
        WHERE    s.SchoolId = @SchoolId AND fa.AcademicYear = @AcademicYear
          AND    s.Status <> 1
        ORDER BY s.Name, p.PaidOn;";

    Dictionary<int, StudentWithPayments> lookup = new Dictionary<int, StudentWithPayments>();

    using (IDbConnection connection = new SqlConnection(_connectionString))
    {
        connection.Query<StudentWithPayments, FeePayment, StudentWithPayments>(
            sql,
            (student, payment) =>
            {
                StudentWithPayments existing;

                if (!lookup.TryGetValue(student.Id, out existing!))
                {
                    existing = student;
                    lookup.Add(student.Id, existing);
                }

                if (payment != null && payment.Id != 0)
                {
                    existing.Payments.Add(payment);
                }

                return existing;
            },
            new { SchoolId = schoolId, AcademicYear = academicYear },
            splitOn: "Id").ToList();
    }

    return lookup.Values.ToList();
}

The dictionary deduplicates the parent. The payment.Id != 0 check skips the placeholder object produced by a LEFT JOIN with no match.

For deeper nesting this gets unwieldy. Two queries plus an in-memory join is often clearer:

List<Student> students = connection.Query<Student>(studentSql, parameters).ToList();

List<int> ids = students.Select(s => s.Id).ToList();

List<FeePayment> payments = connection.Query<FeePayment>(
    "SELECT ... FROM dbo.FeePayment WHERE FeeAccountId IN @Ids", new { Ids = ids }).ToList();

Two round trips, no splitOn, easy to read. That is a good trade.

QueryMultiple

Several result sets in one round trip.

public StudentDashboard GetDashboard(int schoolId, Guid publicId)
{
    const string sql = @"
        SELECT s.Id, s.PublicId, s.SchoolId, s.Name, s.RollNumber, s.ClassName,
               s.Section, s.DateOfBirth, s.ParentName, s.ParentPhone, s.Address, s.Status
        FROM   dbo.Student AS s
        WHERE  s.SchoolId = @SchoolId AND s.PublicId = @PublicId;

        SELECT   r.Id, r.MarksObtained, r.IsAbsent, e.ExamName, e.ExamDate, e.MaxMarks
        FROM     dbo.ExamResult AS r
        JOIN     dbo.Exam       AS e ON e.Id = r.ExamId
        JOIN     dbo.Student    AS s ON s.Id = r.StudentId
        WHERE    s.SchoolId = @SchoolId AND s.PublicId = @PublicId
        ORDER BY e.ExamDate DESC;

        SELECT fa.TotalFees, fa.PaidAmount, fa.DiscountAmount, fa.DueDate
        FROM   dbo.FeeAccount AS fa
        JOIN   dbo.Student    AS s ON s.Id = fa.StudentId
        WHERE  s.SchoolId = @SchoolId AND s.PublicId = @PublicId
          AND  fa.AcademicYear = @AcademicYear;";

    using (IDbConnection connection = new SqlConnection(_connectionString))
    using (SqlMapper.GridReader grid = connection.QueryMultiple(
        sql, new { SchoolId = schoolId, PublicId = publicId, AcademicYear = "2024-25" }))
    {
        StudentDashboard dashboard = new StudentDashboard();

        dashboard.Student = grid.ReadSingleOrDefault<Student>();
        dashboard.Results = grid.Read<ExamResultSummaryDto>().ToList();
        dashboard.FeeAccount = grid.ReadSingleOrDefault<FeeAccountDto>();

        return dashboard;
    }
}

Three queries, one round trip. Read the sets in the order they appear — the GridReader is forward-only, and reading out of order returns the wrong data or throws.

GridReader must be disposed, and it holds the connection until it is.

The same pattern gives a page plus its total count:

using (SqlMapper.GridReader grid = connection.QueryMultiple(sql, parameters))
{
    int totalCount = grid.ReadSingle<int>();
    List<Student> items = grid.Read<Student>().ToList();
}

Async

Every Dapper method has an async twin.

public async Task<List<Student>> GetStudentsByClassAsync(
    int schoolId, string className, CancellationToken cancellationToken)
{
    const string sql = @"
        SELECT   s.Id, s.PublicId, s.SchoolId, s.Name, s.RollNumber, s.ClassName,
                 s.Section, s.DateOfBirth, s.ParentName, s.ParentPhone, s.Address, s.Status
        FROM     dbo.Student AS s
        WHERE    s.SchoolId = @SchoolId AND s.ClassName = @ClassName AND s.Status <> 1
        ORDER BY s.Section, s.Name;";

    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
        CommandDefinition command = new CommandDefinition(
            sql,
            new { SchoolId = schoolId, ClassName = className },
            cancellationToken: cancellationToken);

        IEnumerable<Student> students = await connection.QueryAsync<Student>(command);

        return students.ToList();
    }
}

Sync	Async
`Query<T>`	`QueryAsync<T>`
`QueryFirstOrDefault<T>`	`QueryFirstOrDefaultAsync<T>`
`QuerySingleOrDefault<T>`	`QuerySingleOrDefaultAsync<T>`
`Execute`	`ExecuteAsync`
`ExecuteScalar<T>`	`ExecuteScalarAsync<T>`
`QueryMultiple`	`QueryMultipleAsync`

A CancellationToken needs CommandDefinition — the simpler overloads do not accept one. Passing it means an abandoned web request stops its database work rather than running to completion for nobody.

Rules that matter:

Async all the way. .Result or .Wait() on a Dapper task can deadlock in a synchronisation context and wastes a thread everywhere else.
await before the using block ends. Returning the Task without awaiting disposes the connection while the query is still running.
Async is about throughput, not speed. One query is not faster; a server handling many concurrent requests serves more of them.

Transactions

public async Task<int> RecordPaymentAsync(FeePayment payment, CancellationToken cancellationToken)
{
    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync(cancellationToken);

        using (SqlTransaction transaction = (SqlTransaction)await connection.BeginTransactionAsync(
            cancellationToken))
        {
            try
            {
                int paymentId = await connection.ExecuteScalarAsync<int>(
                    @"INSERT INTO dbo.FeePayment (PublicId, SchoolId, FeeAccountId, Amount,
                                                  PaidOn, PaymentMode, CollectedBy)
                      VALUES (NEWID(), @SchoolId, @FeeAccountId, @Amount,
                              SYSUTCDATETIME(), @PaymentMode, @CollectedBy);
                      SELECT CAST(SCOPE_IDENTITY() AS INT);",
                    payment,
                    transaction);

                await connection.ExecuteAsync(
                    @"UPDATE dbo.FeeAccount
                      SET    PaidAmount = PaidAmount + @Amount
                      WHERE  Id = @FeeAccountId AND SchoolId = @SchoolId;",
                    payment,
                    transaction);

                await transaction.CommitAsync(cancellationToken);

                return paymentId;
            }
            catch
            {
                await transaction.RollbackAsync(cancellationToken);
                throw;
            }
        }
    }
}

Pass the transaction to every Dapper call inside it. The transaction argument is third on most overloads. Omitting it on one call means that statement runs outside the transaction and is not rolled back — a subtle bug, because the code looks right.

Note the connection is opened explicitly here. Dapper only auto-opens when the connection is closed; a transaction requires an open connection, so you open it yourself and the using closes it.

The N+1 problem

// One query for the list, then one per student — 401 round trips for 400 students
List<Student> students = connection.Query<Student>(studentSql, parameters).ToList();

foreach (Student student in students)
{
    student.Payments = connection.Query<FeePayment>(
        "SELECT ... WHERE StudentId = @Id", new { Id = student.Id }).ToList();
}

Fast in development with 20 students and unusable in production with 4,000. The symptom is a page that got gradually slower and nobody knows why.

Three fixes:

One query with a join and multi-mapping — best when the child count per parent is small.

Two queries and an in-memory join — usually the clearest:

List<Student> students = connection.Query<Student>(studentSql, parameters).ToList();
List<int> ids = students.Select(s => s.Id).ToList();

List<FeePayment> payments = connection.Query<FeePayment>(
    "SELECT ... FROM dbo.FeePayment WHERE StudentId IN @Ids", new { Ids = ids }).ToList();

ILookup<int, FeePayment> byStudent = payments.ToLookup(p => p.StudentId);

foreach (Student student in students)
{
    student.Payments = byStudent[student.Id].ToList();
}

QueryMultiple — both sets in one round trip.

Two round trips instead of four hundred. Watch for a loop containing a query; that is the signature of N+1.

Errors you will hit

Message	Cause	Fix
Multi-mapping returns nulls for the child	`splitOn` does not match the column boundary	Set `splitOn` to the first column of the second object
Duplicate parent rows in a multi-map	Expected — one row per child	Group them in code
`The connection does not support MultipleActiveResultSets`	Two queries on one connection at once	Await one before starting the next
Rollback leaves data behind	A command did not receive the transaction	Pass it to every call
`Cannot access a disposed object`	Connection disposed before the await completed	Await inside the `using`

splitOn defaults to Id. If your second object's first column is not called Id, the mapping silently produces nulls rather than failing.

Common mistakes

Returning entities where a DTO was needed, exposing extra columns
An unaliased computed column, silently mapping to nothing
Wrong splitOn, so the second object comes back with defaults
Expecting a LEFT JOIN miss to produce null rather than a default-valued object
Multi-mapping a one-to-many without deduplicating the parent
Reading QueryMultiple sets out of order
Not disposing the GridReader
.Result or .Wait() on an async Dapper call
Returning a Task without awaiting, so the connection disposes early
Omitting the transaction argument on one call inside a transaction
A query inside a loop — N+1

Practice

The course exercises are DTO mapping, async basics and QueryMultiple introduction.

Write StudentFeeSummaryDto and its query. Remove one AS alias on a computed column and confirm the property is silently empty.
Write the exam-result multi-mapping with three types and splitOn: "Id,Id". Then change it to "Id" and record what happens to the third object.
Change the Teacher join to a LEFT JOIN for a subject with no teacher. Confirm you get a default-valued Teacher, not null, then handle it.
Write the one-to-many version with the dictionary. Confirm a student with three payments appears once.
Remove the dictionary and confirm the student appears three times.
Write the dashboard with QueryMultiple. Then read the sets out of order and record the failure.
Convert the whole repository to async with CancellationToken via CommandDefinition.
Call an async method with .Result from a synchronous context and observe the behaviour.
Write RecordPaymentAsync with a transaction. Omit the transaction argument on the UPDATE only, force a failure after it, and confirm the balance change survives the rollback.
Write the N+1 version over 400 students. Time it. Then rewrite it with two queries and a ToLookup, and compare.

Then run the AI drill — validate that an async change remains correct. Ask an assistant to convert a synchronous repository to async, then check its output for: a missing await, .Result anywhere, a CancellationToken accepted but never passed, a connection disposed before the query completes, and a transaction argument dropped from one call.

Exercise 9 is the one to remember: the code compiles, looks correct, and silently leaves the database inconsistent.

You can now

Map joined data into the shape each caller needs
Set splitOn correctly for multi-mapping
Write async repository methods and await them properly
Pass the transaction to every call inside it
Recognise and remove an N+1

Review questions

Why must a computed column be aliased for Dapper to map it?
What does splitOn control, and what is the symptom of getting it wrong?
Why does a LEFT JOIN with no match produce a default object rather than null?
What is the N+1 problem, and what are two ways to fix it?

Next: Repository project

Before you start​

Learning objective​

Topics​

Entities versus DTOs​

Multi-mapping​

One-to-many​

QueryMultiple​

Async​

Transactions​

The N+1 problem​

Errors you will hit​

Common mistakes​

Practice​

You can now​

Review questions​