0

I have following tables and relationship between them. I need the result as: InvoiceId, (WorkDescriptions for that invoice joined by some delimiter), (Sum of Amounts done by PaymentMethod = 'ABC' for that Invoice).

How can I achieve this in a single LINQ Query syntax (not expression/method syntax)?

UPDATE: Note that data in InvoicePayment and InvoiceWorkDescription tables can be empty (scenario where invoice is created but no details are captured yet).

enter image description here

Deepak Agarwal
  • 458
  • 1
  • 4
  • 18
  • Basically you can achieve this by using navigation properties. Since you don't show a class model we can't help you. Neither do you show your own efforts so it's hard to tell where *specifically* you're stuck. The duplicate seems to answer your main question. – Gert Arnold Nov 21 '17 at 16:28

1 Answers1

0

You could try something like this:

var result = 
   from invoice in invoices
   join workDescription in invoiceWorkDescription 
       on invoice.Id equals workDescription.InvoiceId into workDescriptions
   join payment in invoicePayments
       on invoice.Id equals payment.InvoiceId into payments
   select new {
        InvoiceId = invoice.InvoiceId,
        Descriptions = string.Join(", ", workDescriptions.Select(wd => Description),
        TotalPayments = payment.Where(p => p.PaymentMethod == "ABC").Sum(p => p.Amount) }
mageos
  • 1,216
  • 7
  • 15
  • Thanks, but there are 2 main requirements missing from your solution: 1. See UPDATE in my question below. 2. I need to sum the payment based on some condition and result after applying that condition can be a null result – Deepak Agarwal Nov 21 '17 at 15:11
  • I am not sure about 1. I am not fully understanding the Update. As for 2. I have updated my solution. – mageos Nov 21 '17 at 16:14