how to use left join in entity framework query?

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.

Check this link out. It contains an example for Left outer join. http://itworksonmymachine.wordpress.com/2009/09/27/sql-to-linq-cheat-sheet/ — samar, Feb 13 '14 at 08:17

score 5 · Accepted Answer · answered Feb 13 '14 at 08:17

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

score 1 · Answer 2 · answered Feb 13 '14 at 08:15

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

how to use left join in entity framework query?

2 Answers2

Linked