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

Some complex scenarios aren't well mapped by LINQ; I would suggest that joining onto a case is one of them. Re doing it as a cross join... have your profiled it? (i.e. the subsequent SQLfrom the from ... from ... (LINQ) vs the complex join (TSQL)? In many cases the profiler can get the same (or similar) query plan from the old-style approaches.

Answer 2

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 3

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?