Lab 2: Real Orders in a Real Database

45 minutes · Starts from checkpoint-1 · Ends at checkpoint-2

You replace the in-memory catalog with EF Core and SQLite, add customers and orders, validate input, and make every error look the same.

Step 1: Add EF Core and the model

dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Design

Add the remaining entities to Models/:

public class Customer
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public required string Email { get; set; }
}

public enum OrderStatus { Pending, Roasting, Ready, PickedUp, Cancelled }

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public OrderStatus Status { get; set; } = OrderStatus.Pending;
    public decimal Total { get; set; }
    public DateTimeOffset PlacedAt { get; set; }
    public List<OrderLine> Lines { get; set; } = [];
}

public class OrderLine
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
}

Create Data/RoasteryDbContext.cs and one configuration class per entity in Data/Configurations/. Set the connection string in appsettings.Development.json:

"ConnectionStrings": { "Roastery": "Data Source=roastery.db" }

builder.Services.AddDbContext<RoasteryDbContext>(o =>
    o.UseSqlite(builder.Configuration.GetConnectionString("Roastery")));

dotnet ef migrations add InitialCreate
dotnet ef database update

Add the development-only MigrateAsync() and seed block from the concepts page to Program.cs. Seed the same products you used in Lab 1 and two customers.

Step 2: Swap the catalog

Write EfProductCatalog : IProductCatalog backed by the DbContext and change the registration from AddSingleton to AddScoped. Run every Lab 1 request again.

success

Nothing in the HTTP contract changed. The endpoints, the DTOs, and the .http file are identical; only the implementation behind the interface moved. That separation is the reason Module 1 insisted on DTOs.

Step 3: Place an order

Create Contracts/OrderContracts.cs with CreateOrderRequest, OrderLineRequest, OrderDto, OrderLineDto, and OrderSummaryDto, using the annotations shown on the concepts page. Turn on validation:

builder.Services.AddValidation();

Create Endpoints/OrderEndpoints.cs with a /api/orders group and implement POST /:

static async Task<Results<CreatedAtRoute<OrderDto>, ValidationProblem>> CreateOrder(
    CreateOrderRequest request, RoasteryDbContext db, TimeProvider clock, CancellationToken ct)
{
    var ids = request.Lines.Select(l => l.ProductId).ToList();
    var products = await db.Products.Where(p => ids.Contains(p.Id)).ToDictionaryAsync(p => p.Id, ct);

    var errors = new Dictionary<string, string[]>();
    foreach (var (line, i) in request.Lines.Select((l, i) => (l, i)))
    {
        if (!products.TryGetValue(line.ProductId, out var product))
            errors[$"Lines[{i}].ProductId"] = ["Unknown product."];
        else if (product.StockQuantity < line.Quantity)
            errors[$"Lines[{i}].Quantity"] = [$"Only {product.StockQuantity} in stock."];
    }
    if (errors.Count > 0) return TypedResults.ValidationProblem(errors);

    var order = new Order
    {
        CustomerId = 1, // replaced by the token's customer_id claim in Module 3
        PlacedAt = clock.GetUtcNow(),
        Lines = request.Lines.Select(l => new OrderLine
        {
            ProductId = l.ProductId, Quantity = l.Quantity, UnitPrice = products[l.ProductId].Price
        }).ToList()
    };
    order.Total = order.Lines.Sum(l => l.Quantity * l.UnitPrice);
    foreach (var line in order.Lines) products[line.ProductId].StockQuantity -= line.Quantity;

    db.Orders.Add(order);
    await db.SaveChangesAsync(ct);
    return TypedResults.CreatedAtRoute(order.ToDto(), "GetOrder", new { id = order.Id });
}

Send the valid POST in module-2.http, then the one with quantity 0 (fails annotation validation, 400) and the one with a made-up product id (fails your stock check, also 400). Compare the two bodies. They should have the same shape.

Step 4: List orders with paging

Implement GET /api/orders?page=&pageSize=&status= returning PagedResult<OrderSummaryDto>, following the projection pattern on the concepts page. Clamp pageSize to 100. Bind the query with an [AsParameters] record.

Step 5: Enforce the lifecycle

Add Domain/OrderLifecycle.cs:

public static class OrderLifecycle
{
    static readonly Dictionary<OrderStatus, OrderStatus[]> Allowed = new()
    {
        [OrderStatus.Pending]  = [OrderStatus.Roasting, OrderStatus.Cancelled],
        [OrderStatus.Roasting] = [OrderStatus.Ready],
        [OrderStatus.Ready]    = [OrderStatus.PickedUp],
    };

    public static bool CanTransition(OrderStatus from, OrderStatus to) =>
        Allowed.TryGetValue(from, out var next) && next.Contains(to);
}

Implement PUT /api/orders/{id:int}/status that takes { "status": "Roasting" }, checks CanTransition, and throws InvalidOrderTransitionException if the move is illegal. Add AddProblemDetails(), the DomainExceptionHandler from the concepts page, UseExceptionHandler(), and UseStatusCodePages().

Send the legal transition (Pending → Roasting, expect 200) and the illegal one (Pending → PickedUp, expect 409).

Step 6: Compare the errors

You now have four different failures: a binding error (GET /api/orders/abc), an annotation validation error, a hand-built ValidationProblem, and a domain exception. Send all four and confirm every body is application/problem+json with type, title, status, and (where relevant) errors.

Stretch goals

Add RowVersion to Order, the SQLite trigger from the EF Core docs, and catch DbUpdateConcurrencyException in the status endpoint to return 409. Prove it with two overlapping requests.
Add ETag support to GET /api/products/{id}: hash the DTO, return it in ETag, and return 304 when If-None-Match matches.
Add DELETE /api/orders/{id} that only succeeds while the order is Pending and restores stock.

Common pitfalls

Annotations ignored. AddValidation() is missing.
JSON cycle exception. You returned an entity with navigation properties instead of a DTO.
"no such table". The migration ran against a different database file than the one the app is using; check the working directory and the connection string.
AddSingleton on a DbContext-backed service. A DbContext is scoped; a singleton that captures it will fail on the second request.

Checkpoint

git add -A && git commit -m "Lab 2 complete"
# or: git checkout checkpoint-2

Next: Module 3: Identity, Tokens, and Authorization