Linq Query join on multiple conditions

Question

Trying to do a single query which combined data from multiple joins into a single property

Rig
----
   RigId
   Component1Id
   Component2Id
   Component3Id


Work Item
---------
  Id
  ComponentID
  Description

I'm trying to do a query that returns a list of rigs with a single property called history that consists of all the workItems associated with the components in a Rig.

I cant seem to do multiple conditions in a join or do separate joins and concatenate the items into a single property.

So the result is something like

RigId, History (which consists of a list of all the workitems for the rig)

Answer 1

Here is the answer in query syntax:

var ans = from r in Rigs
          join w in WorkItems on r.Component1ID equals w.ComponentID into wg1
          join w in WorkItems on r.Component2ID equals w.ComponentID into wg2
          join w in WorkItems on r.Component3ID equals w.ComponentID into wg3
          select new { r.RigID, History = wg1.Concat(wg2).Concat(wg3).ToList() };

and if you prefer, lambda syntax (this was a bit harder...)

var ans2 = Rigs.GroupJoin(WorkItems, r => r.Component1ID, w => w.ComponentID, (r, w1g) => new { r, h1 = w1g.ToList() })
               .GroupJoin(WorkItems, rh1 => rh1.r.Component2ID, w => w.ComponentID, (rh1, w2g) => new { rh1.r, h2 = rh1.h1.Concat(w2g.ToList()) })
               .GroupJoin(WorkItems, rh2 => rh2.r.Component3ID, w => w.ComponentID, (rh2, w3g) => new { rh2.r.RigID, History = rh2.h2.Concat(w3g.ToList()) });

I don't think using columns for the components if a very good idea - what happens when a Rig has more or fewer than 3 components? You should really have a separate RigComponent table.