Translate Obscure LINQ

Question

Some long-gone developer left the following LINQ query behind with no documentation and I'm struggling to understand what it does (and therefore if it's doing it right). Can someone help translate this, either by breaking it into pieces or providing the SQL equivalent?

Dim matches = From mc In mcs _
              Join ri In r.Items On ri.VIN Equals mc.VIN _
              Group Join t In (From t In claimTarget
                               Order By t.VIN Ascending, t.OrderDate Descending) On _
                        t.VIN Equals mc.VIN Into TargetMatches = Group, _
              InTMS = Count(Not t.PDSCargoItemID = 0), _
              InOTA = Count(Not t.TransportItemInID.IsValueNull) _
              Select Whatever = mc, Match = TargetMatches.FirstOrDefault, Result = ri, InTMS, InOTA

I am struggling in particular with the Group Join and Into TargetMatches = Group pieces:

Group Join t In (From t In claimTarget Order By t.VIN Ascending, t.OrderDate Descending) On _
t.VIN Equals mc.VIN Into TargetMatches = Group

Answer 1

The piece you are struggling with appears to be doing a left outer join against claimTargets on the VIN.

So for each row in mcs that has a matching VIN in r.Items it finds the most recent (by OrderDate) item from claimTarget that has the same VIN, if any

The query returns an anonymous object with 5 fields:

• Whatever = the row from mcs
• Match = the item from claimTarget that has the matching VIN and the most recent OrderDate (or null)
• Result = the item from r.Items that has the same VIN
• InTMS = a count of the number of rows from claimTarget that have the matching VIN where PDSCargoItemID is not 0
• InOTA = a count of the number of rows from claimTarget that have the matching VIN where TransportItemInID is not null