Database track: LINQ, SQL, and query plans

Database puzzles are the part of Katabench where a correct answer can still fail. The rows come back right, and the grade says no, because the query that produced them scanned a table it should have seeked, ran two hundred times instead of once, or dragged half the table across the wire to count it in memory. This guide explains what the track runs, what it shows you, and how to read the evidence.

Two ways to write a query

Most database puzzles hand you an EF Core DbContext and ask for LINQ. A smaller set hands you the same live connection and asks for SQL text.

LINQ over EF Core. The starter method takes the context as its first parameter and the brief's inputs after it. You write LINQ against the entities named in the schema panel, and EF Core translates it:

public bool HasOrders(IotDbContext db, string region)
{
    return db.Measurements.Any(m => m.Device.Region == region);
}

Write synchronous LINQ (ToList, Sum, Any, Count), as the starter does. You never open the connection or manage a transaction; the grader owns both.

Raw SQL with Dapper. Puzzles in the SQL category expect the statement itself. The starter already fetches the connection and shows the query call:

var connection = db.Database.GetDbConnection();
return connection
    .Query<int>("SELECT id FROM books WHERE pages >= @minPages ORDER BY id", new { minPages })
    .ToList();

Values you pass in the anonymous object are sent as bound parameters and referenced with @name. They are never spliced into the string, so parameterized SQL is both the safe and the graded way to write these. The dialect is PostgreSQL.

Either way, the brief's Database schema panel lists the tables, columns, keys, relationships, and a few sample rows. Read it before the first Run; the column names in the sample data are the ones the query has to use.

What runs when you press Run or Submit

Every test gets its own throwaway PostgreSQL database, created for that test, seeded with the fixture the test names, and destroyed afterwards. Visible sample tests use small seeds you can reason about by hand. Hidden gates seed large data, typically tens or hundreds of thousands of rows, so that an inefficient query shape shows up in the timing instead of hiding behind a fast machine.

That is why the starter on many puzzles is a working query that passes the visible samples. It is correct, and on the hidden dataset it times out, transfers too much, or allocates too much. The lesson is the difference between the two, and the evidence below is how you see it.

Read the SQL your code generated

Expand any test row and open its SQL queries disclosure. It lists every statement the database received for that test, in order, with the time each one took. Three shapes to look for:

• One statement. The database did the work in a single round trip. This is the goal on almost every puzzle.
• One statement per row. A loop that touches a navigation property, or a query inside a foreach, produces the N+1 pattern: a short first query followed by a long list of near-identical ones. The fix is to ask for the related data in the first query, with a projection or an Include, or to aggregate in the database.
• A statement with no WHERE, followed by work in C#. The filter, sort, or aggregate ran in memory after loading everything. Move it before the call that materializes the query, so EF Core can translate it.

For visible tests, a sanitized plan outline renders directly under each statement: an indented tree of the plan nodes the planner chose, with the relation and index each node touched and the row estimate. Sequential scans are emphasized so you can watch one flip to an index scan as you change the query.

The query-plan panel

Puzzles with plan rules add a Your query plan panel at the top of the Tests tab after Submit. It shows how many plan rules hold, one row per rule with its description and result, and the plan we graded for each hidden gate, expanded by default. When a gate captured more than one statement, the extra plans are collapsed under a note that one round trip is the goal.

The rules are written in plain language and map to a small set of checks:

Rule	What it checks
No sequential scan on a table	The plan may not read every row of that table. Wrapping the indexed column in a function, or filtering on an expression the index does not cover, is the usual cause.
Uses a named index	Some statement's plan must use the index the puzzle names. The index exists already; your predicate has to be shaped so the planner can use it.
Single statement	The test must be answered with one SQL statement, one round trip.
At most N rows returned	Aggregate, filter, or rank in the database. A query that returns the whole table so C# can count it fails here even when the count is right.
Project the named column	The result must come straight from that column, not from a wider row the code trims afterwards.
Sort by a column in the database	The ORDER BY has to be part of the statement, in the direction the puzzle asks for.

Rules about scans and indexes are scoped to the large seeded gates. On a five-row fixture a sequential scan is the planner's correct choice, and the rules do not penalize it there.

Plan outlines are sanitized before they reach the browser. You see node types, relations, indexes, and row estimates, never the hidden fixture's values or your own output expressions, which is what makes it safe to show you the plan behind a hidden test.

Time and allocation budgets still apply

Database tests report the same server-measured elapsed time and allocated bytes as every other puzzle, and some set an allocation budget. A query that loads a large result set to answer a yes/no question can fail the allocation gate before the timeout ever triggers. When the scorecard shows an amber allocation dimension on a database puzzle, the fix is almost always the same as the plan fix: let the database answer the question and return only the answer.

A diagnosis order

When a database Submit fails:

1. Open Tests, select Only failing, and read the test name. It usually names the size class or the lesson ("Sixty thousand tickets (plan gate)").
2. Expand the failing row and count the SQL statements. More than one for a single answer points at N+1 or client-side work.
3. Read the plan outline. A sequential scan on a large table means the predicate is not using the index, and the rule row says which index it expected.
4. Check the returned-row and allocation evidence. If the query returns far more rows than the answer needs, aggregate or filter server-side.
5. Change the query shape, Run to confirm the samples still pass, and Submit again.

Where to start

The free previews in this track cover the shapes above with small schemas: existence checks, filtering before fetching, projecting only the columns you use, aggregating totals per customer, and a first raw SQL SELECT. The High-Performance Data Access learning path orders the Pro catalog from shaping the query on the server through aggregation, raw SQL, index-aware predicates, and analytical queries.

See Tracks and System Design for how this track fits beside the others, How grading works for the shared result statuses, and the EF Core practice page for a guided first exercise.