Using Dapper to map more than 5 types

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!

score 31 · Accepted Answer · edited Jun 20 '20 at 09:12

31

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 25 '15 at 10:18

Radek

686
6
4

4

Marking this as the answer since it's the most up-to-date! This would have worked perfectly in my use-case 3 years ago! (how time flies!) – Christian Droulers Jun 29 '15 at 15:29
great to have this answer! – noobed Mar 01 '17 at 08:04
Hallelujah! :D Thanks Mister @Radek – Jeancarlo Fontalvo May 12 '17 at 23:54
The provided link is dead. – Tyler Benzing Jun 14 '17 at 00:10
This link should be close to what the original answer was referring to: https://www.tritac.com/blog/dappernet-by-example/ – Jon49 Jul 23 '18 at 18:51
how do we do if we have typeof(ICollection)? – Raphael Ribeiro Mar 05 '21 at 13:43
1

This example is perfect to me, but i can't map the propriedades, any examples? – cura Jun 18 '21 at 18:33
For anyone interested, the parameter name for the type array is `types`. (I always name my Dapper parameters because the method calls can get very long and complex.) – stellr42 Jul 09 '21 at 15:12
The example in this comment from the original pull request is also very helpful: https://github.com/DapperLib/Dapper/pull/308#issuecomment-115995713 – Doug Jan 31 '22 at 16:47

Alex · Answer 2 · 2012-04-18T06:00:06.053

4

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();
   ...
}

edited Apr 18 '12 at 06:00

answered Apr 18 '12 at 05:29

Alex

7,901
1
41
56

I've considered it. But I have ~87 columns to map. And my query maps perfectly to my objects. My last resort is the manual map, but otherwise, I'd very much like not to type out allll the columns :P – Christian Droulers Apr 18 '12 at 05:34
1

I see, you might want to take a look at the Grid Reader option - see my edit :) – Alex Apr 18 '12 at 06:00
Wouldn't want to be running a multitude of queries when one does the trick. But thanks for the help :) – Christian Droulers Apr 18 '12 at 07:01
1

If the performance penalty isn't an issue, I'd go for whatever offers the most usability. Mapping 87 columns is certainly not :P – Alex Apr 18 '12 at 07:23
I ended up modifying Dapper.cs to have generics up to 12 types. Wasn't too hard, but it's a pain. I marked @Alex's post as the answer because it basically answered my question. – Christian Droulers Apr 18 '12 at 16:04

score 2 · Answer 3 · answered Jun 10 '13 at 17:58

2

This has been answered long time ago, but I would like to add my two cents here. Instead of manually modify Dapper's source code, why don't you just create a poco class with those fields and use your query like a table?

The mapping would work fine, I know it is a pain also to do that class definition, but seems easier than dealing with later Dapper's updates.

answered Jun 10 '13 at 17:58

coffekid

621
2
10
25

This code has completely died now (and went open source: https://bitbucket.org/cdroulers/invup). But I might use that for another project later. Thanks for the answer! – Christian Droulers Jun 11 '13 at 18:43

Using Dapper to map more than 5 types

3 Answers3

Linked