SQL限制在linq中等效

本文关键字：linq SQL | 更新日期: 2023-09-27 18:06:01

我正在寻找一个SQL查询下面的linq等效

select * from tableA as A
left join tableB as B on A.Id = B.Id
left join tableC as C on B.Id = C.Id
left join tableD as D on C.Id = D.Id and D.OrderId = B.OrderId

我最感兴趣的是如何正确地限制使用这个表达式的结果:

 and D.OrderId = B.OrderId

c#代码
var data = from a in tableA
join innerB in tableB on a.Id equals innerB.Id into INNERB
from b in INNERB.DefaultIfEmpty()
join innerC in tableC on b.Id equals innerC.Id into INNERC
from c in INNERC.DefaultIfEmpty()
join innerD in tableD on c.Id equals innerD.Id into INNERD
from d in INNERD.DefaultIfEmpty().Where(p=>p.OrderId == b.OrderId)

从db返回的linq结果不匹配SQL查询。如果您能向我解释如何使用应用于每一行的前一个连接表中的变量，我将不胜感激。

我也试过了，但是编译器抛出了一个错误，"b的名字在当前上下文中不存在":

join innerD in tableD.Where(p=>p.OrderId == b.OrderId) on c.Id equals innerD.Id into INNERD
from d in INNERD.DefaultIfEmpty()

SQL限制在linq中等效

查看此答案以获得更简单的LEFT JOIN语法:https://stackoverflow.com/a/4739738/1869660

你的查询应该是:

var data = from a in tableA
           from b in tableB.Where(x => x.Id == a.Id)
                           .DefaultIfEmpty()
           from c in tableC.Where(x => x.Id == b.Id)
                           .DefaultIfEmpty()
           from d in tableC.Where(x => (x.Id == c.Id) && (x.OrderId == b.OrderId))
                           .DefaultIfEmpty()
           select ...
相关文章：