Single-Row Queries with Dapper: QueryFirst, QuerySingle and ExecuteScalar

Version note: The code in this post targets Dapper 2.1 and .NET 10.

Guess First

We run the same SQL with three different parameters through two different methods. Ursula K. Le Guin (AuthorId = 1) has three books in the database, while an author with id 99 does not exist at all:

const string sql = "SELECT id, title, price FROM books WHERE author_id = @AuthorId";

var a = connection.QueryFirst<Book>(sql, new { AuthorId = 1 });
Console.WriteLine($"a: {a.Title}");

var b = connection.QuerySingleOrDefault<Book>(sql, new { AuthorId = 99 });
Console.WriteLine($"b: {b?.Title ?? "null"}");

var c = connection.QuerySingleOrDefault<Book>(sql, new { AuthorId = 1 });
Console.WriteLine($"c: {c?.Title ?? "null"}");

Which of the three throws an exception? Most people think of b first, because there are no rows there. Some say about c: "it says OrDefault, at worst it returns null". Make your own guess; the answer is in the "QuerySingle: A Contract" section.

The Bookstore Database

We use the setup from the first post: a .NET 10 console project, Dapper 2.1.89, Microsoft.Data.Sqlite 10.0.14 and BookstoreDb.cs, which rebuilds the database on every run:

using Dapper;
using Microsoft.Data.Sqlite;

public static class BookstoreDb
{
    public const string ConnectionString = "Data Source=bookstore.db";

    public static void Reset()
    {
        using var connection = new SqliteConnection(ConnectionString);
        connection.Execute("""
            DROP TABLE IF EXISTS order_items;
            DROP TABLE IF EXISTS orders;
            DROP TABLE IF EXISTS books;
            DROP TABLE IF EXISTS authors;

            CREATE TABLE authors (
                id      INTEGER PRIMARY KEY,
                name    TEXT NOT NULL,
                country TEXT
            );
            CREATE TABLE books (
                id             INTEGER PRIMARY KEY,
                author_id      INTEGER NOT NULL REFERENCES authors(id),
                title          TEXT NOT NULL,
                price          REAL NOT NULL,
                published_year INTEGER,
                stock          INTEGER NOT NULL DEFAULT 0
            );
            CREATE TABLE orders (
                id             INTEGER PRIMARY KEY,
                customer_email TEXT NOT NULL,
                created_at     TEXT NOT NULL
            );
            CREATE TABLE order_items (
                id         INTEGER PRIMARY KEY,
                order_id   INTEGER NOT NULL REFERENCES orders(id),
                book_id    INTEGER NOT NULL REFERENCES books(id),
                quantity   INTEGER NOT NULL,
                unit_price REAL NOT NULL
            );

            INSERT INTO authors (id, name, country) VALUES
                (1, 'Ursula K. Le Guin', 'USA'),
                (2, 'Frank Herbert', 'USA'),
                (3, 'Stanislaw Lem', 'Poland'),
                (4, 'Douglas Adams', 'UK'),
                (5, 'Ted Chiang', 'USA');

            INSERT INTO books (id, author_id, title, price, published_year, stock) VALUES
                (1, 1, 'A Wizard of Earthsea', 12.50, 1968, 8),
                (2, 1, 'The Left Hand of Darkness', 14.00, 1969, 3),
                (3, 1, 'The Dispossessed', 15.25, 1974, 0),
                (4, 2, 'Dune', 18.90, 1965, 12),
                (5, 3, 'Solaris', 11.40, 1961, 5),
                (6, 4, 'The Hitchhiker''s Guide to the Galaxy', 9.99, 1979, 20),
                (7, 5, 'Exhalation', 13.20, NULL, 4);
            """);
    }
}

The class carrying the results is the Book from the first post, with Id, Title and Price properties. Program.cs starts with BookstoreDb.Reset(); and opens a single SqliteConnection.

Four Methods, Three Scenarios

The single-row methods differ in how they answer two questions. What should happen when zero rows come back: an exception or a default value? What should happen when more than one row comes back: take the first one, or treat it as an error? First/Single is the answer to the second question, and the OrDefault suffix is the answer to the first. Let's run all four side by side in three scenarios: author 99 (zero books), Ted Chiang (one book) and Le Guin (three books).

string Try(Func<Book?> call)
{
    try
    {
        return call()?.Title ?? "null";
    }
    catch (InvalidOperationException ex)
    {
        return $"{ex.GetType().Name}: {ex.Message}";
    }
}

foreach (var (label, authorId) in new[] { ("0 rows", 99), ("1 row", 5), ("3 rows", 1) })
{
    var p = new { AuthorId = authorId };
    Console.WriteLine($"--- {label}");
    Console.WriteLine($"QueryFirst            {Try(() => connection.QueryFirst<Book>(sql, p))}");
    Console.WriteLine($"QueryFirstOrDefault   {Try(() => connection.QueryFirstOrDefault<Book>(sql, p))}");
    Console.WriteLine($"QuerySingle           {Try(() => connection.QuerySingle<Book>(sql, p))}");
    Console.WriteLine($"QuerySingleOrDefault  {Try(() => connection.QuerySingleOrDefault<Book>(sql, p))}");
}

--- 0 rows
QueryFirst            InvalidOperationException: Sequence contains no elements
QueryFirstOrDefault   null
QuerySingle           InvalidOperationException: Sequence contains no elements
QuerySingleOrDefault  null
--- 1 row
QueryFirst            Exhalation
QueryFirstOrDefault   Exhalation
QuerySingle           Exhalation
QuerySingleOrDefault  Exhalation
--- 3 rows
QueryFirst            A Wizard of Earthsea
QueryFirstOrDefault   A Wizard of Earthsea
QuerySingle           InvalidOperationException: Sequence contains more than one element
QuerySingleOrDefault  InvalidOperationException: Sequence contains more than one element

Read this output once and keep it in mind, because the whole post builds on it. When one row comes back, all four behave the same; the difference is in the edge cases. The exception messages are familiar from LINQ: in these methods Dapper behaves like First() and Single() and throws an InvalidOperationException of the same type with the same message. Version 2.1.66 brought correctness fixes to this family; if you are on a very old version, that is one more reason to upgrade.

QueryFirst: Who Decides the First Row?

In the three-book scenario QueryFirst gave us "A Wizard of Earthsea". Why that one? Because the SQL has no ORDER BY, and this time SQLite returned the rows in id order. "This time" is the important part: in a query where you do not specify an order, the database may return rows in any order it likes. When an index is added, when the data grows or when you move to another database, the "first" row silently changes.

The right way to use QueryFirst is to say explicitly in the SQL which row is first:

var cheapest = connection.QueryFirst<Book>(
    "SELECT id, title, price FROM books ORDER BY price LIMIT 1");

Console.WriteLine($"{cheapest.Title} {cheapest.Price}");

The Hitchhiker's Guide to the Galaxy 9.99

LIMIT 1 does not change the result here, but it puts the work in the right place. QueryFirst says on the C# side that you need one row; the database learns that only from your SQL. If you are picking one row out of a thousand, it is cheaper for the database to do it. The equivalent on SQL Server is TOP 1, and on PostgreSQL it is LIMIT 1 as well.

QuerySingle: A Contract

The answer to the guessing question:

a: A Wizard of Earthsea
b: null
InvalidOperationException: Sequence contains more than one element

b returned null without trouble; it was c that threw. The OrDefault in QuerySingleOrDefault covers only the "no rows" case. More than one row is always a contract violation for Single, and OrDefault does not forgive it.

See this as a feature, not a flaw. When you write QuerySingle, you put a claim into the code: "this query must return exactly one row". When you look something up by a primary key or a unique column, that claim is natural:

var book = connection.QuerySingle<Book>(
    "SELECT id, title, price FROM books WHERE id = @Id",
    new { Id = 4 });

Console.WriteLine(book.Title);

Dune

If someone drops the unique index one day, or a migration creates two customer records with the same email, QuerySingle says so loudly the first time it runs. Had you used QueryFirst in the same place, you would have taken whichever record came first and carried on, and the data bug could go unnoticed for weeks. As an analogy: QueryFirst looks at the first person in the queue, while QuerySingle checks IDs and shuts the door if it sees two people with the same name.

There is a cost: to know whether there is more than one row, Dapper has to look at the second row. For a primary key lookup that cost is zero. But do not use QuerySingle on a query that can return millions of rows on the assumption that "only one will come back anyway"; then the problem lies in the SQL, before any method choice.

OrDefault and default(T)

The OrDefault methods return default(T) for zero rows. For classes that is null. Since Dapper 2.1 the API carries nullable reference type annotations, so QueryFirstOrDefault<Book> declares its return type as Book? and the compiler warns you:

var maybeBook = connection.QueryFirstOrDefault<Book>(
    "SELECT id, title, price FROM books WHERE id = @Id",
    new { Id = 42 });

Console.WriteLine(maybeBook.Title.Length);

Program.cs(10,19): warning CS8602: Dereference of a possibly null reference.

Take this warning seriously; at runtime it becomes a NullReferenceException. The right way is to take the result into a nullable variable and handle the "not found" case explicitly:

Book? missing = connection.QueryFirstOrDefault<Book>(
    "SELECT id, title, price FROM books WHERE id = @Id",
    new { Id = 42 });

Console.WriteLine(missing?.Title ?? "not found");

not found

With value types it gets sneakier. The default value of int is 0, and 0 is often valid data:

int stock = connection.QueryFirstOrDefault<int>(
    "SELECT stock FROM books WHERE id = @Id", new { Id = 3 });
int noRow = connection.QueryFirstOrDefault<int>(
    "SELECT stock FROM books WHERE id = @Id", new { Id = 42 });
int? maybe = connection.QueryFirstOrDefault<int?>(
    "SELECT stock FROM books WHERE id = @Id", new { Id = 42 });

Console.WriteLine($"{stock} | {noRow} | {maybe?.ToString() ?? "null"}");

0 | 0 | null

The first two values are the same: The Dispossessed really has a stock of 0, while book 42 does not exist at all. With int you cannot tell these two cases apart. When you make the target type int?, "no row" comes back as null and "row exists, value 0" as 0.

The same topic has a flip side: the row exists but the column is NULL. Let's try to read Exhalation's publication year with QuerySingle<int>:

try
{
    int year = connection.QuerySingle<int>(
        "SELECT published_year FROM books WHERE id = @Id", new { Id = 7 });
}
catch (DataException ex)
{
    Console.WriteLine(ex.Message);
}

int? knownYear = connection.QuerySingle<int?>(
    "SELECT published_year FROM books WHERE id = @Id", new { Id = 7 });

Console.WriteLine(knownYear?.ToString() ?? "unknown");

Error parsing column 0 (published_year=n/a - Null object cannot be converted to a value type.)
unknown

In the previous post a NULL column silently left an int property on a class at 0. In a single-column query where the target type itself is int, Dapper has no property to write to and throws a System.Data.DataException. The message gives the position and name of the offending column. The fix is the same as before: a nullable type for anything that can be NULL.

A Single Value with ExecuteScalar

Sometimes you do not need a row but a single value: a count, a total, a maximum. ExecuteScalar<T> returns the first column of the first row:

int bookCount = connection.ExecuteScalar<int>("SELECT COUNT(*) FROM books");
object? raw = connection.ExecuteScalar("SELECT COUNT(*) FROM books");

double? noTotal = connection.ExecuteScalar<double?>(
    "SELECT SUM(price) FROM books WHERE author_id = @AuthorId", new { AuthorId = 42 });
double zeroTotal = connection.ExecuteScalar<double>(
    "SELECT SUM(price) FROM books WHERE author_id = @AuthorId", new { AuthorId = 42 });

Console.WriteLine($"{bookCount} | {raw?.GetType().Name} | {noTotal?.ToString() ?? "null"} | {zeroTotal}");

string? topTitle = connection.ExecuteScalar<string>(
    "SELECT title, price FROM books ORDER BY price DESC");

Console.WriteLine(topTitle);

7 | Int64 | null | 0
Dune

The non-generic ExecuteScalar gives you the raw value the provider returned; on SQLite COUNT(*) is an Int64. ExecuteScalar<int> converts it to int for you. There is no column name to match here, so type conversion is allowed.

Watch the two values in the middle. In SQL, SUM returns NULL when no rows match. double? showed this honestly as null, while double turned it into 0. It may look inconsistent that QuerySingle<int> throws on NULL while ExecuteScalar<double> silently returns 0, but that is the real behavior of each; choose knowing which one you are using. If "no total" and "a total of zero" mean different things, use a nullable type; if not, write COALESCE(SUM(price), 0) in SQL and make your intent visible.

The last line shows what ExecuteScalar ignores: we selected two columns and got several rows, but only the first column of the first row came back. ExecuteScalar warns you about neither extra rows nor extra columns.

Common Pitfalls

First, QueryFirst without ORDER BY. It works in tests, because in small tables rows usually come back in insertion order. In production there is no ordering guarantee. Wherever you see QueryFirst, look for an ORDER BY.

Second, reading everything with QueryFirstOrDefault. Chosen "so that it does not throw", this method also stays silent when more than one row comes back and hides data bugs. If you look something up by a primary key or a unique field, use QuerySingle or QuerySingleOrDefault and let Dapper enforce the contract.

Third, OrDefault with value types. QueryFirstOrDefault<int> and ExecuteScalar<decimal> reduce "missing" and "zero" to the same value. If that distinction matters, use a nullable type.

Fourth, showing the exception from QuerySingle to the user. Sequence contains no elements tells nobody anything in an API response. If a missing record is an expected case, use OrDefault and turn it into a meaningful response such as a 404; keep the exception from QuerySingle for truly unexpected situations.

A Small Experiment

★☆☆ Use ExecuteScalar<int> to find the number of books with zero stock, and ExecuteScalar<double> to find the average price (AVG) of all books.

★★☆ Write a query that searches the authors table by the country column and try all four methods for 'USA', 'Poland' and 'Japan'. Which country produces an exception in which method? Guess first, then run it.

★★★ Add ORDER BY price DESC to the query in "Guess First" and watch a change. Then try to silence the exception in c with LIMIT, without breaking the query. Is this "fix" really a fix, or did you just switch off the contract?

In every query in this post we passed values as anonymous objects like new { Id = 4 } and never asked why. That is exactly what the next post is about: parameters, why building SQL by concatenating strings is off-limits, DynamicParameters, IN @ids and DbString. And the series does not end there; as new Dapper versions come out, we will keep going with new posts.