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

anonymous-types c# dapper

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.

Popular Answer

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.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why