Tutorials / Build a REST API with C# and .NET · Lesson 25 of 27

On this page

The Stock Report

The owner of the bookshop walks in and asks: "How much money is sitting on my shelves, and in which kinds of book?" You could hand over the shelf list, one line per book, and let the owner add it up. A better answer is a summary sheet: one line per kind of book, with the totals already worked out.

Our goal: build an endpoint that answers with a summary, one line per category, using a small class made just for that answer. Until now every answer was a row of the table. This is the first answer that is not.

Here is the route:

See why no row of the table can hold a summary.
Write the class that holds one line of the report.
Write the query that groups the books and adds them up.
Add the service and the endpoint, and run it.
Test it.

This lesson leaves out who is allowed to see the report (a later lesson in the security part), the screen that shows it, and the books endpoints, which keep returning the table class for now (a later lesson changes that).

Where we are

This is the solution from the end of business rules and meaningful errors. We add one endpoint with its own service, so these files are new:

BookShop/
  BookShop.slnx
  Directory.Build.props
  Directory.Packages.props
  src/
    BookShop.Api/
      Program.cs                         <- changes (one line)
      GlobalExceptionHandler.cs
      Controllers/
        BooksController.cs
        ReportsController.cs             <- NEW
    BookShop.Data/
      Book.cs
      BookShopDbContext.cs
      IBookService.cs
      BookService.cs
      SellResult.cs
      CategoryStock.cs                   <- NEW
      IStockReportService.cs             <- NEW
      StockReportService.cs              <- NEW
      Migrations/
  tests/
    BookShop.Data.Tests/
      BookServiceTests.cs
      BookValidationTests.cs
      StockReportServiceTests.cs         <- NEW
    BookShop.Api.Tests/
      FakeBookService.cs
      FakeLogger.cs
      BooksControllerTests.cs
      GlobalExceptionHandlerTests.cs
      FakeStockReportService.cs          <- NEW
      ReportsControllerTests.cs          <- NEW

Run every command from the solution folder, BookShop/. No project is added, so there is no solution map.

A report is not a row

Picture the table as it is now, after the owner's stock-take. The three books from reading books are still there. Add two cooking books, so the report has more than one line. In DB Browser, on the Execute SQL tab, run this and press Write Changes:

INSERT INTO Books (Title, Author, Price, Stock, Category) VALUES
  ('Salt Fat Acid Heat', 'Samin Nosrat', 29.00, 4, 'Cooking'),
  ('The Joy of Cooking', 'Irma Rombauer', 32.50, 2, 'Cooking');

The table now holds five books:

Title	Price	Stock	Category
The Pragmatic Programmer	39.90	12	Programming
Clean Code	34.50	7	Programming
Refactoring	44.00	3	Programming
Salt Fat Acid Heat	29.00	4	Cooking
The Joy of Cooking	32.50	2	Cooking

The owner wants one line per category, with three numbers: how many titles (different books), how many copies (the stock added up) and the stock value (what the copies cost together, which is price times stock for each book, added up).

The numbers come straight from the rows. Programming: 12 + 7 + 3 = 22 copies, and 12 × 39.90 + 7 × 34.50 + 3 × 44.00 = 478.80 + 241.50 + 132.00 = 852.30. Cooking: 4 + 2 = 6 copies, and 4 × 29.00 + 2 × 32.50 = 116.00 + 65.00 = 181.00.

Now the problem. Book has an Id, a Title, an Author... A report line has none of those. It has a category and three totals. The endpoint cannot return a Book, because no Book holds "22 copies across three titles". There is no table row for it, and there should not be: the numbers are worked out when someone asks.

So the answer needs a class of its own.

The answer class

A class whose only job is to carry data from one place to another is called a DTO, short for data transfer object. The name is bigger than the idea. It is a plain class with a few properties, made to fit one answer. Create CategoryStock.cs in src/BookShop.Data/. The whole file is:

namespace BookShop.Data;

public class CategoryStock
{
    public string Category { get; set; } = "";
    public int Titles { get; set; }
    public int Copies { get; set; }
    public decimal StockValue { get; set; }
}

One object of this class is one line of the report. It is written like Book: a plain class, properties with { get; set; }, text that starts as "".

Why a new class, and why here?

The honest question: could we skip the class? Two shortcuts would work today.

Return Book and leave most fields empty. That would send a book with no title and no price, and the reader of the answer could not tell a real book from a report line.
Return an anonymous object, a one-off value that C# lets you write without naming a class. It runs, but it has no name. The service interface, the fake and the tests all need to say "this method returns that", and an unnamed shape cannot be said.

A named class is a few lines, and everything else can use its name. It also gives the OpenAPI document, the page behind the test page, something to describe, so a developer reading the API sees four named fields.

It lives in BookShop.Data for the same reason SellResult did in the last lesson: the service returns it, and the service lives there. The API project already references the data project, so the controller can use it.

⚠️ A promise we keep for later. The books endpoints still return Book, the table class itself. This report is the first endpoint that has to use a class made for the answer, and the books endpoints will follow it. A later lesson changes them one by one.

The query

The service has to turn five rows into two lines. In SQL, which you know well, that is a GROUP BY:

SELECT Category,
       COUNT(*)           AS Titles,
       SUM(Stock)         AS Copies,
       SUM(Price * Stock) AS StockValue
FROM Books
GROUP BY Category
ORDER BY Category;

Run in DB Browser, it gives:

Category     Titles  Copies  StockValue
-----------  ------  ------  ----------
Cooking      2       6       181.0
Programming  3       22      852.3

Those are the numbers worked out above. EF Core lets us write the same question in C#, and it writes the SQL for us. This is the C# version, as it will appear in the service:

return await _context.Books
    .GroupBy(b => b.Category)
    .Select(g => new CategoryStock
    {
        Category = g.Key,
        Titles = g.Count(),
        Copies = g.Sum(b => b.Stock),
        StockValue = g.Sum(b => b.Price * b.Stock)
    })
    .OrderBy(r => r.Category)
    .ToListAsync();

Read it from the top:

GroupBy(b => b.Category) puts the books that share a category together. It is the GROUP BY of SQL. What comes out is a list of groups, one per category.
g is one group, the books of one category. g.Key is the value they share: "Programming", for example.
.Select(g => new CategoryStock { ... }) builds one CategoryStock for each group. This is where the answer class is filled in.
g.Count(), g.Sum(...) are the totals. Count is the number of books in the group, and Sum adds up one number per book. b.Price * b.Stock is the value of one book's copies, and the Sum adds those up.
.OrderBy(r => r.Category) sorts the lines, so the report comes out in the same order every time.
.ToListAsync() runs the query and gives the list. As with the other service methods, it is awaited because the database takes time.

EF Core does not load the books and add them up in C#. It sends one query and the database does the adding, which is the efficient way. You can see it in the log if you turn on the command log: this is what EF Core sent for our C#, as printed by the app.

SELECT "b"."Category", COUNT(*) AS "Titles", COALESCE(SUM("b"."Stock"), 0) AS "Copies", COALESCE(ef_sum(ef_multiply("b"."Price", CAST("b"."Stock" AS TEXT))), '0.0') AS "StockValue"
FROM "Books" AS "b"
GROUP BY "b"."Category"
ORDER BY "b"."Category"

It is our GROUP BY, with some extra. COALESCE gives a 0 where a sum would otherwise be empty. ef_sum and ef_multiply are small helper functions that EF Core adds to SQLite. SQLite stores a decimal as text (EF Core and SQLite showed this), so it cannot add the prices exactly by itself, and EF Core brings its own functions to do it. On SQL Server none of this appears: the query would use plain SUM, and the decimal column adds up natively.

The service and the endpoint

Why a new service?

We could add a method to the books service. We do not, for two reasons. Each service has one job: IBookService is about books, and a report across all books is a different job. And the tests stay small: if the report lived in IBookService, the fake book service from the controller tests would have to learn the report too, and so would every fake added later. A report also tends to grow with its own questions (stock by author, sales by month), and it should have a home that can grow. One more reason comes later in the course, when the report will be limited to some users and the books will not. That is easy to say about a separate controller. Not over-engineering: it is one interface and one class, the same shape as the books service, and nothing more.

The interface and the service

Create IStockReportService.cs in src/BookShop.Data/. The whole file is:

namespace BookShop.Data;

public interface IStockReportService
{
    Task<List<CategoryStock>> GetByCategoryAsync();
}

Create StockReportService.cs in the same folder. The whole file is:

using Microsoft.EntityFrameworkCore;

namespace BookShop.Data;

public class StockReportService : IStockReportService
{
    private readonly BookShopDbContext _context;

    public StockReportService(BookShopDbContext context)
    {
        _context = context;
    }

    public async Task<List<CategoryStock>> GetByCategoryAsync()
    {
        return await _context.Books
            .GroupBy(b => b.Category)
            .Select(g => new CategoryStock
            {
                Category = g.Key,
                Titles = g.Count(),
                Copies = g.Sum(b => b.Stock),
                StockValue = g.Sum(b => b.Price * b.Stock)
            })
            .OrderBy(r => r.Category)
            .ToListAsync();
    }
}

It has the same shape as BookService: the DbContext comes in through the constructor, and the one method is the query above.

The controller

Create ReportsController.cs in src/BookShop.Api/Controllers/. The whole file is:

using BookShop.Data;
using Microsoft.AspNetCore.Mvc;

[ApiController]
[Route("reports")]
public class ReportsController : ControllerBase
{
    private readonly IStockReportService _reportService;

    public ReportsController(IStockReportService reportService)
    {
        _reportService = reportService;
    }

    [HttpGet("stock")]
    public async Task<ActionResult<List<CategoryStock>>> GetStock()
    {
        var report = await _reportService.GetByCategoryAsync();
        return report;
    }
}

[Route("reports")] and [HttpGet("stock")] give the address GET /reports/stock. The noun is the report, as HTTP and REST concepts asked.
The method has no parameters. The owner asks for the report, and there is nothing to choose.
There is no 404 and no [ProducesResponseType]. An empty shop is not an error: the report is simply an empty list, with a 200. The OpenAPI document lists the one answer without our help, as it did for reading all the books.

Register it

The service is scoped, like the books service, because it holds the scoped DbContext. Open src/BookShop.Api/Program.cs. The whole file is:

using BookShop.Data;
using Microsoft.EntityFrameworkCore;
using Scalar.AspNetCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.AddOpenApi();
builder.Services.AddExceptionHandler<GlobalExceptionHandler>();
builder.Services.AddProblemDetails();

var connectionString = builder.Configuration.GetConnectionString("BookShop");
builder.Services.AddDbContext<BookShopDbContext>(options => options.UseSqlite(connectionString));
builder.Services.AddScoped<IBookService, BookService>();
builder.Services.AddScoped<IStockReportService, StockReportService>();   // new

var app = builder.Build();

app.UseExceptionHandler();
app.UseStatusCodePages();

if (app.Environment.IsDevelopment())
{
    app.MapOpenApi();
    app.MapScalarApiReference();
}

app.UseHttpsRedirection();

app.MapControllers();

app.Run();

Run it

Start the app (dotnet run --project src/BookShop.Api, or the green button in Visual Studio) and ask for the report:

curl -i http://localhost:5101/reports/stock

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

[
  {
    "category": "Cooking",
    "titles": 2,
    "copies": 6,
    "stockValue": 181.0
  },
  {
    "category": "Programming",
    "titles": 3,
    "copies": 22,
    "stockValue": 852.3
  }
]

(The real body is one long line, shown here with line breaks.) Every number is one we worked out from the table: 2 and 3 titles, 6 and 22 copies, 181.00 and 852.30. The names start with a small letter, as they do for books: that is how the JSON of this API is written. JSON drops the trailing zero, so 852.30 arrives as 852.3, which is the same number.

Two cases the table did not show. We ran each against a copy of the database file, so your data stays as it is:

An empty table answers 200 with []. No book means no category, and so no line. It is not an error.
A book with an empty category gets a line of its own, with an empty name: {"category":"","titles":1,"copies":5,"stockValue":50.0} for a book with five copies at 10.00. Nothing forbids an empty category in our rules yet, so the report shows what is there. Whether it should be forbidden is a decision for the shop.

Test it

The fake

The controller test needs a stand-in for the service, as before. Create FakeStockReportService.cs in tests/BookShop.Api.Tests/. The whole file is:

using BookShop.Data;

namespace BookShop.Api.Tests;

public class FakeStockReportService : IStockReportService
{
    public List<CategoryStock> Report { get; } = new List<CategoryStock>();

    public Task<List<CategoryStock>> GetByCategoryAsync()
    {
        return Task.FromResult(Report);
    }
}

Like the books fake, it holds the data a test puts in, and hands it back. It does no adding: the adding is the service's job, and is tested there.

The service

Create StockReportServiceTests.cs in tests/BookShop.Data.Tests/. It uses the same in-memory SQLite database as the books service tests. The whole file is:

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

namespace BookShop.Data.Tests;

[TestClass]
public class StockReportServiceTests
{
    private SqliteConnection _connection = null!;
    private BookShopDbContext _context = null!;
    private StockReportService _service = null!;

    [TestInitialize]
    public void Setup()
    {
        _connection = new SqliteConnection("Data Source=:memory:");
        _connection.Open();

        var options = new DbContextOptionsBuilder<BookShopDbContext>()
            .UseSqlite(_connection)
            .Options;
        _context = new BookShopDbContext(options);
        _context.Database.EnsureCreated();

        _service = new StockReportService(_context);
    }

    [TestCleanup]
    public void Cleanup()
    {
        _context.Dispose();
        _connection.Dispose();
    }

    [TestMethod]
    public async Task GetByCategoryAsync_NoBooks_ReturnsEmptyList()
    {
        var report = await _service.GetByCategoryAsync();

        Assert.IsEmpty(report);
    }

    [TestMethod]
    public async Task GetByCategoryAsync_OneCategory_AddsUpTitlesCopiesAndValue()
    {
        _context.Books.Add(new Book { Title = "The Pragmatic Programmer", Price = 39.90m, Stock = 12, Category = "Programming" });
        _context.Books.Add(new Book { Title = "Clean Code", Price = 34.50m, Stock = 7, Category = "Programming" });
        _context.Books.Add(new Book { Title = "Refactoring", Price = 44.00m, Stock = 3, Category = "Programming" });
        await _context.SaveChangesAsync();

        var report = await _service.GetByCategoryAsync();

        Assert.HasCount(1, report);
        Assert.AreEqual("Programming", report[0].Category);
        Assert.AreEqual(3, report[0].Titles);
        Assert.AreEqual(22, report[0].Copies);
        Assert.AreEqual(852.30m, report[0].StockValue);
    }

    [TestMethod]
    public async Task GetByCategoryAsync_TwoCategories_ReturnsOneLineEachOrderedByCategory()
    {
        _context.Books.Add(new Book { Title = "Clean Code", Price = 34.50m, Stock = 7, Category = "Programming" });
        _context.Books.Add(new Book { Title = "Salt Fat Acid Heat", Price = 29.00m, Stock = 4, Category = "Cooking" });
        _context.Books.Add(new Book { Title = "The Joy of Cooking", Price = 32.50m, Stock = 2, Category = "Cooking" });
        await _context.SaveChangesAsync();

        var report = await _service.GetByCategoryAsync();

        Assert.HasCount(2, report);
        Assert.AreEqual("Cooking", report[0].Category);
        Assert.AreEqual(2, report[0].Titles);
        Assert.AreEqual(6, report[0].Copies);
        Assert.AreEqual(181.00m, report[0].StockValue);
        Assert.AreEqual("Programming", report[1].Category);
    }

    [TestMethod]
    public async Task GetByCategoryAsync_BookWithNoStock_StillCountsAsATitle()
    {
        _context.Books.Add(new Book { Title = "Clean Code", Price = 34.50m, Stock = 0, Category = "Programming" });
        await _context.SaveChangesAsync();

        var report = await _service.GetByCategoryAsync();

        Assert.AreEqual(1, report[0].Titles);
        Assert.AreEqual(0, report[0].Copies);
        Assert.AreEqual(0m, report[0].StockValue);
    }
}

The four tests are the report from every side: no books, a category added up (the same 22 copies and 852.30 as above, so the reader can check the arithmetic), two categories in order, and a book with no copies, which is still a title on the shelf list. These tests run the real query against a real SQLite database, which is why they can trust the adding.

The controller

Create ReportsControllerTests.cs in tests/BookShop.Api.Tests/. The whole file is:

using BookShop.Data;

namespace BookShop.Api.Tests;

[TestClass]
public class ReportsControllerTests
{
    private FakeStockReportService _fakeService = null!;
    private ReportsController _controller = null!;

    [TestInitialize]
    public void Setup()
    {
        _fakeService = new FakeStockReportService();
        _controller = new ReportsController(_fakeService);
    }

    [TestMethod]
    public async Task GetStock_TwoCategories_ReturnsBothLines()
    {
        _fakeService.Report.Add(new CategoryStock { Category = "Cooking", Titles = 2, Copies = 6, StockValue = 181.00m });
        _fakeService.Report.Add(new CategoryStock { Category = "Programming", Titles = 3, Copies = 22, StockValue = 852.30m });

        var result = await _controller.GetStock();

        Assert.IsNotNull(result.Value);
        Assert.HasCount(2, result.Value);
        Assert.AreEqual("Cooking", result.Value[0].Category);
        Assert.AreEqual(852.30m, result.Value[1].StockValue);
    }

    [TestMethod]
    public async Task GetStock_NoBooks_ReturnsAnEmptyList()
    {
        var result = await _controller.GetStock();

        Assert.IsNotNull(result.Value);
        Assert.IsEmpty(result.Value);
    }
}

The controller has almost nothing to decide, so its tests are small: it passes on what the service gives, and an empty report is an empty list, not a failure.

Run all tests from the solution folder:

dotnet test

Passed!  - Failed:     0, Passed:    18, Skipped:     0, Total:    18, Duration: 128 ms - BookShop.Api.Tests.dll (net10.0)
Passed!  - Failed:     0, Passed:    23, Skipped:     0, Total:    23, Duration: 1 s - BookShop.Data.Tests.dll (net10.0)

Forty-one tests, two projects. (The order and the durations may differ.)

In the test page

The last step of the loop. Open /scalar and find GET /reports/stock, under the group Reports. It has no boxes to fill. Press Test Request and compare the answer with the curl output. The page should list one answer, 200, and describe it as a list of CategoryStock with four fields. As for reading the books, it offers three content types for that answer.

Where we ended

Compare with your solution. The report has its own class, its own service and its own controller:

BookShop/
  BookShop.slnx
  Directory.Build.props
  Directory.Packages.props
  src/
    BookShop.Api/
      Program.cs                         plus the report service registered
      GlobalExceptionHandler.cs
      Controllers/
        BooksController.cs
        ReportsController.cs             GET /reports/stock
    BookShop.Data/
      Book.cs
      BookShopDbContext.cs
      IBookService.cs
      BookService.cs
      SellResult.cs
      CategoryStock.cs                   one line of the report (the DTO)
      IStockReportService.cs             the one question the report asks
      StockReportService.cs              the grouping query
      Migrations/
  tests/
    BookShop.Data.Tests/
      BookServiceTests.cs
      BookValidationTests.cs
      StockReportServiceTests.cs         four tests on a real in-memory database
    BookShop.Api.Tests/
      FakeBookService.cs
      FakeLogger.cs
      BooksControllerTests.cs
      GlobalExceptionHandlerTests.cs
      FakeStockReportService.cs          a list the test fills in
      ReportsControllerTests.cs          two tests

Recap

A report is a summary worked out on request. No row of a table holds it, so no entity can be its answer.
A DTO (data transfer object) is a plain class made to carry one answer. CategoryStock holds one line of the report: the category and three totals.
An unnamed (anonymous) object would run, but the interface, the fake and the tests all need a name to say what the method returns.
GroupBy puts the books of one category together, and Select builds one DTO per group with Count and Sum. EF Core turns it into a single GROUP BY query, so the database does the adding.
The report gets its own service and controller: one job per class, a fake that stays small, and a home that can grow.
An empty shop is an empty list with a 200, not an error.
The books endpoints still return the entity. A later lesson changes that.

Questions to think about

These have no answers here, on purpose. Try them from memory first.

Why can this endpoint not return Book?
What is a DTO, in one sentence?
Who does the adding up: the controller, the service or the database? Why does that matter?
Why does the report have its own service and not a new method on the books service?
The class has four properties and a lot of repeated words. Is there a shorter way to write a class that only carries data? (The next lesson answers this.)

Next lesson: records and small C# shortcuts, a shorter way to write classes like this one.