How to return dynamic types List<dynamic> with Dapper ORM
Solution 1
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.
Solution 2
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.
Solution 3
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
Husni Jabir
Updated on July 09, 2022Comments
-
Husni Jabir almost 2 years
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
?