Query and QuerySingle

After this lesson you can

• Read many rows with Query<T>, one row with QuerySingleOrDefault<T>, and one value with ExecuteScalar<T>
• Choose between QuerySingle, QuerySingleOrDefault, QueryFirst and QueryFirstOrDefault by how many rows you expect

Key takeaways

• Query<T> reads every row as a T; add .ToList() to get a List<T>. No rows gives an empty list, not an error.
• QuerySingleOrDefault<T> returns null for no rows and throws for two or more, which makes it the right choice for a GetById.
• QuerySingle<T> throws when no row comes back, so use it only when the row must exist.
• QueryFirst and QueryFirstOrDefault return the first of several rows; pair them with ORDER BY.
• ExecuteScalar<T> reads one value, such as a count, already as a T, with no cast.

Dapper's reading methods differ in one thing: how many rows you expect back. Choosing the right one turns a wrong number of rows into a clear error instead of a quiet bug.

All the examples use this model:

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; } = "";
    public decimal? UnitPrice { get; set; }
}

Many rows: Query<T>

Query<T> returns every row, each as a T. It gives back an IEnumerable<T>; add .ToList() to get a List<T>, which is what your DAOs return.

using Dapper;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";
using SqlConnection connection = new SqlConnection(connectionString);

List<Product> sauces = connection.Query<Product>(
    "SELECT ProductId, ProductName, UnitPrice FROM Products WHERE ProductName LIKE '%Sauce%' ORDER BY ProductName;").ToList();

Console.WriteLine(quot;{sauces.Count} sauces");
foreach (Product sauce in sauces)
{
    Console.WriteLine(quot;  {sauce.ProductName}");
}

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; } = "";
    public decimal? UnitPrice { get; set; }
}

2 sauces
  Louisiana Fiery Hot Pepper Sauce
  Northwoods Cranberry Sauce

No rows is fine: Query returns an empty list.

One row: QuerySingle and friends

When you expect one row, such as looking up a product by its id, there are four methods. They differ in what they do when the number of rows isn't one:

A DAO's GetById uses QuerySingleOrDefault: there may be no such product (return null), but there can never be two with one id (if there were, something is badly wrong, and an exception says so).

using Dapper;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";
using SqlConnection connection = new SqlConnection(connectionString);

string sql = "SELECT ProductId, ProductName, UnitPrice FROM Products WHERE ProductId = @ProductId;";

Product? chai = connection.QuerySingleOrDefault<Product>(sql, new { ProductId = 1 });
Product? nothing = connection.QuerySingleOrDefault<Product>(sql, new { ProductId = 999 });

Console.WriteLine(chai?.ProductName);
Console.WriteLine(nothing is null);

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; } = "";
    public decimal? UnitPrice { get; set; }
}

Chai
True

QuerySingle is stricter: a missing row is an error, for when the row must exist.

using Dapper;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";
using SqlConnection connection = new SqlConnection(connectionString);

Product product = connection.QuerySingle<Product>(
    "SELECT ProductId, ProductName, UnitPrice FROM Products WHERE ProductId = @ProductId;", new { ProductId = 999 });

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; } = "";
    public decimal? UnitPrice { get; set; }
}

System.InvalidOperationException: Sequence contains no elements

QueryFirst and QueryFirstOrDefault don't mind extra rows; use them with ORDER BY when you want "the top one", such as the newest order.

One value: ExecuteScalar<T>

For a single value, such as a count, use ExecuteScalar<T>:

using Dapper;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";
using SqlConnection connection = new SqlConnection(connectionString);

int count = connection.ExecuteScalar<int>(
    "SELECT COUNT(*) FROM Products WHERE CategoryId = @CategoryId;", new { CategoryId = 1 });

Console.WriteLine(quot;{count} beverages");

12 beverages

No casting: ExecuteScalar<int> returns an int. (QuerySingle<int> would work too.)

Check yourself

2 questions to try on the lesson page.

So what

Many rows, Query; one or none, QuerySingleOrDefault; one value, ExecuteScalar. Picking by how many rows you expect means the wrong number is caught where it happens.

Key terms

• Query<T>()
• QuerySingleOrDefault<T>()
• ExecuteScalar<T>()

Cheat sheets

• Query and QuerySingle