Conditional Joins in LINQ

Question

I have a parent child table relationship. In the example below Foo has a FooID and a nullable ParentFooID that points to a parent record.

The Bar table is always linked to the parent record. This is the SQL I use to get the result.

Select * from Foo f
JOIN  Bar b
  ON b.FooID =  
     CASE 
       WHEN f.ParentFooID is null
         THEN f.FooID
       ELSE f.ParentFooID
     END

I'm having a bit of trouble getting this into a LINQ query. I'd like to avoid a cross join like the following:

    var q = from f in Foo 
            from b in Bar 
            where  b.FooID == (f.ParentFooID ?? f.FooID)

Cheers,

Daniel

Answer 1

Your specific example is using CASE to fall back to a non-null value, which is really just a COALESCE. In which case, this works:

var q = from f in dc.Foos
        join
        b in dc.Bars
        on
        (f.ParentFooID ?? f.FooID)
        equals
        b.FooID
        into grouped
        select grouped;

Which translates into:

SELECT ...
FROM [dbo].[Foo] AS [t0]
LEFT OUTER JOIN [dbo].[Bar] AS [t1]
ON (COALESCE([t0].[ParentFooID],[t0].[FooID])) = [t1].[FooID]
ORDER BY [t0].[FooID], [t1].[BarID]

The key is the left outer join on COALESCE(case1, case2), so the expression converter does seem to understand that.

Answer 2

Doesn't directly answer your question but wouldn't the original query be better phrased without the case conditional as:

Select * 
from Foo f
JOIN Bar b ON b.FooID = f.FooID
Where f.ParentFooID is null
UNION ALL
Select * 
from Foo f
JOIN Bar b ON b.FooID = f.ParentFooID
Where f.ParentFooID is not null

In which case, the LINQ expression should be easier?