c# - 获取未结发票的 LINQ 查询

Question

在我的 Entity Framework 应用程序中，我有一个名为 Invoice.cs 的实体，如下所示:

public class Invoice : IEntity
{
  public Invoice()
  {
    Date = DateTime.UtcNow;
    CreatedOn = DateTime.UtcNow;
  }

  public int Id { get; set; }
  public decimal Amount { get; set; }
  public decimal Discount { get; set; }
  public DateTime Date { get; set; }
  public bool IsDeleted { get; set; }
  public bool IsSaleOrderInvoice { get; set; }
  public InvoiceStatus Status { get; set; }
  public DateTime CreatedOn { get; set; }
  public DateTime? ModifiedOn { get; set; }
  public int OrderId { get; set; }
  public virtual Order Order { get; set; }
  public virtual ICollection<Payment> Payments { get; set; }
}

public enum InvoiceStatus
{
  Outstanding = 0,
  Settled = 1,
  Overpaid = 2
}

我正在尝试查询数据库以获取所有未结发票的列表。未结发票如下:

If the total of the payments made against the Invoice are less than the Invoice Amount, then the invoice is Outstanding.

我一直在研究是否在我的 LINQ 查询中有未清发票，目前看起来像这样:

var outstandingInvoices = from inv in _context.Invoices
    where !inv.IsDeleted && inv.Date >= startDate && inv.Date <= endDate
    select inv;

startDate 和endDate 是传入的参数，用来过滤结果。

Answer 1

我假设 Payment 实体有一个 Amount 属性(否则您应该将其更改为适当的属性名称):

var outstandingInvoices = from inv in _context.Invoices
                          where inv.Payments.Sum(p => p.Amount) < inv.Amount
                          select inv;

您可以选择添加您现在拥有的用于删除发票状态和日期的过滤器，但是根据未结发票的定义，这是您应该检查发票是否未结清的方式。

生成的查询如下所示:

SELECT
    result.Id,
    result.Amount,
    result.Discount,
    result.IsDeleted,
    -- etc
    FROM (SELECT
            i.Id,
            i.Amount,
            i.Discount,
            i.IsDeleted,
            -- etc
            (SELECT SUM(p.Amount) 
             FROM [dbo].[Payments] p 
             WHERE i.Id = p.Invoice_Id) AS totalPayment
          FROM [dbo].[Invoices] AS i
    )  AS result
    WHERE totalPayment < result.Amount