How to return dynamic types List with Dapper ORM

Question

I have been using Dapper.net for a while now and its a very good ORM mapper which works great with .Net dynamic types.

But I noticed that when Dapper retrieves data from a database it returns as DapperRow type.

Is there are any way that I can return it in any other type Like System.Dynamic.ExpandoObject?

Another high-performance solution: https://stackoverflow.com/questions/55308065/how-to-return-listdynamic-with-dapper-net-orm-that-can-bindable — , Mar 21 '19 at 04:12
Instead of returning dynamic, consider returning anonymous types, which provide intellisense and compiler-time checking: https://stackoverflow.com/a/30469302/3606250 — drizin, Jul 21 '20 at 03:23

score 62 · Answer 1 · edited Feb 12 '20 at 01:56

62

Sure!

As per dapper documentation use the query method and get your dymanics:

dynamic account = conn.Query<dynamic>(@"
                    SELECT Name, Address, Country
                    FROM Account
            WHERE Id = @Id", new { Id = Id }).FirstOrDefault();
Console.WriteLine(account.Name);
Console.WriteLine(account.Address);
Console.WriteLine(account.Country);

As you can see you get a dynamic object and you can access its properties as long as they are well defined in the query statement.

If you omit .FirstOrDefault() you get an IEnumerable<dynamic> which you can do whatever you want with it.

edited Feb 12 '20 at 01:56

Bob Horn

33,387
34
113
219

answered Oct 30 '14 at 17:48

e4rthdog

5,103
4
40
89

2

In Oracle the same query results in properties `account.NAME`, `account.ADDRESS`, and `account.COUNTRY` instead – nothingisnecessary Jan 21 '20 at 20:03

score 41 · Accepted Answer · edited May 30 '19 at 06:45

41

The DapperRow object is designed to share a lot of state between rows. For example, if you fetch 40 rows, the column names etc are only stored once. If we used ExpandoObject, this would need to be configured per row. Hence, the use of DapperRow as the behind-the-scenes implementation detail is a deliberate efficiency thing.

Note that the object returned from the dynamic APIs can also be cast as IDictionary<string,object>.

I would, however, be open to supporting other types that support this dictionary usage - of which ExpandoObject is one. So yes, it could be changed such that:

var rows = conn.Query<ExpandoObject>(...);

works. It simply requires code to support it, and that code does not currently exist. So "no, but perhaps in a future build".

Note also that you don't need to use DapperRow at all... The more expected scenario is to use the generic API to materialize your own types.

edited May 30 '19 at 06:45

Mark Cooper

6,738
5
54
92

answered Oct 30 '14 at 18:57

Marc Gravell

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

Hello Marc.Execuse me its old question but I need to know about creating a dynamic row not return it Like OP asked. I want to know how to add a new dynamic row (DapperRow) to a dynamic Query. Its not supported maybe because DapperRow is Internal. and can't created from public project? Did you know please how to do it? Maybe support extension method to add new empty DapperRow (example AddDynamicRow() which only appears for List ). Thanks – deveton Dec 25 '20 at 23:55
2

@deveton that is not a supported scenario, and there are no current plans to add such; if anything, we'd like to *remove* dynamic, now that things like value-tuples and records make better candidates for highly local data access – Marc Gravell Dec 26 '20 at 11:29
Thanks for reply. but sometimes you need to Add new empty row. specially for Bindable Grids in a data-source Level... and Please what record and value-tuples can provide to a Data-Grids? The dynamic is better solution maybe. so please If you take more efforts on supports more dynamic features in Dapper It will help many people... – deveton Dec 26 '20 at 11:33
I need also to know what you will do if you need to add new empty row in List? Maybe you have great details for u. Because I need to use Activator class to create instance of first DapperRow of collection. And that's very slow Thanks – deveton Dec 26 '20 at 11:34
There's also something You must note. Dapper With strongly typed is 100% nice. but why I need to Create 100 C# Classes. to just assign them to SQL-Server? and If I need to add new column which maybe highly needed in many tables. I need to re-build whole project and add new columns to classes. In dynamic, It only need add columns to SQL-Server and it will appears directly in same deployed project. So there's very big difference there. Wish you sir to add more dynamic features ( Many people ask for adding New Empty Row. And please don't forget to see previous comments) – deveton Dec 26 '20 at 11:36
Something Like List("") xx; xx.Add() // return empty dynamic row. that's will be awesome and collections-wise :) – deveton Dec 26 '20 at 11:38
@deveton it sounds like DataTable, as horrible as it is, would work for you. That isn't how most people work with data in 2020, so it isn't a priority to make Dapper work that way – Marc Gravell Dec 26 '20 at 18:18
@deveton thank you for your opinion on what is "better" and "not fair"; I strongly disagree, but you are more than welcome to fork the library on GitHub and make whatever changes you want, locally – Marc Gravell Dec 27 '20 at 10:59

Behzad · Answer 3 · 2017-05-18T18:37:42.733

I have this problem and I solved by this way!

The Query() function returns a collection of dynamics which underneath are actually Dapper.SqlMapper.DapperRow object types. The Dapper.SqlMapper.DapperRow is private. I needed to dynamically add properties to the Dapper.SqlMapper.DapperRow objects but that doesn't appear to work. As a result I wanted to convert the Dapper.SqlMapper.DapperRow into an ExpandoObject.

I was able to build this generic helper method like below.

public class DapperHelpers
{
     public static dynamic ToExpandoObject(object value)
     {
         IDictionary<string, object> dapperRowProperties = value as IDictionary<string, object>;

         IDictionary<string, object> expando = new ExpandoObject();

         foreach (KeyValuePair<string, object> property in dapperRowProperties)
             expando.Add(property.Key, property.Value);

         return expando as ExpandoObject;
     }
}

Then you can use that like this:

IEnumerable<ExpandoObject> result = 
           db.SqlConn.Query(sqlScript)
               .Select(x=> (ExpandoObject)ToExpandoObject(x));

reference: dapper-dot-net issues 166

why do we have to convert the dapper row to Expando object explicitly, it is convertable via following approach right? var rows = conn.Query(...); — Husni Jabir, Apr 22 '16 at 05:28
Maybe in the next version of Dapper that work right but in the version 1.4 which I used, that's converted object have not any property. I found this solution from dapper issues. — Behzad, Apr 23 '16 at 03:54

How to return dynamic types List with Dapper ORM

3 Answers3

Linked