Skip to content
Katabench
Try free

Your First SELECT

Easy About 8 min Free, no account needed

Write a raw SQL query with Dapper: filter rows with WHERE and sort with ORDER BY.

The brief

Most database puzzles here ask you to write LINQ and let EF Core translate it. This track is different: you write the SQL yourself and run it with Dapper. A throwaway PostgreSQL database, a books table (id, title, author, published_year, pages), and the query is plain SQL text.

The starter already hands you the live connection. You run SQL on it with Dapper's Query<T>, which executes your statement and maps each row to T:

C#
var connection = db.Database.GetDbConnection();
return connection.Query<int>("SELECT id FROM books ORDER BY id").ToList();

When T is a simple type like int, Dapper reads the row's first column, so a one-column SELECT is all you need. Values you pass in the anonymous object (like new { minPages }) are sent as bound parameters referenced with @minPages, never pasted into the string, so they are safe from SQL injection.

Your task

Implement LongBooks(RawSqlDbContext db, int minPages) to return the id of every book with at least minPages pages, as a List<int> ordered by id ascending.

Write it as one SQL statement: a SELECT id FROM books with a WHERE clause for the filter and an ORDER BY for the sort.

This puzzle runs against a real, throwaway PostgreSQL database created just for your submission and destroyed afterwards.

Starter code

C#
using System.Collections.Generic;
using System.Linq;
using Dapper;
using Microsoft.EntityFrameworkCore;
using CodeRunner.PuzzleSchemas;

public class Solution
{
    public List<int> LongBooks(RawSqlDbContext db, int minPages)
    {
        // Dapper runs SQL over the connection the sandbox already opened for this puzzle.
        var connection = db.Database.GetDbConnection();

        // TODO: Write the SQL. Right now the WHERE clause matches no rows, so every test comes back
        // empty. Replace `false` with a real condition on `pages` (compare it to the @minPages
        // parameter), and keep the ORDER BY so the ids come back ascending.
        return connection.Query<int>(
            "SELECT id FROM books WHERE false ORDER BY id",
            new { minPages }).ToList();
    }
}

How it is graded

Your LINQ or SQL runs against real PostgreSQL. You see the SQL your code generates on the sample tests and can inspect its execution plan, so you learn when the database did the wrong work.

EF Core practice →

What it practices

SQL basics
SELECT, WHERE, ORDER BY and simple joins written by hand.

Related katas