c# - retrieving nested collections with LINQ to SQL -

c# - retrieving nested collections with LINQ to SQL -

i'm trying list of transferids, each transfer list of chargeids, , each of list of referralmatches

here i've got

(from c in commissions c.transferstatus == "paid" c.adminhasreleased == false bring together r in referralmatches on c.referralmatchid equals r.referralmatchid grouping c new { c.transferid } grp select new { transferid = grp.key.transferid, charges = c in grp grouping c c.chargeid grp2 select new { chargeid = grp2.key, referrals = grp2 } })

this works , close. pulls looks this:

this looks charges belong transferid need referralmatches belong charges belong transferid. i've tried select pull in 'r' running errors.

i think linq people able gather need post if more info needed, kindly allow me know. give thanks you.

edit, adding table samples

the 2 expanded tables have work with. isn't useful maintain in mind referralmatch table has chargeid. 1 chargeid can cover multiple referralmatches 1 time funds available bank transfer occurs...when happens, records created in commissions table.

so i'm looking list of transferids, foreach id list of chargeids, , foreach chargeid list of referralmatches...the innermost list of referralmatches total records table.

edit, more attempts

here's latest attempt

from c in commissions c.transferstatus == "paid" grouping c c.transferid transfergroup select new { transferid = transfergroup.key, charges = c in transfergroup bring together r in referralmatches on c.referralmatchid equals r.referralmatchid grouping c c.chargeid chargegroup select new { chargeid = chargegroup.key, referrals = r in chargegroup select new { referral = r } } }

which pulls this:

but unless i'm reading incorrectly, innermost item still commission table doesn't create sense. need referralmatches have chargeid of [whatever]

you might need separate out look 2 , pull through referrals in first expression, , grouping on sec look follows:

var commissionandreferrals = c in commissions c.transferstatus == "paid" c.adminhasreleased == false bring together r in referralmatches on c.referralmatchid equals r.referralmatchid select new { commisson = c, referral = r }; var result = candr in commissionandreferrals grouping candr candr.commisson.transferid transfergroup select new { transferid = transfergroup.key, charges = c in transfergroup grouping c c.commisson.chargeid chargegroup select new { chargeid = chargegroup.key, referrals = chargegroup.select(x => x.referral) } };

c# sql linq