Switching the SMS API from EF Core to Dapper – Web API | Lesson 81
Introduction
Since Lesson 68, the SMS API has read and written SchoolDb through EF Core. The team has now decided that the API will use Dapper: plain SQL that they write and can read, mapped into objects.
This lesson replaces every EF Core repository with a Dapper one. No controller changes, and only two service methods change. That is the reward for the interfaces from Lessons 46 and 62.
By the end of this lesson, you will be able to:
- Swap a whole data layer behind interfaces, with one registration method.
- Name what EF Core did for you without being asked, and write each part yourself in Dapper.
- Save related changes together with a
SqlTransaction, and detect conflicts withRowVersion. - Prove a swap is safe with the same tests, identical responses, and measured timings.
- Explain when a team picks Dapper, EF Core, or both.
Why this matters
Teams change data-access tools more often than you might think. A report becomes too slow, a database team wants to read every query, or a new lead prefers plain SQL. Without interfaces, a change like this touches every controller and every test.
The risky part is not the new code you write. It is the work the old tool did quietly, which nobody remembers until it stops. This lesson finds that work, line by line.
The concept
EF Core is like a full-service waiter. He remembers what you ordered, notices when you change your mind, and brings everything at once. Dapper is a counter: fast and exact, but you must say every detail yourself.
Example: what EF Core did without being asked
Each row is something the SMS relied on. The right column is what the Dapper code must now do instead:
| EF Core did this for us | Lesson | With Dapper, we write |
|---|---|---|
| Query filters hid removed students | 60, 76 | s.IsDeleted = 0 in every query that reads students |
| Change tracking found what changed | 59 | The service passes the changed rows to the repository |
SaveChangesAsync saved everything together | 62 | One SqlTransaction around the statements |
RowVersion checked for conflicts | 64 | WHERE RowVersion = @RowVersion, then check the row count |
| The DbContext's transaction covered every query | 78 | The lock's connection and transaction, passed to each query |
| Relationship fix-up linked a payment to its invoice | 60 | The entity sets the link itself |
StartsWith escaped % and _ | 75 | Our own escaping for LIKE |
HasConversion<string>() stored enum names | 59 | .ToString() on every enum parameter |
None of these is hard. Forgetting any one of them is a bug that compiles and passes a quick test.
Example: the one-line swap
Controllers ask for interfaces such as IStudentRepository. They never name a class. Only
AddSchoolData says which classes answer:
services.AddScoped<IStudentRepository, EfStudentRepository>();
services.AddScoped<IStudentRepository, DapperStudentRepository>();
The EF Core classes stay in the project as Module 9's examples. Only the registration decides which ones the API uses.
Apply it to the SMS
Step 1: register the Dapper repositories
using Microsoft.Extensions.DependencyInjection;
using SchoolApp.Core.Interfaces;
using SchoolApp.Data.Dapper;
namespace SchoolApp.Data;
// Lesson 69: one call registers everything SchoolApp.Data offers, so the API's Program.cs does not need
// to know the classes inside this project: builder.Services.AddSchoolData(connectionString).
public static class DependencyInjection
{
public static IServiceCollection AddSchoolData(this IServiceCollection services, string connectionString)
{
// Singleton: one SqlConnectionFactory for the whole app; it only holds the connection string.
services.AddSingleton(new SqlConnectionFactory(connectionString));
// Lesson 81: the API reads and writes SchoolDb with Dapper. Each repository opens a connection from
// the pool for each call, so scoped is right: one repository per request, never shared by threads.
// The EF Core repositories (Lessons 59 to 79) stay in this project; only these lines chose them.
services.AddScoped<IStudentRepository, DapperStudentRepository>();
services.AddScoped<IStudentQueries, DapperStudentQueries>(); // Lesson 72
services.AddScoped<IFeeInvoiceRepository, DapperFeeInvoiceRepository>();
services.AddScoped<IMasterDataRepository, DapperMasterDataRepository>(); // Lesson 76
services.AddScoped<IPeopleRepository, DapperPeopleRepository>(); // Lesson 77
services.AddScoped<IEnrollmentRepository, DapperEnrollmentRepository>(); // Lesson 78
services.AddScoped<IAttendanceRepository, DapperAttendanceRepository>(); // Lesson 79
return services;
}
}
There is no AddDbContext any more: the API no longer creates a DbContext at all. The SqlConnectionFactory
from Lesson 57 is the only shared object, and every repository asks it for a connection.
Step 2: queries with filters you can see
The student list is the clearest example. The SQL is built from fixed pieces, and every value from the request travels as a parameter:
using System.Data;
using System.Text;
using Dapper;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Common;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Interfaces;
namespace SchoolApp.Data.Dapper;
// Lesson 81: the student list queries (Lesson 72) with Dapper. EF Core wrote this SQL from LINQ;
// here it is written by hand, and the soft-delete filter is a plain "s.IsDeleted = 0" in every query.
public class DapperStudentQueries : IStudentQueries
{
private readonly SqlConnectionFactory _connections;
public DapperStudentQueries(SqlConnectionFactory connections)
{
_connections = connections;
}
public async Task<PagedResult<StudentDto>> GetPageAsync(StudentFilter filter, int page, int pageSize,
CancellationToken cancellationToken = default)
{
if (page < 1)
{
throw new ArgumentOutOfRangeException(nameof(page), "Page numbers start at 1.");
}
if (pageSize < 1 || pageSize > Paging.MaxPageSize)
{
throw new ArgumentOutOfRangeException(nameof(pageSize), $"The page size must be between 1 and {Paging.MaxPageSize}.");
}
// Each condition is added only when it is given. The SQL text is always our own fixed text;
// every value from the request travels as a parameter, so this is safe from SQL injection.
StringBuilder where = new StringBuilder("WHERE s.IsDeleted = 0");
DynamicParameters parameters = new DynamicParameters();
if (!string.IsNullOrWhiteSpace(filter.Search))
{
string search = filter.Search.Trim();
where.Append(" AND (s.AdmissionNumber = @Search OR s.LastName LIKE @Prefix OR s.FirstName LIKE @Prefix)");
parameters.Add("Search", search);
parameters.Add("Prefix", EscapeLike(search) + "%"); // 'Sin%': can use the LastName, FirstName index
}
if (filter.ClassId is int classId)
{
where.Append("""
AND EXISTS (SELECT 1 FROM dbo.Enrollments AS e
JOIN dbo.AcademicYears AS y ON y.Id = e.AcademicYearId AND y.IsCurrent = 1
JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
WHERE e.StudentId = s.Id AND sec.ClassId = @ClassId)
""");
parameters.Add("ClassId", classId);
}
parameters.Add("Skip", (page - 1) * pageSize);
parameters.Add("Take", pageSize);
// Two statements, one round trip: the total for page numbers, then one page.
string sql = $"""
SELECT COUNT(*) FROM dbo.Students AS s {where};
SELECT {StudentDtoRow.Columns}
FROM dbo.Students AS s {where}
ORDER BY s.LastName, s.FirstName, s.Id
OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;
""";
await using SqlConnection connection = _connections.Create();
await using SqlMapper.GridReader results = await connection.QueryMultipleAsync(
new CommandDefinition(sql, parameters, cancellationToken: cancellationToken));
int total = await results.ReadSingleAsync<int>();
IEnumerable<StudentDtoRow> rows = await results.ReadAsync<StudentDtoRow>();
return new PagedResult<StudentDto>(rows.Select(r => r.ToDto()).ToList(), page, pageSize, total);
}
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();
}
// In LIKE, % and _ are wildcards and [ starts a set. A search for "50%" must find the text "50%",
// so each one is wrapped in [ ]. EF Core's StartsWith did this for us.
private static string EscapeLike(string text) =>
text.Replace("[", "[[]").Replace("%", "[%]").Replace("_", "[_]");
}
Three details replace EF Core habits:
WHERE s.IsDeleted = 0starts every query. There is no query filter to add it for us.QueryMultipleAsyncsends the count and the page together: one round trip, as before.EscapeLikewraps%,_, and[in brackets. Without it, a search for50%would match every name that starts with "50".
Rows with DATE columns land in small classes, such as StudentDtoRow, before becoming DTOs. Dapper cannot
pass a DATE to a record's DateOnly constructor parameter (Lesson 58), but it can set a property.
Step 3: change tracking becomes a list
EF Core noticed when the attendance service changed a loaded record. Dapper cannot notice anything, so two interfaces changed. The repository is now told what changed:
Task SaveAsync(IReadOnlyCollection<AttendanceRecord> newRecords, IReadOnlyCollection<AttendanceRecord> changedRecords,
CancellationToken cancellationToken = default);
The service already knew which records it changed; it now keeps them in a list called changed. The same
happened in GuardianService, which collects the links whose primary contact it switched off.
Show src/SchoolApp.Data/Dapper/DapperAttendanceRepository.cs
using Dapper;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Interfaces;
namespace SchoolApp.Data.Dapper;
// Lesson 81: attendance (Lesson 79) with Dapper. The reports are the same GROUP BY ideas as Lesson 18,
// written as SQL directly instead of LINQ.
public class DapperAttendanceRepository : IAttendanceRepository
{
private readonly SqlConnectionFactory _connections;
public DapperAttendanceRepository(SqlConnectionFactory connections)
{
_connections = connections;
}
public async Task<List<RegisterStudent>> GetRegisterStudentsAsync(int sectionId, int academicYearId,
CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
IEnumerable<RegisterStudent> students = await connection.QueryAsync<RegisterStudent>(new CommandDefinition("""
SELECT e.Id AS EnrollmentId, e.RollNumber, s.AdmissionNumber, s.FirstName + ' ' + s.LastName AS StudentName
FROM dbo.Enrollments AS e
JOIN dbo.Students AS s ON s.Id = e.StudentId AND s.IsDeleted = 0
WHERE e.SectionId = @SectionId AND e.AcademicYearId = @AcademicYearId
ORDER BY e.RollNumber;
""", new { SectionId = sectionId, AcademicYearId = academicYearId }, cancellationToken: cancellationToken));
return students.ToList();
}
public async Task<List<AttendanceRecord>> GetRecordsAsync(int sectionId, DateOnly date, CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
IEnumerable<AttendanceRecord> records = await connection.QueryAsync<AttendanceRecord>(new CommandDefinition("""
SELECT a.Id, a.EnrollmentId, a.AttendanceDate, a.Status, a.Remarks, a.CreatedAt, a.CreatedBy
FROM dbo.AttendanceRecords AS a
JOIN dbo.Enrollments AS e ON e.Id = a.EnrollmentId
JOIN dbo.Students AS s ON s.Id = e.StudentId AND s.IsDeleted = 0
WHERE a.AttendanceDate = @Date AND e.SectionId = @SectionId;
""", new { Date = date, SectionId = sectionId }, cancellationToken: cancellationToken));
return records.ToList();
}
// One transaction: the changed records, then the new ones. A list as the parameter makes Dapper run
// the statement once per record, all inside the transaction.
public async Task SaveAsync(IReadOnlyCollection<AttendanceRecord> newRecords, IReadOnlyCollection<AttendanceRecord> changedRecords,
CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
await connection.OpenAsync(cancellationToken);
await using SqlTransaction transaction = connection.BeginTransaction();
// Status is an enum. Dapper would send its number; the column holds its name, such as "Late".
await connection.ExecuteAsync(new CommandDefinition(
"UPDATE dbo.AttendanceRecords SET Status = @Status, Remarks = @Remarks WHERE Id = @Id;",
changedRecords.Select(r => new { r.Id, Status = r.Status.ToString(), r.Remarks }),
transaction, cancellationToken: cancellationToken));
await connection.ExecuteAsync(new CommandDefinition("""
INSERT INTO dbo.AttendanceRecords (EnrollmentId, AttendanceDate, Status, Remarks, CreatedBy)
VALUES (@EnrollmentId, @AttendanceDate, @Status, @Remarks, @CreatedBy);
""",
newRecords.Select(r => new { r.EnrollmentId, r.AttendanceDate, Status = r.Status.ToString(), r.Remarks, r.CreatedBy }),
transaction, cancellationToken: cancellationToken));
await transaction.CommitAsync(cancellationToken);
}
// A left join from the register to that day's records: students without a record show Status null.
public async Task<List<RegisterLineDto>> GetRegisterAsync(int sectionId, int academicYearId, DateOnly date,
CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
IEnumerable<RegisterLineDto> lines = await connection.QueryAsync<RegisterLineDto>(new CommandDefinition("""
SELECT e.RollNumber, s.AdmissionNumber, s.FirstName + ' ' + s.LastName AS StudentName, a.Status, a.Remarks
FROM dbo.Enrollments AS e
JOIN dbo.Students AS s ON s.Id = e.StudentId AND s.IsDeleted = 0
LEFT JOIN dbo.AttendanceRecords AS a ON a.EnrollmentId = e.Id AND a.AttendanceDate = @Date
WHERE e.SectionId = @SectionId AND e.AcademicYearId = @AcademicYearId
ORDER BY e.RollNumber;
""", new { SectionId = sectionId, AcademicYearId = academicYearId, Date = date },
cancellationToken: cancellationToken));
return lines.ToList();
}
// GROUP BY in SQL: about 10,000 records for a school day become ten rows of counts.
public async Task<List<ClassAttendanceDto>> GetDailySummaryAsync(DateOnly date, CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
IEnumerable<StatusCounts> rows = await connection.QueryAsync<StatusCounts>(new CommandDefinition("""
SELECT c.Name,
SUM(CASE WHEN a.Status = 'Present' THEN 1 ELSE 0 END) AS Present,
SUM(CASE WHEN a.Status = 'Absent' THEN 1 ELSE 0 END) AS Absent,
SUM(CASE WHEN a.Status = 'Late' THEN 1 ELSE 0 END) AS Late,
SUM(CASE WHEN a.Status = 'Leave' THEN 1 ELSE 0 END) AS Leave
FROM dbo.AttendanceRecords AS a
JOIN dbo.Enrollments AS e ON e.Id = a.EnrollmentId
JOIN dbo.Students AS s ON s.Id = e.StudentId AND s.IsDeleted = 0
JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
JOIN dbo.Classes AS c ON c.Id = sec.ClassId
WHERE a.AttendanceDate = @Date
GROUP BY c.DisplayOrder, c.Name
ORDER BY c.DisplayOrder;
""", new { Date = date }, cancellationToken: cancellationToken));
return rows.Select(r => new ClassAttendanceDto(r.Name, r.Present, r.Absent, r.Late, r.Leave,
Percent(r.Present + r.Late, r.Total)))
.ToList();
}
// EF Core brought every status back and counted in C#; here SQL counts and sends one row.
public async Task<StudentAttendanceSummaryDto?> GetStudentSummaryAsync(string admissionNumber, DateOnly from, DateOnly to,
CancellationToken cancellationToken = default)
{
const string sql = """
SELECT s.FirstName + ' ' + s.LastName AS Name,
SUM(CASE WHEN a.Status = 'Present' THEN 1 ELSE 0 END) AS Present,
SUM(CASE WHEN a.Status = 'Absent' THEN 1 ELSE 0 END) AS Absent,
SUM(CASE WHEN a.Status = 'Late' THEN 1 ELSE 0 END) AS Late,
SUM(CASE WHEN a.Status = 'Leave' THEN 1 ELSE 0 END) AS Leave
FROM dbo.Students AS s
LEFT JOIN dbo.Enrollments AS e ON e.StudentId = s.Id
LEFT JOIN dbo.AttendanceRecords AS a ON a.EnrollmentId = e.Id AND a.AttendanceDate BETWEEN @From AND @To
WHERE s.AdmissionNumber = @AdmissionNumber AND s.IsDeleted = 0
GROUP BY s.FirstName, s.LastName;
""";
await using SqlConnection connection = _connections.Create();
StatusCounts? row = await connection.QuerySingleOrDefaultAsync<StatusCounts>(new CommandDefinition(sql, new
{
AdmissionNumber = new DbString { Value = admissionNumber, IsAnsi = true, Length = 15 },
From = from,
To = to
}, cancellationToken: cancellationToken));
if (row is null)
{
return null; // no such student
}
return new StudentAttendanceSummaryDto(admissionNumber, row.Name, from, to, row.Total,
row.Present, row.Absent, row.Late, row.Leave, Percent(row.Present + row.Late, row.Total));
}
private static decimal Percent(int part, int whole) => whole == 0 ? 0m : Math.Round(100m * part / whole, 2);
// A student with no records in the range still has one row from the LEFT JOIN, with Status NULL,
// and CASE turns that into 0: the counts are never NULL.
private sealed class StatusCounts
{
public string Name { get; set; } = "";
public int Present { get; set; }
public int Absent { get; set; }
public int Late { get; set; }
public int Leave { get; set; }
public int Total => Present + Absent + Late + Leave;
}
}
When the parameter is a list, Dapper runs the statement once per item, inside the transaction. Note
Status = r.Status.ToString(). We tested sending the enum itself, and SQL Server refused it:
SqlException 547: The UPDATE statement conflicted with the CHECK constraint "CK_AttendanceRecords_Status".
Dapper had sent 2, the enum's number, to a column that holds names such as Late.
Step 4: a unit of work and a conflict check
Fee payments (Lesson 64) are the hardest case. Two clerks may pay the same invoice at the same moment, and the invoice's status and the new payment must be saved together.
The interface stays exactly as Lesson 64 made it. The Dapper class keeps a short list of the invoices it
loaded, with their RowVersion, for one request:
using Dapper;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;
using SchoolApp.Core.Exceptions;
using SchoolApp.Core.Interfaces;
namespace SchoolApp.Data.Dapper;
// Lesson 81: fee invoices with Dapper, behind the same IFeeInvoiceRepository as Lesson 64.
// EF Core remembered what it loaded and wrote the changes in SaveChangesAsync. Dapper remembers nothing,
// so this class keeps a short list of the invoices it loaded, with their RowVersion, for one request.
public class DapperFeeInvoiceRepository : IFeeInvoiceRepository
{
private readonly SqlConnectionFactory _connections;
private readonly List<LoadedInvoice> _loaded = new List<LoadedInvoice>();
public DapperFeeInvoiceRepository(SqlConnectionFactory connections)
{
_connections = connections;
}
public async Task<FeeInvoice?> GetWithPaymentsAsync(string invoiceNumber, CancellationToken cancellationToken = default)
{
// Multi-mapping (Lesson 58): one row holds the invoice, its RowVersion, the enrollment, and the student.
const string invoiceSql = """
SELECT i.Id, i.InvoiceNumber, i.EnrollmentId, i.InvoiceDate, i.DueDate, i.TotalAmount, i.DiscountAmount,
i.Status, i.CreatedAt, i.CreatedBy, i.UpdatedAt, i.UpdatedBy,
i.RowVersion,
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.FeeInvoices AS i
JOIN dbo.Enrollments AS e ON e.Id = i.EnrollmentId
JOIN dbo.Students AS s ON s.Id = e.StudentId AND s.IsDeleted = 0
WHERE i.InvoiceNumber = @InvoiceNumber;
""";
const string paymentsSql = """
SELECT Id, ReceiptNumber, FeeInvoiceId, Amount, PaidOn, PaymentMode, TransactionReference,
CreatedAt, CreatedBy, UpdatedAt, UpdatedBy, IsDeleted, DeletedAt
FROM dbo.FeePayments
WHERE FeeInvoiceId = @FeeInvoiceId AND IsDeleted = 0
ORDER BY PaidOn;
""";
await using SqlConnection connection = _connections.Create();
byte[] rowVersion = [];
IEnumerable<FeeInvoice> invoices = await connection.QueryAsync<FeeInvoice, VersionRow, Enrollment, StudentRow, FeeInvoice>(
new CommandDefinition(invoiceSql,
new { InvoiceNumber = new DbString { Value = invoiceNumber, IsAnsi = true, Length = 20 } },
cancellationToken: cancellationToken),
(invoice, version, enrollment, student) =>
{
rowVersion = version.RowVersion;
enrollment.Student = student.ToStudent();
invoice.Enrollment = enrollment;
return invoice;
},
splitOn: "RowVersion,Id,Id"); // where the 2nd, 3rd, and 4th objects start
FeeInvoice? found = invoices.SingleOrDefault();
if (found is null)
{
return null;
}
IEnumerable<FeePayment> payments = await connection.QueryAsync<FeePayment>(
new CommandDefinition(paymentsSql, new { FeeInvoiceId = found.Id }, cancellationToken: cancellationToken));
found.Payments.AddRange(payments);
_loaded.Add(new LoadedInvoice(found, found.Status, found.Payments.Count, rowVersion));
return found;
}
// The same format as usp_RecordFeePayment: RCP-<year>-NNNNNN.
public async Task<string> NextReceiptNumberAsync(CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
long next = await connection.ExecuteScalarAsync<long>(
new CommandDefinition("SELECT NEXT VALUE FOR dbo.ReceiptNumbers;", cancellationToken: cancellationToken));
return $"RCP-{DateTime.UtcNow.Year}-{next:000000}";
}
// Writes what changed on the loaded invoices: the invoice's status, then its new payments.
// One transaction: both are saved, or neither. The UPDATE checks RowVersion, as EF Core did.
public async Task SaveChangesAsync(CancellationToken cancellationToken = default)
{
const string updateInvoiceSql = """
UPDATE dbo.FeeInvoices
SET Status = @Status, UpdatedAt = SYSUTCDATETIME()
OUTPUT INSERTED.RowVersion
WHERE Id = @Id AND RowVersion = @RowVersion;
""";
const string insertPaymentSql = """
INSERT INTO dbo.FeePayments (ReceiptNumber, FeeInvoiceId, Amount, PaidOn, PaymentMode, TransactionReference, CreatedBy)
OUTPUT INSERTED.Id
VALUES (@ReceiptNumber, @FeeInvoiceId, @Amount, @PaidOn, @PaymentMode, @TransactionReference, @CreatedBy);
""";
await using SqlConnection connection = _connections.Create();
await connection.OpenAsync(cancellationToken);
await using SqlTransaction transaction = connection.BeginTransaction();
for (int i = 0; i < _loaded.Count; i++)
{
LoadedInvoice loaded = _loaded[i];
FeeInvoice invoice = loaded.Invoice;
List<FeePayment> newPayments = invoice.Payments.Skip(loaded.PaymentCount).ToList();
if (invoice.Status == loaded.Status && newPayments.Count == 0)
{
continue; // nothing changed on this invoice
}
// No row matches when someone else saved the invoice after we read it: its RowVersion moved on.
byte[]? newVersion = await connection.QuerySingleOrDefaultAsync<byte[]>(new CommandDefinition(
updateInvoiceSql,
new { invoice.Id, Status = invoice.Status.ToString(), loaded.RowVersion },
transaction, cancellationToken: cancellationToken));
if (newVersion is null)
{
// Leaving without CommitAsync: disposing the transaction rolls it back.
throw new SchoolRuleException(50014, "The invoice was changed by someone else. Load it again and retry.");
}
foreach (FeePayment payment in newPayments)
{
payment.FeeInvoiceId = invoice.Id;
payment.Id = await connection.QuerySingleAsync<int>(new CommandDefinition(insertPaymentSql, new
{
payment.ReceiptNumber,
payment.FeeInvoiceId,
payment.Amount,
payment.PaidOn,
PaymentMode = payment.PaymentMode.ToString(), // the column holds the name, such as "UPI"
payment.TransactionReference,
payment.CreatedBy
}, transaction, cancellationToken: cancellationToken));
}
_loaded[i] = loaded with { Status = invoice.Status, PaymentCount = invoice.Payments.Count, RowVersion = newVersion };
}
await transaction.CommitAsync(cancellationToken);
}
// What the invoice looked like when it was loaded, to find what changed.
private sealed record LoadedInvoice(FeeInvoice Invoice, InvoiceStatus Status, int PaymentCount, byte[] RowVersion);
private sealed class VersionRow
{
public byte[] RowVersion { get; set; } = [];
}
}
The UPDATE only matches when the RowVersion is still the one we read. When another clerk saved first, no
row matches, OUTPUT returns nothing, and the transaction rolls back with error 50014.
Dapper creates an object first, then fills each column. FeeInvoice had no parameterless constructor, so
it gained a private one. Dapper can use a private constructor; application code cannot, and still goes
through the public one with its checks.
Step 5: the section lock keeps its transaction
In Lesson 78, the section lock was the DbContext's transaction, and every later query joined it automatically. With Dapper, the lock object holds the connection and the transaction, and each method uses them:
Show src/SchoolApp.Data/Dapper/DapperEnrollmentRepository.cs
using Dapper;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Exceptions;
using SchoolApp.Core.Interfaces;
namespace SchoolApp.Data.Dapper;
// Lesson 81: enrollments (Lesson 78) with Dapper, including the lock on one section.
// With EF Core, the DbContext held the transaction, and every query joined it by itself. Here, while a
// section is locked, this repository keeps that connection and transaction, and every method uses them.
public class DapperEnrollmentRepository : IEnrollmentRepository
{
private readonly SqlConnectionFactory _connections;
private DapperSectionLock? _lock; // set while a section is locked
public DapperEnrollmentRepository(SqlConnectionFactory connections)
{
_connections = connections;
}
public async Task<SectionInfo?> GetSectionAsync(int classId, string sectionName, CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
return await connection.QuerySingleOrDefaultAsync<SectionInfo>(new CommandDefinition("""
SELECT sec.Id, c.Name AS ClassName, sec.Name, sec.Capacity
FROM dbo.Sections AS sec
JOIN dbo.Classes AS c ON c.Id = sec.ClassId
WHERE sec.ClassId = @ClassId AND sec.Name = @SectionName;
""", new { ClassId = classId, SectionName = new DbString { Value = sectionName, IsAnsi = true, Length = 2 } },
cancellationToken: cancellationToken));
}
public async Task<ISectionLock> LockSectionAsync(int sectionId, CancellationToken cancellationToken = default)
{
SqlConnection connection = _connections.Create();
await connection.OpenAsync(cancellationToken);
SqlTransaction transaction = connection.BeginTransaction();
// UPDLOCK keeps an update lock on this one Sections row until the transaction ends. A second
// enrollment into the same section waits here; enrollments into other sections do not.
await connection.ExecuteAsync(new CommandDefinition(
"SELECT Id FROM dbo.Sections WITH (UPDLOCK, ROWLOCK) WHERE Id = @SectionId;",
new { SectionId = sectionId }, transaction, cancellationToken: cancellationToken));
_lock = new DapperSectionLock(connection, transaction, () => _lock = null);
return _lock;
}
// These three run inside the lock's transaction, so they see and keep exactly what the lock protects.
public async Task<bool> IsEnrolledAsync(int studentId, int academicYearId, CancellationToken cancellationToken = default)
{
DapperSectionLock held = RequireLock();
return await held.Connection.ExecuteScalarAsync<bool>(new CommandDefinition("""
SELECT CASE WHEN EXISTS (SELECT 1 FROM dbo.Enrollments
WHERE StudentId = @StudentId AND AcademicYearId = @AcademicYearId)
THEN 1 ELSE 0 END;
""", new { StudentId = studentId, AcademicYearId = academicYearId }, held.Transaction,
cancellationToken: cancellationToken));
}
public async Task<SectionFill> GetSectionFillAsync(int sectionId, int academicYearId, CancellationToken cancellationToken = default)
{
// The count is of current students, so removed students free their place (IsDeleted = 0).
// The highest roll number includes removed students: their enrollment rows still hold their numbers
// in UQ_Enrollments_SectionId_AcademicYearId_RollNumber. EF Core needed IgnoreQueryFilters for that;
// in SQL, it is simply a query without the join.
DapperSectionLock held = RequireLock();
return await held.Connection.QuerySingleAsync<SectionFill>(new CommandDefinition("""
SELECT (SELECT COUNT(*) FROM dbo.Enrollments AS e
JOIN dbo.Students AS s ON s.Id = e.StudentId AND s.IsDeleted = 0
WHERE e.SectionId = @SectionId AND e.AcademicYearId = @AcademicYearId) AS Count,
(SELECT ISNULL(MAX(RollNumber), CAST(0 AS SMALLINT)) FROM dbo.Enrollments
WHERE SectionId = @SectionId AND AcademicYearId = @AcademicYearId) AS MaxRollNumber;
""", new { SectionId = sectionId, AcademicYearId = academicYearId }, held.Transaction,
cancellationToken: cancellationToken));
}
public async Task AddAsync(Enrollment enrollment, CancellationToken cancellationToken = default)
{
DapperSectionLock held = RequireLock();
try
{
enrollment.Id = await held.Connection.QuerySingleAsync<int>(new CommandDefinition("""
INSERT INTO dbo.Enrollments (StudentId, AcademicYearId, SectionId, RollNumber, EnrolledOn, CreatedBy)
OUTPUT INSERTED.Id
VALUES (@StudentId, @AcademicYearId, @SectionId, @RollNumber, @EnrolledOn, @CreatedBy);
""", new
{
enrollment.StudentId,
enrollment.AcademicYearId,
enrollment.SectionId,
enrollment.RollNumber,
enrollment.EnrolledOn,
enrollment.CreatedBy
}, held.Transaction, cancellationToken: cancellationToken));
}
catch (SqlException ex) when (SqlErrors.IsUniqueKeyViolation(ex, "UQ_Enrollments_StudentId_AcademicYearId"))
{
// The final guard: the same student enrolled at the same moment into a different section.
throw new SchoolRuleException(50033, "The student is already enrolled in this academic year.", ex);
}
}
public async Task<EnrollmentDto?> GetAsync(int id, CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
EnrollmentRow? row = await connection.QuerySingleOrDefaultAsync<EnrollmentRow>(new CommandDefinition("""
SELECT e.Id, s.AdmissionNumber, s.FirstName + ' ' + s.LastName AS StudentName, y.Name AS AcademicYear,
c.Name AS ClassName, sec.Name AS SectionName, e.RollNumber, e.EnrolledOn
FROM dbo.Enrollments AS e
JOIN dbo.Students AS s ON s.Id = e.StudentId AND s.IsDeleted = 0
JOIN dbo.AcademicYears AS y ON y.Id = e.AcademicYearId
JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
JOIN dbo.Classes AS c ON c.Id = sec.ClassId
WHERE e.Id = @Id;
""", new { Id = id }, cancellationToken: cancellationToken));
return row?.ToDto();
}
private DapperSectionLock RequireLock() =>
_lock ?? throw new InvalidOperationException("Lock the section first, with LockSectionAsync.");
private sealed class DapperSectionLock : ISectionLock
{
private readonly Action _released;
public DapperSectionLock(SqlConnection connection, SqlTransaction transaction, Action released)
{
Connection = connection;
Transaction = transaction;
_released = released;
}
public SqlConnection Connection { get; }
public SqlTransaction Transaction { get; }
public Task CommitAsync(CancellationToken cancellationToken = default) => Transaction.CommitAsync(cancellationToken);
// Disposing a transaction that was not committed rolls it back and releases the lock.
public async ValueTask DisposeAsync()
{
await Transaction.DisposeAsync();
await Connection.DisposeAsync();
_released();
}
}
private sealed class EnrollmentRow
{
public int Id { get; set; }
public string AdmissionNumber { get; set; } = "";
public string StudentName { get; set; } = "";
public string AcademicYear { get; set; } = "";
public string ClassName { get; set; } = "";
public string SectionName { get; set; } = "";
public short RollNumber { get; set; }
public DateOnly EnrolledOn { get; set; }
public EnrollmentDto ToDto() =>
new EnrollmentDto(Id, AdmissionNumber, StudentName, AcademicYear, ClassName, SectionName, RollNumber, EnrolledOn);
}
}
The highest roll number reads removed students too, because their rows still hold their numbers. EF Core
needed IgnoreQueryFilters for that. In SQL, it is simply a query without the join to Students.
Step 6: the order of statements is yours
A student has at most one primary contact, enforced by the filtered unique index
UQ_StudentGuardians_PrimaryContact. When a new primary contact is added, the old one must be switched off
first. EF Core chose that order by itself (Lesson 77).
DapperPeopleRepository.AddLinkAsync writes it out in one transaction: update the changed links, insert the
guardian, insert the link. Swap the first and last statements, and the index refuses the insert.
Show src/SchoolApp.Data/Dapper/DapperPeopleRepository.cs
using Dapper;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Interfaces;
namespace SchoolApp.Data.Dapper;
// Lesson 81: teachers and guardians (Lesson 77) with Dapper.
public class DapperPeopleRepository : IPeopleRepository
{
private const string SelectTeacher = """
SELECT EmployeeCode, FirstName + ' ' + LastName AS FullName, Email, Qualification, JoiningDate
FROM dbo.Teachers
""";
private readonly SqlConnectionFactory _connections;
public DapperPeopleRepository(SqlConnectionFactory connections)
{
_connections = connections;
}
public async Task<List<TeacherDto>> GetTeachersAsync(CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
IEnumerable<TeacherRow> rows = await connection.QueryAsync<TeacherRow>(new CommandDefinition(
SelectTeacher + " WHERE IsActive = 1 AND IsDeleted = 0 ORDER BY EmployeeCode;", cancellationToken: cancellationToken));
return rows.Select(r => r.ToDto()).ToList();
}
public async Task<TeacherDto?> GetTeacherAsync(string employeeCode, CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
TeacherRow? row = await connection.QuerySingleOrDefaultAsync<TeacherRow>(new CommandDefinition(
SelectTeacher + " WHERE EmployeeCode = @EmployeeCode AND IsDeleted = 0;",
new { EmployeeCode = new DbString { Value = employeeCode, IsAnsi = true, Length = 10 } },
cancellationToken: cancellationToken));
return row?.ToDto();
}
// Two statements: does the student exist, and the student's guardians through the junction table.
public async Task<List<GuardianDto>?> GetGuardiansOfStudentAsync(string admissionNumber, CancellationToken cancellationToken = default)
{
const string sql = """
SELECT Id FROM dbo.Students WHERE AdmissionNumber = @AdmissionNumber AND IsDeleted = 0;
SELECT g.Id, g.FirstName + ' ' + g.LastName AS FullName, sg.Relationship, g.Phone, g.Email, sg.IsPrimaryContact
FROM dbo.StudentGuardians AS sg
JOIN dbo.Students AS s ON s.Id = sg.StudentId AND s.IsDeleted = 0
JOIN dbo.Guardians AS g ON g.Id = sg.GuardianId AND g.IsDeleted = 0
WHERE s.AdmissionNumber = @AdmissionNumber
ORDER BY sg.IsPrimaryContact DESC, sg.Relationship;
""";
await using SqlConnection connection = _connections.Create();
await using SqlMapper.GridReader results = await connection.QueryMultipleAsync(new CommandDefinition(sql,
new { AdmissionNumber = new DbString { Value = admissionNumber, IsAnsi = true, Length = 15 } },
cancellationToken: cancellationToken));
int? studentId = await results.ReadSingleOrDefaultAsync<int?>();
if (studentId is null)
{
return null;
}
return (await results.ReadAsync<GuardianDto>()).ToList();
}
// The same junction table, read from the other side.
public async Task<List<StudentDto>?> GetChildrenOfGuardianAsync(int guardianId, CancellationToken cancellationToken = default)
{
string sql = $"""
SELECT COUNT(*) FROM dbo.Guardians WHERE Id = @GuardianId AND IsDeleted = 0;
SELECT {StudentDtoRow.Columns}
FROM dbo.StudentGuardians AS sg
JOIN dbo.Students AS s ON s.Id = sg.StudentId AND s.IsDeleted = 0
WHERE sg.GuardianId = @GuardianId
ORDER BY s.DateOfBirth;
""";
await using SqlConnection connection = _connections.Create();
await using SqlMapper.GridReader results = await connection.QueryMultipleAsync(
new CommandDefinition(sql, new { GuardianId = guardianId }, cancellationToken: cancellationToken));
if (await results.ReadSingleAsync<int>() == 0)
{
return null;
}
return (await results.ReadAsync<StudentDtoRow>()).Select(r => r.ToDto()).ToList();
}
public async Task<List<StudentGuardian>> GetLinksAsync(int studentId, CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
IEnumerable<StudentGuardian> links = await connection.QueryAsync<StudentGuardian>(new CommandDefinition("""
SELECT sg.StudentId, sg.GuardianId, sg.Relationship, sg.IsPrimaryContact
FROM dbo.StudentGuardians AS sg
JOIN dbo.Guardians AS g ON g.Id = sg.GuardianId AND g.IsDeleted = 0
WHERE sg.StudentId = @StudentId;
""", new { StudentId = studentId }, cancellationToken: cancellationToken));
return links.ToList();
}
// One transaction: the changed links first, then the new guardian and the new link.
// The order matters. UQ_StudentGuardians_PrimaryContact allows one primary contact per student, so the
// old primary contact must be switched off before the new one is inserted. EF Core chose this order
// for us (Lesson 77); with Dapper, we write it.
public async Task AddLinkAsync(StudentGuardian link, IReadOnlyCollection<StudentGuardian> changedLinks,
CancellationToken cancellationToken = default)
{
Guardian guardian = link.Guardian ?? throw new ArgumentException("The link needs its new guardian.", nameof(link));
await using SqlConnection connection = _connections.Create();
await connection.OpenAsync(cancellationToken);
await using SqlTransaction transaction = connection.BeginTransaction();
// A list as the parameter: Dapper runs the UPDATE once for each link.
await connection.ExecuteAsync(new CommandDefinition("""
UPDATE dbo.StudentGuardians SET IsPrimaryContact = @IsPrimaryContact
WHERE StudentId = @StudentId AND GuardianId = @GuardianId;
""", changedLinks, transaction, cancellationToken: cancellationToken));
guardian.Id = await connection.QuerySingleAsync<int>(new CommandDefinition("""
INSERT INTO dbo.Guardians (FirstName, LastName, Phone, Email, Occupation, CreatedBy)
OUTPUT INSERTED.Id
VALUES (@FirstName, @LastName, @Phone, @Email, @Occupation, @CreatedBy);
""", new
{
guardian.FirstName,
guardian.LastName,
Phone = new DbString { Value = guardian.Phone, IsAnsi = true, Length = 15 },
Email = new DbString { Value = guardian.Email, IsAnsi = true, Length = 100 },
guardian.Occupation,
guardian.CreatedBy
}, transaction, cancellationToken: cancellationToken));
link.GuardianId = guardian.Id;
await connection.ExecuteAsync(new CommandDefinition("""
INSERT INTO dbo.StudentGuardians (StudentId, GuardianId, Relationship, IsPrimaryContact)
VALUES (@StudentId, @GuardianId, @Relationship, @IsPrimaryContact);
""", new
{
link.StudentId,
link.GuardianId,
Relationship = new DbString { Value = link.Relationship, IsAnsi = true, Length = 10 },
link.IsPrimaryContact
}, transaction, cancellationToken: cancellationToken));
await transaction.CommitAsync(cancellationToken);
}
private sealed class TeacherRow
{
public string EmployeeCode { get; set; } = "";
public string FullName { get; set; } = "";
public string Email { get; set; } = "";
public string? Qualification { get; set; }
public DateOnly JoiningDate { get; set; }
public TeacherDto ToDto() => new TeacherDto(EmployeeCode, FullName, Email, Qualification, JoiningDate);
}
}
Step 7: prove nothing changed
Four kinds of proof, all run on this machine.
The tests. The enrollment tests now run twice, once per tool, including the test where a second clerk waits for the section lock.
Two new tests check the Dapper invoice: its real balance, and a 50014 conflict with nothing half-saved. The suite has 65 tests, all passing.
The same answers. The Lesson 80 build (EF Core) and this build (Dapper) each answered the same 20 GET requests. They covered students, pages, filters, classes, years, subjects, teachers, guardians, enrollments, invoices, registers, and both attendance reports.
A diff of the two outputs found no difference at all.
The writes. Every write from Lessons 75 to 79 ran on the Dapper build:
PUT /api/sections/19/attendance?date=2026-08-10 (4 lines) 200 created 4, updated 0
PUT (the same register again) 200 created 0, updated 0
PUT (Meera changed to Present) 200 created 0, updated 1
POST /api/students (Ishaan Kumar) 201
POST /api/enrollments (5-A) 201 "Enrolled with roll number 1."
POST /api/enrollments (the same student, 5-B) 409 errorCode 50033
POST /api/students/ADM-2017-0003/guardians (new primary) 201; Harpreet Singh is no longer primary
POST /api/subjects (code MATH again) 409 errorCode 50021
DELETE /api/students/ADM-2026-10001 200 "Student ADM-2026-10001 was removed."
A bug the proof found. The first payment saved correctly but answered 500, and the log showed a
NullReferenceException in the mapping code.
The controller used payment.FeeInvoice, a link EF Core's fix-up had always filled in. With Dapper, it was
null.
The fix belongs in the entity, not the repository. RecordPayment now sets FeeInvoice = this, so the link
is there whichever tool loads the invoice. After the fix:
POST /api/fee-invoices/INV-2026-000002/payments {"amount":500,"paymentMode":"Online"} 201
{"status":"success","message":"Payment recorded.","data":{"receiptNumber":"RCP-2026-000030","invoiceNumber":"INV-2026-000002","amount":500,"paymentMode":"Online","paidOn":"2026-10-08T08:24:53.9963994Z","invoiceBalance":25000.00,"invoiceStatus":"PartiallyPaid"}}
All demo rows were removed from SchoolDb afterwards.
Example: how fast, measured
Each request ran five times against each build on LocalDB. The table shows the median, including the HTTP round trip:
| Request | EF Core | Dapper |
|---|---|---|
| One student by admission number | 4.1 ms | 3.5 ms |
| Students in Class 10, page 2 | 18.6 ms | 9.7 ms |
| Search "Singh", page 1 | 30.2 ms | 25.6 ms |
| Every class with section counts | 15.7 ms | 13.3 ms |
| A student's guardians | 5.3 ms | 3.1 ms |
| Daily attendance summary (about 10,000 rows) | 56.7 ms | 51.8 ms |
Dapper saved about 1 to 2 milliseconds on most requests. On the heavy report, both spent nearly all the time inside SQL Server. Your numbers will differ by machine, but the pattern usually holds: the SQL matters more than the tool.
Common mistakes
- Forgetting
IsDeleted = 0. Removed students reappear in lists and counts. Fix: start every student query with the filter, and test with a removed student. - Passing enums as they are. Dapper sends the number; the CHECK constraint refuses it. Fix:
.ToString()for every enum column that stores names. - Several statements without a transaction. A failure halfway leaves half the change saved. Fix:
one
SqlTransaction, committed at the end. - Relying on what the old tool did quietly. Fix-up, tracking, and statement order all disappear. Fix: the table in this lesson, then tests for each item.
- User text straight into
LIKE.%and_become wildcards. Fix: escape them, and pass the value as a parameter.
In real projects
A school group moved its busiest reports from EF Core to Dapper for speed. The reports were faster at once, and everyone was pleased for about a week.
Then a principal noticed that removed students were back in the class lists. The EF Core version had a
query filter; the new SQL had no IsDeleted condition, and no test had a removed student in it.
We added the condition, then a test fixture with one removed student per class. After that, every new query had to pass the "removed student" test before it was merged.
Best practices
- Keep data access behind interfaces, so a tool change stays inside one project.
- Before switching tools, list what the old one did for you, and test each item.
- Use one transaction for any change that spans several statements.
- Check
RowVersionin theWHEREclause, and treat zero rows as a conflict. - Compare old and new with the same tests and the same requests before you remove anything.
Recap
- Only
AddSchoolDatachanged which classes the API uses; no controller changed. - EF Core's quiet work became explicit: filters, change lists, transactions, and conflict checks.
- The same 20 GET answers matched exactly, and 65 tests passed.
- The swap found a hidden dependency on EF Core's fix-up, now fixed in the entity.
- Dapper was 1 to 2 ms faster on most calls; heavy reports depend on the SQL.
Homework
Library module task: Write a single-file library API with GET /api/books and GET /api/books/{isbn}
behind an IBookCatalog interface. Give it two classes: an in-memory catalog and a Dapper catalog that reads
Books and BookCopies.
One line in the setup chooses the class. Both must give identical answers.
A correct result looks like (both stores):
GET /api/books
[{"isbn":"9780143039655","title":"Malgudi Days","author":"R. K. Narayan","copies":1,"available":1},{"isbn":"9780141325293","title":"The Jungle Book","author":"Rudyard Kipling","copies":1,"available":0},{"isbn":"9788173711466","title":"Wings of Fire","author":"A. P. J. Abdul Kalam","copies":2,"available":2}]
GET /api/books/9780143039655
{"isbn":"9780143039655","title":"Malgudi Days","author":"R. K. Narayan","copies":1,"available":1}
GET /api/books/111 404
Show the solution
// Library homework, Lesson 81: one interface, two stores. The controller asks IBookCatalog for books;
// one line in the setup decides whether the answer comes from memory or from SchoolDb with Dapper.
// Run with: dotnet run library-assignment/081-library-repository-swap.cs (Dapper, SchoolDb)
// dotnet run library-assignment/081-library-repository-swap.cs memory (in memory)
#:project ../src/SchoolApp.Data/SchoolApp.Data.csproj
#:sdk Microsoft.NET.Sdk.Web
#:property PublishAot=false
using Dapper;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using SchoolApp.Data;
WebApplicationBuilder builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();
builder.Services.AddSingleton(SqlConnectionFactory.FromEnvironment());
// The only line that knows which store is used.
if (args.Contains("memory"))
{
builder.Services.AddSingleton<IBookCatalog, InMemoryBookCatalog>();
}
else
{
builder.Services.AddScoped<IBookCatalog, DapperBookCatalog>();
}
WebApplication app = builder.Build();
app.MapControllers();
app.Run("http://localhost:5081");
public record BookDto(string Isbn, string Title, string Author, int Copies, int Available);
public interface IBookCatalog
{
Task<List<BookDto>> GetAllAsync(CancellationToken cancellationToken);
Task<BookDto?> FindAsync(string isbn, CancellationToken cancellationToken);
}
// The three sample books, as they are in SchoolDb.
public class InMemoryBookCatalog : IBookCatalog
{
private readonly List<BookDto> _books =
[
new BookDto("9780141325293", "The Jungle Book", "Rudyard Kipling", 1, 0),
new BookDto("9780143039655", "Malgudi Days", "R. K. Narayan", 1, 1),
new BookDto("9788173711466", "Wings of Fire", "A. P. J. Abdul Kalam", 2, 2)
];
public Task<List<BookDto>> GetAllAsync(CancellationToken cancellationToken) =>
Task.FromResult(_books.OrderBy(b => b.Title).ToList());
public Task<BookDto?> FindAsync(string isbn, CancellationToken cancellationToken) =>
Task.FromResult(_books.SingleOrDefault(b => b.Isbn == isbn));
}
// The same answers from SchoolDb. No DateOnly columns, so Dapper can fill the record directly.
public class DapperBookCatalog : IBookCatalog
{
private const string SelectBooks = """
SELECT b.Isbn, b.Title, b.Author,
COUNT(c.Id) AS Copies,
SUM(CASE WHEN c.Status = 'Available' THEN 1 ELSE 0 END) AS Available
FROM dbo.Books AS b
LEFT JOIN dbo.BookCopies AS c ON c.BookId = b.Id
WHERE b.IsDeleted = 0
""";
private readonly SqlConnectionFactory _connections;
public DapperBookCatalog(SqlConnectionFactory connections)
{
_connections = connections;
}
public async Task<List<BookDto>> GetAllAsync(CancellationToken cancellationToken)
{
await using SqlConnection connection = _connections.Create();
IEnumerable<BookDto> books = await connection.QueryAsync<BookDto>(new CommandDefinition(
SelectBooks + " GROUP BY b.Isbn, b.Title, b.Author ORDER BY b.Title;", cancellationToken: cancellationToken));
return books.ToList();
}
public async Task<BookDto?> FindAsync(string isbn, CancellationToken cancellationToken)
{
await using SqlConnection connection = _connections.Create();
return await connection.QuerySingleOrDefaultAsync<BookDto>(new CommandDefinition(
SelectBooks + " AND b.Isbn = @Isbn GROUP BY b.Isbn, b.Title, b.Author;",
new { Isbn = new DbString { Value = isbn, IsAnsi = true, Length = 13 } },
cancellationToken: cancellationToken));
}
}
[ApiController]
[Route("api/books")]
public class BooksController : ControllerBase
{
private readonly IBookCatalog _catalog;
public BooksController(IBookCatalog catalog)
{
_catalog = catalog;
}
[HttpGet]
public Task<List<BookDto>> GetAll(CancellationToken cancellationToken) => _catalog.GetAllAsync(cancellationToken);
[HttpGet("{isbn}")]
public async Task<ActionResult<BookDto>> Get(string isbn, CancellationToken cancellationToken)
{
BookDto? book = await _catalog.FindAsync(isbn, cancellationToken);
return book is null ? NotFound() : book;
}
}
Run it once without arguments (Dapper) and once with memory. A diff of the two lists shows no
difference.
Quiz
Why did no controller change when the SMS switched to Dapper?
Show answer
Answer: B. Controllers depend on interfaces; only AddSchoolData names the classes The registration is the only place that chooses an implementation.
What replaces EF Core's soft-delete query filter in Dapper?
Show answer
Answer: B. IsDeleted = 0 written in every query that reads students Dapper runs exactly the SQL you write.
Why did SQL Server refuse an AttendanceStatus parameter?
Show answer
Answer: B. Dapper sent the enum's number, and the CHECK constraint allows only names Error 547: the column expects names such as Late.
How does DapperFeeInvoiceRepository notice that another clerk saved first?
Show answer
Answer: B. Its UPDATE includes the old RowVersion, so no row matches Zero rows changed means a conflict: error 50014.
What caused the 500 after the first Dapper payment?
Show answer
Answer: B. The code relied on EF Core's fix-up to set payment.FeeInvoice The entity now sets the link itself.
Interview questions
Questions are grouped by experience level. Click a question to see a model answer. All questions: Web API interview questions.
Fresher (0-2 yrs)
F How can you switch an API's data access from EF Core to Dapper without changing the controllers? concept
Keep data access behind interfaces such as IStudentRepository, and let one registration method decide the classes. Controllers and services only ask for the interfaces. In the SMS, AddSchoolData changed from the EfCore classes to the Dapper classes, and no controller changed.
F EF Core had a soft-delete query filter. What must you do instead when you use Dapper? concept
Write the condition yourself, such as IsDeleted = 0, in every query that reads the table, including joins and counts. Dapper runs exactly the SQL you write. A test with one removed student catches a forgotten condition.
F Why might a team choose Dapper for an API instead of EF Core? concept
Dapper runs SQL that the team writes and can read, so queries are exact and easy to review with a database team, and its overhead is very small. EF Core gives change tracking, LINQ, migrations, and less code for simple writes. Many teams use EF Core for writes and Dapper for heavy reads; the SMS chose Dapper for the whole API.
Mid (3-5 yrs)
M Dapper threw SqlException 547 when saving an attendance status. What went wrong? scenario
The enum AttendanceStatus was passed as it is, and Dapper sends an enum's number (Late is 2). The
Status column holds names, and CK_AttendanceRecords_Status refused the number. Pass Status.ToString().
EF Core did this conversion for us with HasConversion<string>().
M How do you save several related changes together with Dapper? coding
Open the connection, start a SqlTransaction, pass the transaction to every Dapper call, and commit at the end. If anything throws, disposing the transaction without committing rolls everything back. In the SMS, the invoice update and the new payment, and the attendance updates and inserts, each use one transaction.
M How do you implement optimistic concurrency with a RowVersion column in Dapper? coding
Read the RowVersion with the row. Update with WHERE Id = @Id AND RowVersion = @RowVersion, and read the new value with OUTPUT INSERTED.RowVersion. If no row comes back, someone else saved first: roll back and report a conflict. The SMS reports it as error 50014, just as the EF Core version did.
Senior (6-9 yrs)
S After switching to Dapper, a payment is saved but the API answers 500 with a NullReferenceException. How do you find and fix it? scenario
Use the traceId to find the log entry and its stack trace. In the SMS it pointed to the mapping code, which read payment.FeeInvoice. EF Core's relationship fix-up had always filled that link; Dapper does not. The right fix is in the entity: RecordPayment sets FeeInvoice = this, so the object model is correct whichever tool loads it. Then add a test that checks the link.
How to answer: Go from symptom to log to cause, explain the hidden ORM behaviour, and say why the fix belongs in the domain model rather than the repository, with a test to keep it fixed.
S How would you prove that replacing a data-access layer did not change the API's behaviour? design
Run the same tests against both implementations (the SMS enrollment tests run once per tool, including the lock test). Call the same requests on the old and new builds and diff the responses; the SMS compared 20 GET answers and found no difference. Exercise every write path, then compare timings. Keep the old implementation until all of this passes.
How to answer: Cover automated tests, side-by-side comparison of real responses, write paths, performance, and a safe rollout with the old code still available.
Architect / Lead (10+ yrs)
A Your company's APIs use EF Core, and one team wants Dapper. How would you set a data-access standard across many teams? design
Agree what must be true whatever the tool: repositories behind interfaces, one transaction per business change, concurrency checks, soft-delete rules, parameterised SQL, and integration tests against a real database. Allow EF Core for most CRUD and Dapper where SQL control or speed matters, decided per service and recorded. Provide shared helpers (connection factory, transaction helper, row-to-DTO patterns) and review checklists for the things EF Core did implicitly. Measure before switching for speed.
How to answer: Separate the non-negotiable rules from the tool choice, give criteria for choosing, and explain how you support teams and enforce the rules.
Resources
- Code for this lesson: git tag
lesson-81-dapper-repositories(code repository coming soon) - Files:
src/SchoolApp.Data/DependencyInjection.cs,src/SchoolApp.Data/Dapper/DapperStudentQueries.cs,src/SchoolApp.Data/Dapper/DapperFeeInvoiceRepository.cs,src/SchoolApp.Data/Dapper/DapperEnrollmentRepository.cs,tests/SchoolApp.Tests/Integration/EnrollmentTests.cs,tests/SchoolApp.Tests/Integration/DapperFeeInvoiceTests.cs,library-assignment/081-library-repository-swap.cs - Video: coming soon
What's next
The SMS now talks to SchoolDb with Dapper and answers in one shape. Next: exams, entering a whole paper's marks at once, and the report card endpoint. Lesson 82: Exams, marks, and report cards
Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.