Operations · 4 min read

ExecuteQueryFirst

This method executes a raw SQL statement directly against the database and returns only the first row as a dynamic or TEntity object. It supports all RDBMS data providers.

This method executes a raw SQL statement directly against the database and returns only the first row as a TResult object (or dynamic/ExpandoObject when no generic type is given). It supports all RDBMS data providers.

It behaves like calling ExecuteQuery<TResult>(...).First(), but stops reading as soon as the first row is fetched instead of materializing the full result set into an IEnumerable — use it whenever the statement is expected to return at least one row and you only care about the first.

Code Snippets

The following example queries the first row that matches from the [dbo].[Person] table.

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.ExecuteQueryFirst<Person>("SELECT * FROM [dbo].[Person] WHERE Id = 10045;");
}

Returning a dynamic object (typed as ExpandoObject):

using (var connection = new SqlConnection(connectionString))
{
    var dynamicPerson = connection.ExecuteQueryFirst("SELECT * FROM [dbo].[Person] WHERE Id = 10045;");
}

Returning an ExpandoObject:

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.ExecuteQueryFirst<ExpandoObject>("SELECT * FROM [dbo].[Person] WHERE Id = 10045;");
}

Returning an IDictionary<string, object>:

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.ExecuteQueryFirst<IDictionary<string, object>>("SELECT * FROM [dbo].[Person] WHERE Id = 10045;");
}

An anonymous type can also be passed as the generic type TResult. This is particularly useful in F#.

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

Typed Result

The result can be inferred as any .NET CLR type.

using (var connection = new SqlConnection(connectionString))
{
    var id = connection.ExecuteQueryFirst<long>("SELECT Id FROM [dbo].[Person] WHERE Name = 'John Doe';");
}

Or for other types such as string and System.DateTime:

using (var connection = new SqlConnection(connectionString))
{
    var name = connection.ExecuteQueryFirst<string>("SELECT Name FROM [dbo].[Person] WHERE Id = 10045;");
    var dateOfBirth = connection.ExecuteQueryFirst<DateTime>("SELECT DateOfBirth FROM [dbo].[Person] WHERE Name = 'John Doe';");
}

Enum types are also supported:

// Enumeration
public enum Gender
{
    Male = 1,
    Female = 2
}

// Inferring
using (var connection = new SqlConnection(connectionString))
{
    var gender = connection.ExecuteQueryFirst<Gender>("SELECT Gender FROM [dbo].[Person] WHERE Name = 'John Doe';");
}

Enumeration inference works for both string column types (e.g., NVARCHAR, TEXT) and non-string column types (e.g., SMALLINT, INT, BIGINT).

Table-Valued Parameters

To execute a Table-Valued Parameter (TVP), create a DataTable and set its name to the name of the User-Defined Type (UDT).

Refer to the Microsoft guidelines for creating a TVP/UDT and calling it from C#/ADO.NET.

var table = new DataTable();
table.TableName = "Name of the UDT";
// Create the 'table' columns/rows

Pass it as an argument value:

using (var connection = new SqlConnection(connectionString))
{
    var result = connection.ExecuteQueryFirst<IdentityTable>("EXEC [sp_StoredProcedureName] @Table = @Table;",
        new { Table = table });
}

Passing of Parameters

Parameters can be passed via any of the following types:

IDbDataParameter
Anonymous Types
ExpandoObject
IDictionary<string, object>
QueryField/QueryGroup

IDbDataParameter

using (var connection = new SqlConnection(connectionString))
{
    var param = new
    {
        Id = new SqlParameter("_", 10045)
    };
    var person = connection.ExecuteQueryFirst<Person>("SELECT * FROM [dbo].[Person] WHERE Id = @Id;", param);
}

The parameter name is not required. The library replaces it with the actual property name from the object.

Anonymous Types

using (var connection = new SqlConnection(connectionString))
{
    var param = new
    {
        Id = 10045
    };
    var person = connection.ExecuteQueryFirst<Person>("SELECT * FROM [dbo].[Person] WHERE Id = @Id;", param);
}

ExpandoObject

using (var connection = new SqlConnection(connectionString))
{
    var param = new ExpandoObject() as IDictionary<string, object>;
    param.Add("Id", 10045);
    var person = connection.ExecuteQueryFirst<Person>("SELECT * FROM [dbo].[Person] WHERE Id = @Id;", param);
}

IDictionary<string, object>

using (var connection = new SqlConnection(connectionString))
{
    var param = new Dictionary<string, object>
    {
        { "Id", 10045 }
    };
    var person = connection.ExecuteQueryFirst<Person>("SELECT * FROM [dbo].[Person] WHERE Id = @Id;", param);
}

QueryField/QueryGroup

using (var connection = new SqlConnection(connectionString))
{
    var param = new []
    {
        new QueryField("Id", 10045)
    };
    var person = connection.ExecuteQueryFirst<Person>("SELECT * FROM [dbo].[Person] WHERE Id = @Id;", param);
}

Or via QueryGroup.

using (var connection = new SqlConnection(connectionString))
{
    var param = new QueryGroup(new []
    {
        new QueryField("Id", 10045)
    });
    var person = connection.ExecuteQueryFirst<Person>("SELECT * FROM [dbo].[Person] WHERE Id = @Id;", param);
}

Array Parameters (for the IN keyword)

Pass an array of values when using the IN keyword.

using (var connection = new SqlConnection(connectionString))
{
    var param = new
    {
        Keys = new [] { 10045, 10102, 11004 }
    };
    var person = connection.ExecuteQueryFirst<Person>("SELECT * FROM [dbo].[Person] WHERE Id IN (@Keys);", param);
}

Any of the parameter types listed in Passing of Parameters can also be used here.

Executing a Stored Procedure

There are two ways to execute a stored procedure. Pass the stored procedure name and set the command type to CommandType.StoredProcedure:

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.ExecuteQueryFirst<Person>("[dbo].[sp_GetPerson]",
        new { Id = 10045 }, commandType: CommandType.StoredProcedure);
}

Or use a native SQL EXEC call:

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.ExecuteQueryFirst<Person>("EXEC [dbo].[sp_GetPerson](@Id);",
        new { Id = 10045 });
}

In the second call, the command text ends with a semicolon and no command type is set.