using case in linq

Question

How can this be written in linq

select * from transactions T

JOIN  TransactionSample ts

ON ts.TransactionID =  

 CASE 

   WHEN T.ParentTransactionID is null

     THEN T.TransactionID

   ELSE T.ParentTransactionID

 END

where T.TransactionID = 227511

Answer 1

A case is basically just a conditional.

...where(ts => ts.TransactionID == (T.ParentTransactionID ?? T.TransactionID))...

Answer 2

I would write that SQL query as

select *, ISNULL(T.ParentTransactionID, T.TransactionID) TransactionId
from transactions T
JOIN TransactionSample ts ON (ts.TransactionID = T.TransactionID OR ts.TransactionID =  T.ParentTransactionID)
where T.TransactionID = 227511

Which would put the TransactionId as a normal column in the final Select query operator in your LINQ expression

Answer 3

This would be written in LINQ as:

using(var dc = new MyDataContext())
{
    var q = from t in Transactions
        join ts in TransactionSamples
        on (t.ParentTransactionID ?? t.TransactionID)
        equals ts.TransactionID
        into joined
        where t.TransactionID = 12345
        select joined;
}

In your example, you are using a CASE to fall back to a non-null value, which is really the same as a COALESCE(case1, case2):

SELECT ...
FROM [dbo].[Transactions] AS [t0]
LEFT OUTER JOIN [dbo].[TransactionSamples] AS [t1]
ON (COALESCE([t0].[ParentTransactionID],[t0].[TransactionID])) = [t1].[TransactionID]