How to join with multiple (OR) conditions in LINQ to SQL?

Question

How would I perform this SQL query

Select Distinct s.* 
from #ScopeIDs x
Join Scopes s on s.ScopeID=x.ScopeID or x.ScopeID is null

in LINQ to SQL? (This query would return all Scopes whose ScopeID is present in #ScopeIDs, unless one of the entries in #ScopeIDs is null, in which case it returns all Scopes).

A "literal" translation doesn't work, since LINQ doesn't support joining with multiple conditions - code along these lines ...

From x in ScopeIDs
Join s in Scopes on s.ScopeID equals x.ScopeID or x.ScopeID equals nothing
Distinct Select s

... doesn't compile.

Answer 1

Not exactly the answer to your question, but I believe your query

Select Distinct s.*  
from #ScopeIDs x 
Join Scopes s on s.ScopeID=x.ScopeID or x.ScopeID is null 

is more commonly expressed as the equivalent

select Distinct s.*  
from #ScopeIDs x 
right join Scopes s on s.ScopeID=x.ScopeID