C# – Select a single row with Dapper

The simplest way to select a single row with Dapper is to use QuerySingleOrDefault() with a SELECT + WHERE query. You can pass in the parameters to specify which record to select and specify the model type. Here’s an example:

using Dapper;

Movie GetMovie(int id)
{
    using (var con = new SqlConnection(connectionString))
    {
        return con.QuerySingleOrDefault<Movie>("SELECT * FROM Movies WHERE Id=@id", 
            param: new { id });
    }
}
Code language: C# (cs)

The SQL query returns a single row from the Movies table and Dapper maps it and returns a Movie object.

Note: I’ll explain how to select a single value (instead of a single row) at the end.

QuerySingle() vs QueryFirst() (and the Default variants)

Dapper has four specialized methods for selecting a single row. These methods differ in how they handle a query returning no results and multiple results. The following table summarizes the differences and when to use which method:

	No results	Multiple results	When to use
QuerySingle()	InvalidOperationException	InvalidOperationException	The query *must* return 1 row.
QuerySingleOrDefault()	Returns null	InvalidOperationException	The query can return 0 or 1 row.
QueryFirst()	InvalidOperationException	Returns first row	The query *must* return 1 or more rows (and you just want the first one).
QueryFirstOrDefault()	Returns null	Returns first row	The query can return 0 or more rows (and you just want the first one).

You can use these methods all in the same way: pass in a SQL query, parameters, and model type. Use whichever method makes sense in your scenario.

Select a single value

Selecting a single value is different than selecting an entire row (multiple columns). The best way to select a single value with Dapper is to use ExecuteScalar() with a SELECT, specifying the primitive type of the single value. Here’s an example:

using Dapper;

int GetMovieCount()
{
    using (var con = new SqlConnection(connectionString))
    {  
        return con.ExecuteScalar<int>("SELECT COUNT(*) FROM Movies");
    }
}
Code language: C# (cs)

The SQL query returns the count of movies. ExecuteScalar() reads the single value and returns it. This is often used to select aggregate values or when you need a single column from a record (such as the movie title).

While you can use QuerySingle()/QueryFirst() (and variants) to get the same result, ExecuteScalar() is the right tool for the job.

• It’s specifically made to select single values (scalars).
• In my testing, it’s up to 25% faster than QuerySingle().