相当于Linq的外连接 [英] Outer Join equivalent with Linq

问题描述

大家好，

我试图将表的主键连接到外键可能为null的另一个表.查询需要返回所有相关条目以及FK为空的所有条目.使用T-SQL，我可以称其为某种外部联接，但是使用Linq?

这是我要在Linq中实现的T-SQL:

Hello everyone,

I''m attempting to join a table''s primary key to another table where the foreign key could be null. The query needs to return all related entries and all the entries where the FK is null. With T-SQL I''d call it an outer join of some sort, but with Linq?

Here''s the T-SQL I''m trying to implement in Linq:

select * from JobTypeParameterMap jtpm
inner join JobTypeParameters jtp on jtpm.JobTypeParameterId = jtp.Id
left outer join JobTypes jt on jtpm.JobTypeId = jt.Id
left outer join Jobs j on jt.Id = j.JobTypeId
where (jtpm.JobTypeId = 1 AND j.Id = 341) or jtpm.JobTypeId IS NULL

JobTypeParameterMap.JobTypeId是可以为空的FK.

我对Linq最好的方法是使用两个查询:

With JobTypeParameterMap.JobTypeId being the FK that can be null.

the best I can do with Linq is by using two queries:

public Job GetJobAndJobTypeParameters(int jobId)
{
List<job> jobList = base.Context.Jobs
.Include("JobParameters")
.Include("JobType")
.Include("JobStatus")
.Where(a => a.Id == jobId)
.ToList();

Job job = jobList[0];

List<jobtypeparametermap> jobTypeParameterMap = base.Context.JobTypeParameterMaps
.Include("JobTypeParameter")
.Where(jtpm => jtpm.JobTypeId == job.JobTypeId || !jtpm.JobTypeId.HasValue).ToList();

foreach (JobTypeParameterMap jtpm in jobTypeParameterMap)
{
job.JobType.JobTypeParameterMaps.Add(jtpm);
}

return job;
}
</jobtypeparametermap></job>

我已经看过GroupJoin运算符，并尝试了一个带有join ... into子句的查询表达式，但是我什至无法编译它，更不用说运行了..

非常感谢您提供任何帮助

I''ve looked at the GroupJoin operator and tried a query expression with a join ... into clause, but I can''t even get the thing to compile, let alone run..

any help is greatly appreciated

推荐答案

没有看到您的代码，很难猜测出您的确切情况，但是本文可以为您提供帮助:

http://www.hookedonlinq.com/OuterJoinSample.ashx [

Without seeing your code, it''s hard to guess your exact scenario, but this article may help you:

http://www.hookedonlinq.com/OuterJoinSample.ashx[^]

这篇关于相当于Linq的外连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！