Nested objects in Dapper Query parameter

Question

I have an object like the following and I would like to send it as a parameter for a Dapper query.

var person = new {
    Name = "John",
    Company = new {
        Name = "ACME"
    }
};

var res = connection.Query("select concat(@Name, @Company_Name) as concatted", person);

My problem is that the @Company_Name does not get used. I tried replacing it with @Company.Name, but that gave an AggregateException, with a SystemNotSupportedException: The member Company of type ..... cannot be used as a parameter value.

What is the correct way to use the nested object of the passed param object inside the query?

Answer 1

You'll need to match the query parameter names in your param object:

 var res = conn.Query("select concat(@Name, @Company_Name) as concatted",
            new { person.Name, Company_Name = person.Company.Name });

Edit: No, you can't pass in the object as it is as dapper does a very straight-forward sql-param to object param mapping.

It has to be either of type: IDynamicParameters , IEnumerable<KeyValuePair<string, object>> or an anonymous object where the param values are evaluated based on the name of the sql param literals. See https://github.com/StackExchange/Dapper/blob/a18dc63c688b5173c5dde8b1243b74e26fa262c4/Dapper/SqlMapper.cs#L2367 on how its implemented.