how to use left join in entity framework query?

asp.net-mvc c# entity-framework linq

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.

1
1
10/9/2017 7:47:48 AM

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()
5
2/13/2014 8:17:53 AM

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow