Operations · 3 min read

QueryFirst

This method queries a table and returns only the first row as a dynamic or TEntity object.

This method queries a table and returns only the first row as a TEntity object (or dynamic/ExpandoObject when targeting a table without a mapped entity). It is almost identical to Query, except that it returns the matched row directly instead of an IEnumerable.

No dedicated CreateQueryFirst method was added to IStatementBuilder. QueryFirst reuses the existing CreateQuery method to generate its command text — the same one Query uses — so custom statement builders keep working unchanged, with no new method to implement or override. Only the first row of the reader’s result is materialized before returning.

Code Snippets

The following example fetches a row from the [dbo].[Person] table by primary key.

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

Query via expression:

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

Or with compound conditions:

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

An EmptyException is thrown if the query did not return any row. If it returns more than one, the extra rows are silently ignored and only the first one is returned — use QuerySingle instead if a result with more than one row should be treated as an error.

Targeting a Table

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

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

Or via dynamics:

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.QueryFirst("[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.QueryFirst<Person>(e => e.Id == 10045,
        fields: fields);
}

Or via dynamics:

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.QueryFirst("[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.QueryFirst<string>(ClassMappedNameCache.Get<Person>(),
        new QueryField("Name", Operation.Like, "%Anders%"),
        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 ExecuteQueryFirst for those types.

Table Hints

Pass a table hint via the hints argument.

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

Or use the SqlServerTableHints class.

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.QueryFirst<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.QueryFirst<Person>(e => e.IsActive == true,
        orderBy: orderBy);
}

Since only one row is returned, orderBy is what decides which matching row comes back when more than one satisfies the filter — omitting it means the database is free to return any of them.

Limiting the Underlying Query

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

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.QueryFirst<Person>(e => e.IsActive == true,
        top: 10);
}

This does not change what is returned — QueryFirst always materializes only the first row of the result set. Passing top can still help the database engine short-circuit its own query plan for large tables.

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.QueryFirst<Person>(e => e.IsActive == true,
        cacheKey: "CacheKey:FirstActivePerson");
}

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