Click here to Skip to main content
15,444,490 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:

SQL
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:

C#
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
Posted
Updated 8-Feb-11 23:14pm
v4
Comments
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:

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

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