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.

Answer 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.