如何用count、groupby和isull子句将SQL查询与LINQ相协调

Question

我有以下SQL查询，我正试图将其转换为LINQ Query

SELECT C.NAME,C.MOBILEPHONE,ISNULL (SUM(P.PAYMENTAMOUNT),0)  AS 
PAYAMOUNT,BILLAMOUNT ,B.ID,BILLNO , BILLDATE FROM CUSTOMERS C
JOIN BILLS B ON B.CUSTOMERID=C.ID
LEFT JOIN BILLPAYMENTS P ON P.BILLID=B.ID
GROUP BY C.NAME ,B.BILLAMOUNT,B.ID,BILLNO,BILLDATE,C.MOBILEPHONE
HAVING B.BILLAMOUNT> ( ISNULL(SUM(P.PAYMENTAMOUNT),0)) 

您如何在LINQ中表示这一点？

我看到了这个的典型实现

var query = from c in db.Customers
join b in db.Bills on c.Id equals b.CustomerId
join p in db.BillPayments on b.Id equals p.BillId into cs
from xx in cs.DefaultIfEmpty()
group xx by new { c.Name, c.MobilePhone, b.BillAmount, b.BillNo, b.Id, b.BillDate } into g
where g.Sum(p => p.PaymentAmount) < g.Key.BillAmount
select new
{
Received = g.Key,
ReceivedTotal =   g.Sum(p => p.PaymentAmount) 
};

但我不确定如何实现以下内容：

HAVING B.BILLAMOUNT> ( ISNULL(SUM(P.PAYMENTAMOUNT),0)) 

Answer 1

所以你有一个Customers的序列，其中每个Customer有零个或多个Bills，每个Bill只属于一个Customer：一个简单的一对多关系。

此外，每个Bill具有零个或多个BillPayments，其中每个BillPayment恰好属于一个Bill，也是一对多关系。

唉，你忘了告诉我们你的课了。如果您遵循实体框架代码优先的约定，您将获得类似于以下内容的内容：

class Customer
{
public int Id {get; set;}
public string Name {get; set;}
...
// every Customer has zero or more Bills (one-to-many)
public virtual ICollection<Bill> Bills {get; set;}
}
class Bill
{
public int Id {get; set;}
public int BillNo {get; set;}
public decimal BillAmount {get; set;}
...
// every Bill belongs to exactly one Customer, using foreign key
public int CustomerId {get; set;}
public virtual Customer Customer {get; set;}
// every Bill has zero or more BillPayments (one-to-many)
public virtual ICollection<BillPayment> BillPayments {get; set;}
}
class BillPayment
{
public int Id {get; set;}
...
// every BillPayment belongs to exactly one Bill, using foreign key
public int BillId {get; set;}
public virtual Bill Bill{get; set;}
// every Bill has zero or more BillPayments (one-to-many)
public virtual ICollection<BillPayment> BillPayments {get; set;}
}

在实体框架中，表的列由非虚拟属性表示，虚拟属性表示表之间的关系。

您还忘记了查询的要求。在我看来，你想要以下内容：

请给我账单的某些属性(Id、BillNo、BillDate、BillAmount(，以及此账单的客户的某些属性，以及所有尚未全额支付的账单的特定属性(姓名和手机(。或者换言之，在所有付款之和小于账单金额的所有账单中。

实体框架的一个好处是，您不必自己进行联接，您可以使用虚拟属性。实体框架知道表之间的关系，并为您进行适当的联接。

为了好玩，我们将添加原始的BillAmount、AmountPayed和RemainingAmount，这样当你用客户的手机给他打电话时，你就可以告诉他还需要支付多少

在需求中，您可以看到Bills的核心作用，因此让我们将其作为起点：

// (1) from all bills, calculate the AmountPaid; remember the original bill data:
var notFullyPaidBills = myDbContext.Bills
.Select(bill => new
{
BillData = bill,
AmountPaid = bill.BillPayments
.Select(billPayment => billPayment.PaymentAmount)
.Sum(),
})
// (2) Keep only those bills that are not fully paid yet
.Where(bill => bill.Bil.BillAmount > bill.AmountPaid)
// (3) from the remaining bills select the required properties:
.Select(bill => new
{
// Customer properties:
CustomerName = bill.BillData.Customer.Name,
MobilePhone = bill.BillData.Customer.MobilePhone,
// bill properties:
BillId = bill.BillData.Id,
BillNo = bill.BillData.BillNo,
BillDate = bill.BillData.Date,
// Amounts:
BillAmount = bill.BillData.BillAmount,
AmountPaid = bill.AmountPaid,
RemainingAmount = bill.BillData.BillAmount - bill.AmountPaid,
});

看到了吗？当使用实体框架类的虚拟属性时，查询看起来会比您自己进行(组(联接时更简单、更直观。