Menu
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;
Any ideas from anyone?
406
You can use LINQ to perform a left outer join by calling the DefaultIfEmpty method on the results of a group join.
In LINQ, an inner join is used to serve a result which contains only those elements from the first data source that appears only one time in the second data source. And if an element of the first data source does not have matching elements, then it will not appear in the result data set.
In LINQ, LEFT JOIN or LEFT OUTER JOIN is used to return all the records or elements from the left side collection and matching the elements from the right side of the collection. In LINQ, to achieve the LEFT Join behavior, it is mandatory to use the "INTO" keyword and "DefaultfEmpty()" method.
From 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()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
