Operations · 4 min read

ExecuteQuerySingle

This method executes a raw SQL statement directly against the database and returns the single row as a dynamic or TEntity object, enforcing that exactly one row is returned. It supports all RDBMS data providers.

This method executes a raw SQL statement directly against the database and returns the single row as a TResult object (or dynamic/ExpandoObject when no generic type is given). Unlike ExecuteQueryFirst, it enforces that the statement returns exactly one row. It supports all RDBMS data providers.

It behaves like calling ExecuteQuery<TResult>(...).Single() — use it whenever more than one matching row would indicate a bug or a data-integrity problem you want surfaced immediately, rather than silently taking the first row like ExecuteQueryFirst does.

Code Snippets

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

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

Returning a dynamic object (typed as ExpandoObject):

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

Returning an ExpandoObject:

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

Returning an IDictionary<string, object>:

using (var connection = new SqlConnection(connectionString))
{
    var person = connection.ExecuteQuerySingle<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, and a MultipleRowsFoundException is thrown if it returns more than one. Use ExecuteQueryFirst instead if extra matching rows should simply be ignored.

Typed Result

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

using (var connection = new SqlConnection(connectionString))
{
    var id = connection.ExecuteQuerySingle<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.ExecuteQuerySingle<string>("SELECT Name FROM [dbo].[Person] WHERE Id = 10045;");
    var dateOfBirth = connection.ExecuteQuerySingle<DateTime>("SELECT DateOfBirth FROM [dbo].[Person] WHERE Id = 10045;");
}

Enum types are also supported:

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

// Inferring
using (var connection = new SqlConnection(connectionString))
{
    var gender = connection.ExecuteQuerySingle<Gender>("SELECT Gender FROM [dbo].[Person] WHERE Id = 10045;");
}

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.ExecuteQuerySingle<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.ExecuteQuerySingle<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.ExecuteQuerySingle<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.ExecuteQuerySingle<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.ExecuteQuerySingle<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.ExecuteQuerySingle<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.ExecuteQuerySingle<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.ExecuteQuerySingle<Person>("SELECT * FROM [dbo].[Person] WHERE SocialSecurityNumber IN (@Keys);", param);
}

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

An IN clause can easily match more than one row. Only use it here when the surrounding condition (or the data itself) guarantees at most one match — otherwise a MultipleRowsFoundException is thrown.

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.ExecuteQuerySingle<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.ExecuteQuerySingle<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.