Teachers and Guardians API: Many-to-Many in Practice – Web API | Lesson 77

Published / updated7 Oct 2026

Introduction

A student has a father, a mother, sometimes another guardian. A guardian can have several children at the school: Rajesh Sharma is the father of both Aarav and Advika. That is a many-to-many relationship, stored in the StudentGuardians table from Lesson 11.

This lesson adds the teaching staff to the API and maps the guardian relationship in EF Core, the gap left open in Module 9. It reads the link from both sides, and adds new guardians while keeping one rule: each student has exactly one primary contact.

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

Map a junction table with extra columns as its own entity with a composite key.
Read a many-to-many link from both sides with nested routes.
Enforce "at most one" with a filtered unique index, and the same rule in a service.
Change several rows in one save, in the right order.
Decide which personal details a list endpoint should and should not return.

Why this matters

Guardians are who the school calls when a child is ill, and who receives fee reminders and report cards. Calling the wrong number, or two numbers for "the" primary contact, is a real problem for real families.

Many-to-many relationships also appear everywhere else: teachers and subjects, students and clubs, books and authors. Mapping one properly once gives you the pattern for all of them.

The concept

Example: a junction table with a payload

StudentGuardians has one row per link: a student, a guardian, the relationship (Father, Mother, or Guardian), and whether this guardian is the primary contact. Those extra columns are the link's payload: facts about the relationship itself, not about either person.

Think of a three-column register in the school office. Read it by the student column to see one child's family; read it by the guardian column to see one parent's children. Same register, two directions.

Example: why siblings share one guardian row

Rajesh Sharma is one row in Guardians, linked twice in StudentGuardians: once to Aarav, once to Advika. When he changes his phone number, the office updates one row, and both children's records are right at once.

With a guardian copied into each child's record, the office would have to remember every copy, and sooner or later one would be missed. Keeping each fact in one place is the normalization rule from Lesson 7, applied to people.

Example: modelling it in EF Core

Without payload	With payload, as in the SMS
EF Core can hide the junction table: `student.Guardians` directly	The junction is its own entity: `StudentGuardian`
No place to store Relationship or IsPrimaryContact	Two one-to-many relationships, one to each side
Key managed by EF Core	Composite key: `(StudentId, GuardianId)`

A composite key is a primary key made of more than one column. A guardian can be linked to a student once, so the pair together is unique.

Example: "at most one" in the database

A student may have many guardians but only one primary contact. A normal unique index on StudentId would allow only one guardian per student. A filtered unique index solves it: it contains only the rows with IsPrimaryContact = 1, so a student can appear there once.

This is the same tool Lesson 10 used for "only one current academic year". The rule lives in the service too, so the API can apply it correctly; the index is the final guard, as in Lesson 75.

Example: what a list should show

Endpoint	Shows	Leaves out
`GET /api/teachers`	Code, name, school email, qualification, joining date	Phone, date of birth
`GET /api/students/{admissionNumber}/guardians`	Names, relationship, phone, email	Occupation

Lists are seen by many people; personal details should appear only where the job needs them. The office needs a guardian's phone to call them.

A staff list does not need every teacher's personal phone. Module 12 adds who may call each endpoint at all.

Apply it to the SMS

Step 1: the index

database/077-primary-contact.sql
-- Lesson 77: each student has at most one primary contact among their guardians.
-- A filtered unique index: only rows with IsPrimaryContact = 1 are in it, so a StudentId can appear
-- there only once. Safe to run more than once.
USE SchoolDb;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UQ_StudentGuardians_PrimaryContact')
    CREATE UNIQUE INDEX UQ_StudentGuardians_PrimaryContact
        ON dbo.StudentGuardians (StudentId)
        WHERE IsPrimaryContact = 1;
GO

-- Check: every student with guardians has exactly one primary contact.
SELECT
    COUNT(*) AS StudentsWithGuardians,
    SUM(CASE WHEN PrimaryContacts = 1 THEN 1 ELSE 0 END) AS WithOnePrimaryContact
FROM (
    SELECT StudentId, SUM(CAST(IsPrimaryContact AS INT)) AS PrimaryContacts
    FROM dbo.StudentGuardians
    GROUP BY StudentId
) AS s;
GO

Output
StudentsWithGuardians WithOnePrimaryContact
--------------------- ---------------------
                   12                    12

All twelve students in the sample data with guardians already have exactly one primary contact, so the index can be created. sms-reference.md records it next to the other script objects.

Step 2: EF Core configurations

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

namespace SchoolApp.Data.EfCore.Configurations;

// Lesson 77: teachers, guardians, and the StudentGuardians junction table (Lesson 11).
public class TeacherConfiguration : IEntityTypeConfiguration<Teacher>
{
    public void Configure(EntityTypeBuilder<Teacher> builder)
    {
        builder.ToTable("Teachers");
        builder.Property(t => t.EmployeeCode).HasMaxLength(10).IsUnicode(false);   // no setter: named here
        builder.Property(t => t.FirstName).HasMaxLength(50);
        builder.Property(t => t.LastName).HasMaxLength(50);
        builder.Property(t => t.Email).HasMaxLength(100).IsUnicode(false);
        builder.Property(t => t.Phone).HasMaxLength(15).IsUnicode(false);
        builder.Property(t => t.Qualification).HasMaxLength(100);
        builder.Property(t => t.Gender)
            .HasColumnType("char(1)")
            .HasConversion(gender => ((char)gender).ToString(), code => (Gender)code[0]);
        builder.HasAuditColumns();
        builder.Property(t => t.DeletedAt).HasPrecision(0);
        builder.HasQueryFilter(SoftDeleteFilter.Name, t => !t.IsDeleted);
    }
}

public class GuardianConfiguration : IEntityTypeConfiguration<Guardian>
{
    public void Configure(EntityTypeBuilder<Guardian> builder)
    {
        builder.ToTable("Guardians");
        builder.Property(g => g.FirstName).HasMaxLength(50);
        builder.Property(g => g.LastName).HasMaxLength(50);
        builder.Property(g => g.Phone).HasMaxLength(15).IsUnicode(false);
        builder.Property(g => g.Email).HasMaxLength(100).IsUnicode(false);
        builder.Property(g => g.Occupation).HasMaxLength(50);
        builder.HasAuditColumns();
        builder.Property(g => g.DeletedAt).HasPrecision(0);
        builder.HasQueryFilter(SoftDeleteFilter.Name, g => !g.IsDeleted);
    }
}

// A many-to-many relationship with extra columns: modelled as its own entity with two one-to-many
// relationships, and a key made of both foreign keys.
public class StudentGuardianConfiguration : IEntityTypeConfiguration<StudentGuardian>
{
    public void Configure(EntityTypeBuilder<StudentGuardian> builder)
    {
        builder.ToTable("StudentGuardians");
        builder.HasKey(sg => new { sg.StudentId, sg.GuardianId });
        builder.Property(sg => sg.Relationship).HasMaxLength(10).IsUnicode(false);

        builder.HasOne(sg => sg.Student).WithMany().HasForeignKey(sg => sg.StudentId);
        builder.HasOne(sg => sg.Guardian).WithMany().HasForeignKey(sg => sg.GuardianId);

        // The filtered unique index from database/077-primary-contact.sql, recorded in the model, so the
        // rule is visible here and EF Core can take it into account when it orders a save's commands.
        builder.HasIndex(sg => sg.StudentId)
            .IsUnique()
            .HasFilter("[IsPrimaryContact] = 1")
            .HasDatabaseName("UQ_StudentGuardians_PrimaryContact");

        // Both ends are soft-deletable, so links to removed people are hidden too (Lesson 76).
        builder.HasQueryFilter(SoftDeleteFilter.Name, sg => !sg.Student!.IsDeleted && !sg.Guardian!.IsDeleted);
    }
}

HasKey(sg => new { sg.StudentId, sg.GuardianId }) declares the composite key. The two HasOne calls are the two one-to-many relationships. The query filter hides links to removed students or guardians, following the rule from Lesson 76, and EF Core starts with no warnings.

Teacher and Guardian both derive from Person (Lesson 39), and both are mapped with their constructors, as Student was in Lesson 59.

Step 3: the service rule

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

namespace SchoolApp.Core.Services;

// Lesson 77: adding a guardian to a student. The rule: a student has at most one primary contact,
// so a new primary contact takes over from the old one, in the same save.
// (The database enforces the same rule with UQ_StudentGuardians_PrimaryContact.)
public class GuardianService
{
    private readonly IStudentRepository _students;
    private readonly IPeopleRepository _people;

    public GuardianService(IStudentRepository students, IPeopleRepository people)
    {
        _students = students;
        _people = people;
    }

    // Returns null when no student has this admission number.
    public async Task<GuardianDto?> AddAsync(string admissionNumber, AddGuardianRequest request,
                                             CancellationToken cancellationToken = default)
    {
        Student? student = await _students.GetByAdmissionNumberAsync(admissionNumber, cancellationToken);
        if (student is null)
        {
            return null;
        }

        if (request.IsPrimaryContact)
        {
            foreach (StudentGuardian existing in await _people.GetLinksAsync(student.Id, cancellationToken))
            {
                existing.IsPrimaryContact = false;
            }
        }

        // The Guardian constructor checks the names and phone (Lessons 38 and 39).
        Guardian guardian = new Guardian(request.FirstName, request.LastName, request.Phone)
        {
            Email = request.Email
        };
        StudentGuardian link = new StudentGuardian
        {
            StudentId = student.Id,
            Guardian = guardian,
            Relationship = request.Relationship,
            IsPrimaryContact = request.IsPrimaryContact
        };
        await _people.AddLinkAsync(link, cancellationToken);

        return new GuardianDto(guardian.Id, guardian.FullName, link.Relationship, guardian.Phone,
                               guardian.Email, link.IsPrimaryContact);
    }
}

When the new guardian is the primary contact, the service loads the student's links and clears the flag on each. The new guardian, the new link, and the changed links are then saved together, in one SaveChangesAsync.

Step 4: the repository and controllers

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

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

namespace SchoolApp.Data.EfCore;

// Lesson 77: teachers and guardians with EF Core.
public class EfPeopleRepository : IPeopleRepository
{
    private readonly SchoolDbContext _db;

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

    public Task<List<TeacherDto>> GetTeachersAsync(CancellationToken cancellationToken = default)
    {
        return _db.Teachers
            .Where(t => t.IsActive)
            .OrderBy(t => t.EmployeeCode)
            .Select(t => new TeacherDto(t.EmployeeCode, t.FirstName + " " + t.LastName, t.Email, t.Qualification, t.JoiningDate))
            .ToListAsync(cancellationToken);
    }

    public Task<TeacherDto?> GetTeacherAsync(string employeeCode, CancellationToken cancellationToken = default)
    {
        return _db.Teachers
            .Where(t => t.EmployeeCode == employeeCode)
            .Select(t => new TeacherDto(t.EmployeeCode, t.FirstName + " " + t.LastName, t.Email, t.Qualification, t.JoiningDate))
            .SingleOrDefaultAsync(cancellationToken);
    }

    public async Task<List<GuardianDto>?> GetGuardiansOfStudentAsync(string admissionNumber, CancellationToken cancellationToken = default)
    {
        int? studentId = await _db.Students
            .Where(s => s.AdmissionNumber == admissionNumber)
            .Select(s => (int?)s.Id)
            .SingleOrDefaultAsync(cancellationToken);
        if (studentId is null)
        {
            return null;
        }

        // Through the junction table: each link brings its guardian and the relationship.
        return await _db.StudentGuardians
            .Where(sg => sg.StudentId == studentId)
            .OrderByDescending(sg => sg.IsPrimaryContact).ThenBy(sg => sg.Relationship)
            .Select(sg => new GuardianDto(sg.GuardianId, sg.Guardian!.FirstName + " " + sg.Guardian.LastName,
                                          sg.Relationship, sg.Guardian.Phone, sg.Guardian.Email, sg.IsPrimaryContact))
            .ToListAsync(cancellationToken);
    }

    public async Task<List<StudentDto>?> GetChildrenOfGuardianAsync(int guardianId, CancellationToken cancellationToken = default)
    {
        if (!await _db.Guardians.AnyAsync(g => g.Id == guardianId, cancellationToken))
        {
            return null;
        }

        // The same junction table, read from the other side.
        return await _db.StudentGuardians
            .Where(sg => sg.GuardianId == guardianId)
            .OrderBy(sg => sg.Student!.DateOfBirth)
            .Select(sg => new StudentDto(sg.Student!.Id, sg.Student.AdmissionNumber,
                                         sg.Student.FirstName + " " + sg.Student.LastName,
                                         sg.Student.DateOfBirth, sg.Student.Gender.ToString(), sg.Student.Status.ToString()))
            .ToListAsync(cancellationToken);
    }

    public Task<List<StudentGuardian>> GetLinksAsync(int studentId, CancellationToken cancellationToken = default)
    {
        return _db.StudentGuardians
            .Include(sg => sg.Guardian)
            .Where(sg => sg.StudentId == studentId)
            .ToListAsync(cancellationToken);
    }

    // One SaveChanges: the new guardian, the new link, and any loaded link that was changed.
    public async Task AddLinkAsync(StudentGuardian link, CancellationToken cancellationToken = default)
    {
        _db.StudentGuardians.Add(link);
        await _db.SaveChangesAsync(cancellationToken);
    }
}

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

namespace SchoolApp.Api.Controllers;

// Lesson 77: the many-to-many link from both sides.
// A student's guardians:   GET and POST /api/students/{admissionNumber}/guardians
// A guardian's children:   GET /api/guardians/{guardianId}/students
// The routes start with "/" inside the actions, so one controller can serve both shapes.
[ApiController]
[Produces("application/json")]
public class GuardiansController : ControllerBase
{
    private readonly IPeopleRepository _people;
    private readonly GuardianService _guardians;

    public GuardiansController(IPeopleRepository people, GuardianService guardians)
    {
        _people = people;
        _guardians = guardians;
    }

    /// <summary>Gets a student's guardians, primary contact first.</summary>
    /// <response code="200">The guardians; an empty list if none are recorded.</response>
    /// <response code="404">No student has this admission number.</response>
    [HttpGet("/api/students/{admissionNumber}/guardians")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<ActionResult<List<GuardianDto>>> GetOfStudent(string admissionNumber, CancellationToken cancellationToken)
    {
        List<GuardianDto>? guardians = await _people.GetGuardiansOfStudentAsync(admissionNumber, cancellationToken);
        return guardians is null ? NotFound() : guardians;
    }

    /// <summary>Adds a new guardian to a student. A new primary contact replaces the old one.</summary>
    /// <response code="201">The guardian was added.</response>
    /// <response code="400">The request is invalid.</response>
    /// <response code="404">No student has this admission number.</response>
    [HttpPost("/api/students/{admissionNumber}/guardians")]
    [ProducesResponseType(StatusCodes.Status201Created)]
    [ProducesResponseType(StatusCodes.Status400BadRequest)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<ActionResult<GuardianDto>> AddToStudent(string admissionNumber, AddGuardianRequest request,
                                                              CancellationToken cancellationToken)
    {
        GuardianDto? added = await _guardians.AddAsync(admissionNumber, request, cancellationToken);
        if (added is null)
        {
            return NotFound();
        }
        return CreatedAtAction(nameof(GetChildren), new { guardianId = added.Id }, added);
    }

    /// <summary>Gets a guardian's children, oldest first.</summary>
    /// <response code="200">The students.</response>
    /// <response code="404">No guardian has this id.</response>
    [HttpGet("/api/guardians/{guardianId:int}/students")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<ActionResult<List<StudentDto>>> GetChildren(int guardianId, CancellationToken cancellationToken)
    {
        List<StudentDto>? children = await _people.GetChildrenOfGuardianAsync(guardianId, cancellationToken);
        return children is null ? NotFound() : children;
    }
}

GuardiansController has no [Route] on the class. Each action's route starts with /, which makes it absolute, so one controller can serve /api/students/.../guardians and /api/guardians/.../students.

Step 5: read both sides

Output
GET /api/students/ADM-2017-0001/guardians
[{"id":1,"fullName":"Rajesh Sharma","relationship":"Father","phone":"9876540101","email":"rajesh.sharma@nexcodingschool.example","isPrimaryContact":true},{"id":2,"fullName":"Neha Sharma","relationship":"Mother","phone":"9876540102","email":null,"isPrimaryContact":false}]

GET /api/guardians/1/students
[{"id":1,"admissionNumber":"ADM-2017-0001","fullName":"Aarav Sharma","dateOfBirth":"2011-04-12","gender":"Male","status":"Active"},{"id":7,"admissionNumber":"ADM-2021-0001","fullName":"Advika Sharma","dateOfBirth":"2015-07-21","gender":"Female","status":"Active"}]

GET /api/students/ADM-2026-00001/guardians
[]

Aarav's guardians come primary contact first. From Rajesh's side, both his children appear, oldest first: the siblings from the SMS reference. A bulk student has no guardians recorded, so the answer is an empty list, not a 404: the student exists, the list is just empty.

GET /api/teachers returned all eight teachers, from TCH-001 Anitha Rao to TCH-008 Arjun Mehta, with no phone numbers in the list.

Example: a new primary contact

Kabir's uncle, Gurpreet Singh, becomes his primary contact:

Output
POST /api/students/ADM-2017-0003/guardians   {"firstName":"Gurpreet","lastName":"Singh","phone":"9876540199","email":null,"relationship":"Guardian","isPrimaryContact":true}
HTTP/1.1 201 Created
Location: http://localhost:5080/api/guardians/23/students
{"id":23,"fullName":"Gurpreet Singh","relationship":"Guardian","phone":"9876540199","email":null,"isPrimaryContact":true}

GET /api/students/ADM-2017-0003/guardians
[{"id":23,"fullName":"Gurpreet Singh","relationship":"Guardian","phone":"9876540199","email":null,"isPrimaryContact":true},{"id":5,"fullName":"Harpreet Singh","relationship":"Father","phone":"9876540105","email":null,"isPrimaryContact":false},{"id":6,"fullName":"Simran Singh","relationship":"Mother","phone":"9876540106","email":null,"isPrimaryContact":false}]

Harpreet Singh, Kabir's father, is no longer the primary contact. The SQL log showed the order EF Core used inside the save: insert the new guardian, update the old link to clear its flag, then insert the new link.

That order matters: inserting the new primary link first would break the index, because for a moment Kabir would have two primary contacts. As an experiment, the index declaration was removed from the configuration; EF Core still ran the update first, so the declaration is not what made it work here. Keeping it in the model documents the rule where developers will look.

The demo guardians were removed afterwards, and the original primary contacts restored.

Example: the index refuses what the code would allow

What if some other code, or a hurried fix in SSMS, made Kabir's mother a primary contact too, while his father still was? Tried inside a transaction that was rolled back:

SSMS
UPDATE dbo.StudentGuardians SET IsPrimaryContact = 1 WHERE StudentId = 3 AND GuardianId = 6;

Output
Msg 2601, Level 14, State 1
Cannot insert duplicate key row in object 'dbo.StudentGuardians' with unique index 'UQ_StudentGuardians_PrimaryContact'. The duplicate key value is (3).

The database refused it. Error 2601 is a unique index violation, one of the two numbers that SqlErrors.IsUniqueKeyViolation from Lesson 75 recognises. No route around the service can break the rule, whether it is another app, a report tool, or a person typing SQL.

Example: validation for people

Output
POST /api/students/ADM-2017-0003/guardians   {"firstName":"","lastName":"Singh","phone":"98765","email":"not-an-email","relationship":"Uncle","isPrimaryContact":false}
HTTP/1.1 400 Bad Request
{"...":"...","errors":{"Email":["The Email field is not a valid e-mail address."],"Phone":["Phone must be 10 digits."],"FirstName":["The FirstName field is required."],"Relationship":["Relationship must be Father, Mother, or Guardian."]}}

Four problems, one response. "Uncle" is refused because the database's CHECK constraint allows only Father, Mother, and Guardian; the request rule matches it, so the client learns before the database says no.

Example: tests for the rule

GuardianServiceTests checks the rule with fakes: a new primary contact leaves exactly one primary, a non-primary guardian leaves the old one alone, and an unknown student returns null. The suite now has 43 tests, all passing.

Common mistakes

Hiding a junction table that has a payload. There is no place for the relationship's own facts. Fix: map it as an entity with a composite key.
"Only one" rules checked only in code. Two requests at once can both pass. Fix: a filtered unique index as well.
404 for an empty list. The client cannot tell "no student" from "no guardians". Fix: 404 only when the parent does not exist.
Personal details in every list. Phone numbers end up on screens that do not need them. Fix: list DTOs with only what lists need.
Duplicating guardians for siblings. Two "Rajesh Sharma" rows drift apart. Fix: link the existing guardian to the second child.

In real projects

From 16 years of .NET projects

A school's emergency call list once showed two primary contacts for some children, and the office called whichever appeared first. One afternoon that was a father who had moved abroad.

The app set the new primary contact but never cleared the old one, and nothing in the database stopped it. Over two years, hundreds of students had collected several "primary" contacts.

We fixed the code, cleaned the data, and added a filtered unique index like the one in this lesson. The code bug could return; the index makes sure the data cannot.

Best practices

Map junction tables with payload columns as entities with composite keys.
Offer both directions of a many-to-many link as nested routes.
Enforce "at most one" rules in a service and with a filtered unique index.
Make related changes in one save, so they succeed or fail together.
Return only the personal details each endpoint's users need.

Recap

StudentGuardians is a many-to-many junction with a payload: relationship and primary contact.
EF Core maps it as an entity with a composite key and two one-to-many relationships.
/api/students/{admissionNumber}/guardians and /api/guardians/{guardianId}/students read both sides.
A new primary contact clears the old one in the same save; a filtered unique index guards the rule.
Lists leave out personal details that their users do not need.

Homework

Library module task: BookIssues links students and book copies, like StudentGuardians links students and guardians. Add GET /api/students/{admissionNumber}/book-issues and GET /api/books/{isbn}/borrowers to a single-file library API, reading SchoolDb with Dapper.

A correct result looks like:

Output
GET /api/students/ADM-2021-0001/book-issues
[{"title":"The Jungle Book","copyNumber":1,"issuedOn":"2026-07-06","returnedOn":null}]
GET /api/books/9788173711466/borrowers
[{"admissionNumber":"ADM-2017-0001","fullName":"Aarav Sharma","issuedOn":"2026-07-01","returnedOn":"2026-07-14"}]

Show the solution

library-assignment/077-library-borrowers.cs
// Library homework, Lesson 77: BookIssues links students and book copies, like StudentGuardians links
// students and guardians. Read the link from both sides: a student's books, and a book's borrowers.
// Run with:  dotnet run library-assignment/077-library-borrowers.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:5077");

// Classes with properties, not positional records: Dapper fills DateOnly through setters only (Lesson 58).
public class BorrowedBook
{
    public string Title { get; set; } = "";
    public short CopyNumber { get; set; }
    public DateOnly IssuedOn { get; set; }
    public DateOnly? ReturnedOn { get; set; }
}

public class Borrower
{
    public string AdmissionNumber { get; set; } = "";
    public string FullName { get; set; } = "";
    public DateOnly IssuedOn { get; set; }
    public DateOnly? ReturnedOn { get; set; }
}

[ApiController]
public class BorrowingController : ControllerBase
{
    private readonly SqlConnectionFactory _connections;

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

    // From the student's side: every book this student has borrowed.
    [HttpGet("/api/students/{admissionNumber}/book-issues")]
    public async Task<IEnumerable<BorrowedBook>> BooksOfStudent(string admissionNumber)
    {
        await using SqlConnection connection = _connections.Create();
        return await connection.QueryAsync<BorrowedBook>("""
            SELECT b.Title, c.CopyNumber, i.IssuedOn, i.ReturnedOn
            FROM dbo.BookIssues AS i
            JOIN dbo.Students AS s ON s.Id = i.StudentId
            JOIN dbo.BookCopies AS c ON c.Id = i.BookCopyId
            JOIN dbo.Books AS b ON b.Id = c.BookId
            WHERE s.AdmissionNumber = @AdmissionNumber
            ORDER BY i.IssuedOn;
            """, new { AdmissionNumber = new DbString { Value = admissionNumber, IsAnsi = true, Length = 15 } });
    }

    // From the book's side: every student who has borrowed any copy of this book.
    [HttpGet("/api/books/{isbn}/borrowers")]
    public async Task<IEnumerable<Borrower>> BorrowersOfBook(string isbn)
    {
        await using SqlConnection connection = _connections.Create();
        return await connection.QueryAsync<Borrower>("""
            SELECT s.AdmissionNumber, s.FirstName + ' ' + s.LastName AS FullName, i.IssuedOn, i.ReturnedOn
            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 AND s.IsDeleted = 0
            WHERE b.Isbn = @Isbn
            ORDER BY i.IssuedOn;
            """, new { Isbn = new DbString { Value = isbn, IsAnsi = true, Length = 13 } });
    }
}

The first try used positional records and failed with "A parameterless default constructor or one matching signature ... is required": the DateOnly trap from Lesson 58. Classes with properties fixed it.

Quiz

What is the payload of a junction table?
1. Its primary key
2. Columns about the relationship itself, such as Relationship and IsPrimaryContact
3. The two foreign keys
4. Its indexes
Show answer
Answer: B. Columns about the relationship itself, such as Relationship and IsPrimaryContact Facts that belong to the link, not to either person.
What is the key of StudentGuardians?
1. An Id column
2. The composite key (StudentId, GuardianId)
3. GuardianId only
4. Relationship
Show answer
Answer: B. The composite key (StudentId, GuardianId) A guardian is linked to a student once.
Which index allows many guardians but only one primary contact per student?
1. A unique index on StudentId
2. A unique index on StudentId filtered to IsPrimaryContact = 1
3. A clustered index on GuardianId
4. No index can
Show answer
Answer: B. A unique index on StudentId filtered to IsPrimaryContact = 1 Only primary-contact rows are in the filtered index.
A bulk student has no guardians recorded. What does GET .../guardians return?
1. 404
2. 200 with an empty list
3. 204
4. 500
Show answer
Answer: B. 200 with an empty list The student exists; the list is just empty.
Why does GET /api/teachers leave out phone numbers?
1. Teachers have none
2. Lists are seen widely; personal details belong only where needed
3. Phones are encrypted
4. JSON cannot hold phones
Show answer
Answer: B. Lists are seen widely; personal details belong only where needed Show only what each endpoint's users need.

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 a many-to-many relationship? Give an example from a school system. concept

Each row on one side can relate to many rows on the other, and the other way round. A student has several guardians, and a guardian can have several children at the school. It is stored in a junction table, such as StudentGuardians, with one row per link.

See the example · Permalink

F What is a composite key? concept

A primary key made of more than one column. In StudentGuardians it is (StudentId, GuardianId): the pair is unique, so a guardian is linked to the same student only once. In EF Core it is declared with HasKey(sg => new { sg.StudentId, sg.GuardianId }).

See the example · Permalink

F Should an endpoint return 404 or an empty list when a student has no guardians? design

An empty list with 200: the student exists, and the answer to "which guardians?" is "none". Return 404 only when the parent resource, the student, does not exist, so clients can tell the two cases apart.

See the example · Permalink

Mid (3-5 yrs)

M How do you map a junction table that has its own columns in EF Core? coding

As an entity of its own, such as StudentGuardian, with a composite key and two one-to-many relationships, one to each side. EF Core's automatic many-to-many mapping hides the junction table and has no place for columns like Relationship or IsPrimaryContact.

See the example · Permalink

M How do you enforce 'at most one primary contact per student' in SQL Server? coding

With a filtered unique index on StudentId that includes only rows where IsPrimaryContact = 1. Students may have many guardians, but only one row per student can be in the index, so a second primary contact fails with error 2601.

See the example · Permalink

M How can one controller serve routes under two different resources? coding

Leave the controller without a class-level route, or ignore it, and start each action's route with "/", which makes it absolute, such as [HttpGet("/api/students/{admissionNumber}/guardians")] and [HttpGet("/api/guardians/{guardianId:int}/students")].

See the example · Permalink

Senior (6-9 yrs)

S Some students show two primary contacts after two years of use. How do you fix the system? scenario

Fix the code so setting a new primary clears the old one in the same save; clean existing data with a script that keeps one primary per student by an agreed rule and reviews unclear cases; then add a filtered unique index so the data can never break the rule again, whatever code or tool writes to it.

How to answer: Cover the code fix, the data repair with its decision rule and verification, and the database constraint as the lasting guard.

See the example · Permalink

S Which personal details should a list endpoint return, and how do you decide? design

Only what the screens using it need: a staff list needs names, codes, and work emails, not personal phones or dates of birth. Put more sensitive details on single-resource endpoints that fewer roles may call, and check every DTO against who will see it.

How to answer: Mention data minimisation, roles and authorization (Module 12), logging and caching risks of personal data, and reviewing DTOs as part of design.

See the example · Permalink

Architect / Lead (10+ yrs)

A How would you model people who play several roles, such as a teacher who is also a parent at the school? design

Separate the person from their roles: one person record with shared identity and contact details, and role records such as Teacher and Guardian that reference it, each with role-specific data and permissions. Logins attach to the person, and authorization checks roles, so one sign-in can see both the staff and the parent views.

How to answer: Compare per-role tables (as in the SMS today) with a person-plus-roles model, discuss duplication, identity, and authorization, and how you would migrate.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-77-teachers-guardians (code repository coming soon)
Files: database/077-primary-contact.sql, src/SchoolApp.Data/EfCore/Configurations/PeopleConfigurations.cs, src/SchoolApp.Core/Services/GuardianService.cs, src/SchoolApp.Api/Controllers/GuardiansController.cs, library-assignment/077-library-borrowers.cs
Video: coming soon

What's next

Students, staff, and families are in place. Next: enrolling a student in a class for the year, with roll numbers, section capacity, and two clerks enrolling at once. Lesson 78: Enrollments

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

Introduction​

Why this matters​

The concept​

Example: a junction table with a payload​

Example: why siblings share one guardian row​

Example: modelling it in EF Core​

Example: "at most one" in the database​

Example: what a list should show​

Apply it to the SMS​

Step 1: the index​

Step 2: EF Core configurations​

Step 3: the service rule​

Step 4: the repository and controllers​

Step 5: read both sides​

Example: a new primary contact​

Example: the index refuses what the code would allow​

Example: validation for people​

Example: tests for the rule​

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: a junction table with a payload

Example: why siblings share one guardian row

Example: modelling it in EF Core

Example: "at most one" in the database

Example: what a list should show

Apply it to the SMS

Step 1: the index

Step 2: EF Core configurations

Step 3: the service rule

Step 4: the repository and controllers

Step 5: read both sides

Example: a new primary contact

Example: the index refuses what the code would allow

Example: validation for people

Example: tests for the rule

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