Using dynamic list of objects in dapper join queries

Question

Using dynamic list of objects in dapper join queries

c# dapper

Question

Is it possible to create the below written dapper query with returntype as IEnumerable<dynamic> as I do not have Product & Supplier POCO.

IEnumerable<Product> products = sqlConnection
                    .Query<Product, Supplier, Product>(
                        @"select Products.*, Suppliers.* 
                          from Products join Suppliers 
                               on Products.SupplierId = Suppliers.Id
                               and suppliers.Id = 2",
                        (a, s) =>
                            {
                                a.Supplier = s;
                                return a;
                            });

What if my sql query was something as below, how would my dapper query be with returntype of IEnumerable<dynamic>

select Products.ProductId,Products.ProductName,Products.ProductCategory, ProductPrice.Amount,ProductPrice.Currency
                              from Products join ProductPrice 
                                   on Products.ProductId = ProductPrice.ProductId

All help is sincerely appreciated.

Thanks

Arnab

View more on Stack Overflow

Matt Cole · Accepted Answer · 2/7/2016 7:02:12 PM

Yes you can map the result of your queries to a list of dynamic objects (documentation here).

    const string sql = @"select Products.ProductId, Products.ProductName, Products.ProductCategory, ProductPrice.Amount, ProductPrice.Currency
                        from Products join ProductPrice 
                        on Products.ProductId = ProductPrice.ProductId";

    IEnumerable<dynamic> products = sqlConnection.Query(sql);

In your first example you are doing multi mapping which maps each table row onto 2 objects instead of one (Product and Supplier) that are then linked by reference before the product is returned. I don't think you could do this with dynamic objects, because Dapper would have no way of knowing how to divide the columns among them. You could confirm this with a test, replacing generic parameters <Product, Supplier, Product> with <dynamic, dynamic, dynamic>.

Skipping the multi-mapping would just mean that the dynamic objects returned would contain both Product and Supplier properties, which might not be a problem for you.

Using dynamic list of objects in dapper join queries

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator