如何使用LINQ进行多表左连接

Question

我正在尝试使用LINQ对三个表进行左连接。 我已经有了以下工作的SQL：

Select j.Id, u.FirstName , u.LastName, u.Role
From    Job j  
    left  join JobTranslator as jt on j.Id = jt.JobId
    left  join JobRevisor as jr on j.Id = jr.JobId
    left  join [User] as u on  jt.UserId = u.Id OR jr.UserId = u.Id
Where   u.Id = someID;

我可以使用以下两个连接来使其工作：

 IQueryable<Job> jobs =
                from j in _db.Jobs

                join jr in _db.JobRevisors on j.Id equals jr.JobId into jrs
                from jrResult in jrs.DefaultIfEmpty()

                join u in _db.Users on jrResult.UserId equals u.Id into jrU
                from jrUResult in jrU.DefaultIfEmpty()

                where jrUResult.Id == userId
                orderby j.Id
                select j;

但是当我尝试连接我的最后一个必需的表时，它不像下面这样工作。

IQueryable<Job> jobs =
                from j in _db.Jobs

                join jt in _db.JobTranslators on j.Id equals jt.JobId into jts
                from jtResult in jts.DefaultIfEmpty()

                join jr in _db.JobRevisors on jtResult.Id equals jr.JobId into jrs
                from jrResult in jrs.DefaultIfEmpty()

                join u in _db.Users on jrResult.UserId equals u.Id into jrU
                from jrUResult in jrU.DefaultIfEmpty()

                join u in _db.Users on jtResult.UserId equals u.Id into jtU
                from jtUResult in jtU.DefaultIfEmpty()

                where jtUResult.Id == userId

                orderby j.Id

                select j;

有没有任何人有想法？

- codingNightmares

---

可能是LINQ中的LEFT OUTER JOIN重复问题。 - Win

或许将第二个u重命名为其他名称？ - Quentin

我不明白的是为什么开发人员一直坚持着将SQL直接翻译成LINQ，而不是使用导航属性、集合和.Include()。这种方式通常会得到一个更直观可用的对象图。 - Craig W.

include不适用于左连接... - HelloWorld

Answer 1

来源自Linq - 多个(或)条件的左连接:

IQueryable<Job> jobs = (from j in _db.Jobs

                join jt in _db.JobTranslators on j.Id equals jt.JobId into jts
                from jtResult in jts.DefaultIfEmpty()

                join jr in _db.JobRevisors on jtResult.Id equals jr.JobId into jrs
                from jrResult in jrs.DefaultIfEmpty()


                join u in _db.Users on jtResult.UserId equals u.Id into jtU
                from jtUResult in jtU.DefaultIfEmpty()

                where jtUResult.Id == userId

                orderby j.Id

                select j).Concat(
                from j in _db.Jobs

                join jt in _db.JobTranslators on j.Id equals jt.JobId into jts
                from jtResult in jts.DefaultIfEmpty()

                join jr in _db.JobRevisors on jtResult.Id equals jr.JobId into jrs
                from jrResult in jrs.DefaultIfEmpty()

                join u in _db.Users on jrResult.UserId equals u.Id into jrU
                from jrUResult in jrU.DefaultIfEmpty()

                where jtUResult.Id == userId

                orderby j.Id

                select j
                ).Distinct()

- Belterius

---

非常感谢！那个方法可行，我只需要稍微调整一下，但基本的连接思路是有效的。再次感谢！ - codingNightmares

说实话，这是我迄今为止见过的最好的 EF 生成的 SQL 左连接脚本。 - raberana