Change SQL Query to LINQ, asp.net MVC

Question

How to change this SQL query to LINQ? I've tried it several times, but it didn't work

SELECT Payment.ID, Payment.TotalGroupID, PaymentTrans.PaymentID, PaymentTrans.TotalGroupID as TotalGroupID1, PaymentTrans.TransferStatus
FROM PaymentTrans INNER JOIN Payment
ON (PaymentTrans.PaymentID = Payment.ID OR PaymentTrans.TotalGroupID = payment.TotalGroupID)
WHERE (PaymentTrans.TransferStatusis NULL  OR (PaymentTrans.TransferStatus <> '01' and PaymentTrans.TransferStatus <> '02'))

and this is my try

var a= (from x in db.PaymentTransactions
        join p in db.Payments
        on
        x.PaymentID equals p.ID
        where x.TransferStatus== null || (x.TransferStatus!= "01" && x.TransferStatus!= "02")
                            select new { x, p }).ToList();

but it still wrong LINQ, because in my query I have 2 conditions in ON Clause. thanks

Answer 1

The answers above filter on both conditions, they should filter one of the conditions according to the question (PaymentID or TotalPaymentID). You can either write two seperate queries and use a union or use a Cartesian product before filtering.

var result = (from paymentTransaction in db.PaymentTransactions
                      join payment in db.Payments on paymentTransaction.PaymentID equals payment.ID
                      where paymentTransaction.TransferStatus == null || (paymentTransaction.TransferStatus != "01" && paymentTransaction.TransferStatus != "02")
                      select new { paymentTransaction, payment }).Union
                     (from paymentTransaction in db.PaymentTransactions
                      join payment in db.Payments on paymentTransaction.TotalGroupID equals payment.TotalGroupID
                      where paymentTransaction.TransferStatus == null || (paymentTransaction.TransferStatus != "01" && paymentTransaction.TransferStatus != "02")
                      select new { paymentTransaction, payment });

        var cartResult = from paymentTransaction in db.PaymentTransactions
                         from payment in db.Payments
                         where paymentTransaction.PaymentID == payment.ID || paymentTransaction.TotalGroupID == payment.TotalGroupID
                         where paymentTransaction.TransferStatus == null || (paymentTransaction.TransferStatus != "01" && paymentTransaction.TransferStatus != "02")
                         select new { paymentTransaction, payment };