Too Many Queries

Every query is a trip to the database. A route that runs a query inside a loop makes one trip per element: one query to list the orders, then one more for each order. With 7 orders that is 1 + 7 = 8 queries; with 10,000 orders, 10,001. This is the N+1 problem: the answer is right, but the route gets slower as the shop grows.

It hides in any loop that runs a query. Here it is on another route, a list of customers with their orders:

cmd.CommandText = "SELECT * FROM customers";
using var customers = cmd.ExecuteReader();
while (customers.Read()) {
    var ordersCmd = db.CreateCommand();
    ordersCmd.CommandText = "SELECT * FROM orders WHERE customer_id = $id";
    ordersCmd.Parameters.AddWithValue("$id", customers["id"]);
    using var orders = ordersCmd.ExecuteReader();
}

The fix is one query with a JOIN. It sends back one row per item, with the columns of its order next to it, and ORDER BY keeps the rows of each order together. Your C# code then groups the rows into orders, with no more queries. In this lesson the check counts the queries of each request and shows the count at /check/queries:

GET /orders         →  200  [{"id": 1, "status": "paid", "items": [{"product_id": 1, "quantity": 2}, {"product_id": 4, "quantity": 5}]}, {"id": 2, "status": "paid", "items": [{"product_id": 3, "quantity": 1}]}, … 7 orders]
GET /check/queries  →  200  {"queries": 1}
POST /orders  {"customer_id": 5, "items": [{"product_id": 8, "quantity": 1}, {"product_id": 5, "quantity": 2}]}  →  201  {"id": 8}
GET /orders         →  200  [… the same 7 orders, then {"id": 8, "status": "new", "items": [{"product_id": 8, "quantity": 1}, {"product_id": 5, "quantity": 2}]}]
GET /check/queries  →  200  {"queries": 1}

Your task: at the end of the file, GET /orders is started for you. Finish it: it answers every order with its id, its status and its items, each with its product_id and quantity, and it asks the database only once.

Assignment
Follow the Coding Tutorial to finish GET /orders with one JOIN query, and group its rows into orders in C#.

Hint
A row starts a new order when result is still empty, or when the last order in it, result.Last(), has another id. Check for the empty list first: result.Last() on an empty list throws an exception. Every row, new order or not, adds its item to the last order.