CODEWITHSUNDEEP
c#linq-to-sql
AndyMcKenna
AndyMcKenna

Reputation: 2647

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?

Upvotes: 19

Views: 21663

Answers (3)

user275801
user275801

Reputation: 1355

I think you can do like this:

from t1 in db.Table1
// inner join with OR condition
from t2 in db.Table2 where t1.col1 == t2.col1 || t1.col2 == t2.col2
// normal inner join
join t3 in db.Table3 on t1.col1 equals t3.col1
// inner join with complex condition
join t4 in db.Table4 on t2.col4 equals t4.col4 where t2.col5.Contains(t4.col5)
// left join with OR condition
from t5 in db.Table5.Where(x => x.col5 == t1.col5 || x.col6 == t1.col6).DefaultIfEmpty()
select new {
    x = 1 // select whatever you want here
}

The underlying SQL query probably won't use native sql joins but the above is just a way to make your code look pretty and organized.

Upvotes: 3

ammills01
ammills01

Reputation: 737

I struggled with this as well until I found the following solution, which worked well for my situation:

var y = from t in db.Teams
        from g in db.Games
        where
        (
            t.ID == g.AwayTeamID
            || t.ID == g.HomeTeamID
        )
           && g.WinningTeamID != 0
           && g.Year == year
        group t by t.ID into grouping
        select grouping;

Under the covers, your solution probably works very close to this one. However, I bet this one is just a bit faster if you benchmark it since it is not JOINING every item in the first dataset with every item in the second dataset, which could be a disaster if either (or both) dataset were really big.

Upvotes: 23

Amit G
Amit G

Reputation: 5485

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".

Upvotes: 3

Related Questions