How to get the value from DapperRow in a query that returns a single result

Question

I've done the following SQL query:

SELECT SUM(SI.UnitaryValue) as Amount 
FROM Services as S
INNER JOIN ServicesItems as SI ON S.ServiceId = SI.ServiceId        
WHERE S.ServiceId = @ID"

In the query: Services has a collection of Service Items. Then, the query returns a single cell with the sum of the unit values of each service item.

Here's my code with Dapper: (Connection is an object that represents my connection string to the database)

using (var cn = Connection)
{
    var sql = @"SELECT SUM(SI.UnitaryValue) as Amount 
        FROM Services as S
        INNER JOIN ServicesItems as SI ON S.ServiceId = SI.ServiceId        
        WHERE S.ServiceId = @ID";

    cn.Open();
    var result = cn.Query(sql, new { ID = serviceId }).SingleOrDefault();

    // ... ??

    return Amount;
}

Amount type must be decimal.

When I run in Debug mode, and analyzing the Locals window I get two possible values:

1- {{DapperRow, Amount = '128.42'}} - when there are records for the research.

2- {{DapperRow, Amount = NULL}} - when there are no records.

I know Dapper's return is a type: dynamic {Dapper.SqlMapper.DapperRow}

I can not access the returned data and recover Amount.

I already looked in https://github.com/StackExchange/Dapper and did not "see" the answer. At least, what I found and tried to implement did not work out.

I found this post here Dapper: How to get value from DapperRow if column name is "count(*)"?, but since the code presented in the question is partial and in response too. I could not implement correctly. He made a mistake too.

I tried this code, but it did not work:

var result = (IDictionary<string,object>)cn.Query(sql, 
             new { ID = serviceId }).SingleOrDefault();

return result["Amount"]

---

Edited after Darthchai's solution:

After I wrote your code with "dynamic", Visual Studio required me to add the reference 'Microsoft.CSharp'. After that, ReSharper suggested changing the "dynamic" to "var".

So for the record, the final code looks like this:

cn.Open();
var result = cn.Query(sql, new { ID = serviceId }).SingleOrDefault();

decimal amount = (result != null && result?.Amount == null) ? 0 : result?.Amount;

return amount;

Answer 1

You can use the dynamic type:

dynamic result = cn.Query(sql, 
         new { ID = serviceId }).SingleOrDefault()

 return result.Amount

Or if you don't want to use dynamic, you could create a class to hold your result, as below

class DbResult
{
    public decimal Amount { get; set; }
}

Then when setting your result variable pass the class into the dapper query method as seen below:

   var result = cn.Query<DbResult>(sql, 
     new { ID = serviceId }).SingleOrDefault()