CODEWITHSUNDEEP
c#entity-frameworklinq
J86
J86

Reputation: 15257

LINQ query to get outstanding invoices

In my Entity Framework application, I have an Entity called Invoice.cs, here is what that looks like:

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
}

I am attempting to query the database to get a list of all outstanding invoices. An outstanding invoice is the following:

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

I'm stuck on working out if an invoice is outstanding or not in my LINQ query, which currently looks like this:

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

startDate and endDate are parameters passed in to filter the result.

Upvotes: 1

Views: 1070

Answers (3)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236248

I assume that Payment entity has an Amount property (otherwise you should change it to appropriate property name):

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

You optionally can add filters which you have now for deleted status and dates of invoice, but from definition of outstanding invoice, this is how you should check whether invoice is outstanding.

Generated query will look like:

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

Upvotes: 3

Ofir Winegarten
Ofir Winegarten

Reputation: 9365

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

Add inv.Payments.Count() < inv.Amount to your where clause

Or if you have an amount in your class and you need to sum them up, use inv.Payments.sum(p=>p.TheAmountProperty) < inv.Amount

Upvotes: 1

Sajeetharan
Sajeetharan

Reputation: 222592

Just use Payments.Sum()

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

Upvotes: 1

Related Questions