Outer Join equivalent with Linq

See more:

C#

LINQ

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

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>

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