Geek Answers Handbook

Conditional joins in LINQ

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

The Bar table is always associated with the parent record. This is the SQL that 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 am having trouble getting a LINQ request. I would like to avoid cross-connect as shown below:

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

Greetings

Daniel

+5
source share
3 answers

In your specific example, it is used CASEto return to a non-zero value, which is actually simple COALESCE. In this 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;

It means:

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]

- COALESCE(case1, case2), , , .

+5

, :

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

LINQ ?

+1

Some complex scenarios do not match LINQ well; I would suggest that one of them is joining case. I repeat this, as a cross-connection ... did you profile it? (i.e. subsequent SQL from from ... from ...(LINQ) vs complex join(TSQL)? In many cases, the profiler can get the same (or similar) query plan from old-style approaches.

0
source

More articles:


All Articles