How to create an anonymous object with property names determined dynamically?

Question

Given an array of values, I would like to create an anonymous object with properties based on these values. The property names would be simply "pN" where N is the index of the value in the array.

For example, given

object[] values = { 123, "foo" };

I would like to create the anonymous object

new { p0 = 123, p1 = "foo" };

The only way I can think of to do this would be to to use a switch or if chain up to a reasonable number of parameters to support, but I was wondering if there was a more elegant way to do this:

object[] parameterValues = new object[] { 123, "foo" };
dynamic values = null;

switch (parameterValues.Length)
{
    case 1:
        values = new { p0 = parameterValues[0] };
        break;
    case 2:
        values = new { p0 = parameterValues[0], p1 = parameterValues[1] };      
        break;
    // etc. up to a reasonable # of parameters
}

Background

I have an existing set of methods that execute sql statements against a database. The methods typically take a string for the sql statement and a params object[] for the parameters, if any. The understanding is that if the query uses parameters, they will be named @p0, @p1, @p2, etc..

Example:

public int ExecuteNonQuery(string commandText, CommandType commandType, params object[] parameterValues) { .... }

which would be called like this:

db.ExecuteNonQuery("insert into MyTable(Col1, Col2) values (@p0, @p1)", CommandType.Text, 123, "foo");

Now I would like to use Dapper within this class to wrap and expose Dapper's Query<T> method, and do so in a way that would be consistent with the existing methods, e.g. something like:

public IEnumerable<T> ExecuteQuery<T>(string commandText, CommandType commandType, params object[] parameterValues) { .... }

but Dapper's Query<T> method takes the parameter values in an anonymous object:

var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid }); 

leading to my question about creating the anonymous object to pass parameters to Dapper.

---

Adding code using the DynamicParameter class as requested by @Paolo Tedesco.

string sql = "select * from Account where Id = @p0 and username = @p1";
dynamic values = new DynamicParameter(123, "test");
var accounts = SqlMapper.Query<Account>(connection, sql, values);

throws an exception at line 581 of Dapper's SqlMapper.cs file:

using (var reader = cmd.ExecuteReader())

and the exception is a SqlException:

Must declare the scalar variable "@p0".

and checking the cmd.Parameters property show no parameters configured for the command.

Solution 1

You are misusing Dapper, you should never need to do this, instead either implement IDynamicParameters or use the specific extremely flexible DynamicParameters class.

In particular:

string sql = "select * from Account where Id = @id and username = @name";
var values = new DynamicParameters();
values.Add("id", 1);
values.Add("name", "bob");
var accounts = SqlMapper.Query<Account>(connection, sql, values);

DynamicParameters can take in an anonymous class in the constructor. You can concat DynamicParameters using the AddDynamicParams method.

Further more, there is no strict dependency on anon-types. Dapper will allow for concrete types as params eg:

class Stuff
{
   public int Thing { get; set; }
}

...

cnn.Execute("select @Thing", new Stuff{Thing = 1});

Kevin had a similar question: Looking for a fast and easy way to coalesce all properties on a POCO - DynamicParameters works perfectly here as well without any need for magic hoop jumping.

Solution 2

Not exactly an anonymous object, but what about implementing a DynamicObject which returns values for p1 ... pn based on the values in the array? Would that work with Dapper?

Example:

using System;
using System.Dynamic;
using System.Text.RegularExpressions;

class DynamicParameter : DynamicObject {

    object[] _p;

    public DynamicParameter(params object[] p) {
        _p = p;
    }

    public override bool TryGetMember(GetMemberBinder binder, out object result) {
        Match m = Regex.Match(binder.Name, @"^p(\d+)$");
        if (m.Success) {
            int index = int.Parse(m.Groups[1].Value);
            if (index < _p.Length) {
                result = _p[index];
                return true;
            }
        }
        return base.TryGetMember(binder, out result);
    }

}

class Program {
    static void Main(string[] args) {
        dynamic d1 = new DynamicParameter(123, "test");
        Console.WriteLine(d1.p0);
        Console.WriteLine(d1.p1);
    }
}

Solution 3

You cannot dynamically create anonymous objects. But Dapper should work with dynamic object. For creating the dynamic objects in a nice way, you could use Clay. It enables you to write code like

var person = New.Person();
person["FirstName"] = "Louis";
// person.FirstName now returns "Louis"

Comments

[Jeff Ogata]

+1, thanks, this is great but unfortunately Dapper doesn't like it :(. It ends up trying to execute the db command without any parameters at all -- not sure why yet, but I think it is trying to evaluate the DynamicParameter instance as a parameter value.

[Paolo Tedesco]

@adrift: can you post a small code sample that shows your problem?

[Jeff Ogata]

edited the question to include code showing the exception generated when trying to use DynamicParameter.