Using Dapper to map more than 5 types

c# dapper sql

Question

I am currently building a SELECT query that joins 12 tables together. I've been using Dapper for all my other queries and it works great. Problem is, the generic methods only have to five generic parameters.

I've previously modified the code to support up to 6 for another query, but now I really don't think I should be hacking 6 more levels of generics.

Is there a way to pass dapper an array of types, and it returns the results as an array of objects, which I can cast manually if I have to?

I also might be approaching the problem the wrong way! Any help will be appreciated!

Accepted Answer

In a project I worked on I saw something like this to get more than 7 types mapped. We used Dapper 1.38:

connection.Query<TypeOfYourResult>
(
   queryString,
   new[]
   {
      typeof(TypeOfArgument1),
      typeof(TypeOfArgument2),
      ...,
      typeof(TypeOfArgumentN)
   },
   objects =>
   {
      TypeOfArgument1 arg1 = objects[0] as TypeOfArgument1;
      TypeOfArgument2 arg2 = objects[1] as TypeOfArgument2;
      ...
      TypeOfArgumentN argN = objects[N] as TypeOfArgumentN;

     // do your processing here, e.g. arg1.SomeField = arg2, etc.
     // also initialize your result

     var result = new TypeOfYourResult(...)

     return result;
   },
   parameters,
   splitOn: "arg1_ID,arg2_ID, ... ,argN_ID"
);

The queryString is self-explanatory. The splitOn parameter says how Dapper should split the columns from the SELECT statement so that everything can be mapped properly to the objects, you can read about it here.


Popular Answer

You could use a dynamic query and map it afterwards. Something like this

var result = conn.Query<dynamic>(query).Select(x => new Tuple<Type1, Type2, Type3, Type4, Type5>( 
// type initialization here 
    new Type1(x.Property1,x.Property2),
    new Type2(x.Property3,x.Property4),
    new Type3(x.Property5,x.Property6) etc....));

Edit: With a rather huge result set, another option might be to use multiple querys and then use a Grid Reader. That might work for you.

There's the example taken from the dapper age:

var sql = 
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
   var customer = multi.Read<Customer>().Single();
   var orders = multi.Read<Order>().ToList();
   var returns = multi.Read<Return>().ToList();
   ...
} 



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why