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!

Answer 2

If you are using C# 7 or higher, you can also use value tuples to map the returned rows. This avoids usage of dynamic, and has better type safety.

The query in question may then look like this (line breaks added to prevent scrolling):

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

var firstSum = query.FirstOrDefault().Sum;