将 SQL 查询更改为 LINQ、asp.net MVC

Question

如何将此SQL查询更改为LINQ？我已经尝试了几次，但没有用

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

这是我的尝试

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();

但它仍然是错误的 LINQ，因为在我的查询中，我在 ON 子句中有 2 个条件。

谢谢

Answer 1

试试这个

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();

Answer 2

上面的答案会过滤这两个条件，它们应该根据问题(PaymentID 或 TotalPaymentID(过滤其中一个条件。您可以编写两个单独的查询并使用联合，也可以在筛选之前使用笛卡尔积。

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

Answer 3

不能在 LINQ 中添加多个 ON。 上述问题的解决方案可以这样解决。

提示：只需使用多个 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();