# Query and QuerySingle

Query<T> reads many rows into objects; QuerySingle, QuerySingleOrDefault, QueryFirst and QueryFirstOrDefault read one; ExecuteScalar<T> reads one value. Pick by how many rows you expect.

Journeyman · C# · T-SQL · Updated November 3, 2026

## 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:

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

## Many rows: Query&lt;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.

```csharp
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($"{sauces.Count} sauces");
foreach (Product sauce in sauces)
{
    Console.WriteLine($"  {sauce.ProductName}");
}

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

```text
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:

| Method | No rows | One row | Two or more rows |
|---|---|---|---|
| `QuerySingle<T>` | Throws | Returns it | Throws |
| `QuerySingleOrDefault<T>` | Returns `null` | Returns it | Throws |
| `QueryFirst<T>` | Throws | Returns it | Returns the first |
| `QueryFirstOrDefault<T>` | Returns `null` | Returns it | Returns the first |

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

```csharp
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; }
}
```

```text
Chai
True
```

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

```csharp
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; }
}
```

```text
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&lt;T>

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

```csharp
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($"{count} beverages");
```

```text
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&lt;T>()
- QuerySingleOrDefault&lt;T>()
- ExecuteScalar&lt;T>()

## Cheat sheets

- [Query and QuerySingle](https://www.ask-codey.com/cheat-sheets/dapper-query-and-querysingle-cheat-sheet)

Source: https://www.ask-codey.com/lessons/dapper-query-and-querysingle