C# – Select a single row with Dapper

by

When you want to select a single row with Dapper, the simplest option is to use QuerySingleOrDefault() with a SELECT + WHERE query (and pass in the parameters / model type like usual). Here’s an example:

using Dapper;

public 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)

This 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)

Normally you’d use Dapper’s Query() method to execute SQL queries when you expect zero or more rows in the results. But Dapper has four specialized methods when you want just one 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 resultsMultiple resultsWhen to use
QuerySingle()InvalidOperationExceptionInvalidOperationExceptionThe query *must* return 1 row.
QuerySingleOrDefault()Returns nullInvalidOperationExceptionThe query can return 0 or 1 row.
QueryFirst()InvalidOperationExceptionReturns first rowThe query *must* return 1 or more rows (and you just want the first one).
QueryFirstOrDefault()Returns nullReturns first rowThe 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;

public 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().

Related Articles