Repository Pattern and Layered Architecture – Data Access | Lesson 62

Published / updated7 Oct 2026

Introduction

SchoolApp can now reach SchoolDb three ways, and each way has its own class with its own methods. If StudentService used EfStudentRepository directly, it would be tied to EF Core forever.

The repository pattern puts an interface between the business rules and the data access. The service asks the interface for students; it never knows whether the answer came from memory, ADO.NET, Dapper, or EF Core.

By the end of this lesson, you will be able to:

Explain the layers of SchoolApp and the direction of their dependencies.
Design a repository interface around what the application needs.
Implement one interface with three data-access tools and swap them freely.
Explain the unit of work, and why a DbContext already is one.
Recognise when a repository adds value, and when it only adds code.

Why this matters

Business rules change slowly; data-access tools change more often. Teams move from ADO.NET to Dapper, add EF Core, or replace a slow query with a stored procedure. With a repository in between, those changes stay inside one project.

It also makes testing possible. In Lesson 63, the same service is tested with an in-memory repository in milliseconds, and with the real database in an integration test.

The concept

Example: the layers of SchoolApp

SchoolApp now has three projects, each with one kind of job:

Layer (project)	Holds	Knows about
`SchoolApp.Console` (later `SchoolApp.Api`)	Starting the program, choosing implementations	Core and Data
`SchoolApp.Core`	Entities, rules, services, DTOs, interfaces	Nothing else in SchoolApp
`SchoolApp.Data`	ADO.NET, Dapper, EF Core, SQL	Core

Every arrow points toward Core. Core holds the most important code, the rules, so it depends on nothing that changes often.

Example: dependency inversion

Normally, high-level code calls low-level code, so it depends on it: the service would reference the data project. Here, the arrow is reversed. Core defines IStudentRepository, and Data implements it.

This is the D in SOLID from Lesson 46: depend on abstractions, not on details. The detail, SQL, depends on the abstraction, not the other way round.

Think of a school office that hires a courier company. The office writes the rules: pick up by 10, deliver by 4. Any courier who follows them can do the job, and changing couriers never changes the office's rules.

Example: the unit of work

A unit of work groups several changes so they are saved together, or not at all. Admitting a student, creating the enrollment, and issuing the first invoice should succeed or fail as one.

EF Core's DbContext already is a unit of work: it collects every change and SaveChangesAsync writes them in one transaction. With ADO.NET or Dapper, you build that yourself with a shared connection and a transaction, which Lesson 64 covers.

Apply it to the SMS

Step 1: the interface, made asynchronous

The interface from Lesson 46 had synchronous methods. Every real store is I/O, so it becomes asynchronous (Lesson 53), with a CancellationToken on each method:

src/SchoolApp.Core/Interfaces/IStudentRepository.cs
using SchoolApp.Core.Entities;

namespace SchoolApp.Core.Interfaces;

// Lesson 46: what the application needs from student storage, and nothing more.
// Lesson 62: asynchronous, because every real store is I/O (Lesson 53). Implemented four ways:
// in memory (console), ADO.NET, Dapper, and EF Core (SchoolApp.Data).
public interface IStudentRepository
{
    Task<Student?> GetByAdmissionNumberAsync(string admissionNumber, CancellationToken cancellationToken = default);

    // Saves a new student and sets its Id.
    Task AddAsync(Student student, CancellationToken cancellationToken = default);
}

It still holds only what StudentService needs: find one student and add one. Methods such as SearchByLastNameAsync stay on the concrete classes until a service needs them.

Step 2: the service

src/SchoolApp.Core/Services/StudentService.cs
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Interfaces;
using SchoolApp.Core.Mapping;

namespace SchoolApp.Core.Services;

// Lesson 46: one job (admitting and finding students), and it depends on an interface,
// not on any particular database code. The repository is passed in through the constructor.
// Lesson 62: asynchronous, like the repository it uses.
public class StudentService
{
    private readonly IStudentRepository _students;

    public StudentService(IStudentRepository students)
    {
        _students = students;
    }

    public async Task<StudentDto> AdmitAsync(CreateStudentRequest request, CancellationToken cancellationToken = default)
    {
        if (await _students.GetByAdmissionNumberAsync(request.AdmissionNumber, cancellationToken) is not null)
        {
            throw new InvalidOperationException($"Admission number {request.AdmissionNumber} is already in use.");
        }

        Student student = request.ToEntity();
        await _students.AddAsync(student, cancellationToken);
        return student.ToDto();
    }

    public async Task<StudentDto?> FindAsync(string admissionNumber, CancellationToken cancellationToken = default)
    {
        Student? student = await _students.GetByAdmissionNumberAsync(admissionNumber, cancellationToken);
        return student?.ToDto();
    }
}

The service's rule, "admission numbers are unique", lives here once. Not one line of it mentions SQL, connections, or EF Core.

Step 3: three implementations

Each repository from Lessons 57 to 61 now declares : IStudentRepository and gains an AddAsync. The ADO.NET and Dapper versions run an INSERT with OUTPUT INSERTED.Id, INSERTED.CreatedAt, so the new Id and the database's CreatedAt come back in the same round trip.

Show src/SchoolApp.Data/AdoNet/AdoNetStudentRepository.cs

src/SchoolApp.Data/AdoNet/AdoNetStudentRepository.cs
using System.Data;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;
using SchoolApp.Core.Interfaces;

namespace SchoolApp.Data.AdoNet;

// Lesson 57: plain ADO.NET. Every step is written by hand: open a connection, build a command,
// add parameters, run it, and copy each column of each row into a C# object.
// Lesson 62: implements IStudentRepository, so StudentService can use it.
public class AdoNetStudentRepository : IStudentRepository
{
    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 AdoNetStudentRepository(SqlConnectionFactory connections)
    {
        _connections = connections;
    }

    // ExecuteScalar: returns the first column of the first row. Made for COUNT(*).
    public async Task<int> CountActiveAsync(CancellationToken cancellationToken = default)
    {
        await using SqlConnection connection = _connections.Create();
        await connection.OpenAsync(cancellationToken);

        await using SqlCommand command = new SqlCommand(
            "SELECT COUNT(*) FROM dbo.Students WHERE Status = 'Active' AND IsDeleted = 0;", connection);

        object? count = await command.ExecuteScalarAsync(cancellationToken);
        return (int)count!;
    }

    // ExecuteReader: rows, read one at a time. @AdmissionNumber is a parameter: its value travels
    // to SQL Server separately from the SQL text, so it can never change what the query does.
    public async Task<Student?> GetByAdmissionNumberAsync(string admissionNumber, CancellationToken cancellationToken = default)
    {
        await using SqlConnection connection = _connections.Create();
        await connection.OpenAsync(cancellationToken);

        await using SqlCommand command = new SqlCommand(
            SelectStudent + " WHERE AdmissionNumber = @AdmissionNumber AND IsDeleted = 0;", connection);
        command.Parameters.Add("@AdmissionNumber", SqlDbType.VarChar, 15).Value = admissionNumber;

        await using SqlDataReader reader = await command.ExecuteReaderAsync(cancellationToken);
        if (await reader.ReadAsync(cancellationToken))
        {
            return MapStudent(reader);
        }
        return null;  // no row: no such student
    }

    public async Task<List<Student>> SearchByLastNameAsync(string lastName, CancellationToken cancellationToken = default)
    {
        await using SqlConnection connection = _connections.Create();
        await connection.OpenAsync(cancellationToken);

        await using SqlCommand command = new SqlCommand(
            SelectStudent + " WHERE LastName = @LastName AND IsDeleted = 0 ORDER BY Id;", connection);
        command.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = lastName;

        List<Student> students = new List<Student>();
        await using SqlDataReader reader = await command.ExecuteReaderAsync(cancellationToken);
        while (await reader.ReadAsync(cancellationToken))
        {
            students.Add(MapStudent(reader));
        }
        return students;
    }

    // Lesson 62: INSERT with OUTPUT returns the new Id and the database's CreatedAt in the same round trip.
    public async Task AddAsync(Student student, CancellationToken cancellationToken = default)
    {
        await using SqlConnection connection = _connections.Create();
        await connection.OpenAsync(cancellationToken);

        await using SqlCommand command = new SqlCommand("""
            INSERT INTO dbo.Students (AdmissionNumber, FirstName, LastName, DateOfBirth, Gender, AdmissionDate, Address, Status, CreatedBy)
            OUTPUT INSERTED.Id, INSERTED.CreatedAt
            VALUES (@AdmissionNumber, @FirstName, @LastName, @DateOfBirth, @Gender, @AdmissionDate, @Address, @Status, @CreatedBy);
            """, connection);
        command.Parameters.Add("@AdmissionNumber", SqlDbType.VarChar, 15).Value = student.AdmissionNumber;
        command.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = student.FirstName;
        command.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = student.LastName;
        command.Parameters.Add("@DateOfBirth", SqlDbType.Date).Value = student.DateOfBirth;
        command.Parameters.Add("@Gender", SqlDbType.Char, 1).Value = ((char)student.Gender).ToString();
        command.Parameters.Add("@AdmissionDate", SqlDbType.Date).Value = student.AdmissionDate;
        command.Parameters.Add("@Address", SqlDbType.NVarChar, 200).Value = (object?)student.Address ?? DBNull.Value;
        command.Parameters.Add("@Status", SqlDbType.VarChar, 12).Value = student.Status.ToString();
        command.Parameters.Add("@CreatedBy", SqlDbType.Int).Value = (object?)student.CreatedBy ?? DBNull.Value;

        await using SqlDataReader reader = await command.ExecuteReaderAsync(cancellationToken);
        await reader.ReadAsync(cancellationToken);
        student.Id = reader.GetInt32(0);
        student.CreatedAt = reader.GetDateTime(1);
    }

    // A stored procedure: CommandType.StoredProcedure, and the command text is only its name.
    public async Task<List<ClassListEntryDto>> GetClassListAsync(int classId, string sectionName, CancellationToken cancellationToken = default)
    {
        await using SqlConnection connection = _connections.Create();
        await connection.OpenAsync(cancellationToken);

        await using SqlCommand command = new SqlCommand("dbo.usp_GetClassList", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@ClassId", SqlDbType.Int).Value = classId;
        command.Parameters.Add("@SectionName", SqlDbType.VarChar, 2).Value = sectionName;

        List<ClassListEntryDto> entries = new List<ClassListEntryDto>();
        await using SqlDataReader reader = await command.ExecuteReaderAsync(cancellationToken);
        while (await reader.ReadAsync(cancellationToken))
        {
            entries.Add(new ClassListEntryDto(
                reader.GetInt32(reader.GetOrdinal("EnrollmentId")),
                reader.GetInt32(reader.GetOrdinal("StudentId")),
                reader.GetString(reader.GetOrdinal("AdmissionNumber")),
                reader.GetString(reader.GetOrdinal("FullName")),
                reader.GetInt16(reader.GetOrdinal("RollNumber"))));  // SMALLINT is short in C#
        }
        return entries;
    }

    // Copies one row into a Student. Columns are found by name, so their order in the SELECT does not matter.
    private static Student MapStudent(SqlDataReader reader)
    {
        Student student = new Student(
            reader.GetString(reader.GetOrdinal("AdmissionNumber")),
            reader.GetString(reader.GetOrdinal("FirstName")),
            reader.GetString(reader.GetOrdinal("LastName")),
            reader.GetFieldValue<DateOnly>(reader.GetOrdinal("DateOfBirth")),
            (Gender)reader.GetString(reader.GetOrdinal("Gender"))[0]);  // "M" becomes Gender.Male

        student.Id = reader.GetInt32(reader.GetOrdinal("Id"));
        student.AdmissionDate = reader.GetFieldValue<DateOnly>(reader.GetOrdinal("AdmissionDate"));
        student.Status = Enum.Parse<StudentStatus>(reader.GetString(reader.GetOrdinal("Status")));
        student.CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt"));

        // A NULL column arrives as DBNull, so nullable columns are checked first.
        int address = reader.GetOrdinal("Address");
        student.Address = reader.IsDBNull(address) ? null : reader.GetString(address);
        int createdBy = reader.GetOrdinal("CreatedBy");
        student.CreatedBy = reader.IsDBNull(createdBy) ? null : reader.GetInt32(createdBy);
        int updatedAt = reader.GetOrdinal("UpdatedAt");
        student.UpdatedAt = reader.IsDBNull(updatedAt) ? null : reader.GetDateTime(updatedAt);
        int updatedBy = reader.GetOrdinal("UpdatedBy");
        student.UpdatedBy = reader.IsDBNull(updatedBy) ? null : reader.GetInt32(updatedBy);

        return student;
    }
}

Show src/SchoolApp.Data/Dapper/DapperStudentRepository.cs

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;
using SchoolApp.Core.Interfaces;

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.
// Lesson 62: implements IStudentRepository, so StudentService can use it.
public class DapperStudentRepository : IStudentRepository
{
    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();
    }

    // Lesson 62: the same INSERT as the ADO.NET version; the OUTPUT row arrives as a value tuple.
    public async Task AddAsync(Student student, CancellationToken cancellationToken = default)
    {
        const string sql = """
            INSERT INTO dbo.Students (AdmissionNumber, FirstName, LastName, DateOfBirth, Gender, AdmissionDate, Address, Status, CreatedBy)
            OUTPUT INSERTED.Id, INSERTED.CreatedAt
            VALUES (@AdmissionNumber, @FirstName, @LastName, @DateOfBirth, @Gender, @AdmissionDate, @Address, @Status, @CreatedBy);
            """;

        await using SqlConnection connection = _connections.Create();
        (int id, DateTime createdAt) = await connection.QuerySingleAsync<(int, DateTime)>(new CommandDefinition(sql, new
        {
            AdmissionNumber = new DbString { Value = student.AdmissionNumber, IsAnsi = true, Length = 15 },
            student.FirstName,
            student.LastName,
            student.DateOfBirth,
            Gender = new DbString { Value = ((char)student.Gender).ToString(), IsAnsi = true, IsFixedLength = true, Length = 1 },
            student.AdmissionDate,
            student.Address,
            Status = new DbString { Value = student.Status.ToString(), IsAnsi = true, Length = 12 },
            student.CreatedBy
        }, cancellationToken: cancellationToken));

        student.Id = id;
        student.CreatedAt = createdAt;
    }

    // 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
            };
        }
    }
}

Show src/SchoolApp.Data/EfCore/EfStudentRepository.cs

src/SchoolApp.Data/EfCore/EfStudentRepository.cs
using Microsoft.EntityFrameworkCore;
using SchoolApp.Core.Common;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;
using SchoolApp.Core.Interfaces;

namespace SchoolApp.Data.EfCore;

// Lesson 59: the same operations as Lessons 57 and 58, with EF Core. You write LINQ;
// EF Core writes the SQL, adds the parameters, and creates the Student objects.
// Lesson 60: no more "!s.IsDeleted" here: the soft-delete query filter adds it to every query.
// Lesson 62: implements IStudentRepository, so StudentService can use it.
public class EfStudentRepository : IStudentRepository
{
    private readonly SchoolDbContext _db;

    public EfStudentRepository(SchoolDbContext db)
    {
        _db = db;
    }

    public Task<int> CountActiveAsync(CancellationToken cancellationToken = default)
    {
        return _db.Students.CountAsync(s => s.Status == StudentStatus.Active, cancellationToken);
    }

    public Task<Student?> GetByAdmissionNumberAsync(string admissionNumber, CancellationToken cancellationToken = default)
    {
        return _db.Students.SingleOrDefaultAsync(s => s.AdmissionNumber == admissionNumber, cancellationToken);
    }

    // Lesson 62: Add only marks the student as Added; SaveChangesAsync writes it and sets Id.
    public async Task AddAsync(Student student, CancellationToken cancellationToken = default)
    {
        _db.Students.Add(student);
        await _db.SaveChangesAsync(cancellationToken);
    }

    public Task<List<Student>> SearchByLastNameAsync(string lastName, CancellationToken cancellationToken = default)
    {
        return _db.Students
            .Where(s => s.LastName == lastName)
            .OrderBy(s => s.Id)
            .ToListAsync(cancellationToken);
    }

    // Lesson 61: one page of students for a list screen. The projection reads only the DTO's columns
    // (projections are never tracked), and Skip/Take become OFFSET/FETCH, so only one page is sent.
    public async Task<PagedResult<StudentDto>> GetPageAsync(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}.");
        }

        int total = await _db.Students.CountAsync(cancellationToken);
        List<StudentDto> items = await _db.Students
            .OrderBy(s => s.LastName).ThenBy(s => s.FirstName).ThenBy(s => s.Id)  // a stable order for paging
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .Select(s => new StudentDto(s.Id, s.AdmissionNumber, s.FirstName + " " + s.LastName,
                                        s.DateOfBirth, s.Gender.ToString(), s.Status.ToString()))
            .ToListAsync(cancellationToken);

        return new PagedResult<StudentDto>(items, page, pageSize, total);
    }

    // EF Core can still run your own SQL. In SqlQuery, each {value} becomes a parameter, not text,
    // so this is as safe from SQL injection as Lessons 57 and 58.
    public Task<List<ClassListEntryDto>> GetClassListAsync(int classId, string sectionName, CancellationToken cancellationToken = default)
    {
        return _db.Database
            .SqlQuery<ClassListEntryDto>($"EXEC dbo.usp_GetClassList @ClassId = {classId}, @SectionName = {sectionName}")
            .ToListAsync(cancellationToken);
    }
}

The EF Core version is two lines: Add marks the student as new, and SaveChangesAsync writes it and fills Id. The in-memory version moved to the async interface too:

src/SchoolApp.Console/InMemoryStudentRepository.cs
using SchoolApp.Core.Entities;
using SchoolApp.Core.Interfaces;

// Lesson 46: a stand-in for the database, kept in memory, for trying the service and for tests.
// Lesson 62: asynchronous like the interface, and it can start with some students already in it.
class InMemoryStudentRepository : IStudentRepository
{
    private readonly Dictionary<string, Student> _byAdmissionNumber = new(StringComparer.OrdinalIgnoreCase);
    private int _nextId = 10013;  // SchoolDb already has 10,012 students

    public InMemoryStudentRepository(params IEnumerable<Student> students)
    {
        foreach (Student student in students)
        {
            _byAdmissionNumber.Add(student.AdmissionNumber, student);
        }
    }

    public Task<Student?> GetByAdmissionNumberAsync(string admissionNumber, CancellationToken cancellationToken = default)
    {
        // Nothing to wait for in memory, so the result is wrapped in a finished task.
        return Task.FromResult(_byAdmissionNumber.GetValueOrDefault(admissionNumber));
    }

    public Task AddAsync(Student student, CancellationToken cancellationToken = default)
    {
        student.Id = _nextId++;
        _byAdmissionNumber.Add(student.AdmissionNumber, student);
        return Task.CompletedTask;
    }
}

Step 4: the composition root

The composition root is the one place where concrete classes are chosen and connected. In the console app it is Program.cs; in the Web API it will be the dependency injection setup in Module 10.

Show src/SchoolApp.Console/Program.cs

src/SchoolApp.Console/Program.cs
// Lesson 62: the repository pattern. One StudentService, four repositories behind one interface.
// The service's code is identical every time; only the object passed to its constructor changes.
using System.Transactions;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;
using SchoolApp.Core.Interfaces;
using SchoolApp.Core.Services;
using SchoolApp.Data;
using SchoolApp.Data.AdoNet;
using SchoolApp.Data.Dapper;
using SchoolApp.Data.EfCore;

SqlConnectionFactory connections = SqlConnectionFactory.FromEnvironment();
await using SchoolDbContext db = SchoolDbContextFactory.Create(connections);

// The in-memory store starts with Kabir, so all four can answer the same questions.
Student kabirInMemory = new Student("ADM-2017-0003", "Kabir", "Singh", new DateOnly(2011, 1, 30), Gender.Male) { Id = 3 };

// The composition root: the one place that decides which implementation is used.
IStudentRepository[] repositories =
[
    new InMemoryStudentRepository(kabirInMemory),
    new AdoNetStudentRepository(connections),
    new DapperStudentRepository(connections),
    new EfStudentRepository(db),
];

CreateStudentRequest ishaan = new CreateStudentRequest(
    "ADM-2026-10001", "Ishaan", "Kumar", new DateOnly(2016, 4, 12), Gender.Male, new DateOnly(2026, 10, 7), null);
CreateStudentRequest kabirAgain = new CreateStudentRequest(
    "ADM-2017-0003", "Kabir", "Singh", new DateOnly(2011, 1, 30), Gender.Male, new DateOnly(2017, 6, 1), null);

foreach (IStudentRepository repository in repositories)
{
    StudentService service = new StudentService(repository);
    Console.WriteLine(repository.GetType().Name);

    StudentDto? kabir = await service.FindAsync("ADM-2017-0003");
    Console.WriteLine($"  Find:  {kabir?.FullName}, Id {kabir?.Id}, {kabir?.Gender}");

    // A TransactionScope that is never completed rolls back everything inside it when it is
    // disposed, so the new student is saved, found again, and then removed. (Lesson 63 explains.)
    using (TransactionScope scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
    {
        StudentDto admitted = await service.AdmitAsync(ishaan);
        StudentDto? foundAgain = await service.FindAsync(ishaan.AdmissionNumber);
        Console.WriteLine($"  Admit: {admitted.FullName} got Id {admitted.Id}; found again: {foundAgain is not null}");
    }
    Console.WriteLine($"  After the rollback, still there: {await service.FindAsync(ishaan.AdmissionNumber) is not null}");

    try
    {
        await service.AdmitAsync(kabirAgain);
    }
    catch (InvalidOperationException ex)
    {
        Console.WriteLine($"  Admit again: {ex.Message}");
    }
}

Output
InMemoryStudentRepository
  Find:  Kabir Singh, Id 3, Male
  Admit: Ishaan Kumar got Id 10013; found again: True
  After the rollback, still there: True
  Admit again: Admission number ADM-2017-0003 is already in use.
AdoNetStudentRepository
  Find:  Kabir Singh, Id 3, Male
  Admit: Ishaan Kumar got Id 10018; found again: True
  After the rollback, still there: False
  Admit again: Admission number ADM-2017-0003 is already in use.
DapperStudentRepository
  Find:  Kabir Singh, Id 3, Male
  Admit: Ishaan Kumar got Id 10019; found again: True
  After the rollback, still there: False
  Admit again: Admission number ADM-2017-0003 is already in use.
EfStudentRepository
  Find:  Kabir Singh, Id 3, Male
  Admit: Ishaan Kumar got Id 10020; found again: True
  After the rollback, still there: False
  Admit again: Admission number ADM-2017-0003 is already in use.

The service behaved the same with all four repositories. Your database Ids will be different, and they grow on every run: like the receipt sequence in Lesson 57, an IDENTITY value is never given back after a rollback.

Example: what the rollback reveals

Inside a TransactionScope that is never completed, each database repository saved Ishaan, found him, and then lost him when the scope ended. The in-memory repository kept him: a dictionary does not take part in database transactions.

This is a small leaky abstraction: the interface looks the same, but the behaviour differs in a way the interface does not show. Know where your implementations differ, and test the important behaviour against the real one too, which Lesson 63 does.

Example: generic repositories

A common design is one IRepository<T> with GetById, GetAll, Add, Update, and Delete for every table. It looks tidy, but it tends to cause trouble:

Generic `IRepository<T>`	Specific `IStudentRepository`
Methods for every table, used or not	Only the methods the application needs
`GetAll()` invites loading 10,012 rows	Purpose-made queries, such as paging
Often returns `IQueryable<T>`, leaking EF Core to callers	Returns finished results
Duplicates what `DbSet<T>` already does	Adds meaning: names from the school's language

SchoolApp uses specific repositories. Each method's name says what the school needs, such as GetByAdmissionNumberAsync, not just what the database can do.

Example: when a repository is not worth it

A repository earns its place when the rules are worth protecting, when the data access may change, or when tests need a fake. A small tool with three screens and no rules has none of those reasons.

In such an app, using the DbContext directly is simpler and perfectly fine. EF Core can be tested against a real database too. The pattern is a tool, not a law: add it where it removes a real cost, the same test as for the design patterns in Lesson 56.

Common mistakes

Data references in Core. One using Microsoft.EntityFrameworkCore in Core ties every rule to EF Core. Fix: Core references no data-access package.
Returning IQueryable from a repository. Callers then build queries, and the database leaks through. Fix: return lists, single objects, or pages.
A generic repository wrapped around EF Core. It hides features and repeats DbSet. Fix: specific methods, or use the DbContext directly where it is enough.
Choosing implementations all over the code. new EfStudentRepository(...) in ten places. Fix: one composition root.
Saving inside every repository method when several changes belong together. Partial saves follow. Fix: a unit of work for operations that span several changes.

In real projects

From 16 years of .NET projects

A school group I worked with had to move its reporting from EF Core to Dapper after the data grew fivefold. In the first application, EF Core queries were spread through 60 controller actions, and the move took three months.

In the second application, every query sat behind repository interfaces. The same move took two weeks: new implementations in the data project, one line changed per repository in the composition root.

The interfaces cost a few extra files at the start. They paid for themselves the first time the data access had to change.

Best practices

Keep Core free of data-access packages; Data references Core, never the reverse.
Design repository interfaces from what services need, named in the school's language.
Return finished results: entities, DTOs, or pages, never IQueryable.
Choose implementations only in the composition root.
Treat the DbContext as the unit of work for EF Core; build one explicitly for ADO.NET and Dapper.

Recap

The repository pattern puts an interface between rules and data access.
Core defines the interface; Data implements it: dependency inversion.
One StudentService worked unchanged with four repositories.
The composition root is the one place that picks implementations.
A DbContext is already a unit of work.
Specific repositories beat a generic IRepository<T> in most applications.

Homework

Library module task: Create an IBookCatalog interface with one method, FindByIsbnAsync, and a LibraryDesk service that answers "can this book be issued?". Implement the catalog twice: in memory and with Dapper. Run the same desk with both and show identical answers.

A correct result looks like:

Output
InMemoryBookCatalog
  Wings of Fire: yes, 2 available
  The Jungle Book: no, every copy is issued
  9999999999999: no such book
DapperBookCatalog
  Wings of Fire: yes, 2 available
  The Jungle Book: no, every copy is issued
  9999999999999: no such book

Show the solution

library-assignment/062-library-repository.cs
// Library homework, Lesson 62: a library desk service that depends on an interface, with two
// implementations: one in memory, one with Dapper on SchoolDb.
// Run with:  dotnet run library-assignment/062-library-repository.cs
#:project ../src/SchoolApp.Data/SchoolApp.Data.csproj
#:property PublishAot=false
using Dapper;
using Microsoft.Data.SqlClient;
using SchoolApp.Data;

IBookCatalog[] catalogs =
[
    new InMemoryBookCatalog(
        new BookAvailability("9788173711466", "Wings of Fire", 2),
        new BookAvailability("9780141325293", "The Jungle Book", 0)),
    new DapperBookCatalog(SqlConnectionFactory.FromEnvironment()),
];

foreach (IBookCatalog catalog in catalogs)
{
    LibraryDesk desk = new LibraryDesk(catalog);
    Console.WriteLine(catalog.GetType().Name);
    Console.WriteLine($"  {await desk.CanIssueAsync("9788173711466")}");
    Console.WriteLine($"  {await desk.CanIssueAsync("9780141325293")}");
    Console.WriteLine($"  {await desk.CanIssueAsync("9999999999999")}");
}

// What the desk needs from storage, and nothing more.
interface IBookCatalog
{
    Task<BookAvailability?> FindByIsbnAsync(string isbn);
}

record BookAvailability(string Isbn, string Title, int AvailableCopies);

// The service: library rules only, no SQL. It works with any IBookCatalog.
class LibraryDesk
{
    private readonly IBookCatalog _catalog;

    public LibraryDesk(IBookCatalog catalog)
    {
        _catalog = catalog;
    }

    public async Task<string> CanIssueAsync(string isbn)
    {
        BookAvailability? book = await _catalog.FindByIsbnAsync(isbn);
        if (book is null)
        {
            return $"{isbn}: no such book";
        }
        return book.AvailableCopies > 0
            ? $"{book.Title}: yes, {book.AvailableCopies} available"
            : $"{book.Title}: no, every copy is issued";
    }
}

class InMemoryBookCatalog : IBookCatalog
{
    private readonly Dictionary<string, BookAvailability> _books;

    public InMemoryBookCatalog(params BookAvailability[] books)
    {
        _books = books.ToDictionary(b => b.Isbn);
    }

    public Task<BookAvailability?> FindByIsbnAsync(string isbn) => Task.FromResult(_books.GetValueOrDefault(isbn));
}

class DapperBookCatalog : IBookCatalog
{
    private readonly SqlConnectionFactory _connections;

    public DapperBookCatalog(SqlConnectionFactory connections)
    {
        _connections = connections;
    }

    public async Task<BookAvailability?> FindByIsbnAsync(string isbn)
    {
        await using SqlConnection connection = _connections.Create();
        return await connection.QuerySingleOrDefaultAsync<BookAvailability>("""
            SELECT b.Isbn, b.Title,
                   (SELECT COUNT(*) FROM dbo.BookCopies AS c WHERE c.BookId = b.Id AND c.Status = 'Available') AS AvailableCopies
            FROM dbo.Books AS b
            WHERE b.Isbn = @Isbn AND b.IsDeleted = 0;
            """, new { Isbn = new DbString { Value = isbn, IsAnsi = true, Length = 13 } });
    }
}

LibraryDesk contains the library's rule and nothing else. It cannot tell which catalog it was given, which is exactly the point.

Quiz

Which project defines IStudentRepository?
1. SchoolApp.Data
2. SchoolApp.Core
3. SchoolApp.Console
4. Each repository defines its own
Show answer
Answer: B. SchoolApp.Core Core defines what it needs; Data implements it.
What is the composition root?
1. The base class of all entities
2. The one place where concrete implementations are chosen
3. The database's master table
4. The first migration
Show answer
Answer: B. The one place where concrete implementations are chosen Program.cs in the console app; dependency injection setup in the Web API.
Why did the in-memory repository still find Ishaan after the rollback?
1. A bug in the service
2. A dictionary does not take part in database transactions
3. The rollback failed
4. TransactionScope needs Complete
Show answer
Answer: B. A dictionary does not take part in database transactions Same interface, different behaviour: a leaky abstraction to know about.
Which object is already a unit of work in EF Core?
1. DbSet
2. DbContext
3. IQueryable
4. SqlConnection
Show answer
Answer: B. DbContext It collects changes and saves them in one transaction.
Why avoid returning IQueryable from a repository?
1. It is slower
2. Callers build queries, so data-access details leak out
3. It cannot be awaited
4. It only works with Dapper
Show answer
Answer: B. Callers build queries, so data-access details leak out Repositories should return finished results.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: Architecture interview questions.

Fresher (0-2 yrs)

F What is the repository pattern? concept

An interface between business code and data access. The service asks the repository for entities, such as GetByAdmissionNumberAsync, and never sees SQL, connections, or the ORM. Implementations can use ADO.NET, Dapper, EF Core, or memory without the service changing.

See the example · Permalink

F What are the layers of a typical .NET application, and which way do the dependencies point? concept

A host layer (console or Web API) that starts the app and wires implementations, a core layer with entities, rules, services, and interfaces, and a data layer with the database code. Host and Data reference Core; Core references neither, so the rules depend on nothing that changes often.

See the example · Permalink

F What is a composition root? concept

The single place where concrete implementations are chosen and connected to the interfaces that use them. In SchoolApp's console it is Program.cs; in an ASP.NET Core app it is the dependency injection registration at startup. Everywhere else, code receives interfaces through constructors.

See the example · Permalink

Mid (3-5 yrs)

M How does the repository pattern apply dependency inversion? concept

The high-level core project defines the interface it needs, IStudentRepository, and the low-level data project implements it. The compile-time dependency points from Data to Core, the opposite of the call direction, so the rules never depend on database details.

See the example · Permalink

M What is a unit of work, and how does it relate to EF Core's DbContext? concept

A unit of work collects several changes and saves them together in one transaction, or not at all. A DbContext already is one: it tracks changes and SaveChangesAsync writes them atomically. With ADO.NET or Dapper you build it yourself, sharing one connection and transaction across operations.

See the example · Permalink

M Why should a repository not return IQueryable? concept

Callers would then compose queries, so database concerns, such as what can be translated to SQL and when the query runs, leak into the service layer, and the repository can no longer be swapped or faked faithfully. Return finished results: entities, DTOs, or pages.

See the example · Permalink

Senior (6-9 yrs)

S A teammate proposes a generic IRepository<T> with GetAll, GetById, Add, Update, and Delete for every table on top of EF Core. How do you respond? design

It mostly duplicates DbSet<T>, offers methods nobody should call, such as GetAll on 10,000 students, and often leaks IQueryable. Specific repositories with methods named after real needs, or the DbContext directly in simple apps, give the same isolation with clearer intent.

How to answer: Acknowledge the goal (consistency), list the concrete problems, propose the alternative, and say when you would accept a small generic base class for shared plumbing.

See the example · Permalink

S Your in-memory test repository and the real database behave differently in a test. Give an example and say how you would handle it. scenario

In SchoolApp, a rolled-back transaction removed a new student from SQL Server, but the in-memory repository kept it, because a dictionary does not take part in transactions. Unique constraints, case rules, and generated values can differ too. Keep fast unit tests on fakes for rules, and run integration tests against the real database for behaviour that depends on it.

How to answer: Name the leaky abstraction, give a concrete example, and describe the two-level test strategy that covers it.

See the example · Permalink

Architect / Lead (10+ yrs)

A When is adding a repository layer not worth it, and how do you decide for a new system? design

In small apps with few rules, a stable data-access choice, and tests that can use a real database, a repository adds files without removing a cost; the DbContext can be used directly. Add repositories where rules need protecting, data access may change, or tests need fakes, and keep the decision reversible by keeping database code out of the core.

How to answer: Give criteria rather than a yes or no, connect them to costs (change, testing, complexity), and show how a layered structure lets you add the pattern later without a rewrite.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-62-repository-pattern (code repository coming soon)
Files: src/SchoolApp.Core/Interfaces/IStudentRepository.cs, src/SchoolApp.Core/Services/StudentService.cs, src/SchoolApp.Data/EfCore/EfStudentRepository.cs, src/SchoolApp.Console/Program.cs, library-assignment/062-library-repository.cs
Video: coming soon

What's next

The service works with any repository. Next: proving it, with fast unit tests on an in-memory repository and integration tests that run the same checks against the real SchoolDb. Lesson 63: Testing data access

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: the layers of SchoolApp​

Example: dependency inversion​

Example: the unit of work​

Apply it to the SMS​

Step 1: the interface, made asynchronous​

Step 2: the service​

Step 3: three implementations​

Step 4: the composition root​

Example: what the rollback reveals​

Example: generic repositories​

Example: when a repository is not worth it​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: the layers of SchoolApp

Example: dependency inversion

Example: the unit of work

Apply it to the SMS

Step 1: the interface, made asynchronous

Step 2: the service

Step 3: three implementations

Step 4: the composition root

Example: what the rollback reveals

Example: generic repositories

Example: when a repository is not worth it

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next