0

I want to convert following sql query to linq.

This is what I'm trying to convert

SELECT IH.ID, IH.InvNo, IH.PaymentStatus, SUM(TotalAmount) - 
        (SELECT SUM(Amount) FROM Student WHERE InvNo=bh.InvNo) AS CollectableAmount
FROM InvoiceHeader AS IH 
INNER JOIN InvoiceDetail AS ID ON IH.ID=BD.BillId
WHERE IH.StudentID=0103 AND IH.PaymentStatus<>1
GROUP BY IH.ID, IH.InvNo, IH.PaymentStatus

I tried like this but I don't know how to get sub query inside this linq

from ih in InvoiceHeaders
                       join bd in InvoiceDetails on ih.ID equals id.BillId
                       where bh.StudentID = 0103 && bh.PaymentStatus != 1
                       select new { ID = ih.ID, BillNumber = ih.BillNumber, PaymentStatus = ih.PaymentStatus })

It says that from is not valid inside this, 

select new { ID = ih.ID, BillNumber = ih.BillNumber, PaymentStatus = ih.PaymentStatus, (from s in student) })
Kelum Srimal
  • 159
  • 1
  • 3
  • 17
  • 1
    Have you tried something like https://www.linqpad.net/Download.aspx ? – level_zebra Jul 03 '19 at 09:39
  • I didn't use it sir.could you help me to convert this manually – Kelum Srimal Jul 03 '19 at 09:46
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. – NetMage Jul 03 '19 at 18:32
  • In your SQL, what is `TotalAmount` and why you're joining with `InvoiceDetails` while you don't use anything from that table? for your question, maybe using method syntax will help you – Ammar Jul 04 '19 at 07:01

0 Answers0