c# - 如何使用 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;

有什么想法吗？

Answer 1

来自 Linq - left join on multiple (OR) conditions :

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