Dapper.NET Pseudo-Positional Parameters (for providers that don't support named parameters)


Some ADO.NET providers (most notably: OleDB) do not support named parameters; parameters are instead specified only by position, with the ? place-holder. Dapper would not know what member to use for these, so dapper allows an alternative syntax, ?foo?; this would be the same as @foo or :foo in other SQL variants, except that dapper will replace the parameter token completely with ? before executing the query.

This works in combination with other features such as list expansion, so the following is valid:

string region = "North";
int[] users = ...
var docs = conn.Query<Document>(@"
     select * from Documents
     where Region = ?region?
     and OwnerId in ?users?", new { region, users }).AsList();

The .region and .users members are used accordingly, and the SQL issued is (for example, with 3 users):

     select * from Documents
     where Region = ?
     and OwnerId in (?,?,?)

Note, however, that dapper does not allow the same parameter to be used multiple times when using this feature; this is to prevent having to add the same parameter value (which could be large) multiple times. If you need to refer to the same value multiple times, consider declaring a variable, for example:

declare @id int = ?id?; // now we can use @id multiple times in the SQL

If variables are not available, you can use duplicate member names in the parameters - this will also make it obvious that the value is being sent multiple times:

int id = 42;
connection.Execute("... where ParentId = $id0$ ... SomethingElse = $id1$ ...",
      new { id0 = id, id1 = id });