Attendance API: Whole-Section Registers and Daily Reports – Web API | Lesson 79

Published / updated8 Oct 2026

Introduction

Attendance is the most frequent thing a school records: every enrolled student, every school day. SchoolDb already holds about 600,000 attendance records from 60 school days of bulk data (Lesson 15).

A teacher does not mark students one by one through the API. She fills in the whole register for her section and sends it once. This lesson builds that endpoint, its rules, and the reports the office reads afterwards.

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

Design an endpoint that saves a whole register in one request.
Make it idempotent, so sending it twice is harmless, and corrections are easy.
Check date rules with TimeProvider, so tests control what "today" is.
Validate a register for completeness: every student once, nobody else.
Report daily and per-student attendance with GROUP BY in SQL.

Why this matters

Attendance screens are used on phones, in classrooms, often on weak school Wi-Fi. Requests time out and get sent again. An API that creates duplicates on a retry, or rejects a correction, turns a two-minute task into a support call.

The reports drive real decisions: which children were absent today, who is below the attendance needed for exams. They must be correct, and they must stay fast as the table grows.

The concept

Example: one register, one request

Think of the paper register: one sheet per section per day. The teacher fills in every line, hands it in, and if she made a mistake, she corrects that line. There is never a second sheet for the same day.

The API follows the paper:

Method and route	Meaning
`GET /api/sections/{sectionId}/attendance?date=2026-07-01`	Read the register for that day
`PUT /api/sections/{sectionId}/attendance?date=2026-08-10`	Save the whole register for that day

PUT fits because the register for a section and day is one resource, replaced as a whole. The route is the one planned in sms-reference.md back in Phase 0.

Example: idempotent saving

PUT is idempotent (Lesson 65): sending the same register twice must leave the same result as sending it once. So the service does not blindly insert. For each student, it updates that day's record if one exists, and creates one only if not.

A second identical request therefore creates nothing and updates nothing. A request with one changed line updates exactly that line. The unique constraint on (EnrollmentId, AttendanceDate) from Lesson 13 is the final guard against duplicates.

Example: rules for a register

Number	Rule	Why
50040	Not a future date	Attendance records what happened
50041	Not a Sunday	The school week is Monday to Saturday
50042	Inside the current academic year	Records belong to this year's enrollments
50043	Every roll number exists in the section	No typos, no students from other sections
50044	Every enrolled student is included	A forgotten student must not look "present"
50045	No roll number twice	One answer per student

All are 422 except 50031, an unknown section, which is 404. The date rules need "today", which brings in TimeProvider.

Example: TimeProvider

Code that calls DateTime.Now directly is hard to test: the answer changes every day. Lesson 69's homework solved this with a home-made IClock. .NET 8 added the same idea to .NET itself: TimeProvider.

The app registers TimeProvider.System, the real clock. A test passes a TimeProvider stopped at a fixed moment, so "today" is the same on every run, and a "future date" stays future forever.

Apply it to the SMS

Step 1: requests and results

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

namespace SchoolApp.Core.Dtos;

// Lesson 79: taking and reading attendance.

// One line of the register a teacher submits: a roll number and what happened.
public record AttendanceEntry(
    [Range(1, 999)] short RollNumber,
    [EnumDataType(typeof(AttendanceStatus))] AttendanceStatus Status,
    [StringLength(200)] string? Remarks);

// The whole section's register for one day.
public record TakeAttendanceRequest([Required, MinLength(1)] List<AttendanceEntry> Entries);

// A student on the register, as the rules need it.
public record RegisterStudent(int EnrollmentId, short RollNumber, string AdmissionNumber, string StudentName);

// One line of a register as clients see it; Status is null when attendance was not taken that day.
public record RegisterLineDto(short RollNumber, string AdmissionNumber, string StudentName, string? Status, string? Remarks);

public record TakeAttendanceResult(int SectionId, DateOnly Date, int Present, int Absent, int Late, int Leave,
                                   int Created, int Updated);

// Present and Late count as attended (the same rule as Lesson 18's SQL).
public record ClassAttendanceDto(string ClassName, int Present, int Absent, int Late, int Leave, decimal AttendancePercent);

public record StudentAttendanceSummaryDto(string AdmissionNumber, string StudentName, DateOnly From, DateOnly To,
                                          int Days, int Present, int Absent, int Late, int Leave, decimal AttendancePercent);

The teacher sends roll numbers, not enrollment ids: roll numbers are what she sees on her list. The service translates them, using the section's register.

Step 2: the service

src/SchoolApp.Core/Services/AttendanceService.cs
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;
using SchoolApp.Core.Exceptions;
using SchoolApp.Core.Interfaces;

namespace SchoolApp.Core.Services;

// Lesson 79: taking a section's attendance for one day. The whole register is sent at once, and sending
// it again for the same day replaces the earlier answers: taking attendance is idempotent.
public class AttendanceService
{
    private readonly IMasterDataRepository _masterData;
    private readonly IAttendanceRepository _attendance;
    private readonly TimeProvider _time;

    // TimeProvider: .NET's own clock abstraction. The app gets the real clock; tests can pass a fixed one.
    public AttendanceService(IMasterDataRepository masterData, IAttendanceRepository attendance, TimeProvider time)
    {
        _masterData = masterData;
        _attendance = attendance;
        _time = time;
    }

    public async Task<TakeAttendanceResult> TakeAsync(int sectionId, DateOnly date, TakeAttendanceRequest request,
                                                      CancellationToken cancellationToken = default)
    {
        DateOnly today = DateOnly.FromDateTime(_time.GetLocalNow().DateTime);
        if (date > today)
        {
            throw new SchoolRuleException(50040, $"Attendance cannot be taken for a future date ({date:yyyy-MM-dd}).");
        }
        if (date.DayOfWeek == DayOfWeek.Sunday)
        {
            throw new SchoolRuleException(50041, $"{date:yyyy-MM-dd} is a Sunday, not a school day.");
        }

        AcademicYearDto year = await _masterData.GetCurrentAcademicYearAsync(cancellationToken)
            ?? throw new SchoolRuleException(50035, "No academic year is marked as current.");
        if (date < year.StartDate || date > year.EndDate)
        {
            throw new SchoolRuleException(50042, $"{date:yyyy-MM-dd} is outside the academic year {year.Name}.");
        }

        List<RegisterStudent> register = await _attendance.GetRegisterStudentsAsync(sectionId, year.Id, cancellationToken);
        if (register.Count == 0)
        {
            throw new SchoolRuleException(50031, $"Section {sectionId} does not exist or has no students in {year.Name}.");
        }

        // The register must be complete and exact: every enrolled student once, nobody else.
        short? repeated = request.Entries.GroupBy(e => e.RollNumber).Where(g => g.Count() > 1).Select(g => (short?)g.Key).FirstOrDefault();
        if (repeated is not null)
        {
            throw new SchoolRuleException(50045, $"Roll number {repeated} appears more than once.");
        }
        Dictionary<short, RegisterStudent> byRoll = register.ToDictionary(s => s.RollNumber);
        List<short> unknown = request.Entries.Select(e => e.RollNumber).Where(r => !byRoll.ContainsKey(r)).ToList();
        if (unknown.Count > 0)
        {
            throw new SchoolRuleException(50043, $"No student has roll number {string.Join(", ", unknown)} in this section.");
        }
        List<short> missing = byRoll.Keys.Except(request.Entries.Select(e => e.RollNumber)).Order().ToList();
        if (missing.Count > 0)
        {
            throw new SchoolRuleException(50044, $"The register is incomplete: roll number {string.Join(", ", missing)} missing.");
        }

        // Update what was already taken that day; create the rest.
        Dictionary<int, AttendanceRecord> existing = (await _attendance.GetRecordsAsync(sectionId, date, cancellationToken))
            .ToDictionary(r => r.EnrollmentId);
        List<AttendanceRecord> created = new List<AttendanceRecord>();
        int updated = 0;
        foreach (AttendanceEntry entry in request.Entries)
        {
            int enrollmentId = byRoll[entry.RollNumber].EnrollmentId;
            if (existing.TryGetValue(enrollmentId, out AttendanceRecord? record))
            {
                if (record.Status != entry.Status || record.Remarks != entry.Remarks)
                {
                    record.Status = entry.Status;
                    record.Remarks = entry.Remarks;
                    updated++;
                }
            }
            else
            {
                created.Add(new AttendanceRecord
                {
                    EnrollmentId = enrollmentId,
                    AttendanceDate = date,
                    Status = entry.Status,
                    Remarks = entry.Remarks
                });
            }
        }
        await _attendance.SaveAsync(created, cancellationToken);

        int Count(AttendanceStatus status) => request.Entries.Count(e => e.Status == status);
        return new TakeAttendanceResult(sectionId, date, Count(AttendanceStatus.Present), Count(AttendanceStatus.Absent),
                                        Count(AttendanceStatus.Late), Count(AttendanceStatus.Leave), created.Count, updated);
    }
}

The date rules come first, because they need no database. Then the register is loaded and compared with the request: repeats, unknown roll numbers, missing roll numbers. Only a complete, exact register reaches the save.

The loop counts real changes only. A line with the same status and remarks as before is not an update, so an identical second request reports created: 0, updated: 0.

Step 3: the repository

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

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

namespace SchoolApp.Data.EfCore;

// Lesson 79: attendance with EF Core. The reports group in SQL and return only the counts.
public class EfAttendanceRepository : IAttendanceRepository
{
    private readonly SchoolDbContext _db;

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

    public Task<List<RegisterStudent>> GetRegisterStudentsAsync(int sectionId, int academicYearId, CancellationToken cancellationToken = default)
    {
        return _db.Enrollments
            .Where(e => e.SectionId == sectionId && e.AcademicYearId == academicYearId)
            .OrderBy(e => e.RollNumber)
            .Select(e => new RegisterStudent(e.Id, e.RollNumber, e.Student!.AdmissionNumber,
                                             e.Student.FirstName + " " + e.Student.LastName))
            .ToListAsync(cancellationToken);
    }

    public Task<List<AttendanceRecord>> GetRecordsAsync(int sectionId, DateOnly date, CancellationToken cancellationToken = default)
    {
        return _db.AttendanceRecords
            .Where(a => a.AttendanceDate == date && a.Enrollment!.SectionId == sectionId)
            .ToListAsync(cancellationToken);
    }

    public async Task SaveAsync(IEnumerable<AttendanceRecord> newRecords, CancellationToken cancellationToken = default)
    {
        _db.AttendanceRecords.AddRange(newRecords);
        await _db.SaveChangesAsync(cancellationToken);   // inserts and updates in one transaction
    }

    // A left join from the register to that day's records: students without a record show Status null.
    public Task<List<RegisterLineDto>> GetRegisterAsync(int sectionId, int academicYearId, DateOnly date,
                                                        CancellationToken cancellationToken = default)
    {
        return _db.Enrollments
            .Where(e => e.SectionId == sectionId && e.AcademicYearId == academicYearId)
            .OrderBy(e => e.RollNumber)
            .Select(e => new
            {
                e.RollNumber,
                e.Student!.AdmissionNumber,
                StudentName = e.Student.FirstName + " " + e.Student.LastName,
                Record = _db.AttendanceRecords.FirstOrDefault(a => a.EnrollmentId == e.Id && a.AttendanceDate == date)
            })
            .Select(x => new RegisterLineDto(x.RollNumber, x.AdmissionNumber, x.StudentName,
                                             x.Record == null ? null : x.Record.Status.ToString(),
                                             x.Record == null ? null : x.Record.Remarks))
            .ToListAsync(cancellationToken);
    }

    public async Task<List<ClassAttendanceDto>> GetDailySummaryAsync(DateOnly date, CancellationToken cancellationToken = default)
    {
        // GROUP BY in SQL: about 10,000 records for a school day become ten rows of counts.
        var rows = await _db.AttendanceRecords
            .Where(a => a.AttendanceDate == date)
            .GroupBy(a => new { a.Enrollment!.Section!.SchoolClass!.DisplayOrder, a.Enrollment.Section.SchoolClass.Name })
            .OrderBy(g => g.Key.DisplayOrder)
            .Select(g => new
            {
                g.Key.Name,
                Present = g.Count(a => a.Status == AttendanceStatus.Present),
                Absent = g.Count(a => a.Status == AttendanceStatus.Absent),
                Late = g.Count(a => a.Status == AttendanceStatus.Late),
                Leave = g.Count(a => a.Status == AttendanceStatus.Leave)
            })
            .ToListAsync(cancellationToken);

        return rows.Select(r => new ClassAttendanceDto(r.Name, r.Present, r.Absent, r.Late, r.Leave,
                                                       Percent(r.Present + r.Late, r.Present + r.Absent + r.Late + r.Leave)))
                   .ToList();
    }

    public async Task<StudentAttendanceSummaryDto?> GetStudentSummaryAsync(string admissionNumber, DateOnly from, DateOnly to,
                                                                          CancellationToken cancellationToken = default)
    {
        var student = await _db.Students
            .Where(s => s.AdmissionNumber == admissionNumber)
            .Select(s => new { s.Id, s.AdmissionNumber, Name = s.FirstName + " " + s.LastName })
            .SingleOrDefaultAsync(cancellationToken);
        if (student is null)
        {
            return null;
        }

        List<AttendanceStatus> statuses = await _db.AttendanceRecords
            .Where(a => a.Enrollment!.StudentId == student.Id && a.AttendanceDate >= from && a.AttendanceDate <= to)
            .Select(a => a.Status)
            .ToListAsync(cancellationToken);

        int present = statuses.Count(s => s == AttendanceStatus.Present);
        int absent = statuses.Count(s => s == AttendanceStatus.Absent);
        int late = statuses.Count(s => s == AttendanceStatus.Late);
        int leave = statuses.Count(s => s == AttendanceStatus.Leave);
        return new StudentAttendanceSummaryDto(student.AdmissionNumber, student.Name, from, to, statuses.Count,
                                               present, absent, late, leave, Percent(present + late, statuses.Count));
    }

    private static decimal Percent(int part, int whole) => whole == 0 ? 0m : Math.Round(100m * part / whole, 2);
}

SaveAsync adds the new records; EF Core already tracks the loaded ones that changed. One SaveChangesAsync writes both, in one transaction.

AttendanceRecord gets a matching soft-delete filter through its enrollment, the rule from Lesson 76, so EF Core still starts without warnings:

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

namespace SchoolApp.Data.EfCore.Configurations;

// Lesson 79: dbo.AttendanceRecords (Lesson 13). Only CreatedAt and CreatedBy, because of the volume.
public class AttendanceConfiguration : IEntityTypeConfiguration<AttendanceRecord>
{
    public void Configure(EntityTypeBuilder<AttendanceRecord> builder)
    {
        builder.ToTable("AttendanceRecords");
        builder.Property(a => a.Status).HasMaxLength(7).IsUnicode(false).HasConversion<string>();
        builder.Property(a => a.Remarks).HasMaxLength(200);
        builder.Property(a => a.CreatedAt).HasPrecision(0).HasDefaultValueSql("SYSUTCDATETIME()");
        builder.HasOne(a => a.Enrollment).WithMany().HasForeignKey(a => a.EnrollmentId);

        // Matching the Enrollment filter (Lesson 76): records of removed students are hidden too.
        builder.HasQueryFilter(SoftDeleteFilter.Name, a => !a.Enrollment!.Student!.IsDeleted);
    }
}

Step 4: the controller

Show src/SchoolApp.Api/Controllers/AttendanceController.cs

src/SchoolApp.Api/Controllers/AttendanceController.cs
using System.ComponentModel.DataAnnotations;
using Microsoft.AspNetCore.Mvc;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Interfaces;
using SchoolApp.Core.Services;

namespace SchoolApp.Api.Controllers;

// Lesson 79: taking and reading attendance.
//   GET and PUT /api/sections/{sectionId}/attendance?date=2026-07-01   (the route from sms-reference.md)
//   GET /api/attendance/daily-summary?date=2026-07-01
//   GET /api/students/{admissionNumber}/attendance-summary?from=2026-06-01&to=2026-08-08
[ApiController]
[Produces("application/json")]
public class AttendanceController : ControllerBase
{
    private readonly AttendanceService _service;
    private readonly IAttendanceRepository _attendance;
    private readonly IMasterDataRepository _masterData;

    public AttendanceController(AttendanceService service, IAttendanceRepository attendance, IMasterDataRepository masterData)
    {
        _service = service;
        _attendance = attendance;
        _masterData = masterData;
    }

    /// <summary>Gets a section's register for one day; Status is null for students not yet marked.</summary>
    /// <response code="200">The register, by roll number.</response>
    /// <response code="404">The section does not exist or has no students this year.</response>
    [HttpGet("/api/sections/{sectionId:int}/attendance")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<ActionResult<List<RegisterLineDto>>> GetRegister(int sectionId, [FromQuery, Required] DateOnly date,
                                                                       CancellationToken cancellationToken)
    {
        AcademicYearDto? year = await _masterData.GetCurrentAcademicYearAsync(cancellationToken);
        if (year is null)
        {
            return NotFound();
        }
        List<RegisterLineDto> register = await _attendance.GetRegisterAsync(sectionId, year.Id, date, cancellationToken);
        return register.Count == 0 ? NotFound() : register;
    }

    /// <summary>Takes (or corrects) a section's attendance for one day. The whole register is required.</summary>
    /// <response code="200">Saved; the counts, and how many records were created or updated.</response>
    /// <response code="400">The request is invalid.</response>
    /// <response code="404">The section does not exist or has no students this year (50031).</response>
    /// <response code="422">A date or register rule was broken (50040 to 50045).</response>
    [HttpPut("/api/sections/{sectionId:int}/attendance")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status400BadRequest)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    [ProducesResponseType(StatusCodes.Status422UnprocessableEntity)]
    public Task<TakeAttendanceResult> Take(int sectionId, [FromQuery, Required] DateOnly date, TakeAttendanceRequest request,
                                           CancellationToken cancellationToken)
    {
        return _service.TakeAsync(sectionId, date, request, cancellationToken);
    }

    /// <summary>Gets attendance counts for every class on one day.</summary>
    [HttpGet("/api/attendance/daily-summary")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    public Task<List<ClassAttendanceDto>> GetDailySummary([FromQuery, Required] DateOnly date, CancellationToken cancellationToken)
    {
        return _attendance.GetDailySummaryAsync(date, cancellationToken);
    }

    /// <summary>Gets one student's attendance between two dates.</summary>
    /// <response code="200">The counts and the attendance percentage.</response>
    /// <response code="404">No student has this admission number.</response>
    [HttpGet("/api/students/{admissionNumber}/attendance-summary")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<ActionResult<StudentAttendanceSummaryDto>> GetStudentSummary(
        string admissionNumber, [FromQuery, Required] DateOnly from, [FromQuery, Required] DateOnly to,
        CancellationToken cancellationToken)
    {
        StudentAttendanceSummaryDto? summary = await _attendance.GetStudentSummaryAsync(admissionNumber, from, to, cancellationToken);
        return summary is null ? NotFound() : summary;
    }
}

[FromQuery, Required] DateOnly date makes the date mandatory: leaving it out gives 400 with "The date field is required." Program.cs registers AttendanceService and TimeProvider.System.

Step 5: take a register, twice, then correct it

10-A is section 19, with Aarav, Diya, Kabir, and Meera as roll numbers 1 to 4. The .http file sends their register for Monday 2026-08-10, the first school day after the bulk data:

Output
PUT /api/sections/19/attendance?date=2026-08-10   (1 Present, 2 Present, 3 Late "Bus was late", 4 Absent)
{"sectionId":19,"date":"2026-08-10","present":2,"absent":1,"late":1,"leave":0,"created":4,"updated":0}

PUT (the same register again)
{"sectionId":19,"date":"2026-08-10","present":2,"absent":1,"late":1,"leave":0,"created":0,"updated":0}

PUT (Meera changed to Present, "Arrived after a doctor's visit")
{"sectionId":19,"date":"2026-08-10","present":3,"absent":0,"late":1,"leave":0,"created":0,"updated":1}

The retry changed nothing, and the correction changed one line. GET for the same date then shows Kabir Late with "Bus was late", and Meera Present with her note. For 2026-07-01, the sample data's day, it shows Kabir Absent with the remark "Fever".

Example: every rule

Request	Status	Detail
Roll numbers 1 to 3 only	422, 50044	The register is incomplete: roll number 4 missing.
An extra roll number 9	422, 50043	No student has roll number 9 in this section.
Roll number 2 twice	422, 50045	Roll number 2 appears more than once.
Date 2026-08-09	422, 50041	2026-08-09 is a Sunday, not a school day.
Date 2026-12-01	422, 50040	Attendance cannot be taken for a future date (2026-12-01).
Date 2026-05-30	422, 50042	2026-05-30 is outside the academic year 2026-27.
Section 9999	404, 50031	Section 9999 does not exist or has no students in 2026-27.
No `date`	400	The date field is required.

Every rule answered with a message a teacher's app can show as it is. The demo records were removed from SchoolDb afterwards.

Example: tests with a fixed clock

AttendanceServiceTests stops the clock at Wednesday 2026-10-07 with a small FixedTimeProvider class. The "future date" test uses 2026-12-01 and will still pass next year, because "today" never moves in the test.

The tests cover a complete register, a repeat that creates nothing, the three date rules as one theory, and a missing student. The suite has 53 tests, all passing.

Step 6: the reports

The office's daily view counts every class for one day:

Output (first and last classes)
GET /api/attendance/daily-summary?date=2026-07-01
[{"className":"Class 1","present":880,"absent":60,"late":30,"leave":30,"attendancePercent":91}, ...,
 {"className":"Class 10","present":885,"absent":61,"late":30,"leave":30,"attendancePercent":90.95}]

And one student over a period:

Output
GET /api/students/ADM-2017-0003/attendance-summary?from=2026-07-01&to=2026-07-31
{"admissionNumber":"ADM-2017-0003","studentName":"Kabir Singh","from":"2026-07-01","to":"2026-07-31","days":3,"present":2,"absent":1,"late":0,"leave":0,"attendancePercent":66.67}

The percentage follows Lesson 18: Present and Late count as attended. Kabir was absent on one of the three recorded days.

Example: how fast over 600,000 rows

The daily summary took 283 ms the first time and 120 ms the second. A bulk student's 60-day summary took 20 ms, then 6 ms. The work happens in SQL Server: GROUP BY turns about 10,000 records of a day into ten rows, and only those travel to the API.

The date filter uses IX_AttendanceRecords_AttendanceDate, and the student filter the unique key on (EnrollmentId, AttendanceDate), both from Module 5. The numbers look so even, about 91% everywhere, because the bulk data generator spreads absences evenly; real schools are messier.

Common mistakes

One request per student. Forty requests per class, and half-saved registers when one fails. Fix: the whole register in one request, saved in one transaction.
Blind inserts. A retry duplicates the day, or fails. Fix: update existing records, create only missing ones.
Accepting partial registers. A forgotten student looks present. Fix: require every enrolled student.
DateTime.Now in rules. Tests break as days pass. Fix: TimeProvider.
Calculating reports in C#. 600,000 rows travel to the API to produce ten numbers. Fix: GROUP BY in SQL, with the right index.

In real projects

From 16 years of .NET projects

A school's attendance app saved each student with its own request. On a slow network, a teacher saved 40 students, saw an error on student 23, and tapped Save again.

The first 22 students now had two records each, the rest one. The monthly report showed some children at 104% attendance, and the principal stopped trusting the reports altogether.

We changed it to one idempotent request per register, as in this lesson, and repaired the duplicates. Retries became harmless, and the reports became believable again.

Best practices

Save a section's register for a day as one resource, with one PUT.
Make saving idempotent: update what exists, create what is missing.
Validate registers for completeness and exactness.
Use TimeProvider for "today" in business rules.
Aggregate in SQL, filtered on indexed columns.

Recap

A teacher sends the whole register; PUT /api/sections/{sectionId}/attendance?date= saves it.
The second identical request created nothing; a correction updated one line.
Rules 50040 to 50045 check dates and the register; all answer 422.
TimeProvider let tests fix "today" at 2026-10-07.
Reports grouped about 10,000 records into ten rows in about 120 ms.

Homework

Library module task: Add GET /api/book-issues/overdue to a single-file library API: every book not returned and past its due date, with the days late and the fine (2 per day) as on an asOf date. When asOf is left out, use today from TimeProvider.

A correct result looks like:

Output
GET /api/book-issues/overdue?asOf=2026-08-01
[{"title":"The Jungle Book","studentName":"Advika Sharma","dueOn":"2026-07-20","daysLate":12,"fine":24}]
GET /api/book-issues/overdue?asOf=2026-07-15
[]

Show the solution

library-assignment/079-library-overdue.cs
// Library homework, Lesson 79: a daily report, like the attendance summary. Every book not yet returned
// and past its due date, with the fine as on a date. The date defaults to today, from TimeProvider.
// Run with:  dotnet run library-assignment/079-library-overdue.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());
builder.Services.AddSingleton(TimeProvider.System);
WebApplication app = builder.Build();
app.MapControllers();
app.Run("http://localhost:5079");

public class OverdueIssue
{
    public string Title { get; set; } = "";
    public string StudentName { get; set; } = "";
    public DateOnly DueOn { get; set; }
    public int DaysLate { get; set; }
    public decimal Fine { get; set; }
}

[ApiController]
[Route("api/book-issues/overdue")]
public class OverdueController : ControllerBase
{
    private readonly SqlConnectionFactory _connections;
    private readonly TimeProvider _time;

    public OverdueController(SqlConnectionFactory connections, TimeProvider time)
    {
        _connections = connections;
        _time = time;
    }

    // GET /api/book-issues/overdue?asOf=2026-08-01, or without asOf for today.
    [HttpGet]
    public async Task<IEnumerable<OverdueIssue>> Get([FromQuery] DateOnly? asOf)
    {
        DateOnly date = asOf ?? DateOnly.FromDateTime(_time.GetLocalNow().DateTime);
        await using SqlConnection connection = _connections.Create();
        return await connection.QueryAsync<OverdueIssue>("""
            SELECT b.Title,
                   s.FirstName + ' ' + s.LastName AS StudentName,
                   i.DueOn,
                   DATEDIFF(DAY, i.DueOn, @AsOf) AS DaysLate,
                   DATEDIFF(DAY, i.DueOn, @AsOf) * 2 AS Fine
            FROM dbo.BookIssues AS i
            JOIN dbo.BookCopies AS c ON c.Id = i.BookCopyId
            JOIN dbo.Books AS b ON b.Id = c.BookId
            JOIN dbo.Students AS s ON s.Id = i.StudentId
            WHERE i.ReturnedOn IS NULL AND i.DueOn < @AsOf
            ORDER BY i.DueOn;
            """, new { AsOf = date });
    }
}

Without asOf, the answer depends on the day you run it: on 2026-10-08 the book was 80 days late, with a fine of 160. The class uses properties, not a positional record, because of Dapper and DateOnly (Lesson 58).

Quiz

Why does the attendance endpoint use PUT for a section and date?
1. PUT is faster
2. The day's register is one resource, replaced as a whole, and PUT is idempotent
3. POST is not allowed
4. For caching
Show answer
Answer: B. The day's register is one resource, replaced as a whole, and PUT is idempotent Sending the same register twice changed nothing.
What did the second identical request report?
1. created 4
2. created 0, updated 0
3. 409 Conflict
4. updated 4
Show answer
Answer: B. created 0, updated 0 Existing records with the same values are not changed.
Why is a register with roll numbers 1 to 3 refused for a section of four?
1. Too short
2. A missing student would look present: the register must be complete (50044)
3. Roll 4 is invalid
4. The date is wrong
Show answer
Answer: B. A missing student would look present: the register must be complete (50044) Every enrolled student must be included.
What does TimeProvider give the tests?
1. Faster tests
2. A fixed today, so date rules give the same result every run
3. A database
4. Time zones only
Show answer
Answer: B. A fixed today, so date rules give the same result every run The test clock stopped at 2026-10-07.
Where are the daily summary's counts calculated?
1. In the controller
2. In SQL Server, with GROUP BY
3. In the browser
4. In a background job
Show answer
Answer: B. In SQL Server, with GROUP BY About 10,000 records become ten rows before leaving the database.

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 Why take a whole section's attendance in one request instead of one request per student? design

One request is faster on weak networks, and it can be saved in one transaction, so a register is never half saved. It also lets the API check the register as a whole, such as that every enrolled student is included exactly once.

See the example · Permalink

F What does it mean that the attendance PUT is idempotent, and why does it matter? concept

Sending the same register twice leaves the same result as sending it once: the second request created and updated nothing. On school Wi-Fi, apps retry timed-out requests, so retries must be harmless instead of creating duplicate records.

See the example · Permalink

F How is the attendance percentage calculated in the SMS? concept

(Present + Late) divided by all recorded days, times 100, the same rule as Lesson 18's SQL. Kabir had 2 present days out of 3 recorded in July, which is 66.67%.

See the example · Permalink

Mid (3-5 yrs)

M What is TimeProvider, and why use it instead of DateTime.Now? coding

.NET's built-in abstraction for the current time. The app registers TimeProvider.System; tests pass a provider stopped at a fixed moment. Rules such as "no future dates" then give the same result on every test run, instead of changing as real days pass.

See the example · Permalink

M How do you implement 'update if it exists, otherwise insert' for a batch with EF Core? coding

Load the existing rows for the batch's key (section and date) as tracked entities, then for each item update the matching entity if its values differ, or add a new one. One SaveChangesAsync writes the updates and inserts in one transaction, with a unique constraint as the final guard against duplicates.

See the example · Permalink

M Why should attendance reports be aggregated in SQL rather than in C#? concept

A day has about 10,000 records. GROUP BY in SQL Server sends back ten rows of counts; loading the records into the API to count them would move all 10,000 rows for the same ten numbers. With an index on the date, the SMS's daily summary took about 120 ms over 600,000 rows.

See the example · Permalink

Senior (6-9 yrs)

S Reports show some students above 100% attendance. What likely happened, and how do you prevent it? scenario

Duplicate records for the same student and day, usually from retried requests that inserted again. Repair the duplicates, add a unique constraint on (enrollment, date) if missing, and make the save idempotent: one request per register, updating existing records instead of inserting blindly.

How to answer: Work from symptom to cause (duplicates), name the missing guards (constraint, idempotency), and cover the data repair with verification.

See the example · Permalink

S Should the attendance endpoint accept a partial register? Discuss the trade-off. design

Requiring the complete register prevents a forgotten student from looking present or unrecorded, and keeps every day's data comparable. Accepting partial registers suits workflows such as marking late arrivals later; then the API needs explicit "not marked" handling in reports. The SMS requires complete registers and allows corrections by sending the register again.

How to answer: Weigh data quality against flexibility, describe what reports must do in each design, and say how corrections work.

See the example · Permalink

Architect / Lead (10+ yrs)

A Attendance grows by 10,000 rows a day across many schools. How would you keep it fast and manageable for years? design

Index for the main access paths (date; enrollment and date), keep reports aggregated in SQL, pre-compute daily and monthly summaries in a summary table updated after each register, archive or partition old years by date, and serve dashboards from the summaries instead of the raw rows.

How to answer: Estimate the growth, then cover indexing, summary tables, partitioning (SQL Extras), archiving, and how reports stay correct when summaries are updated.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-79-attendance (code repository coming soon)
Files: src/SchoolApp.Core/Services/AttendanceService.cs, src/SchoolApp.Data/EfCore/EfAttendanceRepository.cs, src/SchoolApp.Api/Controllers/AttendanceController.cs, tests/SchoolApp.Tests/Unit/AttendanceServiceTests.cs, library-assignment/079-library-overdue.cs
Video: coming soon

What's next

Attendance is recorded and reported. Next: one response shape for every SMS answer, success or error, built with one common method. Lesson 80: A common API response wrapper

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

Introduction​

Why this matters​

The concept​

Example: one register, one request​

Example: idempotent saving​

Example: rules for a register​

Example: TimeProvider​

Apply it to the SMS​

Step 1: requests and results​

Step 2: the service​

Step 3: the repository​

Step 4: the controller​

Step 5: take a register, twice, then correct it​

Example: every rule​

Example: tests with a fixed clock​

Step 6: the reports​

Example: how fast over 600,000 rows​

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: one register, one request

Example: idempotent saving

Example: rules for a register

Example: TimeProvider

Apply it to the SMS

Step 1: requests and results

Step 2: the service

Step 3: the repository

Step 4: the controller

Step 5: take a register, twice, then correct it

Example: every rule

Example: tests with a fixed clock

Step 6: the reports

Example: how fast over 600,000 rows

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