c# - How to return dynamic types List<dynamic> with Dapper ORM

ID : 131362

viewed : 8

Tags : c#.netormdapperc#

Top 3 Answer for c# - How to return dynamic types List<dynamic> with Dapper ORM

vote vote

97

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.

vote vote

84

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.

vote vote

76

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

Top 3 video Explaining c# - How to return dynamic types List<dynamic> with Dapper ORM

Related QUESTION?