Dapper.NET Parameter Syntax Reference Value Inlining

Example

Sometimes the convenience of a parameter (in terms of maintenance and expressiveness), may be outweighed by its cost in performance to treat it as a parameter. For example, when page size is fixed by a configuration setting. Or a status value is matched to an enum value. Consider:

var orders = connection.Query<Order>(@"
select top (@count) * -- these brackets are an oddity of SQL Server
from Orders
where CustomerId = @customerId
and Status = @open", new { customerId, count = PageSize, open = OrderStatus.Open });

The only real parameter here is customerId - the other two are pseudo-parameters that won't actually change. Often the RDBMS can do a better job if it detects these as constants. Dapper has a special syntax for this - {=name} instead of @name - which only applies to numeric types. (This minimizes any attack surface from SQL injection). An example is as follows:

var orders = connection.Query<Order>(@"
select top {=count} *
from Orders
where CustomerId = @customerId
and Status = {=open}", new { customerId, count = PageSize, open = OrderStatus.Open });

Dapper replaces values with literals before issuing the SQL, so the RDBMS actually sees something like:

select top 10 *
from Orders
where CustomerId = @customerId
and Status = 3

This is particularly useful when allowing RDBMS systems to not just make better decisions, but to open up query plans that actual parameters prevent. For example, if a column predicate is against a parameter, then a filtered index with specific values on that columns cannot be used. This is because the next query may have a parameter apart from one of those specified values.

With literal values, the query optimizer is able to make use of the filtered indexes since it knows the value cannot change in future queries.