If I have the following tables/entities:
Invoice
- InvoiceId (PK)
- InvoiceAmount
InvoicePayment
- InvoicePaymentId (PK)
- InvoiceId (FK)
- PaymentAmount
How can I construct a Linq to Entity query that selects invoices with an outstanding amount - bearing in mind that the existence of a payment does not indicate that the invoice does not have an outstanding amount (i.e. partial payment are possible).
I would go for something along the lines of this
from i in invoices
where i.Payments.Sum(p=>p.PaymentAmount) < i.InvoiceAmount
select i;