retrieving nested collections with LINQ to SQL

Question

I'm trying to get back a list of TransferIds, for each transfer a list of ChargeIds, and for each of those a list of ReferralMatches

here is what I've got

(from c in Commissions
where c.TransferStatus == "Paid"
where c.AdminHasReleased == false
join r in ReferralMatches on c.ReferralMatchId equals r.ReferralMatchId
group c by new { c.TransferId } into grp
select new { 
   TransferId = grp.Key.TransferId,
   Charges = from c in grp
             group c by c.ChargeId into grp2
             select new { 
                ChargeId = grp2.Key, 
                Referrals = grp2 }
})

This works and is very close. It pulls back something that looks like this: enter image description here

This looks like Charges that belong to a TransferId but what I need is ReferralMatches that belong to Charges that belong to a transferId. I've tried another select to pull in 'r' but running into errors.

I think LINQ people will be able to gather what they need from this post but if more info is needed, kindly let me know. Thank you.

EDIT, adding table samples

The two expanded tables are what I have to work with. It probably isn't useful but keep in mind that the ReferralMatch table also has ChargeId. One chargeId can cover multiple ReferralMatches but once the funds are available a bank transfer occurs...when that happens, records are created in the Commissions table.

So what I'm looking for is a list of TransferIds, foreach Id a list of chargeIds, and foreach chargeId a list of ReferralMatches...the innermost list of ReferralMatches would be full records from that table.

enter image description here

EDIT, more attempts

here's my latest attempt

from c in Commissions
where c.TransferStatus == "paid"
group c by c.TransferId into transferGroup
select new {
    TransferId = transferGroup.Key,
    Charges = from c in transferGroup
              join r in ReferralMatches on c.ReferralMatchId equals r.ReferralMatchId
              group c by c.ChargeId into chargeGroup
              select new {
                ChargeId = chargeGroup.Key,
                Referrals = from r in chargeGroup
                            select new {
                                Referral = r
                            }
              }
}

which pulls up something like this: enter image description here

but unless I'm reading this incorrectly, the innermost item is still commission table which doesn't make sense. I need that to be ReferralMatches that have a ChargeId of [whatever]

I am unclear on exactly what you are wanting to achieve - the example resultset looks like a list of TransferIds, for each TransferId a list of ChargeIds (although the sample only has one for each Transfer group), and within these, a list of ReferralMatches for each ChargeId. Could you please indicate what your expected result object graph should look like for the sample data? Could you also post the object structure of Commissions and ReferralMatches? — devduder
– devduder, Commented Jun 22, 2014 at 5:25
yep, I'll post the tables. The example result is indeed a list of TransferIds, foreach a list of ChargeIds...but within that is not correct...I know it says Referrals but those records are from the Commissions table not the ReferralMatch table. I need that innermost set to be a full collection of ReferralMatches based on the chargeId. posting tables... — J Benjamin
– J Benjamin, Commented Jun 22, 2014 at 6:54
Great, thanks for the update. One other thing, from the object structure, it looks like there will always only be one ChargeId per Transfer, so there will never be a list per Transfer, is that right? — devduder
– devduder, Commented Jun 22, 2014 at 7:32
I think it would make more sense and be easier to maintain if you broke this behemoth into several smaller queries. — JDwyer
– JDwyer, Commented Jun 22, 2014 at 7:46
sorry for the delay. Regarding one chargeId per transfer, no, this is only showing that way because I only have a few test rows in there. There will be one or many Referrals that belong to Charges, There will also be one or many Charges belonging to Transfers. The scenario is that payment, or Charge, is made for a number of referrals. Once that payment clears, a bank transfer occurs. Bank transfers happen daily and can cover any charges that have cleared that day. So: A single transfer has multiple charges which have multiple line items (referrals) — J Benjamin
– J Benjamin, Commented Jun 23, 2014 at 4:53

devduder · Accepted Answer · 2014-06-22 10:25:51Z

1

You might need to separate out the expression into two and pull through the Referrals in the first expression, and then group on a second expression as follows:

        var commissionAndReferrals = 
            from c in Commissions
            where c.TransferStatus == "Paid"
            where c.AdminHasReleased == false
            join r in ReferralMatches on c.ReferralMatchId equals r.ReferralMatchId
            select new { Commisson = c, Referral = r };

        var result =
            from cAndR in commissionAndReferrals
            group cAndR by cAndR.Commisson.TransferId into transferGroup
            select new
            {
                TransferId = transferGroup.Key,
                Charges = from c in transferGroup
                          group c by c.Commisson.ChargeId into chargeGroup
                          select new
                          {
                              ChargeId = chargeGroup.Key,
                              Referrals = chargeGroup.Select(x => x.Referral)
                          }
            };

edited Jun 22, 2014 at 10:25

answered Jun 22, 2014 at 10:04

devduder

4042 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

J Benjamin Over a year ago

thanks for the responses, would be pretty sweet to nail all this in one database shot but if nothing else I've learned a good deal about LINQ grouping. I guess I'll do some performance testing and see what the pros and cons are to this way or just using several hits to build a complex object with multiple simpler methods...or for that matter, maybe it'd be better to shove all the data into separate objects, forget the nesting, and just use LINQ at the View level to gather and sort the data...hmm

Collectives™ on Stack Overflow

retrieving nested collections with LINQ to SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related