Operations · 3 min read

QuerySingle

This method queries a table and returns the single row as a dynamic or TEntity object, enforcing that exactly one row is returned.

This method queries a table and returns the single row as a TEntity object (or dynamic/ExpandoObject when targeting a table without a mapped entity). Unlike QueryFirst, it enforces that the query returns exactly one row.

No dedicated CreateQuerySingle method was added to IStatementBuilder. QuerySingle reuses the existing CreateQuery method to generate its command text — the same one Query and QueryFirst use — so custom statement builders keep working unchanged, with no new method to implement or override.

Code Snippets

The following example fetches the single row that matches from the [dbo].[Person] table.

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.QuerySingle<Person>(10045);
}

Query via expression:

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.QuerySingle<Person>(e => e.Id == 10045);
}

Or with compound conditions:

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.QuerySingle<Person>(
        e => e.FirstName == "John" && e.LastName == "Doe");
}

An EmptyException is thrown if the query did not return any row, and a MultipleRowsFoundException is thrown if it returned more than one. Use QueryFirst instead if extra matching rows should simply be ignored.

Targeting a Table

To target a specific table, pass the literal table name.

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.QuerySingle<Person>("[dbo].[Person]",
        10045);
}

Or via dynamics:

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.QuerySingle("[dbo].[Person]",
        10045);
}

The result is a dynamic object of type ExpandoObject.

Specific Columns

To query specific columns, pass a list of fields in the fields argument.

using (var connection = new SqlConnection(connectionString))
{
    var fields = Field.Parse<Person>(e => new
    {
        e.Id,
        e.Name,
        e.DateOfBirth,
        e.DateInsertedUtc
    });
    var person = connection.QuerySingle<Person>(e => e.Id == 10045,
        fields: fields);
}

Or via dynamics:

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.QuerySingle("[dbo].[Person]",
        new { Id = 10045 },
        fields: Field.From("Id", "Name", "DateOfBirth", "DateInsertedUtc"));
}

Type Result

The result can be inferred directly as a string type.

using (var connection = new SqlConnection(connectionString))
{
    var name = connection.QuerySingle<string>(ClassMappedNameCache.Get<Person>(),
        new QueryField("Id", 10045),
        fields: Field.From(nameof(Person.Name)));
}

Type inference works for string but not for other non-class types (e.g., long, int, System.DateTime), since TEntity is constrained to class. Use ExecuteQuerySingle for those types.

Table Hints

Pass a table hint via the hints argument.

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.QuerySingle<Person>(10045,
        hints: "WITH (NOLOCK)");
}

Or use the SqlServerTableHints class.

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.QuerySingle<Person>(10045,
        hints: SqlServerTableHints.TabLock);
}

Ordering the Results

Pass an array of OrderField objects in the orderBy argument.

using (var connection = new SqlConnection(connectionString))
{
    var orderBy = OrderField.Parse(new
    {
        LastName = Order.Descending,
        FirstName = Order.Ascending
    });
    var person = connection.QuerySingle<Person>(e => e.SocialSecurityNumber == "123-45-6789",
        orderBy: orderBy);
}

orderBy has no effect on whether a MultipleRowsFoundException is thrown — it only controls which row’s data is used for the error-free, single-match case. Pair QuerySingle with a filter that is expected to match at most one row.

Limiting the Underlying Query

The top argument is still accepted and forwarded to the generated command text, the same way it is for Query and QueryFirst.

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.QuerySingle<Person>(e => e.Id == 10045,
        top: 10);
}

Setting top to a value greater than 1 does not relax the single-row requirement — if the underlying query still returns more than one row within that cap, a MultipleRowsFoundException is thrown.

Caching the Result

Pass a literal string key in the cacheKey argument to cache the result.

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.QuerySingle<Person>(e => e.Id == 10045,
        cacheKey: "CacheKey:Person:10045");
}

The default cache expiration is 180 minutes. Override it by passing an integer value in the cacheItemExpiration argument.