Selecting Single Rows Of Data With Dapper

Dapper provides a number of methods for selecting single rows of data, depending on how you want to work with the data that you retrieve.

Method Description Exception(s)
QuerySingle Use when only one row is expected to be returned. Returns a dynamic type InvalidOperationException, when the query returns zero or more than one element
QuerySingle<T> Use when only one row is expected to be returned. Returns an instance of the type specified by the T type parameter InvalidOperationException, when the query returns zero or more than one element
QuerySingleOrDefault Use when zero or one row is expected to be returned. Returns a dynamic type or null InvalidOperationException, when the query returns more than one element
QuerySingleOrDefault<T> Use when zero or one row is expected to be returned. Returns an instance of the type specified by the T type parameter or null InvalidOperationException, when the query returns more than one element
QueryFirst Returns the first of one or more rows as a dynamic type InvalidOperationException, when the query returns zero elements
QueryFirst<T> Returns the first of one or more rows as an instance of the type specified by the T type parameter InvalidOperationException, when the query returns zero elements
QueryFirstOrDefault Returns the first of one or more rows as a dynamic type or null if no results are returned
QueryFirstOrDefault<T> Returns the first of one or more rows as an instance of the type specified by the T type parameter or null if no results are returned

The QuerySingle methods

QuerySingle and QuerySingle<T>

The QuerySingle and QuerySingle<T> methods and their async counterparts are designed to be used when you expect only one row to match the criteria specified in the query. The difference between QuerySingle and QuerySingle<T> is that QuerySingle returns results as a dynamic type whereas QuerySingle<T> returns an instance of the type represented by T type argument. The following examples query the Products table from the Microsoft Northwind sample database. The schema of the table is as follows:

Northwind Products Table

The first example returns dynamic types:

using (var connection = new SQLiteConnection(connString))
{
    var sql = "select * from products where productid = 1";
    var product = connection.QuerySingle(sql);
    Console.WriteLine($"{product.ProductID} {product.ProductName}");
    Console.ReadLine();
}

The next example works with a Product type, which has the following definition based on the table schema:

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public int? SupplierId { get; set; }
    public int? CategoryId { get; set; }
    public string QuantityPerUnit { get; set; }
    public decimal UnitPrice { get; set; }
    public short? UnitsInStock { get; set; }
    public short? UnitsOnOrder { get; set; }
    public short? ReorderLevel { get; set; }
    public bool Discontinued { get; set; }
    public DateTime? DiscontinuedDate { get; set; }

    public Category Category { get; set; }
    public Supplier Supplier { get; set; }
    public ICollection<OrderDetail> OrderDetails { get; set; } = new HashSet<OrderDetail>();
}

The example is identical to the first one except for the presence of the <Product> type parameter:

using (var connection = new SQLiteConnection(connString))
{
    var sql = "select * from products where productid = 1";
    var product = connection.QuerySingle<Product>(sql);
    Console.WriteLine($"{product.ProductId} {product.ProductName}");
    Console.ReadLine();
}

Asynchronous Options

The asynchronous version of the Query method is QueryAsync:

using (var connection = new SQLiteConnection(connString))
{
    var sql = "select * from customers";
    var customers = await connection.QueryAsync(sql);
    foreach(var customer in customers)
    {
        Console.WriteLine($"{customer.CustomerID} {customer.CompanyName}");
    }
    Console.ReadLine();
}

The asynchronous version of the Query<T> method is QueryAsync<T>:

using (var connection = new SQLiteConnection(connString))
{
    var sql = "select * from customers";
    var customers = await connection.QueryAsync<Customer>(sql);
    foreach(var customer in customers)
    {
        Console.WriteLine($"{customer.CustomerId} {customer.CompanyName}");
    }
    Console.ReadLine();
}

Selecting A Subset Of Columns With Query

The examples thus far use the wildcard character (*) to include all columns in the target table. More often than not, you only want to retrieve data from a subset of columns. You do this by restricting the list of columns in the SQL to only include the ones that you want, but you can still pass the relevant object in to the type parameter of the Query<T> method. Properties of the object that aren't featured in the column list will be set to their default values. In the following example, the Discontinued column in the Products table is not included as part of the select statement, but it is used in a filter:

using (var connection = new SqlConnection(connString))
{
    var sql = "select ProductId, Productname from Products where Discontinued = 1";
    var products = connection.Query<Product>(sql);
    foreach (var product in products)
    {
        Console.WriteLine($"{product.ProductId} {product.ProductName}: {product.Discontinued}");
    }
    Console.ReadLine();
}

Despite the fact that only rows were selected where Discontinued is true, the output shows that the Discontinued property on the generated objects is false in all cases. This is because no value was made available for Dapper to map to the objects it created, so the property values are set to their defaults.

Image

If you try to reference a column that isn't included in the select list when returning dynamic types, the value will always be null:

using (var connection = new SqlConnection(connString))
{
    var sql = "select productId, productname from products where discontinued = 1";
    var products = connection.Query(sql);
    foreach (var product in products)
    {
        Console.WriteLine($"{product.productId} {product.productname}: {product.Discontinued}");
    }
    Console.ReadLine();
}

No value is printed to the output for the Discontinued property:

Image

Last updated: 21/05/2019 13:53:59