c# Entity Framework - linq to entity query with subquery

Go To StackoverFlow.com

0

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

2012-04-05 23:32
by Rob


1

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;
2012-04-05 23:38
by Luke McGregor
Thanks @Luke McGregor, I was applying too much thought to it and trying to to something with .any() which isn't necessary - Rob 2012-04-05 23:57
Nah you definitally want a sum here, You can do the same as this in full fluent syntax but i think it looks readable like it i - Luke McGregor 2012-04-05 23:59
Ads