Master Data API: Years, Classes, Sections, and Subjects – Web API | Lesson 76

Published / updated7 Oct 2026

Introduction

Master data is the reference information every other feature depends on: academic years, classes, sections, and subjects. An attendance screen needs a class and a section; a marks screen needs a subject; every report needs a year.

Master data is read constantly and changed rarely: a new subject a few times in a school's life, a new year once a year. This lesson builds endpoints that fit that pattern, including a header that lets clients reuse answers instead of asking again.

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

Explain what master data is and how it differs from transaction data.
Build read endpoints that return nested data, such as classes with their sections.
Tell clients how long they may keep a response with Cache-Control.
Add a create endpoint that answers 409 for duplicates the database rejects.
Name routes for two-word resources, such as /api/academic-years.

Why this matters

On a busy morning, every teacher's app loads the class and section lists before taking attendance. Without caching, that is the same question sent to the server thousands of times, with the same answer every time.

Master data also protects everything else. If two subjects called "Maths" and "Mathematics" both exist, marks split between them, and report cards show both. Uniqueness rules on master data keep the rest of the data clean.

The concept

Example: master data and transaction data

	Master data	Transaction data
SMS tables	AcademicYears, Classes, Sections, Subjects	AttendanceRecords, Marks, FeePayments
Changes	Rarely, by an administrator	Constantly, by teachers and clerks
Volume	Tens or hundreds of rows	Hundreds of thousands of rows
Read by	Almost every screen	The screens about that activity
Good fit for caching	Yes	Rarely

Think of two drawers in the school office: the subject list and timetable, opened all day and changed once a year, and the attendance sheets, filled every hour.

Example: Cache-Control

A response can carry a Cache-Control header that tells clients, and any cache between them and the server, how long they may reuse it. Cache-Control: public,max-age=3600 means "anyone may keep this for an hour".

The [ResponseCache(Duration = 3600)] attribute adds that header to an action's responses. It does not make the server remember anything; it only gives clients permission. Server-side caching, where the API itself keeps answers in memory, comes in Module 12.

Whether a client listens depends on the client. Browsers honour the header automatically, while app HTTP libraries vary: some need their cache switched on.

.NET's own HttpClient, for example, does not cache at all by default. The header is an offer that each client team can then decide to use.

Data	Changes	Suitable `max-age`
Academic years, subjects	A few times a year	An hour
Classes with student counts	During admissions	A few minutes
A student's attendance today	Every few minutes	None

Example: nested responses

A class on its own is rarely useful: screens need its sections too. Returning classes with their sections in one response saves the client from asking once per class. That is the N+1 problem from Lesson 61, seen from the client's side.

ClassDto holds a list of SectionDto. EF Core fills both from one LINQ statement, and each section brings its current student count, which the class picker can show next to the name.

Apply it to the SMS

Step 1: DTOs and an interface

src/SchoolApp.Core/Dtos/MasterDataDtos.cs
using System.ComponentModel.DataAnnotations;

namespace SchoolApp.Core.Dtos;

// Lesson 76: the master data every other feature depends on (Lesson 9's tables).

public record AcademicYearDto(int Id, string Name, DateOnly StartDate, DateOnly EndDate, bool IsCurrent);

// StudentCount: students enrolled in the section in the current academic year.
public record SectionDto(int Id, string Name, byte Capacity, int StudentCount);

public record ClassDto(int Id, string Name, IReadOnlyList<SectionDto> Sections);

public record SubjectDto(int Id, string Code, string Name);

// What a client sends to add a subject. Codes are short capitals, such as MATH (sms-reference.md).
public record CreateSubjectRequest(
    [Required, RegularExpression("^[A-Z]{2,10}$", ErrorMessage = "Code must be 2 to 10 capital letters, such as MATH.")]
    string Code,
    [Required, StringLength(50)] string Name);

src/SchoolApp.Core/Interfaces/IMasterDataRepository.cs
using SchoolApp.Core.Dtos;

namespace SchoolApp.Core.Interfaces;

// Lesson 76: academic years, classes with their sections, and subjects. Mostly read; subjects can be added.
public interface IMasterDataRepository
{
    Task<List<AcademicYearDto>> GetAcademicYearsAsync(CancellationToken cancellationToken = default);

    Task<AcademicYearDto?> GetCurrentAcademicYearAsync(CancellationToken cancellationToken = default);

    // Every class in display order, each with its sections and their current student counts.
    Task<List<ClassDto>> GetClassesAsync(CancellationToken cancellationToken = default);

    Task<List<SubjectDto>> GetSubjectsAsync(CancellationToken cancellationToken = default);

    Task<SubjectDto?> GetSubjectAsync(string code, CancellationToken cancellationToken = default);

    // Throws SchoolRuleException 50021 when the code or the name is already used.
    Task<SubjectDto> AddSubjectAsync(CreateSubjectRequest request, CancellationToken cancellationToken = default);
}

Every read returns DTOs, never entities. The one write, adding a subject, takes a validated request: codes are 2 to 10 capital letters, matching ENG, MATH, and CS in SchoolDb.

Step 2: the EF Core repository

src/SchoolApp.Data/EfCore/EfMasterDataRepository.cs
using Microsoft.EntityFrameworkCore;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Exceptions;
using SchoolApp.Core.Interfaces;

namespace SchoolApp.Data.EfCore;

// Lesson 76: master data with EF Core. Reads are projections straight into DTOs (Lesson 61).
public class EfMasterDataRepository : IMasterDataRepository
{
    private readonly SchoolDbContext _db;

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

    public Task<List<AcademicYearDto>> GetAcademicYearsAsync(CancellationToken cancellationToken = default)
    {
        return _db.AcademicYears
            .OrderByDescending(y => y.StartDate)
            .Select(y => new AcademicYearDto(y.Id, y.Name, y.StartDate, y.EndDate, y.IsCurrent))
            .ToListAsync(cancellationToken);
    }

    // The filtered unique index UQ_AcademicYears_IsCurrent (Lesson 10) allows at most one current year.
    public Task<AcademicYearDto?> GetCurrentAcademicYearAsync(CancellationToken cancellationToken = default)
    {
        return _db.AcademicYears
            .Where(y => y.IsCurrent)
            .Select(y => new AcademicYearDto(y.Id, y.Name, y.StartDate, y.EndDate, y.IsCurrent))
            .SingleOrDefaultAsync(cancellationToken);
    }

    // A nested projection: each class, its sections, and each section's count, in one LINQ statement.
    public Task<List<ClassDto>> GetClassesAsync(CancellationToken cancellationToken = default)
    {
        return _db.Classes
            .OrderBy(c => c.DisplayOrder)
            .Select(c => new ClassDto(
                c.Id,
                c.Name,
                _db.Sections
                    .Where(s => s.ClassId == c.Id)
                    .OrderBy(s => s.Name)
                    .Select(s => new SectionDto(s.Id, s.Name, s.Capacity,
                                                s.Enrollments.Count(e => e.AcademicYear!.IsCurrent)))
                    .ToList()))
            .ToListAsync(cancellationToken);
    }

    public Task<List<SubjectDto>> GetSubjectsAsync(CancellationToken cancellationToken = default)
    {
        return _db.Subjects
            .OrderBy(s => s.Name)
            .Select(s => new SubjectDto(s.Id, s.Code, s.Name))
            .ToListAsync(cancellationToken);
    }

    public Task<SubjectDto?> GetSubjectAsync(string code, CancellationToken cancellationToken = default)
    {
        return _db.Subjects
            .Where(s => s.Code == code)
            .Select(s => new SubjectDto(s.Id, s.Code, s.Name))
            .SingleOrDefaultAsync(cancellationToken);
    }

    public async Task<SubjectDto> AddSubjectAsync(CreateSubjectRequest request, CancellationToken cancellationToken = default)
    {
        Subject subject = new Subject { Code = request.Code, Name = request.Name.Trim() };
        _db.Subjects.Add(subject);
        try
        {
            await _db.SaveChangesAsync(cancellationToken);
        }
        catch (DbUpdateException ex) when (SqlErrors.IsUniqueKeyViolation(ex))
        {
            _db.Entry(subject).State = EntityState.Detached;
            // UQ_Subjects_Code and UQ_Subjects_Name: either can be the one that refused.
            throw new SchoolRuleException(50021, $"A subject with code {request.Code} or name {request.Name} already exists.", ex);
        }
        return new SubjectDto(subject.Id, subject.Code, subject.Name);
    }
}

GetClassesAsync is a nested projection: for each class, a list of sections, each with a count of current-year enrollments. AddSubjectAsync translates a unique-key error into SchoolRuleException 50021, the same pattern as the duplicate admission number in Lesson 75. The new number is recorded in sms-reference.md, and the exception handler maps it to 409.

Step 3: the controllers

src/SchoolApp.Api/Controllers/AcademicYearsController.cs
using Microsoft.AspNetCore.Mvc;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Interfaces;

namespace SchoolApp.Api.Controllers;

// Lesson 76: academic years. Two words, so the route uses a hyphen: /api/academic-years.
// [ResponseCache] adds a Cache-Control header: clients may reuse the answer for an hour.
[ApiController]
[Route("api/academic-years")]
[Produces("application/json")]
public class AcademicYearsController : ControllerBase
{
    private readonly IMasterDataRepository _masterData;

    public AcademicYearsController(IMasterDataRepository masterData)
    {
        _masterData = masterData;
    }

    /// <summary>Gets every academic year, newest first.</summary>
    [HttpGet]
    [ResponseCache(Duration = 3600)]
    [ProducesResponseType(StatusCodes.Status200OK)]
    public Task<List<AcademicYearDto>> GetAll(CancellationToken cancellationToken)
    {
        return _masterData.GetAcademicYearsAsync(cancellationToken);
    }

    /// <summary>Gets the current academic year.</summary>
    /// <response code="200">The current year.</response>
    /// <response code="404">No year is marked as current.</response>
    [HttpGet("current")]
    [ResponseCache(Duration = 3600)]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<ActionResult<AcademicYearDto>> GetCurrent(CancellationToken cancellationToken)
    {
        AcademicYearDto? year = await _masterData.GetCurrentAcademicYearAsync(cancellationToken);
        return year is null ? NotFound() : year;
    }
}

Show SubjectsController and the updated ClassesController

src/SchoolApp.Api/Controllers/SubjectsController.cs
using Microsoft.AspNetCore.Mvc;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Interfaces;

namespace SchoolApp.Api.Controllers;

// Lesson 76: subjects. Read by every screen; added rarely, by the school's admin (Module 12 limits who).
[ApiController]
[Route("api/subjects")]
[Produces("application/json")]
public class SubjectsController : ControllerBase
{
    private readonly IMasterDataRepository _masterData;

    public SubjectsController(IMasterDataRepository masterData)
    {
        _masterData = masterData;
    }

    /// <summary>Gets every subject, sorted by name.</summary>
    [HttpGet]
    [ProducesResponseType(StatusCodes.Status200OK)]
    public Task<List<SubjectDto>> GetAll(CancellationToken cancellationToken)
    {
        return _masterData.GetSubjectsAsync(cancellationToken);
    }

    /// <summary>Gets one subject by its code, such as MATH.</summary>
    /// <response code="200">The subject.</response>
    /// <response code="404">No subject has this code.</response>
    [HttpGet("{code}")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<ActionResult<SubjectDto>> Get(string code, CancellationToken cancellationToken)
    {
        SubjectDto? subject = await _masterData.GetSubjectAsync(code, cancellationToken);
        return subject is null ? NotFound() : subject;
    }

    /// <summary>Adds a subject.</summary>
    /// <response code="201">The subject was added.</response>
    /// <response code="400">The request is invalid.</response>
    /// <response code="409">The code or the name is already used (error 50021).</response>
    [HttpPost]
    [ProducesResponseType(StatusCodes.Status201Created)]
    [ProducesResponseType(StatusCodes.Status400BadRequest)]
    [ProducesResponseType(StatusCodes.Status409Conflict)]
    public async Task<ActionResult<SubjectDto>> Add(CreateSubjectRequest request, CancellationToken cancellationToken)
    {
        SubjectDto subject = await _masterData.AddSubjectAsync(request, cancellationToken);
        return CreatedAtAction(nameof(Get), new { code = subject.Code }, subject);
    }
}

src/SchoolApp.Api/Controllers/ClassesController.cs
using Microsoft.AspNetCore.Mvc;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Interfaces;

namespace SchoolApp.Api.Controllers;

// Lesson 72: a nested route. The students of a section of a class:
// GET /api/classes/10/sections/A/students. Route constraints reject impossible values before the action runs.
// Lesson 76: GET /api/classes lists every class with its sections.
[ApiController]
[Route("api/classes")]
[Produces("application/json")]
public class ClassesController : ControllerBase
{
    private readonly IStudentQueries _queries;
    private readonly IMasterDataRepository _masterData;

    public ClassesController(IStudentQueries queries, IMasterDataRepository masterData)
    {
        _queries = queries;
        _masterData = masterData;
    }

    /// <summary>Gets every class, with its sections and how many students each has this year.</summary>
    [HttpGet]
    [ResponseCache(Duration = 300)]   // counts change during admissions, so five minutes, not an hour
    [ProducesResponseType(StatusCodes.Status200OK)]
    public Task<List<ClassDto>> GetAll(CancellationToken cancellationToken)
    {
        return _masterData.GetClassesAsync(cancellationToken);
    }

    /// <summary>Gets the current year's class list for one section, in roll number order.</summary>
    /// <param name="classId">The class, 1 to 10.</param>
    /// <param name="sectionName">The section, such as A.</param>
    /// <param name="cancellationToken">Cancelled when the client gives up.</param>
    /// <response code="200">The students, in roll number order.</response>
    /// <response code="404">No such class or section, or nobody is enrolled in it.</response>
    // int: whole numbers only. range(1,10): classes 1 to 10. length(1,2): section names A to V.
    [HttpGet("{classId:int:range(1,10)}/sections/{sectionName:length(1,2)}/students")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<ActionResult<List<ClassListEntryDto>>> GetSectionStudents(
        int classId, string sectionName, CancellationToken cancellationToken)
    {
        List<ClassListEntryDto> students = await _queries.GetClassListAsync(classId, sectionName, cancellationToken);
        if (students.Count == 0)
        {
            return NotFound();
        }
        return students;
    }
}

/api/academic-years follows the route rule from Lesson 72: lowercase, plural, and a hyphen between words. current is a literal segment, a fixed word. So /api/academic-years/current reads like a sentence.

Step 4: read the master data

Output
GET /api/academic-years
HTTP/1.1 200 OK
Cache-Control: public,max-age=3600
[{"id":2,"name":"2026-27","startDate":"2026-06-01","endDate":"2027-03-31","isCurrent":true},{"id":1,"name":"2025-26","startDate":"2025-06-01","endDate":"2026-03-31","isCurrent":false}]

GET /api/academic-years/current
HTTP/1.1 200 OK
Cache-Control: public,max-age=3600
{"id":2,"name":"2026-27","startDate":"2026-06-01","endDate":"2027-03-31","isCurrent":true}

Both carry the one-hour header. A client that asks again within the hour may use its saved copy, with no request at all.

Example: classes with sections

GET /api/classes returned 10 classes, each with 22 sections, A to V, with Cache-Control: public,max-age=300. Class 10's first three sections:

Output (part)
{"id":10,"name":"Class 10","sections":[{"id":19,"name":"A","capacity":40,"studentCount":4},{"id":20,"name":"B","capacity":40,"studentCount":2},{"id":201,"name":"C","capacity":50,"studentCount":50}, ...]}

10-A has the four fixed students, 10-B the two, and 10-C fifty from the bulk data. Adding up every section's count gives 10,012, every student in SchoolDb, as each one has exactly one enrollment this year.

Example: one query for the whole tree

The SQL log showed that the nested projection became a single query:

Output (from the API log)
SELECT [c].[Id], [c].[Name], [s].[Id], [s].[Name], [s].[Capacity], (
    SELECT COUNT(*)
    FROM [Enrollments] AS [e]
    INNER JOIN [AcademicYears] AS [a] ON [e].[AcademicYearId] = [a].[Id]
    WHERE [s].[Id] = [e].[SectionId] AND [a].[IsCurrent] = CAST(1 AS bit))
FROM [Classes] AS [c]
LEFT JOIN [Sections] AS [s] ON [c].[Id] = [s].[ClassId]
ORDER BY [c].[DisplayOrder], [c].[Id], [s].[Name]

LEFT JOIN keeps a class even if it has no sections yet; the count is a subquery per section. EF Core then groups the 220 rows back into 10 classes with their sections. One round trip for the whole tree.

Example: a warning that hid a wrong count

The same log also contained a warning, printed every time the app first used EF Core:

Output (from the API log)
Entity 'Student' has a global query filter defined and is the required end of a relationship with the entity 'Enrollment'. This may lead to unexpected results when the required entity is filtered out.

It meant this: removed students are hidden (Lesson 60), but their enrollments were not. To test it, Kabir was marked as deleted in SSMS, and GET /api/classes still counted 4 students in 10-A. The removed student was still in the count.

The fix is the one the warning suggests: give Enrollment the same named soft-delete filter, through its student. After that, 10-A showed 3 with Kabir removed, and 4 again when he was restored.

Two new warnings then appeared for Mark and FeeInvoice, which require an enrollment, so they got matching filters too. The log now starts with no warnings.

src/SchoolApp.Data/EfCore/Configurations/EnrollmentConfiguration.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using SchoolApp.Core.Entities;

namespace SchoolApp.Data.EfCore.Configurations;

// Lesson 59: dbo.Enrollments and its three foreign keys.
public class EnrollmentConfiguration : IEntityTypeConfiguration<Enrollment>
{
    public void Configure(EntityTypeBuilder<Enrollment> builder)
    {
        builder.ToTable("Enrollments");
        builder.HasKey(e => e.Id);

        // One student has many enrollments (one per year); each enrollment has one student.
        builder.HasOne(e => e.Student).WithMany(s => s.Enrollments).HasForeignKey(e => e.StudentId);
        builder.HasOne(e => e.Section).WithMany(s => s.Enrollments).HasForeignKey(e => e.SectionId);
        builder.HasOne(e => e.AcademicYear).WithMany().HasForeignKey(e => e.AcademicYearId);

        builder.HasAuditColumns();

        // Lesson 76: the same soft-delete filter as Student. Without it, enrollments of removed students
        // were still counted, and EF Core warned about it at startup.
        builder.HasQueryFilter(SoftDeleteFilter.Name, e => !e.Student!.IsDeleted);
    }
}

A finance report that must include removed students' invoices can still switch the filter off with IgnoreQueryFilters([SoftDeleteFilter.Name]). Hiding is the default; seeing everything is a deliberate choice. Read your startup warnings: this one had been there since Lesson 60.

Example: adding a subject

Output
POST /api/subjects   {"code":"ART","name":"Art"}
HTTP/1.1 201 Created
Location: http://localhost:5080/api/subjects/ART
{"id":7,"code":"ART","name":"Art"}

POST /api/subjects   {"code":"ART","name":"Art"}
HTTP/1.1 409 Conflict
Cache-Control: no-cache,no-store
{"type":"https://tools.ietf.org/html/rfc9110#section-15.5.10","title":"Conflict","status":409,"detail":"A subject with code ART or name Art already exists.","errorNumber":50021,"traceId":"00-ea33f6f8d77cb926e6cfcab8d1728ef0-ca11240f7b8a83c4-00"}

POST /api/subjects   {"code":"MATHS","name":"Mathematics"}
HTTP/1.1 409 Conflict
{"...":"...","detail":"A subject with code MATHS or name Mathematics already exists.","errorNumber":50021}

POST /api/subjects   {"code":"art","name":""}
HTTP/1.1 400 Bad Request
{"...":"...","errors":{"Code":["Code must be 2 to 10 capital letters, such as MATH."],"Name":["The Name field is required."]}}

The third request shows why both columns are unique: MATHS is a new code, but "Mathematics" already exists. Without UQ_Subjects_Name, the school would now have two Mathematics subjects. The test subject was removed from SchoolDb afterwards.

Example: errors are never cached

Look again at the 409: it carries Cache-Control: no-cache,no-store, although no code asked for it. ASP.NET Core's exception handling adds that header to every error it writes, so no client or proxy ever keeps an error and shows it again later.

That matters most for master data. A cached "not found" for the current academic year could hide the year from an app for an hour after an administrator created it.

Example: one current year

GetCurrentAcademicYearAsync uses SingleOrDefaultAsync, which throws if two rows match. That cannot happen: the filtered unique index UQ_AcademicYears_IsCurrent from Lesson 10 allows only one row with IsCurrent = 1. The code relies on a rule the database guarantees.

Changing the current year is a two-step job, switching one year off and another on, so it must run in one transaction (Lesson 24), and only an administrator should be allowed to do it.

Common mistakes

Duplicate master data. Marks split between "Maths" and "Mathematics". Fix: unique constraints on codes and names, translated into 409.
Caching transaction data. Parents see yesterday's attendance. Fix: cache only data that changes rarely, for as long as it can be wrong.
One request per class for its sections. The client makes 11 requests instead of 1. Fix: a nested response.
Hard-coding classes or subjects in the app. Adding a subject needs an app release. Fix: load master data from the API.
Letting anyone change master data. One wrong edit affects every report. Fix: admin-only writes (Module 12).

In real projects

From 16 years of .NET projects

A school's mobile app loaded the subject list from the API every time a teacher opened a screen. That made about nine thousand identical requests each school morning, all for six subjects that had not changed in years.

Adding Cache-Control: max-age=3600 to the master data endpoints took ten minutes. The phone's HTTP client already honoured it, and those requests dropped to a few hundred.

The lesson was not that caching is clever. It was that the server should say what the client may keep; the clients were ready to listen all along.

Best practices

Return master data as DTOs, nested where screens need the children too.
Send Cache-Control with a max-age that matches how often the data changes.
Keep codes and names unique in the database, and answer duplicates with 409.
Load master data from the API in clients; never hard-code it.
Restrict master data changes to administrators.

Recap

Master data is read constantly and changed rarely; transaction data is the opposite.
[ResponseCache(Duration = ...)] adds Cache-Control, letting clients reuse answers.
Error responses get no-cache,no-store automatically.
GET /api/classes returns every class with its sections and current counts in one response.
Duplicate subject codes or names answer 409 with error 50021.

Homework

Library module task: Add GET /api/categories to a single-file library API, returning each book category with its number of books, copies, and available copies, read from SchoolDb with one grouped query. Let clients keep the answer for ten minutes.

A correct result looks like:

Output
HTTP/1.1 200 OK
Cache-Control: public,max-age=600
[{"category":"Biography","books":1,"copies":2,"available":2},{"category":"Fiction","books":2,"copies":2,"available":1}]

Show the solution

library-assignment/076-library-categories.cs
// Library homework, Lesson 76: the library's own master data. Book categories with their book counts,
// read from SchoolDb with Dapper, and a Cache-Control header so clients reuse the answer.
// Run with:  dotnet run library-assignment/076-library-categories.cs
#: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());
WebApplication app = builder.Build();
app.MapControllers();
app.Run("http://localhost:5076");

public record CategoryDto(string Category, int Books, int Copies, int Available);

[ApiController]
[Route("api/categories")]
public class CategoriesController : ControllerBase
{
    private readonly SqlConnectionFactory _connections;

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

    // GET /api/categories: one row per category, from the books themselves (no separate table).
    [HttpGet]
    [ResponseCache(Duration = 600)]   // categories change rarely: ten minutes
    public async Task<IEnumerable<CategoryDto>> GetAll()
    {
        await using SqlConnection connection = _connections.Create();
        return await connection.QueryAsync<CategoryDto>("""
            SELECT b.Category,
                   COUNT(DISTINCT b.Id) AS Books,
                   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
            GROUP BY b.Category
            ORDER BY b.Category;
            """);
    }
}

COUNT(DISTINCT b.Id) counts books once, even though the join repeats each book for every copy. The LEFT JOIN keeps a book that has no copies yet, with zero copies.

Quiz

Which of these is master data?
1. Today's attendance
2. The list of subjects
3. A fee payment
4. A mark
Show answer
Answer: B. The list of subjects Subjects change rarely and are read by almost every screen.
What does [ResponseCache(Duration = 3600)] do?
1. Stores the response on the server
2. Adds Cache-Control: max-age=3600, so clients may reuse the answer for an hour
3. Makes the action run once an hour
4. Compresses the response
Show answer
Answer: B. Adds Cache-Control: max-age=3600, so clients may reuse the answer for an hour It gives clients permission; server-side caching comes in Module 12.
Why did adding MATHS with the name Mathematics fail?
1. MATHS is too long
2. UQ_Subjects_Name: the name already exists
3. The code must be lowercase
4. Subjects cannot be added
Show answer
Answer: B. UQ_Subjects_Name: the name already exists Both the code and the name are unique.
Which header did the 409 response carry?
1. Cache-Control: public,max-age=3600
2. Cache-Control: no-cache,no-store
3. No Cache-Control header
4. Location
Show answer
Answer: B. Cache-Control: no-cache,no-store Errors are never cached.
Why does GET /api/classes include each class's sections?
1. It is required by REST
2. So the client does not make one request per class
3. Sections have no own route
4. For security
Show answer
Answer: B. So the client does not make one request per class One nested response instead of eleven requests.

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 What is master data, and how is it different from transaction data? concept

Master data is reference information that other features depend on, read constantly and changed rarely, such as academic years, classes, sections, and subjects. Transaction data records daily activity, changes constantly, and grows large, such as attendance, marks, and payments.

See the example · Permalink

F What does the Cache-Control header with max-age do? concept

It tells clients and caches how long they may reuse a response without asking the server again, such as Cache-Control: public,max-age=3600 for one hour. In ASP.NET Core, [ResponseCache(Duration = 3600)] adds it; the server itself stores nothing.

See the example · Permalink

F Why return classes together with their sections in one response? design

Screens almost always need both. Returning them nested saves the client from making one more request per class, the N+1 problem on the client's side, and EF Core can build the whole tree with one SQL query.

See the example · Permalink

Mid (3-5 yrs)

M Why should error responses never be cached, and what does ASP.NET Core do about it? concept

A cached error, such as a 404 for the current academic year, could be shown long after the problem was fixed. ASP.NET Core's exception handling adds Cache-Control: no-cache,no-store to the error responses it writes, so clients and proxies do not keep them.

See the example · Permalink

M How do you choose a max-age for an endpoint? design

By how long the data may be stale without harm. Subjects and academic years can be an hour or more; classes with live student counts a few minutes during admissions; attendance or balances not at all. When in doubt, start short and lengthen it after watching how the data changes.

See the example · Permalink

M What does the EF Core warning about a global query filter on the required end of a relationship mean? concept

A principal entity, such as Student, is filtered, but a dependent that requires it, such as Enrollment, is not. Queries on the dependent then still return rows whose principal is hidden, such as enrollments of removed students being counted. Give the dependent a matching filter through its navigation, or make the relationship optional.

See the example · Permalink

Senior (6-9 yrs)

S Two subjects, Maths and Mathematics, exist and marks are split between them. How do you fix it and prevent it? scenario

Merge them: choose one, move every mark, exam paper, and allocation to it in one transaction, then remove the other. Prevent it with unique constraints on both the code and the name, translated into a 409, and restrict master data changes to administrators.

How to answer: Cover the data repair, its transaction and verification, the constraints, the API's answer, and the permission rule.

See the example · Permalink

S An app loads the same subject list thousands of times each morning. What can the API do? scenario

Send Cache-Control with a suitable max-age so clients reuse the answer, and support conditional requests with ETag so clients that do ask get 304 Not Modified. Add server-side caching for the data itself if the database is still busy, and invalidate it when an administrator changes the list.

How to answer: Separate client caching, conditional requests, and server caching, and explain invalidation when master data changes.

See the example · Permalink

Architect / Lead (10+ yrs)

A How would you manage master data shared by many schools in a multi-school product? design

Separate global reference data, such as a standard subject catalogue, from school-specific data, such as sections and timetables; scope every school-specific row by school; version changes to shared data; cache per school with clear invalidation; and let administrators change only their own school's data.

How to answer: Discuss global versus tenant-owned data, isolation, caching keys, change control, and migration of existing schools when the shared catalogue changes. Module 13 builds this.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-76-master-data (code repository coming soon)
Files: src/SchoolApp.Core/Dtos/MasterDataDtos.cs, src/SchoolApp.Data/EfCore/EfMasterDataRepository.cs, src/SchoolApp.Api/Controllers/AcademicYearsController.cs, src/SchoolApp.Api/Controllers/SubjectsController.cs, library-assignment/076-library-categories.cs
Video: coming soon

What's next

Years, classes, sections, and subjects are now in place for every screen. Next: the people who teach and the people who look after the students, teachers and guardians, including the many-to-many link between students and their guardians. Lesson 77: Teachers and guardians

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

Introduction​

Why this matters​

The concept​

Example: master data and transaction data​

Example: Cache-Control​

Example: nested responses​

Apply it to the SMS​

Step 1: DTOs and an interface​

Step 2: the EF Core repository​

Step 3: the controllers​

Step 4: read the master data​

Example: classes with sections​

Example: one query for the whole tree​

Example: a warning that hid a wrong count​

Example: adding a subject​

Example: errors are never cached​

Example: one current year​

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: master data and transaction data

Example: Cache-Control

Example: nested responses

Apply it to the SMS

Step 1: DTOs and an interface

Step 2: the EF Core repository

Step 3: the controllers

Step 4: read the master data

Example: classes with sections

Example: one query for the whole tree

Example: a warning that hid a wrong count

Example: adding a subject

Example: errors are never cached

Example: one current year

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