# Query and QuerySingle

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

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

By Gregor Dzierzon

## After this lesson you can

- Read many rows with Query&lt;T>, one row with QuerySingleOrDefault&lt;T>, and one value with ExecuteScalar&lt;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.)

## Do it: pick the reading method for a one-row query

Every DAO method that reads one row has to pick one of the four methods from the table above. The choice comes down to two yes-or-no questions about the query. Write a method that takes the two answers and names the Dapper method to call.

The method gets two values:

- `noRowsIsNormal`, a `bool`: `true` when finding no row is a normal answer (give back `null`); `false` when a missing row means something is wrong (throw).
- `manyRowsIsNormal`, a `bool`: `true` when two or more rows are normal and you want the first one (the query has an `ORDER BY`); `false` when two or more rows mean something is wrong (throw).

For example, a DAO's `GetById(int productId)`. A product with that id may not exist, so no rows is normal. Two products can never share an id, so many rows is not normal:

| `noRowsIsNormal` | `manyRowsIsNormal` | Method |
|---|---|---|
| `true` | `false` | `QuerySingleOrDefault` |

So `ChooseMethod(true, false)` returns `"QuerySingleOrDefault"`.

**Your job:** write the method `string ChooseMethod(bool noRowsIsNormal, bool manyRowsIsNormal)` so it returns the method's name, as a `string`, for all four pairs of answers:

- `false`, `false` returns `"QuerySingle"`: the row must exist, and there's only ever one (a product your code has just added, read back by its new id).
- `true`, `false` returns `"QuerySingleOrDefault"`: the example, `GetById`.
- `false`, `true` returns `"QueryFirst"`: the first of rows that must exist (the most expensive product, sorted by price).
- `true`, `true` returns `"QueryFirstOrDefault"`: the first row, if there is one (a customer's newest order; a new customer has none).

Practiced live on the lesson page: write the C# right there and check it.

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