Search code examples
c#linq-to-sql

LINQ join with OR

I want to do a JOIN with LINQ using an OR statement.

Here is the SQL query I'm starting with:

SELECT  t.id
FROM Teams t
INNER JOIN Games g 
   ON (g.homeTeamId = t.id OR g.awayTeamId = t.id) 
  AND g.winningTeamId != 0
  AND g.year = @year
GROUP BY t.id

I'm having trouble converting that ON clause to LINQ. This is where I'm at:

var y = from t in db.Teams
        join g in db.Games on t.ID equals g.AwayTeamID //missing HomeTeamID join
        where g.WinningTeamID != 0
           && g.Year == year
        group t by t.ID into grouping
        select grouping;

I think I could use:

join g in db.Games on 1 equals 1
where (t.ID == g.HomeTeamID || t.ID == g.AwayTeamID)

and this works but seems kind of seems hacky. Is there a better way?

Solution

  • The where clause applies a boolean condition, so using "||" is the way to go. You can chain multiple where clauses but I believe that will give you a "and" operation, rather than an "or".