Dapper sql Query with joins that goes past 7

Question

I have a query that needs about 9 objects that's coming from joins.

select
 :
  fields
 :
from tbl1 t1
join tbl2 t2 on t2.id = t1.id
join tbl3 t3 on t3.id = t1.id
join tbl4 t4 on t4.id = t1.id
join tbl5 t5 on t5.id = t1.id
join tbl6 t6 on t6.id = t1.id
left outer join tbl7 on t7.id = t1.id
left outer join tbl8 on t8.id = t6.id
left outer join tbl9 on t9.id = t1.id
left outer joint tbl10 on t10.id = t9.id
    where ...

And would execute as usual

db.Query<poco1, poco2,.... poco10, poco1>(query, (poco1, poco2,.... poco10) => {

   return ..stuffs
}).ToList()

The problem with this is that, query only accepts 7. Was wondering what would be another option to get all those objects (9) with one query. If I'm going to use a stored proc, how will I map the data to it's objects?

edit tbl represents the poco, but made changes to make it clear they're poco

When I use Dapper's Query method, I create a plain old CLR object (POCO) that corresponds to the "fields" in your query (i.e., it has properties that match the fields in name and type). Then I use that POCO type as my template parameter. I'm not sure what your types (tbl1...tbl9) represent. — Flydog57, Aug 28 '18 at 02:25
that represents the poco. i thought it would be straight forward. but ill edit — gdubs, Aug 28 '18 at 02:39
Query is a generic function. The thing (or things) in the angle brackets is (are) types, the POCO type. — Flydog57, Aug 28 '18 at 03:26

score 2 · Accepted Answer · answered Aug 28 '18 at 18:24

2

The Signature you are looking for is

 public static IEnumerable<TReturn> Query<TReturn>(this IDbConnection cnn, string sql, Type[] types, Func<object[], TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)

It allows more than 7 types.

answered Aug 28 '18 at 18:24

Richard Hubley

2,180
22
29

i see so just put the types in a variable maybe and then add that in there? ill try that thank you! – gdubs Aug 29 '18 at 06:37

score 0 · Answer 2 · answered Aug 29 '18 at 14:34

Let's put some fields in that query (and we are going to put it in a string called queryString):

select
   t1.FooStr
   t2.BarInt
   t3.QuuzDate
   t4.IsAFoo
   t5.And
   t6.So
   t7.On
from tbl1 t1
join tbl2 t2 on t2.id = t1.id
join tbl3 t3 on t3.id = t1.id
join tbl4 t4 on t4.id = t1.id
join tbl5 t5 on t5.id = t1.id
join tbl6 t6 on t6.id = t1.id
left outer join tbl7 on t7.id = t1.id
left outer join tbl8 on t8.id = t6.id
left outer join tbl9 on t9.id = t1.id
left outer joint tbl10 on t10.id = t9.id
where ...

Then create a class like:

  public class SomeType
  {
      public string FooStr { get; set; }
      public int BarInt { get; set; }
      public DateTime  QuuzDate { get; set; }
      public bool IsAFoo { get; set; }
      public string And { get; set; }
      public string So { get; set; }
      public string On { get; set; }
  }

your query would end up looking like:

var results = myConnection.Query<SomeType>(queryString);

if your where clause looked like:

where t1.FooStr = @Name and t2.BarInt > @MinVal

then the query would look like:

var results = myConnection.Query<SomeType>(queryString, new {Name = "Flydog", MinVal = 10});

The POCO class has nothing to do with what the tables in the query are, it is determined by what the query's resultset looks like.

I believe they are looking for a nested object structure. eg. poco1.poco2 — Richard Hubley, Aug 29 '18 at 20:15
Using Dapper "Mulit Mapping" to try to generate nested queries has always left me disappointed. Even Sam Saffron recommends managing a "nested" query as two queries stitched together in code: https://stackoverflow.com/questions/7508322/how-do-i-map-lists-of-nested-objects-with-dapper — Flydog57, Aug 29 '18 at 20:31
the number of times poco1 (or poco2,3,4,5..) fields get repeated in the result set of the query may be an extreme waste of bytes or may be only a small nuisance. Hard to tell without fields and row counts. I will assume they have run the query and are happy with it's performance — Richard Hubley, Aug 30 '18 at 14:43
In most cases this recommendation is the way to go. Not for 9 nested POCO levels (I wonder if the OP design is not over-normalized), but is a good guidance in more generic cases, so I don't understand the downvotes. This still could help people with 3 levels instead of 9 — zameb, Nov 06 '18 at 07:24

Dapper sql Query with joins that goes past 7

2 Answers2