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

Gabriel Llorico · Answer 1 · 2019-08-07T09:01:07.193

2

try this

var query = (from x in db.PaymentTransactions
             join p in db.Payments 
             on x.PaymentID equals p.ID //main condition of join
             where  ((x.TransferStatus == null || 
             (x.TransferStatus != "01" && x.TransferStatus!= "02")) //your `where` condition
             || x.TotalGroupID == p.TotalGroupID) //your second or join
             select new {x,p})
            .ToList();

edited Aug 07 '19 at 09:01

answered Aug 07 '19 at 07:26

Gabriel Llorico

1,783
1
13
23

It's not working, if I execute a SQL query I get 13 data, but if I debug the LINQ that you provide only get 10 data. in SQL Query, they should filter one of the conditions (PaymentID or Total Payment). – chaeusang chen Aug 07 '19 at 08:34
hi, I cant use `select new { x, p }) .ToList();` because there is a red line in the code – chaeusang chen Aug 07 '19 at 08:59
@chaeusangchen can you hover and what is the message? – Gabriel Llorico Aug 07 '19 at 09:00
@chaeusangchen hope it has helped you :D – Gabriel Llorico Aug 07 '19 at 09:14

score 1 · Answer 2 · answered Aug 07 '19 at 07:21

1

You cannot add multiple ON in LINQ. the solution of your problem above can be solved like this.

Hint: just use multiple Where.

var result = 
(
    from trans in db.PaymentTransactions
    join payment in db.payments
    on trans.PaymentID equals payment.ID
    where trans.TotalGroupID == payment.TotalGroupID
    where x.TransferStatus== null || (x.TransferStatus!= "01" && x.TransferStatus!= "02")
    select new 
    {
        //your properties
    }

).ToList();

answered Aug 07 '19 at 07:21

Mirza Ghulam Rasyid

174
4

It's not working, if I execute a SQL query I get 13 data, but if I debug the LINQ that you provide only get 10 data. in SQL Query, they should filter one of the conditions (PaymentID or Total Payment). – chaeusang chen Aug 07 '19 at 08:34

vdL · Accepted Answer · 2019-08-07T11:44:17.783

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 };

I get an error at `select new (paymentTransaction, payment)`. the error is `(range valiable)? payment Transaction` — chaeusang chen, Aug 07 '19 at 08:37

Change SQL Query to LINQ, asp.net MVC

3 Answers3