Click here to Skip to main content
14,974,076 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
lw@zi 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[^]
   

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