I would like to know how to convert this SQL Query into a LinQ equivalent:
SELECT nContractDeliverables.* FROM (nContractDeliverables INNER(Join) nContractPaymentLines ON (nContractDeliverables.Del
iverableNo
= nContractPaymentLines.Deli
verableNo)
AND (nContractDeliverables.[MR
No] = nContractPaymentLines.[Mr No])) LEFT JOIN nFacilityDeliverables ON (nContractDeliverables.Del
iverableNo
= nFacilityDeliverables.Deli
verableNo)
AND (nContractDeliverables.[MR
No] = nFacilityDeliverables.[MR No]) WHERE (((nFacilityDeliverables.F
acility) Is Null)) and nContractDeliverables.[mr no] = " & ntq(ContractNo) & " and not nz(AdditionalWorkFlag) Order by nContractDeliverables.[Mr no], nContractDeliverables.Deli
verableNo;
")
I know to do standard Joins ... but this one baffles me ...
This is what I've written so far, but I'm not sure if it is correct ...
Dim rsContractDeliverables = From cd In dc2.nContractDeliverables Join _
fd In dc.nFacilityDeliverables On cd.DeliverableNo Equals fd.DeliverableNo And _
fd.MR_No Equals cd.MR_No Group Join _
pl In dc3.nContractPaymentLines On cd.DeliverableNo Equals pl.DeliverableNo And _
cd.MR_No Equals pl.Mr_No Into Group Where _
((fd.Facility Is Nothing) And cd.MR_No = ntq(pContractNumber) And cd.AdditionalWorkFlag <> Nothing) Order By _
cd.MR_No, cd.DeliverableNo
I'm basing the above work on an article where "Group Join" seems to be used to translate LEFT JOINS.
See:
http://news.speeple.com/msdn.com/2007/12/31/converting-sql-to-linq-part-6-joins-bill-horst.htmPS: Disregard ntq functions ...
Start Free Trial