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