Dapper - how to work with dynamic objects

Question

I'm using Dapper to query from SQL and have a dynamic query as such:

var returns = conn.Query(dynamicQuery);

When I then cycle through the results, I would like to find out what the type of date I am handling is so I tried doing the following:

foreach (var result in results)
{
    MessageBox.Show(result.GetType().ToString());
}

But it always fails on the MessageBox with the error Cannot perform runtime binding on a null reference.

If I use this instead:

var returns = conn.Query<object>(dynamicQuery);

Then the command works, but it gives me a Dapper.SqlMapper+DapperRow object type.

How can I find the type of a dynamic variable?

Have you tried `foreach (var result in results.Where(r=>r!=null))` — Alberto, Nov 05 '13 at 17:18
Same error, also I'm only returning one row on my query which I know is of data type `DateTime` — cogumel0, Nov 05 '13 at 17:26
null ref means nothing returned. Have you tried checking returns!=null first? — Macke, Jan 04 '16 at 08:02

score 21 · Accepted Answer · answered Nov 05 '13 at 19:14

21

With the dynamic api, it is expected that you know the shape in terms of columns, i.e.

foreach(dynamic row in query) {
    int id = row.Id;
    //...
}

However, each row also implements IDictionary<string, object> if things are less clear: so cast to that.

Alternatively, if (comments) you know there is a single cell of type date time:

var when = conn.Query<DateTime>(...).Single();

answered Nov 05 '13 at 19:14

Marc Gravell

1,026,079
266
2,566
2,900

If there is no result, will a FirstOrDefault return null into when? – arviman Jan 20 '16 at 11:33
1

@arviman yes, `Query(...).FirstOrDefault()` or `QueryFirstOrDefault(...)` will return `null` if there are zero rows. – Marc Gravell Jan 20 '16 at 14:34
@MarcGravell I confirmed this works. Thanks for the QueryFirstOrDefault tip. Your knowledge is amazing. – arviman Jan 20 '16 at 16:54

score -4 · Answer 2 · answered Jan 06 '15 at 06:20

-4

int RecCount = ((dynamic)res[0]).RecCount;

answered Jan 06 '15 at 06:20

FriendsKenny

150
3
11

1

While this code block may answer the question, it would be best if you could provide some explanation for why it does so. – DavidPostill Jan 06 '15 at 07:10

kev mac · Answer 3 · 2017-02-17T03:29:19.687

Friend Function GetDepartment(ByVal DateFrom As String, ByVal DateTo As String)
    Dim xSQL As New System.Text.StringBuilder
    xSQL.AppendLine("SELECT SUM(a.Quantity ) AS quantity, ")
    xSQL.AppendLine("    SUM(a.TotalAmount ) AS totalamount, ")
    xSQL.AppendLine("    a.ProductID, ")
    xSQL.AppendLine("    c.DepartmentID, ")
    xSQL.AppendLine("    a.LongName, ")
    xSQL.AppendLine("    c.Department ")
    xSQL.AppendLine("FROM Transaction01Details a ")
    xSQL.AppendLine("    INNER JOIN Product00header b ON a.ProductID = b.ProductID ")
    xSQL.AppendLine("    INNER JOIN Department00header c ON b.DepartmentID = c.DepartmentID ")
    xSQL.AppendLine("WHERE (a.Tag4 = 'i') ")
    xSQL.AppendLine("    AND (a.TransDate BETWEEN @Date1 AND @Date2) ")
    xSQL.AppendLine("GROUP BY a.ProductID ")
    xSQL.AppendLine("ORDER BY a.LongName ")

    ' Lambda Expression
    Dim lambda = cn.Query(xSQL.ToString, New With {.Date1 = DateFrom, .Date2 = DateTo}).Select(Function(p) New With {.ProductID = CStr(p.ProductID), _
                                                                                    .DepartmentID = CStr(p.DepartmentID), _
                                                                                    .LongName = CStr(p.LongName), _
                                                                                    .Department = CStr(p.Department), _
                                                                                    .Quantity = CDec(p.Quantity), _
                                                                                    .TotalAmount = CDec(p.TotalAmount)}).ToList

    ' Linq Expression
    Dim linq = (From p In cn.Query(xSQL.ToString, New With {.Date1 = DateFrom, .Date2 = DateTo})
                Select New With {.ProductID = CStr(p.ProductID), ' Note, All p.Object is also dynamic
                                 .DepartmentID = CStr(p.DepartmentID), 
                                 .LongName = CStr(p.LongName),
                                 .Department = CStr(p.Department),
                                 .Quantity = CDec(p.Quantity),
                                 .TotalAmount = CDec(p.TotalAmount)}).ToList


    ' in linq, no need to declare function and also no need to put this  --- >  _ to continue the statement


End Function

You can Achieve dynamic mapping thru this in visual basic.. i gave the fish.. its your turn to cook in

Dapper - how to work with dynamic objects

3 Answers3