c# - ServiceStack LoadReferences when using SQL Query -

is possible load references when instead of using code below:

sqlexpression<customer> q = db.from<customer>(); q.join<customer,customeraddress>((cust,address) => cust.id == address.customerid);  list<customer> dbcustomers = db.loadselect(q); 

using this:

public class kpitotal : ikpitotal {     public datetime date { get; set; }      public int teamid { get; set; }     public team team { get; set; }      public int accountid { get; set; }     public account account { get; set; }      public double total { get; set; } }  var result = dbcon.selectfmt<kpitotal>(@"select convert(date, t.transactiondate) [date], tm.teamid,a.accountnumber, count(distinct(t.requisitionnumber)) total                                     task.tbltransactions t                                     inner join task.tblrequisitions r on r.requisitionnumber = t.requisitionnumber                                     inner join task.tblaccounts on a.accountnumber = r.accountnumber                                     inner join team tm on tm.divisionid = a.divisionid                                     t.transactiontypenumber = 201 , a.isactive = 1                                      , t.transactiondate between {0} , {1}                                     group convert(date, t.transactiondate), tm.teamname, a.accountname                                     order 1,2 desc", daterange.start, daterange.end); 

because result object (kpitotal) has references 2 child tables, , automatic load references, instead of getting foreach block.

i'm assuming want load in team , account above query. loadselect method sniffs poco model , generates query pulls related db records based on foreign key relationships core object you're querying. generates query similar each referenced / joined poco (very pseudo-coded):

select * team /* related poco */ team.id in (select teamid [original query clase]) 

basically, single query bring teamss or accounts.

with servicestack.ormlite v4.0.40, there new merge extension method stitch object references based in more manual process.

in case, can query kpitotal results, run 2 separate queries fetch team , account lists, merge them in. basically:

var result = dbcon.selectfmt<kpitotal>(/* gnarly sql */); var teams = dbcon.select(/* relevant teams */); var accounts = dbcon.select(/* relevant accounts */);  result.merge(teams); result.merge(accounts);  debug.writeline(result.dump());    // output console / debug window, whatever