Conditional Joins in LINQ

.net, c#, conditional-statements, join, linq-to-sql

Solution

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.

Problem

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

Original source