Linq to sql join with multiple "OR" filters

Question

I need to implement this in Linq-to-sql

SELECT DISTINCT c.*
FROM dbo.Collector_Capital_Equipment c
LEFT OUTER JOIN dbo.Lab_Space s ON c.Room = s.Entry_Bar_Code 
                                OR c.HomeRoom = s.Entry_Bar_Code
WHERE s.id = 1021645

All the research I've done on this says that linq-to-sql can't support an "OR" multiple join, and the suggestions are to instead do two joins like this:

SELECT DISTINCT c.*
FROM dbo.Collector_Capital_Equipment c
LEFT OUTER JOIN dbo.Lab_Space s ON c.Room = s.Entry_Bar_Code 
LEFT OUTER JOIN dbo.Lab_Space s2 ON c.HomeRoom = s2.Entry_Bar_Code
WHERE s.id = 1021645

Those aren't actually the same query though as they'll return different results. Short of just putting the raw SQL into my C# program at this point, is there any way to accomplish the above?

Is there something like `s.Entry_Bar_Code IN (c.Room, HomeRoom)`? — Lukasz Szozda, Jan 11 '19 at 21:22
See my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) - basically, use `Where` on a cross join. — NetMage, Jan 11 '19 at 21:29
@GertArnold CROSS JOIN with multiple conditions in where clause is not the same as LEFT JOIN with multiple conditions. — Lukasz Szozda, Jan 12 '19 at 12:19
@LukaszSzozda You're right, it's not the same thing, It's the same idea though: abandon join syntax and use a predicate. — Gert Arnold, Jan 12 '19 at 12:21
@GertArnold It all depends what OP really wants to achieve. In his scenario it is simple `INNER JOIN` - probably he is not aware of that. With correct `LEFT JOIN` you get different resultset. **[Demo](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=e205e31374999eb1d92ff59819fc5cc7)** — Lukasz Szozda, Jan 12 '19 at 12:24
@LukaszSzozda Yeah, that's tricky. If it's inner join, I'd prefer an EXIST statement for that matter, wouldn't require DISTINCT. — Gert Arnold, Jan 12 '19 at 12:33
There may not be a room or homeroom set which was why I used the left — Gargoyle, Jan 12 '19 at 19:15

Lukasz Szozda · Answer 1 · 2019-01-12T12:35:00.263

Let's start from beginning. If you use WHERE with column from OUTER JOIN table it means that your query:

SELECT DISTINCT c.*
FROM dbo.Collector_Capital_Equipment c
LEFT OUTER JOIN dbo.Lab_Space s ON c.Room = s.Entry_Bar_Code 
                                OR c.HomeRoom = s.Entry_Bar_Code
WHERE s.id = 1021645

is logically equivalent to:

SELECT DISTINCT c.*
FROM dbo.Collector_Capital_Equipment c
JOIN dbo.Lab_Space s 
  ON c.Room = s.Entry_Bar_Code 
  OR c.HomeRoom = s.Entry_Bar_Code
WHERE s.id = 1021645;

And this could be achieved with CROSS JOIN(pseudocode):

var q = from c in Collector_Capital_Equipment
       from s in Lab_Space
       where s.id == 1021645 
          && (s.Entry_Bar_Code == c.Room || c.HomeRoom == s.Entry_Bar_Code)
       select ...

I assume that you really want to generate query:

SELECT DISTINCT c.*
FROM dbo.Collector_Capital_Equipment c
LEFT OUTER JOIN dbo.Lab_Space s 
  ON (c.Room = s.Entry_Bar_Code OR c.HomeRoom = s.Entry_Bar_Code)
  AND s.id = 1021645

which could be represented as:

SELECT c.*
FROM dbo.Collector_Capital_Equipment c
LEFT OUTER JOIN dbo.Lab_Space s 
  ON c.Room = s.Entry_Bar_Code AND s.id = 1021645
INTERSECT
SELECT c.*
FROM dbo.Collector_Capital_Equipment c
LEFT OUTER JOIN dbo.Lab_Space s 
  ON c.HomeRoom = s.Entry_Bar_Code AND s.id = 1021645

And above query could be achieved with LINQ using set operators.

db<>fiddle demo

You're raising good points here, esp. the condition on `s.Id` turning this into a de-facto inner join. — Gert Arnold, Jan 13 '19 at 10:16

score 0 · Answer 2 · answered Jan 11 '19 at 21:34

0

I don't think I've ever seen someone use an OR clause on a join before. Does MS SQL Server even support that in SQL?

I'd probably just split this off into two queries, to be honest.

SELECT entry_bar_code 
FROM dbo.Lab_Space WHERE id = 1021645;

With the result from that being fed into

SELECT DISTINCT * 
FROM dbo.Collector_Capital_Equipment 
WHERE c.room == <barcode> OR c.homeRoom == <barcode>

answered Jan 11 '19 at 21:34

Nathan Yaskiw

1
2

An `OR` clause in a join predicate is in fact entirely legitimate. You can structure your join predicates with as much complexity as where clauses. – e_i_pi Jan 11 '19 at 21:45
`Does MS SQL Server even support that in SQL?` Of course it works in SQL Server, even more any condition that could be used with `ON` clause. **[demo](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=f7016d62c98810bc14f7a7136f51a722)** – Lukasz Szozda Jan 12 '19 at 11:57

Linq to sql join with multiple "OR" filters

2 Answers2