How to return dynamic values in Dapper

Question

I have a query where I am returning dynamic data and am unable to retrieve the values through Dapper as listed in their documentation (https://github.com/StackExchange/Dapper).

They have a sample of code like this:

var rows = connection.Query("select 1 A, 2 B union all select 3, 4");

Assert.Equal(1, (int)rows[0].A);

Yet, when I do the following, I cannot access any of the members of the result of my query:

var query = db.Query("SELECT SUM(UserRating) as 'Sum', AVG(UserRating) as 'Average', COUNT(*) as 'Total' FROM ActivityLogs");

query.FirstOrDefault(); // {{DapperRow, Sum= '3', Average = '3', Total = '1'}}
var sum = query[0].Sum; // error!

Error message:

Error CS0021: Cannot apply indexing with [] to an expression of type 'object'

How do I get to my fields?

Answer 1

The solution is to do something like this. You need to add the return type of the query to dynamic and then cast each row into an IDictionary<string, object>. Once you do that, you'll be able to get the value for your query by key like so:

IEnumerable<dynamic> query = db.Query<dynamic>("SELECT SUM(UserRating) as 'Sum', AVG(UserRating) as 'Average', COUNT(*) as 'Total' FROM ActivityLogs");
foreach (var rows in query)
{
    var fields = rows as IDictionary<string, object>;
    var sum = fields["Sum"];
    // ...
}

I hope it helps others!