Using numerical parameter placeholders in Dapper

Question

Just started working with Dapper (from Nuget) and couldn't figure out the following:

This works:

_connection.Query<MySalesPerson>(@"select * from Sales.SalesPerson where territoryId = @id",
                    new {id = territory.TerritoryID});

This doesn't works:

_connection.Query<MySalesPerson>(@"select * from Sales.SalesPerson where territoryId = @0",
                    new {id = territory.TerritoryID});

So it's not taking up the numerical placeholders, it this the standard behavior or am I missing out something. Same works like a breeze with PetaPoco

score 1 · Accepted Answer · edited May 23 '17 at 12:15

Dapper uses an object (not a list) for query parameters. It means it can't (reliably) use indices to obtain property values (because, formally, property order in an object is unspecified).

In detail you should check CreateParamInfoGenerator() method, emitted code uses GetProperties() to read all public parameters from your object. There isn't much you can do unless you fork and change it.

Code to transform parameter index to property name is straightforward, property ordering may be achieved with code from C# Get FieldInfos/PropertyInfos in the original order?

Note that GetProperties() doesn't support tricky usages (for example implementing IDynamicMetaObjectProvider to map property names to indices however you're able to emit your own type from an array of values. Note that member name limitations are set by language, not by CLR or by CIL then you can create a type with properties which name is a digit. This is a proof of concept:

object CreatePropertiesFromValues(params object[] args) {
   // Code to emit new type...

    int index = 0;
    foreach (object arg in args) {
        var name = index.ToString();
        var type = typeof(object); // We don't need strongly typed object!
        var field = typeBuilder.DefineField("_" + name, type, FieldAttributes.Private);

        var property = typeBuilder.DefineProperty(name, PropertyAttributes.HasDefault, type, null);

        var method = typeBbuilder.DefineMethod("get_" + name,
            MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig,
            type, Type.EmptyTypes);

        var generator = method.GetILGenerator();
        generator.Emit(OpCodes.Ldarg_0);
        generator.Emit(OpCodes.Ldfld, field);
        generator.Emit(OpCodes.Ret);

        property.SetGetMethod(method);

        ++index;
    }

    // Code to create an instance of this new type and to set
    // property values (up to you if adding a default constructor
    // with emitted code to initialize each field or using _plain_
    // Reflection).
}

Now you can use it like this:

_connection.Query<MySalesPerson>(@"select * from Sales.SalesPerson where territoryId = @0", 
    CreatePropertiesFromValues(territory.TerritoryID));

Well...it's always funny to play with Reflection Emit but it's a lot of work just to add support for positional parameters. It may be easier to change Dapper code (even if that function is, honestly, a big convoluted mess).

As final note...now we also have Roslyn then we may know order in which properties are declared (and maybe even more) however so far I didn't play with it...

Thanks for the detailed explanation, let me see how I can fit in the same stuff — Mrinal Kamboj, Sep 25 '15 at 10:42

Using numerical parameter placeholders in Dapper

1 Answers1