Retrieve a Single Row with Dapper in C#

By FoxLearn 1/18/2025 2:53:30 AM 32

When you need to retrieve a single row from a database using Dapper, the simplest approach is to use the QuerySingleOrDefault() method.

C# Retrieve a Single Row with Dapper

This method works with a SELECT query combined with a WHERE clause, and you can pass in parameters or a model type as usual.

using Dapper;

public Book GetBook(int bookId)
{
    using (var connection = new SqlConnection(connectionString))
    {
        return connection.QuerySingleOrDefault<Book>("SELECT * FROM Books WHERE BookId = @bookId", 
            param: new { bookId });
    }
}

In this example, the SQL query fetches a single row from the Books table where the BookId matches the provided parameter. Dapper automatically maps the result to a Book object. If no match is found, it will return null because of the QuerySingleOrDefault() method.

QuerySingle() vs. QueryFirst() (and their Default Variants)

Dapper provides several specialized methods when you are expecting just one row from a query.

No results	Multiple results	When to use
`QuerySingle()`	Throws `InvalidOperationException`	When the query must return exactly 1 row.
`QuerySingleOrDefault()`	Returns `null`	When the query can return 0 or 1 row, and you can handle `null`.
`QueryFirst()`	Returns the first row	When the query must return at least 1 row, but you're interested in just the first one.
`QueryFirstOrDefault()`	Returns `null`	When the query can return 0 or more rows, and you want the first one or `null` if none exist.

All of these methods work similarly: you provide a SQL query, parameters, and a model type.

Select a Single Value

If you need to retrieve a single value (like a count or a specific column value), it’s best to use ExecuteScalar().

using Dapper;

public decimal GetTotalPrice()
{
    using (var connection = new SqlConnection(connectionString))
    {
        return connection.ExecuteScalar<decimal>("SELECT SUM(Price) FROM Products");
    }
}

In this case, the SQL query calculates the total price of all products by summing the Price column. ExecuteScalar() returns the scalar value directly (in this case, a decimal). This is the best choice when you need a single value from the database, like an aggregate or a single column result.

While QuerySingle() or QueryFirst() could also be used in some scenarios, ExecuteScalar() is designed specifically for this purpose and tends to be faster up to 25% faster than using QuerySingle() in some tests.