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 · Accepted Answer · 2019-08-07 09:01:07Z

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 7, 2019 at 9:01

answered Aug 7, 2019 at 7:26

Gabriel Llorico

1,8131 gold badge15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

chaeusang chen Over a year ago

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 Over a year ago

hi, I cant use select new { x, p }) .ToList(); because there is a red line in the code

Gabriel Llorico Over a year ago

@chaeusangchen can you hover and what is the message?

Gabriel Llorico Over a year ago

@chaeusangchen hope it has helped you :D

vdL · Accepted Answer · 2019-08-07 11:44:17Z

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

edited Aug 7, 2019 at 11:44

answered Aug 7, 2019 at 8:08

vdL

2771 silver badge8 bronze badges

2 Comments

chaeusang chen Over a year ago

I get an error at select new (paymentTransaction, payment). the error is (range valiable)? payment Transaction

vdL Over a year ago

Updated the queries, I made some typos, code now compiles.

Mirza Ghulam Rasyid · Accepted Answer · 2019-08-07 07:21:03Z

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 7, 2019 at 7:21

Mirza Ghulam Rasyid

1844 bronze badges

1 Comment

chaeusang chen Over a year ago

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).

Collectives™ on Stack Overflow

Change SQL Query to LINQ, asp.net MVC

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related