Clever Geek Handbook

Using case in linq

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
+2


source to share


3 answers


The case is mostly conditional.



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

+3


source


It 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 CASE

to revert to a non-null value, which is indeed the same as COALESCE(case1, case2)

:

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


source


I would write that the 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

To put the TransactionId as a regular column in the final Select query statement in a LINQ expression

-1


source






More articles:


All Articles