CODEWITHSUNDEEP
c#sqllinqlinq-to-sqlc#-4.0
Benjamin
Benjamin

Reputation: 11

Need help on LINQ query left joins and multiple fields

I have the following SQL query that I would 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!

Upvotes: 1

Views: 1250

Answers (2)

Ronald Wildenberg
Ronald Wildenberg

Reputation: 32094

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.

Upvotes: 3

Alexander Taran
Alexander Taran

Reputation: 6725

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

Upvotes: 1

Related Questions