Question
I am working with entity framework 4.5. I have to convert an SQL query to entity query:
SELECT Customer.CustCode, Invoice.InvoiceId, Invoice.BatchNumber, Invoice.InvoiceDate, Invoice.AdjustFlag, Invoice.InvoiceAmount,
Invoice.InvoiceNote, Invoice.AmountPaid, Customer.BillingContact, Customer.BillingCompany, Customer.BillingStreet1,
Customer.BillingStreet2, Customer.BillingCity, Customer.BillingState, Customer.BillingZip, [Order].PickupDate, [Order].OrderNumber,
[Order].OrderTotal, [Order].ProNumber, [Order].PickupCompany, [Order].PickupCity, [Order].PickUpState, [Order].Dcompany,
[Order].Dcity, [Order].Dstate, CONVERT(varchar(5), DeliverInTime, 114) AS DelInTime, [Order].PiecesWeight1, [Order].BaseRATE,
[Order].POD, [Order].Requester, [Order].Po1, [Order].Po2, AccessorialCharge.Description,
OrderDriverExtraCharge.AccessorialChargeDesc, OrderDriverExtraCharge.AccessorialChargeAmount, [Order].NormalDiscount,
- 1 * [Order].DISCAmount AS DISCAmount
FROM (((Invoice INNER JOIN
[Order] ON Invoice.InvoiceId = [Order].InvoiceId) INNER JOIN
Customer ON Invoice.CustID = Customer.CustID) LEFT JOIN
OrderDriverExtraCharge ON [Order].OrderNumberId = OrderDriverExtraCharge.OrderNumberId) LEFT JOIN
AccessorialCharge ON OrderDriverExtraCharge.AccessorialChargeId = AccessorialCharge.AccessorialChargeId
where Invoice.InvoiceId = '1117782'
If I change OrderDriverExtraCharge.OrderNumberId) LEFT JOIN to OrderDriverExtraCharge.OrderNumberId) JOIN (simple join) or inner join it's not showing the right result.
I have tried this:
from I in db.Invoices
join O in db.Orders on I.InvoiceId equals O.InvoiceId
join C in db.Customers on I.CustId equals C.CustId
join OD in db.OrderDriverExtraCharges on O.OrderNumberId equals OD.OrderNumberId
join AC in db.AccessorialCharges on OD.AccessorialChargeId equals AC.AccessorialChargeId
where I.InvoiceId == invoice.InvoiceId
select new PrintInvoiceViewModel()
But it is not showing the required results. Please help me, I will mark your answer if it worked for me. Thank you.
Accepted Answer
You can do this:
from I in db.Invoices
join O in db.Orders on I.InvoiceId equals O.InvoiceId
join C in db.Customers on I.CustId equals C.CustId
from OD in db.OrderDriverExtraCharges
.Where(w=>w.OrderNumberId==O.OrderNumberId).DefaultIfEmpty()
from AC in db.AccessorialCharges
.Where(w=>w.AccessorialChargeId==OD.AccessorialChargeId).DefaultIfEmpty()
where I.InvoiceId == invoice.InvoiceId
select new PrintInvoiceViewModel()
Popular Answer
Your should use DefaultIfEmpty method, which returns the elements of an IEnumerable<T>, or a default valued singleton collection if the sequence is empty:
from I in db.Invoices
join O in db.Orders on I.InvoiceId equals O.InvoiceId
join C in db.Customers on I.CustId equals C.CustId
join OD in db.OrderDriverExtraCharges on O.OrderNumberId equals OD.OrderNumberId into ODs
from OD in ODs.DefaultIfEmpty()
join AC in db.AccessorialCharges on OD.AccessorialChargeId equals AC.AccessorialChargeId into ACs
from AS in ACs.DefaultIfEmpty()
where I.InvoiceId == invoice.InvoiceId
select new PrintInvoiceViewModel()
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
