Start learning free

LESSON 78 OF 173 · BACKEND: APIS WITH A DATABASE

Too Many Queries in C#

Practice

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. The order_items table has no id column of its own, so sort the rows by orders.id only. Inside each order, keep the items in the order the query sends them back.


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.


STEP 1 OF 6
    Ctrl + Enter to check

    Db N Plus One in C#

    Keep going with C#1,600+ step-by-step lessons, from your first line of code to coding interviews. Free to start.
    Start learning free