Dapper.NET Parameter Syntax Reference List Expansions
Example
A common scenario in database queries is IN (...) where the list here is generated at runtime. Most RDBMS lack a good metaphor for this - and there is no universal cross-RDBMS solution for this. Instead, dapper provides some gentle automatic command expansion. All that is requires is a supplied parameter value that is IEnumerable. A command involving @foo is expanded to (@foo0,@foo1,@foo2,@foo3) (for a sequence of 4 items). The most common usage of this would be IN:
int[] orderIds = ...
var orders = connection.Query<Order>(@"
select *
from Orders
where Id in @orderIds", new { orderIds });
This then automatically expands to issue appropriate SQL for the multi-row fetch:
select *
from Orders
where Id in (@orderIds0, @orderIds1, @orderIds2, @orderIds3)
with the parameters @orderIds0 etc being added as values taken from the arrray.
Note that the fact that it isn't valid SQL originally is intentional, to ensure that this feature is not used mistakenly. This feature also works correctly with the OPTIMIZE FOR / UNKNOWN query-hint in SQL Server; if you use:
option (optimize for
(@orderIds unknown))
it will expand this correctly to:
option (optimize for
(@orderIds0 unknown, @orderIds1 unknown, @orderIds2 unknown, @orderIds3 unknown))
