Click here to Skip to main content
15,390,011 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
.Where(a => a.Id == jobId)

Job job = jobList[0];

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

foreach (JobTypeParameterMap jtpm in jobTypeParameterMap)

return 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
Updated 8-Feb-11 23:14pm
dan!sh 8-Feb-11 13:13pm
It will be very easy to help if you could provide the code you are using.

1 solution

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900