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

Leave a Comment