Published / updated29 Aug 2026

EF Core Student Records API

Before you start

You need: all of Articles 01–07, and the Dapper repository from Track 07 to compare against.

In Visual Studio: the schema comes from migrations only — you must be able to drop the database, run Update-Database, and have the API work.

Time: 6–10 hours.

Goal

Demonstrate that you can build a working EF Core data layer, verify the SQL it generates, and make a reasoned choice between EF Core and Dapper for a given operation.

Assignment

Build a student records API for NexCoding Academy. Deliver a solution plus one document.

Deliverable	Contents
Domain/	Entities and enums, no EF Core attributes
Data/	DbContext, IEntityTypeConfiguration<T> classes, migrations
Api/	Controllers, request and response DTOs
Tests/	Integration tests against a real database
COMPARISON.md	EF Core versus Dapper, with measurements

Required endpoints

Method	Route	Behaviour
GET	/api/students	Paged, filtered by term and class
GET	/api/students/{publicId}	One student with fee summary
POST	/api/students	Create, returning 201 and the PublicId
PUT	/api/students/{publicId}	Update — must not destroy unsent fields
DELETE	/api/students/{publicId}	Soft delete
GET	/api/exams/{examId}/results	Results with student and subject
POST	/api/exams/{examId}/results	Bulk save — insert, update and remove in one call
GET	/api/reports/outstanding-fees	Fee report

Non-negotiable requirements

• SchoolId comes from the authentication claim, never from the request body or route
• Every query filters by SchoolId
• PublicId in routes; the integer Id never leaves the data layer
• Every read-only query is untracked
• No lazy loading
• Every decimal has explicit precision
• RollNumber unique per school
• Absent results store null marks, never 0
• A concurrency token on Student
• Migrations reviewed and committed; no Database.Migrate() on startup

Worked example: the update endpoint

This is the endpoint the assignment is really testing.

[HttpPut("{publicId:guid}")]
public async Task<IActionResult> Update(
    Guid publicId, StudentUpdateRequest request, CancellationToken ct)
{
    int schoolId = User.GetSchoolId();

    Student? student = await _context.Students
        .FirstOrDefaultAsync(s => s.SchoolId == schoolId && s.PublicId == publicId, ct);

    if (student == null)
    {
        return NotFound();
    }

    bool duplicate = await _context.Students.AnyAsync(
        s => s.SchoolId == schoolId
          && s.RollNumber == request.RollNumber
          && s.PublicId != publicId, ct);

    if (duplicate)
    {
        ModelState.AddModelError(nameof(request.RollNumber),
            "This roll number is already used by another student.");
        return ValidationProblem(ModelState);
    }

    student.Name = request.Name;
    student.RollNumber = request.RollNumber;
    student.ClassName = request.ClassName;
    student.Section = request.Section;
    student.ParentName = request.ParentName;
    student.ParentPhone = request.ParentPhone;
    student.Address = request.Address;

    try
    {
        await _context.SaveChangesAsync(ct);
    }
    catch (DbUpdateConcurrencyException)
    {
        return Conflict(new { message = "This student was changed by another user. Reload and try again." });
    }
    catch (DbUpdateException ex) when (ex.InnerException is SqlException sql
                                       && (sql.Number == 2627 || sql.Number == 2601))
    {
        ModelState.AddModelError(nameof(request.RollNumber),
            "This roll number is already used by another student.");
        return ValidationProblem(ModelState);
    }

    return NoContent();
}

Six things a reviewer will check:

Detail	What breaks without it
Load then modify	Update on a detached entity wipes DateOfBirth, SchoolId and every unsent field
SchoolId from the claim	A leaked PublicId reaches another school's record
s.PublicId != publicId in the duplicate check	Saving an unchanged roll number reports a conflict with itself
Both the check and the catch	The check gives a clean message; the constraint wins the race between two users
DbUpdateConcurrencyException handled	One user's edit is silently overwritten by another's
Inner SqlException.Number	Matching on message text breaks under localisation

Worked example: the read endpoints

[HttpGet]
public async Task<ActionResult<PagedResult<StudentListDto>>> Search(
    [FromQuery] string? term, [FromQuery] string? className,
    [FromQuery] int page = 1, [FromQuery] int pageSize = 20,
    CancellationToken ct = default)
{
    int schoolId = User.GetSchoolId();

    IQueryable<Student> query = _context.Students
        .AsNoTracking()
        .Where(s => s.SchoolId == schoolId && s.Status != StudentStatus.Inactive);

    if (!string.IsNullOrWhiteSpace(term))
    {
        query = query.Where(s => s.Name.Contains(term) || s.RollNumber.Contains(term));
    }

    if (!string.IsNullOrWhiteSpace(className))
    {
        query = query.Where(s => s.ClassName == className);
    }

    int totalCount = await query.CountAsync(ct);

    List<StudentListDto> items = await query
        .OrderBy(s => s.ClassName).ThenBy(s => s.Section).ThenBy(s => s.Name).ThenBy(s => s.Id)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .Select(s => new StudentListDto
        {
            PublicId = s.PublicId,
            RollNumber = s.RollNumber,
            Name = s.Name,
            ClassSection = s.ClassName + " - " + s.Section,
            ParentPhone = s.ParentPhone
        })
        .ToListAsync(ct);

    return Ok(new PagedResult<StudentListDto>
    {
        Items = items, TotalCount = totalCount, Page = page, PageSize = pageSize
    });
}

The trailing ThenBy(s => s.Id) is what stops a student appearing on two pages while another is never shown.

[HttpGet("{publicId:guid}")]
public async Task<ActionResult<StudentDetailDto>> GetDetail(Guid publicId, CancellationToken ct)
{
    int schoolId = User.GetSchoolId();

    StudentDetailDto? student = await _context.Students
        .AsNoTracking()
        .Where(s => s.SchoolId == schoolId && s.PublicId == publicId)
        .Select(s => new StudentDetailDto
        {
            PublicId = s.PublicId,
            Name = s.Name,
            RollNumber = s.RollNumber,
            ClassName = s.ClassName,
            Section = s.Section,
            DateOfBirth = s.DateOfBirth,
            ParentName = s.ParentName,
            ParentPhone = s.ParentPhone,

            TotalFees = s.FeeAccounts
                .Where(fa => fa.AcademicYear == CurrentAcademicYear)
                .Sum(fa => fa.TotalFees),

            TotalPaid = s.FeeAccounts
                .Where(fa => fa.AcademicYear == CurrentAcademicYear)
                .SelectMany(fa => fa.Payments)
                .Where(p => !p.IsCancelled)
                .Sum(p => p.Amount),

            RecentResults = s.ExamResults
                .OrderByDescending(r => r.Exam.ExamDate)
                .Take(5)
                .Select(r => new ResultSummaryDto
                {
                    ExamName = r.Exam.ExamName,
                    SubjectName = r.Exam.Subject.Name,
                    MarksObtained = r.MarksObtained,
                    MaxMarks = r.Exam.MaxMarks,
                    IsAbsent = r.IsAbsent
                })
                .ToList()
        })
        .FirstOrDefaultAsync(ct);

    if (student == null)
    {
        return NotFound();
    }

    return Ok(student);
}

Projection rather than Include: the sums are computed in SQL, no entity is materialised, and the response carries exactly the fields the screen shows. The Include version would load every payment and result into memory to compute the same two numbers.

Worked example: COMPARISON.md

The point of this document is a reasoned choice, backed by measurement — not a preference.

Operation: student list, 400 rows, paged 20 at a time

EF Core (projection, AsNoTracking)
  Lines of code:        18
  Statements issued:    2  (count + page)
  Columns fetched:      5
  Generated SQL:        [paste it]
  Notes:                Conditional filters compose naturally with IQueryable.

Dapper
  Lines of code:        34
  Statements issued:    1  (QueryMultiple)
  Columns fetched:      5
  SQL:                  [paste it]
  Notes:                Conditional WHERE needs string building or the
                        (@X IS NULL OR col = @X) pattern.

Verdict: EF Core. The filter composition is worth more than one round trip here.

Operation: outstanding fees report

EF Core
  Generated SQL:        [paste it]
  Issue:                The GROUP BY over cancelled-payment exclusion produced
                        a correlated subquery per row.

Dapper
  SQL:                  CTE aggregating payments once, then joined.
  Rows read:            [measure both]

Verdict: Dapper. The SQL is the hard part of this operation, and writing it
         directly is clearer than coaxing LINQ into producing it.

Operation: promote 400 students to the next class

EF Core, tracked loop:   400 rows loaded, 400 UPDATEs batched
EF Core, ExecuteUpdate:  1 UPDATE, no rows loaded
Dapper:                  1 UPDATE

Verdict: ExecuteUpdate or Dapper. The tracked loop is the wrong tool, and it is
         the version a fresher writes first.

Submission template

COMPARISON.md

Model configuration:
  Where configuration lives and why (annotations vs Fluent API):
  Conventions overridden, and what the default would have produced:
  Constraints expressed in the model:

Migrations:
  Migration list and what each does:
  Any hand-edited migration and why:
  How migrations are applied to production:

Query strategy:
  Which queries are tracked and which are not:
  Where projection was used instead of Include, and why:
  Any AsSplitQuery, and the row count it fixed:
  Generated SQL for the three most important queries:

Write strategy:
  Update pattern used, and what the alternative would have destroyed:
  Where ExecuteUpdate was used, and what it skips:
  Concurrency token and what it prevents:

Multi-tenant isolation:
  Where SchoolId comes from:
  How it is enforced on every query:
  The test that fails if it is removed:

EF Core versus Dapper:
  Per operation: lines, statements, columns, generated SQL, verdict:
  Where you would use each in this application, and why:

Deliberately not done, and why:

Verification

The schema comes from migrations. Drop the database, run Update-Database in the Package Manager Console, and the API works. No hand-created objects.

Tenant isolation holds. Seed two schools. For every endpoint, confirm a school 1 token never returns or modifies school 2 data. Then remove SchoolId from one query and confirm a test fails — an isolation you cannot break on purpose is an isolation you have not tested.

The update endpoint preserves unsent fields. PUT with only Name and ClassName. Read the row and confirm RollNumber, DateOfBirth, ParentName and SchoolId are unchanged. Then rewrite it with DbSet.Update on a detached entity, repeat, and record exactly what was destroyed.

Concurrency is detected. Load one student in two contexts, save both. The second must return 409, not overwrite.

Absent results store null. Save an absent result and read the row in SSMS. MarksObtained must be NULL. Then confirm the check constraint rejects IsAbsent = 1 with marks of 0 inserted directly.

No N+1. Log the SQL for every endpoint and count the statements. The results endpoint over 40 students must not issue 41 queries.

Read queries are untracked. After a GET, assert ChangeTracker.Entries().Count() == 0.

Generated SQL is reasonable. Paste ToQueryString() for the three most important queries into COMPARISON.md. If you cannot defend a query's SQL, it is not finished.

AI practice

Two AI exercises from this track's syllabus. Do both after the project works, and apply Track 18's discipline — every answer is a hypothesis until you have run it.

1. Ask AI to explain generated migration code. Paste a migration and ask what Up and Down each do, and what data is lost if Down runs. A DropColumn in Down loses every value in that column — and knowing that before a rollback rather than during one is the point.
2. Review an EF query for unnecessary loading. Paste a query loading students with their fee accounts and exam results, and ask what SQL it generates and how many round trips it makes. Then enable command logging and count them. Generated EF code produces N+1 patterns often — 801 queries where one was expected — and the log is the only place it is visible.

Check every generated query for the SchoolId filter and the soft-delete filter as well. EF Core will happily generate a correct-looking query that returns another school's rows.

Track 18 — Reviewing AI-generated code — has the full checklist.

Self-assessment

Your submission is complete when someone can run your migrations against an empty server, exercise every endpoint, read COMPARISON.md, and see which decisions were deliberate.

Four specific tests of quality:

• Does a test fail when the tenant filter is removed? If nothing fails, the isolation is unproven.
• Does PUT with a partial body leave the other columns intact? This is the defining EF Core web bug, and it returns 204 while destroying data.
• Does COMPARISON.md contain generated SQL and measurements, or only opinions? "EF Core is easier" is not a finding; a statement count is.
• Does it say where Dapper is the better tool? A submission concluding EF Core wins every operation has not looked at the report query.

Track completion criteria

You can build basic EF Core CRUD, manage migrations, work with common relationships, and choose Dapper or EF Core for a simple scenario.

Specifically, you can:

• Explain what EF Core provides beyond Dapper, and what it costs
• Configure a model that produces the schema you intended, verified in the migration
• Recognise a data-losing migration before applying it
• Write LINQ that translates, and read the generated SQL to confirm it
• Use projection and AsNoTracking for reads, tracking for writes
• Diagnose cartesian explosion and N+1 from a statement count
• Update a detached entity without destroying unsent fields
• Detect a concurrency conflict instead of silently overwriting
• Say, for a given operation, whether EF Core or Dapper is the better tool — and why

The syllabus recommends Track 10 — ASP.NET Core Development next. If you are working through the tracks in order, Track 09 — Web Development Foundation comes first and builds the browser skills the frontend tracks assume.