如何使用实体框架查询左连接？ [英] how to use left join in entity framework query?

问题描述

我与实体框架工作4.5。我有一个SQL查询转换为实体查询：

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' 

如果我修改 OrderDriverExtraCharge.OrderNumberId）LEFT JOIN 到 OrderDriverExtraCharge.OrderNumberId）JOIN （简单连接）或内加入其不显示结果仪式

if i changes OrderDriverExtraCharge.OrderNumberId) LEFT JOIN to OrderDriverExtraCharge.OrderNumberId) JOIN (simple join)or inner join its not showing the rite result.

我已经在尝试这样做： -

i have tried this in :-

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 not showing the required results. please help me , i will mark your answer if it work for me. thank you

推荐答案

您可以这样做：

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

这篇关于如何使用实体框架查询左连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！