Need help on LINQ query left joins and multiple fields

I have the following SQL query that I w开发者_如何学Could like to rewrite into LINQ:

SELECT gp.Name
 , r.DateOfRace
 , ISNULL(SUM(rr.Points), 0) AS Points
FROM Races r
INNER JOIN GrandPrix gp
 ON r.GrandPrixId = gp.GrandPrixId
LEFT JOIN Predictions p
 ON p.RaceId = r.RaceId
 AND p.AdditionalUserInformationId = 2
LEFT JOIN RaceResults rr
 ON p.DriverId = rr.DriverId
 AND p.FinishPosition = rr.FinishPosition
 AND p.RaceId = rr.RaceId
WHERE r.Season = 2010
GROUP BY gp.Name
 , p.RaceId
 , r.DateOfRace

And this is much I got, when it's still working:

from races in Races
join grandprix in GrandPrixes
on races.GrandPrixId equals grandprix.GrandPrixId
from Predictions in Predictions.Where(v => v.RaceId == races.RaceId).DefaultIfEmpty()
select new
{
 DateOfRace = races.DateOfRace,
 GrandPrix = grandprix.Name,
}

When I go further, things go wrong - I can't for example get the AND p.AdditionalUserInformationId = 2 right.

I hope somebody can help!

You can do the following:

join p in Predictions
    on new { p.RaceId, p.AdditionalUserInformationId } =
       new { r.RaceId, AdditionalUserInformationId = 2 } into ps
from p in ps.DefaultIfEmpty()
join rr in RaceResults
    on new { p.DriverId, p.RaceId, p.FinishPosition } =
       new { rr.DriverId, rr.RaceId, rr.FinishPosition } into rrs
from rr in rrs.DefaultIfEmpty()

You use the ability of C# to structurally compare anonymous types. Two anonymous types are created with the same properties, which makes them instances of the same class. These instances can then be compared.

join grandprix in GrandPrixes
on new {races.GrandPrixId, p.AdditionalUserInformationId} equals new {grandprix.GrandPrixId,2}