Dashboard API: Many Numbers, One Round Trip – Web API | Lesson 84

Published / updated8 Oct 2026

Module 11 · Lesson 84Advanced50 minInterview:FMSA

Before this lesson: Fees API: Bill a Whole Class at Once and Report Outstanding Fees – Web API, Indexes: Clustered, Nonclustered and Covering – SQL Server

Introduction

The office's first screen is a dashboard: how many students and teachers, yesterday's attendance, fees collected and owed, and how far marks entry has got. Every endpoint we need for those numbers already exists, spread over six different routes.

This lesson builds one endpoint that answers the whole screen. Then it measures it, finds the slow part, and fixes it with an index.

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

• Design a summary endpoint shaped by one screen, not by one table.
• Send several queries in one round trip with Dapper's QueryMultiple.
• Measure each SQL statement with SET STATISTICS TIME and SET STATISTICS IO.
• Fix a slow query with a covering index, and prove the fix with numbers.
• Let clients cache an answer that may be a minute old.

Why this matters

A dashboard is opened all day, by many people, often on phones. Six requests per screen means six waits on a slow network, and six times the work for the server.

It is also the first impression of the whole system. A dashboard that takes seconds makes the SMS feel slow, however fast everything else is.

The concept

Think of shopping with a list. Five items on one trip is far quicker than five trips, even though the shop and the items are the same. QueryMultiple is the list: five SQL statements, one trip to SQL Server.

Example: one screen, one endpoint

Tile on the screen	Without a dashboard endpoint	Inside GET /api/dashboard/summary
Students and teachers	/api/students, /api/teachers	statement 1
Full sections	/api/classes	statement 2
Latest attendance	/api/attendance/daily-summary	statement 3
Fees	/api/fee-invoices/outstanding	statement 4
Marks entry progress	/api/exams and every paper's marks	statement 5

The separate endpoints stay. They serve their own screens; the dashboard serves this one.

Example: measure before you fix

Two settings make SQL Server report the cost of each statement:

Setting	Reports	Use it to find
SET STATISTICS TIME ON	CPU and elapsed milliseconds	the slowest statement
SET STATISTICS IO ON	pages read from each table	why it is slow

A page is 8 KB. Reading 1,600 pages to count one day's attendance means SQL Server is reading far more than it needs.

Example: a covering index

An index covers a query when it holds every column the query needs. SQL Server then reads only the index and never touches the table. INCLUDE adds columns to the index's leaf level without making them part of the key.

Apply it to the SMS

Step 1: the dashboard's shape

src/SchoolApp.Core/Dtos/DashboardDtos.cs

namespace SchoolApp.Core.Dtos;

// Lesson 84: the office dashboard. Every number the first screen shows, from one request.

// The latest school day with attendance, on or before the dashboard's date.
public record AttendanceSnapshot(DateOnly Date, int Recorded, int Present, int Absent, int Late, int Leave, decimal AttendancePercent);

// This academic year's invoices: what was billed, what came in, and what is still owed.
public record FeeSnapshot(decimal Invoiced, decimal Collected, decimal Outstanding, int OverdueInvoices);

// The latest exam that has started, and how far marks entry has got.
public record ExamSnapshot(string Name, DateOnly StartDate, int Papers, int PapersWithMarks);

public record DashboardDto(
    DateOnly AsOf,
    string AcademicYear,
    int ActiveStudents,
    int EnrolledThisYear,
    int ActiveTeachers,
    int Sections,
    int FullSections,
    AttendanceSnapshot? LatestAttendance,
    FeeSnapshot Fees,
    ExamSnapshot? LatestExam);

LatestAttendance and LatestExam can be null: early in the year there is no attendance yet, and before the first exam there is no exam.

Step 2: five statements, one round trip

src/SchoolApp.Data/Dapper/DapperDashboardQueries.cs

using Dapper;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Interfaces;

namespace SchoolApp.Data.Dapper;

// Lesson 84: the dashboard in one round trip. Five SELECTs travel to SQL Server as one batch, and
// QueryMultiple reads their answers back in the same order (the idea from Lesson 58's GetSummaryAsync).
public class DapperDashboardQueries : IDashboardQueries
{
    private const string Sql = """
        -- 1. People: active students, enrolled this year, active teachers.
        SELECT (SELECT COUNT(*) FROM dbo.Students WHERE Status = 'Active' AND IsDeleted = 0) AS ActiveStudents,
               (SELECT COUNT(*) FROM dbo.Enrollments AS e
                JOIN dbo.Students AS s ON s.Id = e.StudentId AND s.IsDeleted = 0
                WHERE e.AcademicYearId = @YearId) AS EnrolledThisYear,
               (SELECT COUNT(*) FROM dbo.Teachers WHERE IsActive = 1 AND IsDeleted = 0) AS ActiveTeachers;

        -- 2. Sections, and how many are full this year. The counts are grouped once, then joined:
        --    18 ms, where counting again for each of 220 sections took 67 ms.
        SELECT COUNT(*) AS Sections,
               SUM(CASE WHEN ISNULL(f.Students, 0) >= sec.Capacity THEN 1 ELSE 0 END) AS FullSections
        FROM dbo.Sections AS sec
        LEFT JOIN (SELECT e.SectionId, COUNT(*) AS Students
                   FROM dbo.Enrollments AS e
                   JOIN dbo.Students AS s ON s.Id = e.StudentId AND s.IsDeleted = 0
                   WHERE e.AcademicYearId = @YearId
                   GROUP BY e.SectionId) AS f ON f.SectionId = sec.Id;

        -- 3. The latest school day with attendance, on or before @AsOf. The covering index from
        --    084-dashboard-index.sql answers both statements without touching the table.
        DECLARE @Day DATE = (SELECT MAX(AttendanceDate) FROM dbo.AttendanceRecords WHERE AttendanceDate <= @AsOf);
        SELECT @Day AS Date,
               COUNT(*) AS Recorded,
               SUM(CASE WHEN a.Status = 'Present' THEN 1 ELSE 0 END) AS Present,
               SUM(CASE WHEN a.Status = 'Absent'  THEN 1 ELSE 0 END) AS Absent,
               SUM(CASE WHEN a.Status = 'Late'    THEN 1 ELSE 0 END) AS Late,
               SUM(CASE WHEN a.Status = 'Leave'   THEN 1 ELSE 0 END) AS Leave
        FROM dbo.AttendanceRecords AS a
        JOIN dbo.Enrollments AS e ON e.Id = a.EnrollmentId
        JOIN dbo.Students AS s ON s.Id = e.StudentId AND s.IsDeleted = 0
        WHERE a.AttendanceDate = @Day;

        -- 4. Fees for this year's invoices (cancelled invoices are not owed).
        SELECT ISNULL(SUM(i.TotalAmount - i.DiscountAmount), 0) AS Invoiced,
               ISNULL(SUM(p.Paid), 0) AS Collected,
               SUM(CASE WHEN i.Status IN ('Unpaid', 'PartiallyPaid') AND i.DueDate < @AsOf THEN 1 ELSE 0 END) AS OverdueInvoices
        FROM dbo.FeeInvoices AS i
        JOIN dbo.Enrollments AS e ON e.Id = i.EnrollmentId AND e.AcademicYearId = @YearId
        OUTER APPLY (SELECT SUM(fp.Amount) AS Paid
                     FROM dbo.FeePayments AS fp
                     WHERE fp.FeeInvoiceId = i.Id AND fp.IsDeleted = 0) AS p
        WHERE i.Status <> 'Cancelled';

        -- 5. The latest exam that has started, with marks entry progress.
        SELECT TOP (1) x.Name, x.StartDate,
               (SELECT COUNT(*) FROM dbo.ExamSubjects AS es WHERE es.ExamId = x.Id) AS Papers,
               (SELECT COUNT(*) FROM dbo.ExamSubjects AS es
                WHERE es.ExamId = x.Id AND EXISTS (SELECT 1 FROM dbo.Marks AS m WHERE m.ExamSubjectId = es.Id)) AS PapersWithMarks
        FROM dbo.Exams AS x
        WHERE x.AcademicYearId = @YearId AND x.StartDate <= @AsOf
        ORDER BY x.StartDate DESC;
        """;

    private readonly SqlConnectionFactory _connections;

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

    public async Task<DashboardDto> GetSummaryAsync(AcademicYearDto year, DateOnly asOf, CancellationToken cancellationToken = default)
    {
        await using SqlConnection connection = _connections.Create();
        await using SqlMapper.GridReader results = await connection.QueryMultipleAsync(
            new CommandDefinition(Sql, new { YearId = year.Id, AsOf = asOf }, cancellationToken: cancellationToken));

        // Read in the same order as the SELECTs above.
        PeopleRow people = await results.ReadSingleAsync<PeopleRow>();
        SectionsRow sections = await results.ReadSingleAsync<SectionsRow>();
        AttendanceRow attendance = await results.ReadSingleAsync<AttendanceRow>();
        FeesRow fees = await results.ReadSingleAsync<FeesRow>();
        ExamRow? exam = await results.ReadSingleOrDefaultAsync<ExamRow>();

        AttendanceSnapshot? latestAttendance = attendance.Date is DateOnly day
            ? new AttendanceSnapshot(day, attendance.Recorded, attendance.Present ?? 0, attendance.Absent ?? 0,
                                     attendance.Late ?? 0, attendance.Leave ?? 0,
                                     Percent((attendance.Present ?? 0) + (attendance.Late ?? 0), attendance.Recorded))
            : null;   // no attendance taken yet this year

        return new DashboardDto(
            asOf, year.Name, people.ActiveStudents, people.EnrolledThisYear, people.ActiveTeachers,
            sections.Sections, sections.FullSections ?? 0,
            latestAttendance,
            new FeeSnapshot(fees.Invoiced, fees.Collected, fees.Invoiced - fees.Collected, fees.OverdueInvoices ?? 0),
            exam is null ? null : new ExamSnapshot(exam.Name, exam.StartDate, exam.Papers, exam.PapersWithMarks));
    }

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

    // SUM over no rows is NULL, so those counts are nullable; COUNT never is.
    private sealed class PeopleRow
    {
        public int ActiveStudents { get; set; }
        public int EnrolledThisYear { get; set; }
        public int ActiveTeachers { get; set; }
    }

    private sealed class SectionsRow
    {
        public int Sections { get; set; }
        public int? FullSections { get; set; }
    }

    private sealed class AttendanceRow
    {
        public DateOnly? Date { get; set; }
        public int Recorded { get; set; }
        public int? Present { get; set; }
        public int? Absent { get; set; }
        public int? Late { get; set; }
        public int? Leave { get; set; }
    }

    private sealed class FeesRow
    {
        public decimal Invoiced { get; set; }
        public decimal Collected { get; set; }
        public int? OverdueInvoices { get; set; }
    }

    private sealed class ExamRow
    {
        public string Name { get; set; } = "";
        public DateOnly StartDate { get; set; }
        public int Papers { get; set; }
        public int PapersWithMarks { get; set; }
    }
}

The answers are read in the same order as the SELECTs. A SUM over no rows is NULL in SQL, so those row properties are int? and read as 0.

Step 3: the endpoint

src/SchoolApp.Api/Controllers/DashboardController.cs

using Microsoft.AspNetCore.Mvc;
using SchoolApp.Api.Models;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Exceptions;
using SchoolApp.Core.Interfaces;

namespace SchoolApp.Api.Controllers;

// Lesson 84: the office dashboard. One request instead of one per number on the screen.
[ApiController]
[Route("api/dashboard")]
[Produces("application/json")]
[ProducesErrorResponseType(typeof(ApiResponse))]
public class DashboardController : ControllerBase
{
    private readonly IDashboardQueries _dashboard;
    private readonly IMasterDataRepository _masterData;
    private readonly TimeProvider _time;

    public DashboardController(IDashboardQueries dashboard, IMasterDataRepository masterData, TimeProvider time)
    {
        _dashboard = dashboard;
        _masterData = masterData;
        _time = time;
    }

    /// <summary>The numbers for the office's first screen, as of today or of a given date.</summary>
    /// <param name="asOf">Leave it out for today; give a date to see the dashboard as it was then.</param>
    /// <param name="cancellationToken">Cancelled when the client gives up.</param>
    /// <response code="200">Students, teachers, sections, the latest attendance, fees, and the latest exam.</response>
    /// <response code="422">No academic year is marked as current (50035).</response>
    [HttpGet("summary")]
    [ResponseCache(Duration = 60)]   // a minute old is fine for a dashboard; the office refreshes often
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status422UnprocessableEntity)]
    public async Task<ApiResponse<DashboardDto>> GetSummary([FromQuery] DateOnly? asOf, CancellationToken cancellationToken)
    {
        AcademicYearDto year = await _masterData.GetCurrentAcademicYearAsync(cancellationToken)
            ?? throw new SchoolRuleException(50035, "No academic year is marked as current.");
        DateOnly date = asOf ?? DateOnly.FromDateTime(_time.GetLocalNow().DateTime);

        DashboardDto summary = await _dashboard.GetSummaryAsync(year, date, cancellationToken);
        return ApiResponse.Ok(summary, $"Dashboard for {date:yyyy-MM-dd}.");
    }
}

asOf shows the dashboard as it was on any date. [ResponseCache(Duration = 60)] sends Cache-Control: public,max-age=60, so a browser may reuse the answer for a minute.

Step 4: the first result

Output

GET /api/dashboard/summary
{"status":"success","message":"Dashboard for 2026-10-08.","data":{"asOf":"2026-10-08","academicYear":"2026-27","activeStudents":10012,"enrolledThisYear":10012,"activeTeachers":8,"sections":220,"fullSections":200,"latestAttendance":{"date":"2026-08-08","recorded":10000,"present":8800,"absent":600,"late":300,"leave":300,"attendancePercent":91},"fees":{"invoiced":510000.00,"collected":105000.00,"outstanding":405000.00,"overdueInvoices":10},"latestExam":{"name":"Half Yearly","startDate":"2026-09-21","papers":60,"papersWithMarks":60}}}

On 2026-07-01 the same endpoint showed that day's attendance (10,012 records) and "latestExam":null, because Unit Test 1 started on 2026-07-20.

The numbers were right, but the endpoint took about 200 ms. The six separate endpoints together took about 190 ms. One round trip had saved nothing, so something inside was slow.

Step 5: find the slow statement

Running the batch in sqlcmd with SET STATISTICS TIME ON gave one line per statement:

Statement	Elapsed
1. Students and teachers	16 ms
2. Sections	67 ms
3. Latest attendance day, then its counts	3 ms + 126 ms
4. Fees	1 ms
5. Latest exam	6 ms

SET STATISTICS IO ON explained statement 3: 1,606 pages read from AttendanceRecords to count 10,000 rows.

Step 6: why the Lesson 26 index was not enough

Lesson 26 created IX_AttendanceRecords_AttendanceDate with INCLUDE (Status). That covers "count one day by status". The dashboard also joins each record to its enrollment, to leave out removed students, so it needs EnrollmentId too.

Our first fix script checked "does the index include Status?" before rebuilding. It did, so the script changed nothing, and the timings did not move. The check now looks for EnrollmentId:

database/084-dashboard-index.sql

-- Lesson 84: widen the Lesson 26 index for "one day's attendance".
-- IX_AttendanceRecords_AttendanceDate already INCLUDEs Status, which is enough to count a day by status.
-- The dashboard also joins each record to its enrollment, to leave out removed students, so it needs
-- EnrollmentId too. Without it, SQL Server reads the table instead of the index (about 1,600 pages).
-- DROP_EXISTING rebuilds the same index, with the same name, in one step. Safe to run more than once.
USE SchoolDb;
GO

IF NOT EXISTS (
    SELECT 1
    FROM sys.index_columns AS ic
    JOIN sys.indexes AS i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
    WHERE i.name = N'IX_AttendanceRecords_AttendanceDate' AND ic.is_included_column = 1 AND c.name = N'EnrollmentId')
BEGIN
    CREATE INDEX IX_AttendanceRecords_AttendanceDate
        ON dbo.AttendanceRecords (AttendanceDate)
        INCLUDE (EnrollmentId, Status)
        WITH (DROP_EXISTING = ON);
END;
GO

-- Check: the index's key column and its included columns.
SELECT c.name AS ColumnName,
       CASE WHEN ic.is_included_column = 1 THEN 'included' ELSE 'key' END AS Role
FROM sys.index_columns AS ic
JOIN sys.indexes AS i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.name = N'IX_AttendanceRecords_AttendanceDate'
ORDER BY ic.is_included_column, ic.index_column_id;
GO

DROP_EXISTING = ON rebuilds the index in one step and keeps its name.

Step 7: measure again

	Before	After
Pages read for one day's attendance	1,606	44
Attendance statement	126 ms	about 31 ms
Sections statement (rewritten with GROUP BY)	67 ms	about 16 ms
Whole batch in SQL Server	about 220 ms	about 68 ms
GET /api/dashboard/summary (warm)	about 200 ms	about 65 ms
The six separate endpoints together	about 190 ms	about 115 ms

The sections statement first counted once per section with OUTER APPLY. Grouping the counts once and joining them was four times faster.

The separate endpoints also got faster, because the attendance report uses the same index. On this machine the dashboard now needs about half their time, in one request instead of six.

Step 8: tests

DashboardTests checks the numbers against the sample data on two dates: 2026-10-08 (latest attendance 2026-08-08, Half Yearly with 60 of 60 papers marked) and 2026-07-01 (no exam yet).

Fee totals are not checked there. FeeBillingTests creates invoices at the same time when tests run in parallel, and a test must never depend on another test's timing. The suite has 84 tests, all passing.

Common mistakes

1. Building the dashboard from many API calls. Each tile waits on its own request. Fix: one endpoint shaped by the screen.
2. Guessing which query is slow. Time goes into the wrong fix. Fix: STATISTICS TIME and IO per statement.
3. An index that covers half the query. One missing column sends SQL Server back to the table. Fix: include every column the query reads.
4. A "safe to re-run" check that tests the wrong thing. The script runs and changes nothing. Fix: check for the change you are about to make, then read the result.
5. Reading QueryMultiple out of order. Each read gets the wrong statement's answer. Fix: read in the order of the SELECTs.

In real projects

From 16 years of .NET projects

A school's dashboard called eleven endpoints when it opened. On the office's slow connection, the screen took four seconds to fill, and staff opened it many times a day.

We added one summary endpoint with a single round trip, then measured each statement. One count read the whole attendance table; a covering index fixed it.

The screen filled in under half a second. Nobody noticed the eleven endpoints were still there: they now only served the pages that needed them.

Best practices

• Shape summary endpoints around a screen, and keep the detailed endpoints too.
• Send related queries in one round trip with QueryMultiple.
• Measure every statement before changing anything, and again after.
• Make indexes cover the queries that run most often.
• Let clients cache numbers that may be a little old.

Recap

• GET /api/dashboard/summary answers the office's first screen in one request.
• Five statements share one round trip through QueryMultiple.
• STATISTICS TIME and IO found the slow statement: 1,606 pages for one day.
• Adding EnrollmentId to the index took that to 44 pages; the endpoint went from about 200 to 65 ms.
• ResponseCache lets clients reuse the answer for a minute.

Homework

Library module task: Build GET /api/library/dashboard?asOf= for the librarian, in one round trip. It returns the number of titles and copies, copies available, books on loan and overdue as on asOf, fines due (2 per day), and the three most issued titles.

A correct result looks like:

Output

GET /api/library/dashboard?asOf=2026-08-01
{"status":"success","message":"Library dashboard for 2026-08-01.","data":{"asOf":"2026-08-01","books":3,"copies":4,"available":3,"onLoan":1,"overdue":1,"finesDue":24,"mostIssued":[{"title":"The Jungle Book","timesIssued":1},{"title":"Wings of Fire","timesIssued":1}]}}
GET /api/library/dashboard?asOf=2026-07-10
... "onLoan":2,"overdue":0,"finesDue":0 ...

Show the solution

library-assignment/084-library-dashboard.cs

// Library homework, Lesson 84: the librarian's dashboard in one request and one round trip.
// Three SELECTs travel as one batch; QueryMultiple reads them back in order.
// Run with:  dotnet run library-assignment/084-library-dashboard.cs
#:project ../src/SchoolApp.Data/SchoolApp.Data.csproj
#:sdk Microsoft.NET.Sdk.Web
#:property PublishAot=false
using System.Text.Json.Serialization;
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:5084");

public class ApiResponse<T>
{
    public string Status { get; init; } = "success";
    public string Message { get; init; } = "";
    [JsonPropertyOrder(1)]
    public T? Data { get; init; }
}

public class Shelf
{
    public int Books { get; set; }
    public int Copies { get; set; }
    public int Available { get; set; }
}

public class Loans
{
    public int OnLoan { get; set; }
    public int Overdue { get; set; }
    public decimal FinesDue { get; set; }
}

public record TopBook(string Title, int TimesIssued);

public record LibraryDashboard(DateOnly AsOf, int Books, int Copies, int Available, int OnLoan, int Overdue,
                               decimal FinesDue, IReadOnlyList<TopBook> MostIssued);

[ApiController]
[Route("api/library/dashboard")]
public class LibraryDashboardController : ControllerBase
{
    private const string Sql = """
        -- 1. The shelf: titles, copies, and copies available now.
        SELECT (SELECT COUNT(*) FROM dbo.Books WHERE IsDeleted = 0) AS Books,
               COUNT(*) AS Copies,
               SUM(CASE WHEN c.Status = 'Available' THEN 1 ELSE 0 END) AS Available
        FROM dbo.BookCopies AS c
        JOIN dbo.Books AS b ON b.Id = c.BookId AND b.IsDeleted = 0;

        -- 2. Loans as on @AsOf: out, overdue, and the fines on overdue books (2 per day).
        SELECT COUNT(*) AS OnLoan,
               SUM(CASE WHEN i.DueOn < @AsOf THEN 1 ELSE 0 END) AS Overdue,
               ISNULL(SUM(CASE WHEN i.DueOn < @AsOf THEN DATEDIFF(DAY, i.DueOn, @AsOf) * 2 ELSE 0 END), 0) AS FinesDue
        FROM dbo.BookIssues AS i
        WHERE i.IssuedOn <= @AsOf AND (i.ReturnedOn IS NULL OR i.ReturnedOn > @AsOf);

        -- 3. The most issued titles.
        SELECT TOP (3) b.Title, COUNT(*) AS TimesIssued
        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
        GROUP BY b.Title
        ORDER BY TimesIssued DESC, b.Title;
        """;

    private readonly SqlConnectionFactory _connections;

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

    // GET /api/library/dashboard?asOf=2026-08-01
    [HttpGet]
    public async Task<ApiResponse<LibraryDashboard>> Get([FromQuery] DateOnly asOf)
    {
        await using SqlConnection connection = _connections.Create();
        await using SqlMapper.GridReader results = await connection.QueryMultipleAsync(Sql, new { AsOf = asOf });
        Shelf shelf = await results.ReadSingleAsync<Shelf>();
        Loans loans = await results.ReadSingleAsync<Loans>();
        List<TopBook> top = (await results.ReadAsync<TopBook>()).ToList();

        return new ApiResponse<LibraryDashboard>
        {
            Message = $"Library dashboard for {asOf:yyyy-MM-dd}.",
            Data = new LibraryDashboard(asOf, shelf.Books, shelf.Copies, shelf.Available, loans.OnLoan, loans.Overdue,
                                        loans.FinesDue, top)
        };
    }
}

The shelf numbers describe the copies now, while loans are counted as on asOf. On 2026-07-10 both loans were still out, so two books were on loan.

Quiz

1. What does Dapper's QueryMultiple do?

   1. Runs one query many times
   2. Sends several statements in one round trip and reads each answer in order
   3. Runs queries in parallel threads
   4. Caches results
   Show answer

   Answer: B. Sends several statements in one round trip and reads each answer in order Five SELECTs, one trip to SQL Server.

2. How did we find which dashboard statement was slow?

   1. By guessing
   2. SET STATISTICS TIME showed each statement's time, and STATISTICS IO showed the pages read
   3. From the API log
   4. By removing statements one by one
   Show answer

   Answer: B. SET STATISTICS TIME showed each statement's time, and STATISTICS IO showed the pages read Attendance took 126 ms and read 1,606 pages.

3. Why was the Lesson 26 index not enough for the dashboard?

   1. It was corrupted
   2. It included Status but not EnrollmentId, which the join needs
   3. It was on the wrong table
   4. It was too large
   Show answer

   Answer: B. It included Status but not EnrollmentId, which the join needs One missing column sent SQL Server back to the table.

4. Why did the first version of the index script change nothing?

   1. It had a syntax error
   2. Its re-run check looked for Status, which was already included, so it skipped the rebuild
   3. It ran on the wrong database
   4. Indexes cannot be changed
   Show answer

   Answer: B. Its re-run check looked for Status, which was already included, so it skipped the rebuild A re-run check must test for the change you are about to make.

5. What does [ResponseCache(Duration = 60)] do here?

   1. Caches on the server for 60 seconds
   2. Sends Cache-Control: public,max-age=60, so clients may reuse the answer for a minute
   3. Limits requests to 60 per minute
   4. Times out after 60 seconds
   Show answer

   Answer: B. Sends Cache-Control: public,max-age=60, so clients may reuse the answer for a minute Server-side caching is a Module 12 topic.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: Web API interview questions.

Fresher (0-2 yrs)

F Why build a dashboard endpoint instead of letting the screen call the existing endpoints? design

One request is quicker than six, especially on phones and slow networks, and it is less work for the server. The endpoint is shaped by the screen, not by the tables. The SMS keeps the detailed endpoints for their own screens and adds GET /api/dashboard/summary for the first screen.

See the example · Permalink

F What does Dapper's QueryMultiple do, and what must you be careful about? coding

It sends several SQL statements in one round trip and returns a reader for their results. You read each result in the same order as the statements; reading out of order maps the wrong rows. The SMS dashboard reads five results this way.

See the example · Permalink

F What is a covering index? concept

An index that holds every column a query needs, so SQL Server reads only the index and never the table. INCLUDE adds columns to the index without making them part of the key. In the SMS, adding EnrollmentId to the attendance date index cut one day's count from 1,606 pages read to 44.

See the example · Permalink

Mid (3-5 yrs)

M How do you find which statement in a SQL batch is slow? coding

Run it with SET STATISTICS TIME ON to get the CPU and elapsed time of each statement, and SET STATISTICS IO ON to see how many pages each one reads from each table. The SMS dashboard's attendance statement took 126 ms and read 1,606 pages, which pointed straight at a missing index column.

See the example · Permalink

M An index includes Status, yet a query on that table still reads the table. Why? scenario

The query needs another column the index does not hold. In the SMS, the dashboard joins each attendance record to its enrollment, so it needs EnrollmentId as well as Status. Rebuilding the index with INCLUDE (EnrollmentId, Status) and DROP_EXISTING = ON made it cover the query.

See the example · Permalink

M What does [ResponseCache(Duration = 60)] do on an ASP.NET Core action? concept

It adds the header Cache-Control: public,max-age=60, so browsers and proxies may reuse the response for a minute. It does not cache anything on the server by itself. It suits numbers such as a dashboard that may safely be a little old.

See the example · Permalink

Senior (6-9 yrs)

S You added an index to speed up a query, but the timings did not change at all. What do you check? scenario

Check that the change was really made: in the SMS, the script's re-run check looked for a column that was already included, so it skipped the rebuild. Then read the index definition from sys.index_columns, and compare pages read with STATISTICS IO before and after. Only trust a fix once the numbers move.

How to answer: Start from verifying the change itself, then measure with the right tools, and explain how you would prevent a silent no-op in future scripts.

See the example · Permalink

S A dashboard test fails sometimes when the whole suite runs, but never alone. What is likely wrong? scenario

It depends on data that other tests change at the same time: test classes run in parallel. In the SMS, fee totals change while FeeBillingTests creates invoices, so the dashboard test checks only numbers that no other test changes. Alternatives are separate test data, a collection that runs tests one after another, or a database per test run.

How to answer: Explain parallel test execution, shared data, and the options to isolate tests, with their costs.

See the example · Permalink

Architect / Lead (10+ yrs)

A The dashboard is opened by thousands of users across many schools. How would you keep it fast? design

Keep one round trip per dashboard and covering indexes for its queries. Cache the result per school for a short time on the server (memory or a distributed cache), refreshed in the background. Pre-compute heavy numbers such as daily attendance into summary tables. Send Cache-Control so clients reuse answers, and watch query times per school in monitoring.

How to answer: Layer the answer: query efficiency, server-side caching with clear freshness rules, pre-computation, and measurement in production.

See the example · Permalink

Resources

• Code for this lesson: git tag lesson-84-dashboard (code repository coming soon)
• Files: src/SchoolApp.Data/Dapper/DapperDashboardQueries.cs, src/SchoolApp.Api/Controllers/DashboardController.cs, database/084-dashboard-index.sql, tests/SchoolApp.Tests/Integration/DashboardTests.cs, library-assignment/084-library-dashboard.cs
• Video: coming soon

What's next

Module 11 is complete: every SMS feature has an endpoint, every answer has one shape, and the data layer is Dapper. Next, Module 12 makes the API ready for real users, starting with signing in. Lesson 85: Signing in with JWT

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