Student Management API: Search, Update, and Remove – Web API | Lesson 75

Published / updated7 Oct 2026

Introduction

Module 11 builds the School Management System's real features as API endpoints. It starts with the students themselves: the office must be able to find a student quickly, correct their details, and remove them when they leave.

CRUD stands for create, read, update, and delete: the four basic things you do with any record. SchoolApp.Api can already create and read students. This lesson adds search, update, and delete, and fixes a bug that only appeared once delete existed.

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

Add search and filter options to a list endpoint.
Update a resource with PUT, and explain how PUT differs from PATCH.
Remove a resource with a soft delete behind DELETE.
Fill audit columns automatically with an EF Core interceptor.
Let the database's unique constraint catch duplicates that code cannot see.

Why this matters

The office uses these endpoints dozens of times a day: a parent calls, the clerk searches for the child, corrects an address, or marks a transfer. If search is slow or an update loses data, every one of those calls goes badly.

Delete is where data mistakes become permanent. A soft delete keeps the record, so fee history, attendance, and marks still make sense after a student leaves.

The concept

Example: the full set of student endpoints

Method and route	Does	Success	Failures
`GET /api/students?search=&classId=&page=&pageSize=`	List, filtered and paged	200	400
`GET /api/students/{admissionNumber}`	Read one	200	404
`POST /api/students`	Admit (Lesson 73)	201	400, 409
`PUT /api/students/{admissionNumber}`	Update names, address, status	200	400, 404
`DELETE /api/students/{admissionNumber}`	Remove (soft delete)	204	404

Example: PUT and PATCH

Think of a student's paper file. PUT replaces the whole front page with a new one: the client sends every field, and missing ones count as cleared. PATCH crosses out and rewrites only some lines.

	`PUT`	`PATCH`
Body	Every field that may change	Only the fields to change
Idempotent	Yes: sending it twice gives the same result	Depends on the change
Simple to build and validate	Yes	Needs a format for "change these fields"

SchoolApp uses PUT with UpdateStudentRequest: four fields, all required except the address. For a screen that edits one field, the client sends the other three unchanged.

A successful PUT may answer 204 No Content or 200 OK with the resource. SchoolApp returns 200 with the updated student, so the screen can show the saved values without a second GET.

Example: DELETE as a soft delete

A student who leaves still has invoices, payments, attendance, and marks. Removing the row would break every one of those links, or the database would refuse it because of the foreign keys.

So DELETE marks the student as deleted (Lesson 14) and answers 204. The query filter from Lesson 60 then hides them everywhere. Like the paper file moved to the archive cabinet: gone from the office, still there if the auditors ask.

Example: who checks duplicates

StudentService checks that an admission number is free before admitting. But that check uses the repository, which, like every query, cannot see soft-deleted students. Two clerks admitting the same number at the same moment would also both pass it.

Only the database sees every row at the moment of the insert. Its UNIQUE constraint is the final guard (Lesson 10); the code's job is to turn its error into a clear answer.

Apply it to the SMS

Step 1: UpdateAsync on the repository

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

namespace SchoolApp.Core.Interfaces;

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

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

    // Lesson 75: saves the changes made to a student loaded from this repository: names, address,
    // status, and soft delete. The admission number, date of birth, and gender never change here.
    Task UpdateAsync(Student student, CancellationToken cancellationToken = default);
}

All five implementations gained UpdateAsync: ADO.NET and Dapper with an UPDATE statement, EF Core with SaveChangesAsync, and the in-memory and test fakes. The ADO.NET version:

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

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

namespace SchoolApp.Data.AdoNet;

// Lesson 57: plain ADO.NET. Every step is written by hand: open a connection, build a command,
// add parameters, run it, and copy each column of each row into a C# object.
// Lesson 62: implements IStudentRepository, so StudentService can use it.
public class AdoNetStudentRepository : IStudentRepository
{
    private const string SelectStudent = """
        SELECT Id, AdmissionNumber, FirstName, LastName, DateOfBirth, Gender,
               AdmissionDate, Address, Status, CreatedAt, CreatedBy, UpdatedAt, UpdatedBy
        FROM dbo.Students
        """;

    private readonly SqlConnectionFactory _connections;

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

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

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

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

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

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

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

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

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

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

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

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

        // Lesson 75: the UNIQUE constraint is the final check for duplicates.
        try
        {
            await using SqlDataReader reader = await command.ExecuteReaderAsync(cancellationToken);
            await reader.ReadAsync(cancellationToken);
            student.Id = reader.GetInt32(0);
            student.CreatedAt = reader.GetDateTime(1);
        }
        catch (SqlException ex) when (SqlErrors.IsUniqueKeyViolation(ex))
        {
            throw SqlErrors.DuplicateAdmissionNumber(student.AdmissionNumber, ex);
        }
    }

    // Lesson 75: one UPDATE for the columns a student may change, including the soft-delete columns.
    // UpdatedAt comes from SQL Server's clock, like CreatedAt.
    public async Task UpdateAsync(Student student, CancellationToken cancellationToken = default)
    {
        await using SqlConnection connection = _connections.Create();
        await connection.OpenAsync(cancellationToken);

        await using SqlCommand command = new SqlCommand("""
            UPDATE dbo.Students
            SET FirstName = @FirstName, LastName = @LastName, Address = @Address, Status = @Status,
                IsDeleted = @IsDeleted, DeletedAt = @DeletedAt,
                UpdatedAt = SYSUTCDATETIME(), UpdatedBy = @UpdatedBy
            WHERE Id = @Id;
            """, connection);
        command.Parameters.Add("@Id", SqlDbType.Int).Value = student.Id;
        command.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = student.FirstName;
        command.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = student.LastName;
        command.Parameters.Add("@Address", SqlDbType.NVarChar, 200).Value = (object?)student.Address ?? DBNull.Value;
        command.Parameters.Add("@Status", SqlDbType.VarChar, 12).Value = student.Status.ToString();
        command.Parameters.Add("@IsDeleted", SqlDbType.Bit).Value = student.IsDeleted;
        command.Parameters.Add("@DeletedAt", SqlDbType.DateTime2).Value = (object?)student.DeletedAt ?? DBNull.Value;
        command.Parameters.Add("@UpdatedBy", SqlDbType.Int).Value = (object?)student.UpdatedBy ?? DBNull.Value;

        await command.ExecuteNonQueryAsync(cancellationToken);
    }

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

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

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

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

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

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

        return student;
    }
}

The UPDATE lists only the columns a student may change. The admission number, date of birth, and gender are left out, so no bug elsewhere can change them through this method, even by accident.

Step 2: the service

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

namespace SchoolApp.Core.Services;

// Lesson 46: one job (admitting and finding students), and it depends on an interface,
// not on any particular database code. The repository is passed in through the constructor.
// Lesson 62: asynchronous, like the repository it uses.
// Lesson 74: a duplicate admission number is a school rule error, 50020, which the API turns into 409.
// Lesson 75: update and remove. Both return "not found" as null or false; the API turns that into 404.
public class StudentService
{
    private readonly IStudentRepository _students;

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

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

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

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

    public async Task<StudentDto?> UpdateAsync(string admissionNumber, UpdateStudentRequest request,
                                               CancellationToken cancellationToken = default)
    {
        Student? student = await _students.GetByAdmissionNumberAsync(admissionNumber, cancellationToken);
        if (student is null)
        {
            return null;
        }

        request.ApplyTo(student);   // Lesson 45; the Student setters still check the names (Lesson 38)
        await _students.UpdateAsync(student, cancellationToken);
        return student.ToDto();
    }

    // Soft delete (Lesson 14): the row stays in SchoolDb, marked deleted, and disappears from every query.
    public async Task<bool> RemoveAsync(string admissionNumber, CancellationToken cancellationToken = default)
    {
        Student? student = await _students.GetByAdmissionNumberAsync(admissionNumber, cancellationToken);
        if (student is null)
        {
            return false;
        }

        student.SoftDelete(DateTime.UtcNow);
        await _students.UpdateAsync(student, cancellationToken);
        return true;
    }
}

UpdateAsync and RemoveAsync return null or false when the student does not exist, and the controller turns that into 404. Not finding a student is an expected outcome, not an error, so it is not an exception either.

Step 3: UpdatedAt, filled automatically

With EF Core, nothing set UpdatedAt until now (Module 9 left this open). An interceptor is code EF Core calls at certain moments; this one runs just before every save:

src/SchoolApp.Data/EfCore/AuditColumnsInterceptor.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using SchoolApp.Core.Entities;

namespace SchoolApp.Data.EfCore;

// Lesson 75: an interceptor runs inside SaveChanges. This one sets UpdatedAt on every changed entity
// that has audit columns, so no repository or service has to remember to do it.
public class AuditColumnsInterceptor : SaveChangesInterceptor
{
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(DbContextEventData eventData,
        InterceptionResult<int> result, CancellationToken cancellationToken = default)
    {
        if (eventData.Context is not null)
        {
            DateTime now = DateTime.UtcNow;
            foreach (var entry in eventData.Context.ChangeTracker.Entries<AuditableEntity>())
            {
                if (entry.State == EntityState.Modified)
                {
                    entry.Entity.UpdatedAt = now;
                }
            }
        }
        return base.SavingChangesAsync(eventData, result, cancellationToken);
    }
}

It finds every changed entity that has audit columns, and sets UpdatedAt. AddSchoolData and SchoolDbContextFactory both register it, so no repository and no future developer can forget.

Step 4: search and filters

StudentFilter carries the optional search text and class. EfStudentRepository.GetPageAsync adds a Where only for the parts that are given:

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

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

namespace SchoolApp.Data.EfCore;

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

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

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

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

    // Lesson 62: Add only marks the student as Added; SaveChangesAsync writes it and sets Id.
    public async Task AddAsync(Student student, CancellationToken cancellationToken = default)
    {
        _db.Students.Add(student);
        try
        {
            await _db.SaveChangesAsync(cancellationToken);
        }
        catch (DbUpdateException ex) when (SqlErrors.IsUniqueKeyViolation(ex))   // Lesson 75
        {
            _db.Entry(student).State = EntityState.Detached;   // forget the failed insert
            throw SqlErrors.DuplicateAdmissionNumber(student.AdmissionNumber, ex);
        }
    }

    // Lesson 75: the student was loaded by this DbContext, so EF Core already knows what changed.
    // AuditColumnsInterceptor fills UpdatedAt while saving.
    public async Task UpdateAsync(Student student, CancellationToken cancellationToken = default)
    {
        await _db.SaveChangesAsync(cancellationToken);
    }

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

    // Lesson 61: one page of students for a list screen. The projection reads only the DTO's columns
    // (projections are never tracked), and Skip/Take become OFFSET/FETCH, so only one page is sent.
    // Lesson 75: with a filter. Each condition is added only when it is given, so the SQL stays simple.
    public async Task<PagedResult<StudentDto>> GetPageAsync(StudentFilter filter, int page, int pageSize,
                                                            CancellationToken cancellationToken = default)
    {
        if (page < 1)
        {
            throw new ArgumentOutOfRangeException(nameof(page), "Page numbers start at 1.");
        }
        if (pageSize < 1 || pageSize > Paging.MaxPageSize)
        {
            throw new ArgumentOutOfRangeException(nameof(pageSize), $"The page size must be between 1 and {Paging.MaxPageSize}.");
        }

        IQueryable<Student> students = _db.Students;
        if (!string.IsNullOrWhiteSpace(filter.Search))
        {
            string search = filter.Search.Trim();
            // StartsWith becomes LIKE 'text%', which can use the LastName, FirstName index (Lesson 26).
            students = students.Where(s => s.AdmissionNumber == search
                                           || s.LastName.StartsWith(search)
                                           || s.FirstName.StartsWith(search));
        }
        if (filter.ClassId is int classId)
        {
            students = students.Where(s => s.Enrollments.Any(e => e.AcademicYear!.IsCurrent && e.Section!.ClassId == classId));
        }

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

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

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

StartsWith becomes LIKE 'Singh%', which can use the LastName, FirstName index from Lesson 26. A "contains" search, LIKE '%Singh%', cannot, and would scan all 10,012 rows.

Example: the SQL behind the filters

With SQL logging on in Development (Lesson 70), GET /api/students?search=Singh&classId=10 showed the count query EF Core wrote:

Output (from the API log)
SELECT COUNT(*)
FROM [Students] AS [s]
WHERE [s].[IsDeleted] = CAST(0 AS bit) AND ([s].[AdmissionNumber] = @search OR [s].[LastName] LIKE @search_startswith ESCAPE N'' OR [s].[FirstName] LIKE @search_startswith ESCAPE N'') AND EXISTS (
    SELECT 1
    FROM [Enrollments] AS [e]
    INNER JOIN [AcademicYears] AS [a] ON [e].[AcademicYearId] = [a].[Id]
    INNER JOIN [Sections] AS [s0] ON [e].[SectionId] = [s0].[Id]
    WHERE [s].[Id] = [e].[StudentId] AND [a].[IsCurrent] = CAST(1 AS bit) AND [s0].[ClassId] = @classId)

Every value is a parameter, so the search box cannot inject SQL (Lesson 57). StartsWith became LIKE with a prepared @search_startswith value, and ESCAPE makes sure a % typed by a user is treated as a character, not a wildcard. The class filter became EXISTS, the subquery from Lesson 19.

Step 5: the controller

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

src/SchoolApp.Api/Controllers/StudentsController.cs
using System.ComponentModel.DataAnnotations;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
using SchoolApp.Api.Options;
using SchoolApp.Core.Common;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Interfaces;
using SchoolApp.Core.Services;

namespace SchoolApp.Api.Controllers;

// Lesson 68: the first SMS endpoint backed by SchoolDb. The controller only translates HTTP:
// the route value goes into StudentService, and the result comes back as 200 or 404.
// Lesson 71: /// comments and Produces... attributes describe the endpoint in the OpenAPI document.
// Lesson 72: a paged list at GET /api/students, with page and pageSize from the query string.
// Lesson 73: POST /api/students admits a student.
// Lesson 75: search and class filters, PUT to update, and DELETE to remove (soft delete).
[ApiController]
[Route("api/students")]
[Produces("application/json")]
public class StudentsController : ControllerBase
{
    private readonly StudentService _students;
    private readonly IStudentQueries _queries;
    private readonly SchoolOptions _school;

    // ASP.NET Core passes in these services for each request (dependency injection, Lesson 69).
    public StudentsController(StudentService students, IStudentQueries queries, IOptions<SchoolOptions> school)
    {
        _students = students;
        _queries = queries;
        _school = school.Value;
    }

    // GET /api/students?search=Singh&classId=10&page=2&pageSize=20
    /// <summary>Gets one page of students, sorted by last name, first name, and Id.</summary>
    /// <param name="search">An admission number, or the start of a first or last name.</param>
    /// <param name="classId">Only students enrolled in this class (1 to 10) in the current academic year.</param>
    /// <param name="page">The page number, starting at 1.</param>
    /// <param name="pageSize">Students per page, 1 to 100. Leave it out for the school's default.</param>
    /// <param name="cancellationToken">Cancelled when the client gives up.</param>
    /// <response code="200">The page, with the total count for page numbers.</response>
    /// <response code="400">The page or page size is out of range.</response>
    [HttpGet]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status400BadRequest)]
    public async Task<ActionResult<PagedResult<StudentDto>>> GetPage(
        [FromQuery, StringLength(50)] string? search = null,
        [FromQuery, Range(1, 10)] int? classId = null,
        [FromQuery, Range(1, int.MaxValue)] int page = 1,
        [FromQuery, Range(1, Paging.MaxPageSize)] int? pageSize = null,
        CancellationToken cancellationToken = default)
    {
        StudentFilter filter = new StudentFilter(search, classId);
        return await _queries.GetPageAsync(filter, page, pageSize ?? _school.DefaultPageSize, cancellationToken);
    }

    // GET /api/students/ADM-2017-0003
    // ActionResult<StudentDto>: either a StudentDto (200 OK) or another result, such as 404.
    /// <summary>Gets one student by admission number.</summary>
    /// <param name="admissionNumber">The student's admission number, such as ADM-2017-0003.</param>
    /// <param name="cancellationToken">Cancelled when the client gives up.</param>
    /// <response code="200">The student.</response>
    /// <response code="404">No student has this admission number.</response>
    [HttpGet("{admissionNumber}")]
    [ProducesResponseType(StatusCodes.Status200OK)]        // the body type comes from ActionResult<StudentDto>
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<ActionResult<StudentDto>> GetByAdmissionNumber(string admissionNumber, CancellationToken cancellationToken)
    {
        StudentDto? student = await _students.FindAsync(admissionNumber, cancellationToken);
        if (student is null)
        {
            return NotFound();
        }
        return student;   // converted to Ok(student)
    }

    // POST /api/students, with a CreateStudentRequest as JSON in the body.
    // Lesson 73: [ApiController] reads the body (model binding) and checks its rules (validation)
    // before this method runs. An invalid request gets 400 and never reaches it.
    /// <summary>Admits a new student.</summary>
    /// <param name="request">The new student's details.</param>
    /// <param name="cancellationToken">Cancelled when the client gives up.</param>
    /// <response code="201">The student was admitted; Location gives the new student's address.</response>
    /// <response code="400">The request is invalid; errors lists each problem.</response>
    [HttpPost]
    [ProducesResponseType(StatusCodes.Status201Created)]
    [ProducesResponseType(StatusCodes.Status400BadRequest)]
    public async Task<ActionResult<StudentDto>> Admit(CreateStudentRequest request, CancellationToken cancellationToken)
    {
        StudentDto admitted = await _students.AdmitAsync(request, cancellationToken);

        // 201 Created, a Location header pointing at GET /api/students/{admissionNumber}, and the student.
        return CreatedAtAction(nameof(GetByAdmissionNumber), new { admissionNumber = admitted.AdmissionNumber }, admitted);
    }

    // PUT /api/students/ADM-2017-0003: replaces the details a client may change.
    /// <summary>Updates a student's names, address, and status.</summary>
    /// <param name="admissionNumber">The student to update.</param>
    /// <param name="request">The new values; every field must be sent, as PUT replaces them all.</param>
    /// <param name="cancellationToken">Cancelled when the client gives up.</param>
    /// <response code="200">The student after the update.</response>
    /// <response code="400">The request is invalid.</response>
    /// <response code="404">No student has this admission number.</response>
    [HttpPut("{admissionNumber}")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status400BadRequest)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<ActionResult<StudentDto>> Update(string admissionNumber, UpdateStudentRequest request,
                                                      CancellationToken cancellationToken)
    {
        StudentDto? updated = await _students.UpdateAsync(admissionNumber, request, cancellationToken);
        return updated is null ? NotFound() : updated;
    }

    // DELETE /api/students/ADM-2017-0003: a soft delete; the row stays in SchoolDb.
    /// <summary>Removes a student. The record is kept, marked deleted, and no longer returned.</summary>
    /// <param name="admissionNumber">The student to remove.</param>
    /// <param name="cancellationToken">Cancelled when the client gives up.</param>
    /// <response code="204">The student was removed.</response>
    /// <response code="404">No student has this admission number, or it was already removed.</response>
    [HttpDelete("{admissionNumber}")]
    [ProducesResponseType(StatusCodes.Status204NoContent)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<IActionResult> Remove(string admissionNumber, CancellationToken cancellationToken)
    {
        bool removed = await _students.RemoveAsync(admissionNumber, cancellationToken);
        return removed ? NoContent() : NotFound();
    }
}

Step 6: send the requests

Search for Singhs in Class 10, then for one admission number, then for a first name:

Output
GET /api/students?search=Singh&classId=10&pageSize=5
{"page":1,"pageSize":5,"totalCount":68,"totalPages":14,"hasNextPage":true} first: ADM-2026-09002 Bhavya Singh
GET /api/students?search=ADM-2017-0003      1 match: Kabir Singh
GET /api/students?search=Kab                1 match: Kabir Singh
GET /api/students?classId=11                400: classId must be between 1 and 10

Then admit Ishaan (Lesson 73), update him, and remove him:

Output
PUT /api/students/ADM-2026-10001   {"firstName":"Ishaan","lastName":"Kumar","address":"4 Garden Lane","status":"Active"}
HTTP/1.1 200 OK
{"id":10063,"admissionNumber":"ADM-2026-10001","fullName":"Ishaan Kumar","dateOfBirth":"2016-04-12","gender":"Male","status":"Active"}

DELETE /api/students/ADM-2026-10001
HTTP/1.1 204 No Content

DELETE /api/students/ADM-2026-10001
HTTP/1.1 404 Not Found

GET /api/students/ADM-2026-10001
HTTP/1.1 404 Not Found

A PUT with an empty first name never reaches the service: [ApiController] answers 400 with "FirstName":["The FirstName field is required."], from the attributes added to UpdateStudentRequest.

In SSMS, Ishaan's row is still there. A query after the PUT, and another after the DELETE, showed:

SSMS
SELECT Address, CreatedAt, UpdatedAt, IsDeleted, DeletedAt
FROM dbo.Students
WHERE AdmissionNumber = 'ADM-2026-10001';

Moment	Address	CreatedAt	UpdatedAt	IsDeleted	DeletedAt
After `PUT`	4 Garden Lane	2026-10-07 15:32:12	2026-10-07 15:32:12	0	NULL
After `DELETE`	4 Garden Lane	2026-10-07 15:32:12	(set again)	1	2026-10-07 15:32:13

UpdatedAt was filled by the interceptor, not by any line in the service; the PUT ran within the same second as the admission. The record, its history, and its admission number all stay. The second DELETE found nobody to remove: the result is the same, so DELETE is idempotent, as Lesson 65 promised.

Example: the duplicate only the database saw

Now admit ADM-2026-10001 again. The first time this was tried, before the fix, the answer was a 500. The service's check could not see the soft-deleted Ishaan, so it allowed the insert, and the UNIQUE constraint refused it with SQL Server error 2627, which nothing translated.

The fix sits in the data layer. A small helper recognises unique-key errors:

src/SchoolApp.Data/SqlErrors.cs
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Exceptions;

namespace SchoolApp.Data;

// Lesson 75: SQL Server error numbers that the data layer turns into school rule errors.
// 2627: a UNIQUE or PRIMARY KEY constraint was broken. 2601: a unique index was broken.
public static class SqlErrors
{
    public static bool IsUniqueKeyViolation(Exception exception)
    {
        for (Exception? current = exception; current is not null; current = current.InnerException)
        {
            if (current is SqlException sql && (sql.Number == 2627 || sql.Number == 2601))
            {
                return true;
            }
        }
        return false;
    }

    // The same error as StudentService's own check, for duplicates only the database can see:
    // a soft-deleted student who still holds the number, or two clerks admitting at the same moment.
    public static SchoolRuleException DuplicateAdmissionNumber(string admissionNumber, Exception inner)
    {
        return new SchoolRuleException(50020, $"Admission number {admissionNumber} is already in use.", inner);
    }
}

Each repository's AddAsync catches that error and throws the same SchoolRuleException 50020 as the service. Now the answer is:

Output
HTTP/1.1 409 Conflict
{"type":"https://tools.ietf.org/html/rfc9110#section-15.5.10","title":"Conflict","status":409,"detail":"Admission number ADM-2026-10001 is already in use.","errorNumber":50020,"traceId":"00-0babd426e65c32c78208f79735589cc6-85e5fff063c73f94-00"}

The same fix covers two clerks admitting the same number at once. The integration test from Lesson 63 now expects SchoolRuleException 50020, with error 2627 still inside it, for all three data tools.

Example: tests

Two unit tests check update and remove with the fake repository. Two integration tests run for ADO.NET, Dapper, and EF Core: a changed address is saved, and a soft-deleted student is no longer found. The suite now has 40 tests, all passing.

Common mistakes

Hard-deleting records that others refer to. History breaks, or foreign keys refuse. Fix: soft delete, with a query filter.
PUT that changes fields the client did not send. Missing fields become empty. Fix: require every changeable field, or use PATCH deliberately.
Trusting only a code check for uniqueness. Soft-deleted rows and races slip past. Fix: a database constraint, translated into a clear error.
Setting UpdatedAt by hand in some places. Others forget. Fix: one interceptor.
LIKE '%text%' searches on big tables. They scan every row. Fix: StartsWith, or full-text search when "contains" is really needed.

In real projects

From 16 years of .NET projects

A school's admin screen had a real delete button for students. A clerk removed a student who had left mid-year, and the next month's fee report crashed: invoices pointed to a student that no longer existed.

The database had foreign keys on most tables, but not on the old fee table, so the delete went through. Restoring the student from a backup took an evening.

We replaced every delete of people with a soft delete, and added foreign keys everywhere. Data that has history should be archived, never destroyed.

Best practices

Give list endpoints search, filters, and paging together.
Use PUT with a full request for updates; return the updated resource.
Implement DELETE as a soft delete for records with history, returning 204.
Fill audit columns in one place, such as an interceptor.
Keep unique constraints in the database, and translate their errors.

Recap

CRUD for students: search and filter, update with PUT, soft delete with DELETE.
PUT replaces every changeable field and is idempotent; PATCH changes some.
A soft delete keeps history and hides the row through the query filter.
AuditColumnsInterceptor fills UpdatedAt on every EF Core save.
The UNIQUE constraint caught a duplicate the service could not see; it now answers 409.

Homework

Library module task: Write a single-file library API with books in memory.

Add a title search.
Add a PUT that updates a book's title and category.
Add a DELETE that marks a book as deleted, so it disappears from the list.
Update Malgudi Days, remove The Jungle Book, and list the books.

A correct result looks like:

Output
GET /api/books?search=jungle
[{"isbn":"9780141325293","title":"The Jungle Book","category":"Fiction"}]
PUT /api/books/9780143039655
{"isbn":"9780143039655","title":"Malgudi Days (Illustrated)","category":"Fiction"}
DELETE /api/books/9780141325293
HTTP/1.1 204 No Content
GET /api/books
[{"isbn":"9780143039655","title":"Malgudi Days (Illustrated)","category":"Fiction"},{"isbn":"9788173711466","title":"Wings of Fire","category":"Biography"}]

Show the solution

library-assignment/075-library-books-crud.cs
// Library homework, Lesson 75: list with search, update with PUT, and remove with a soft delete,
// for books kept in memory.
// Run with:  dotnet run library-assignment/075-library-books-crud.cs
#:sdk Microsoft.NET.Sdk.Web
#:property PublishAot=false
using System.ComponentModel.DataAnnotations;
using Microsoft.AspNetCore.Mvc;

WebApplicationBuilder builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();
builder.Services.AddProblemDetails();
builder.Services.AddSingleton<BookShelf>();
WebApplication app = builder.Build();
app.UseStatusCodePages();
app.MapControllers();
app.Run("http://localhost:5075");

public class Book
{
    public required string Isbn { get; init; }
    public required string Title { get; set; }
    public required string Category { get; set; }
    public bool IsDeleted { get; set; }
}

public record BookDto(string Isbn, string Title, string Category);
public record UpdateBookRequest([Required, StringLength(200)] string Title, [Required, StringLength(50)] string Category);

public class BookShelf
{
    private readonly List<Book> _books =
    [
        new Book { Isbn = "9788173711466", Title = "Wings of Fire", Category = "Biography" },
        new Book { Isbn = "9780141325293", Title = "The Jungle Book", Category = "Fiction" },
        new Book { Isbn = "9780143039655", Title = "Malgudi Days", Category = "Fiction" },
    ];

    // Removed books are hidden everywhere, like the query filter in SchoolApp (Lesson 60).
    public IEnumerable<Book> Visible => _books.Where(b => !b.IsDeleted);

    public Book? Find(string isbn) => Visible.FirstOrDefault(b => b.Isbn == isbn);
}

[ApiController]
[Route("api/books")]
public class BooksController : ControllerBase
{
    private readonly BookShelf _shelf;

    public BooksController(BookShelf shelf)
    {
        _shelf = shelf;
    }

    // GET /api/books?search=jungle: matches any part of the title, ignoring case.
    [HttpGet]
    public IEnumerable<BookDto> List([FromQuery] string? search)
    {
        IEnumerable<Book> books = _shelf.Visible;
        if (!string.IsNullOrWhiteSpace(search))
        {
            books = books.Where(b => b.Title.Contains(search.Trim(), StringComparison.OrdinalIgnoreCase));
        }
        return books.OrderBy(b => b.Title).Select(b => new BookDto(b.Isbn, b.Title, b.Category));
    }

    [HttpPut("{isbn}")]
    public ActionResult<BookDto> Update(string isbn, UpdateBookRequest request)
    {
        Book? book = _shelf.Find(isbn);
        if (book is null)
        {
            return NotFound();
        }
        book.Title = request.Title;
        book.Category = request.Category;
        return new BookDto(book.Isbn, book.Title, book.Category);
    }

    [HttpDelete("{isbn}")]
    public IActionResult Remove(string isbn)
    {
        Book? book = _shelf.Find(isbn);
        if (book is null)
        {
            return NotFound();
        }
        book.IsDeleted = true;   // kept, but hidden: issue history still points to it
        return NoContent();
    }
}

library-assignment/075-update-book.json
{
  "title": "Malgudi Days (Illustrated)",
  "category": "Fiction"
}

The removed book is kept with IsDeleted = true, because issue history may still point to it. A second DELETE answers 404, through UseStatusCodePages as a ProblemDetails.

Quiz

What does DELETE /api/students/{admissionNumber} do in SchoolApp?
1. Removes the row
2. Marks the student as deleted and hides them: a soft delete
3. Moves the row to another table
4. Nothing
Show answer
Answer: B. Marks the student as deleted and hides them: a soft delete The row stays, with IsDeleted = 1, so history still makes sense.
How is PUT different from PATCH?
1. PUT is faster
2. PUT sends every changeable field; PATCH sends only the ones to change
3. PATCH is only for deletes
4. There is no difference
Show answer
Answer: B. PUT sends every changeable field; PATCH sends only the ones to change PUT replaces the whole set of fields and is idempotent.
Why did re-admitting a removed student's number give 500 before the fix?
1. The JSON was wrong
2. The service could not see the soft-deleted row, so the UNIQUE constraint refused the insert
3. The route was wrong
4. DELETE failed
Show answer
Answer: B. The service could not see the soft-deleted row, so the UNIQUE constraint refused the insert The database is the only place that sees every row.
What fills UpdatedAt for EF Core saves now?
1. The controller
2. AuditColumnsInterceptor, before every save
3. A database trigger
4. The client
Show answer
Answer: B. AuditColumnsInterceptor, before every save One place, so nobody can forget.
Why search with StartsWith rather than Contains on student names?
1. Contains is not allowed
2. LIKE 'text%' can use an index; LIKE '%text%' scans every row
3. StartsWith ignores case
4. Contains returns too few
Show answer
Answer: B. LIKE 'text%' can use an index; LIKE '%text%' scans every row The LastName, FirstName index from Lesson 26 helps only with StartsWith.

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 does CRUD mean, and which HTTP methods does each part use? concept

Create, read, update, and delete: the basic operations on a record. In a REST API, create is POST, read is GET, update is PUT or PATCH, and delete is DELETE, each on a resource address such as /api/students/{admissionNumber}.

See the example · Permalink

F What is a soft delete, and why use it for students? concept

Marking a record as deleted, such as IsDeleted = 1 with a timestamp, instead of removing the row. Students have invoices, payments, attendance, and marks; removing the row would break that history or be refused by foreign keys. A query filter hides soft-deleted rows everywhere.

See the example · Permalink

F What status codes should PUT and DELETE return? concept

PUT: 200 with the updated resource, or 204 with no body; 400 for invalid input; 404 when the resource does not exist. DELETE: 204 when removed; 404 when there is nothing to remove, including a second delete of the same resource.

See the example · Permalink

Mid (3-5 yrs)

M How would you implement optional search and filter parameters on a list endpoint with EF Core? coding

Start from the IQueryable, add a Where only for each parameter that was given, then count, sort, and page. EF Core turns it into one parameterised SQL query; StartsWith becomes LIKE 'text%', which can use an index, and a related-table filter becomes EXISTS.

See the example · Permalink

M What is an EF Core SaveChanges interceptor, and what would you use one for? coding

A class EF Core calls during SaveChanges, such as before the SQL is sent. It can inspect and change tracked entities, for example setting UpdatedAt on every modified entity with audit columns, so no repository or service has to remember to do it.

See the example · Permalink

M Why is a uniqueness check in code not enough, even when it runs before every insert? concept

The check cannot see rows hidden by filters, such as soft-deleted records, and two requests can both pass it at the same moment before either inserts. Only the database sees every row at insert time, so keep a UNIQUE constraint and translate its error (2627 or 2601) into a clear 409.

See the example · Permalink

Senior (6-9 yrs)

S A school asks to permanently delete a student's data on request. How does that fit with soft delete? scenario

Soft delete covers normal removals. A permanent erasure request is a separate, audited process: check legal retention rules for fee and academic records, anonymise or remove personal fields while keeping financial and statistical records that must stay, record who approved it, and run it in a transaction.

How to answer: Distinguish routine removal from data-protection erasure, mention retention obligations, anonymisation, auditing, and backups that also hold the data.

See the example · Permalink

S When would you add PATCH to an API that already has PUT? design

When clients often change one or two fields of a large resource, when several clients edit different fields of the same record, or when sending every field risks overwriting newer data. Use a clear format, such as JSON Merge Patch or JSON Patch, validate the result after applying it, and combine it with a concurrency check.

How to answer: Weigh payload size and lost-update risk against complexity, name the patch formats, and explain validating the patched result rather than the patch itself.

See the example · Permalink

Architect / Lead (10+ yrs)

A How would you design search for a system with millions of students across many schools? design

Prefix searches on indexed name and number columns for the common cases, always scoped by school and paged with a stable sort. For "contains", spelling mistakes, or several fields at once, use full-text search or a dedicated search engine fed by changes from the database, and keep the API contract the same whichever engine answers.

How to answer: Start from the query patterns, show the index each needs, explain where LIKE stops scaling, and how a search engine is kept in sync.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-75-students-crud (code repository coming soon)
Files: src/SchoolApp.Core/Services/StudentService.cs, src/SchoolApp.Data/EfCore/AuditColumnsInterceptor.cs, src/SchoolApp.Data/SqlErrors.cs, src/SchoolApp.Api/Controllers/StudentsController.cs, library-assignment/075-library-books-crud.cs
Video: coming soon

What's next

Students can now be found, admitted, updated, and removed, end to end. Next: the master data every other feature depends on, academic years, classes, sections, and subjects. Lesson 76: Master data

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

Introduction​

Why this matters​

The concept​

Example: the full set of student endpoints​

Example: PUT and PATCH​

Example: DELETE as a soft delete​

Example: who checks duplicates​

Apply it to the SMS​

Step 1: UpdateAsync on the repository​

Step 2: the service​

Step 3: UpdatedAt, filled automatically​

Step 4: search and filters​

Example: the SQL behind the filters​

Step 5: the controller​

Step 6: send the requests​

Example: the duplicate only the database saw​

Example: tests​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: the full set of student endpoints

Example: PUT and PATCH

Example: DELETE as a soft delete

Example: who checks duplicates

Apply it to the SMS

Step 1: UpdateAsync on the repository

Step 2: the service

Step 3: UpdatedAt, filled automatically

Step 4: search and filters

Example: the SQL behind the filters

Step 5: the controller

Step 6: send the requests

Example: the duplicate only the database saw

Example: tests

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