Hints

This feature allows you to optimize command execution against the database by embedding hint keywords into SQL DML statements. See the Microsoft documentation here.

Raw-SQL

For raw SQL, hints are included directly in the SQL text you provide.

The following example queries dirty data from the [dbo].[Order] table:

using (var connection = new SqlConnection(connectionString))
{
    var orders = connection.ExecuteQuery<Order>("SELECT * FROM [dbo].[Order] WITH (NOLOCK);");
}

The following example acquires a table lock on [dbo].[Person] during an insert:

var person = new
{
    Name = "John Doe",
    BirthDay = DateTime.Parse("1970-01-01"),
    IsActive = true
};
using (var connection = new SqlConnection(connectionString))
{
    var affectedRows = connection.ExecuteNonQuery("INSERT INTO [dbo].[Person] WITH (TABLOCK) ([Name], [DateOfBirth], [IsActive], [CreatedDateUtc]) VALUES (@Name, @BirthDay, @IsActive, GETUTCDATE());");
}

Fluent-Methods

Most operations accept a hints argument that accepts a literal string, giving full control over query optimization.

The following are the fluent-method equivalents of the raw SQL examples above:

using (var connection = new SqlConnection(connectionString))
{
    var orders = connection.QueryAll<Order>(hints: "WITH (NOLOCK)");
}

Acquiring a table lock during an insert:

var person = new
{
    Name = "John Doe",
    BirthDay = DateTime.Parse("1970-01-01"),
    IsActive = true
};
using (var connection = new SqlConnection(connectionString))
{
    var id = connection.Insert<Person>(person, hints: "WITH (TABLOCK)");
}

The SqlServerTableHints class can also be used to pass hints:

var person = new
{
    Name = "John Doe",
    BirthDay = DateTime.Parse("1970-01-01"),
    IsActive = true
};
using (var connection = new SqlConnection(connectionString))
{
    var id = connection.Insert<Person>(person, hints: SqlServerTableHints.TabLock);
}

The hints argument is only supported for SQL Server. Passing hints for other RDBMS providers will throw an exception.