如何使用LINQ保留多张表的联接 [英] How to left join multiple tables with LINQ

问题描述

我正试图让LINQ联接三个表.我的SQL工作如下:

I'm trying to left join three tables with LINQ. I have the SQL working as below:

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;

我可以将其与两个联接一起使用，如下所示:

I can get it to work with two joins like below:

 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;

但是当我尝试加入最后一个需要的表时，它就不能像下面那样工作.

But when I try to join my last needed table it doesn't work like below.

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;

任何人有什么想法吗?

推荐答案

来自 Linq-在多个(OR)条件下左联接:

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

这篇关于如何使用LINQ保留多张表的联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！