I've been following a few examples from this website on how to create Linq Left Outer Join queries but I haven't found any examples of where the "outer key in the left join doesn't point to the inner key but instead points to a previous key". Bear with me for that phrasing I know it's not correct but have a look at the following snippets of code and maybe it will be clearer.
Specifically, see the first left join where sp.SalesPersonID = j.SalesPersonID.
select rt.Name as ResourceType, s.FirstName + ' ' + s.Surname as Supervisor, sp.FirstName + ' ' + sp.LastName as SalesPerson, tr.OrderCodeID, tr.SkillID
, j.CustomerName, j.JobNumber
from dbo.TaskResource tr join projects.Task t on t.ID = tr.taskiD
join dbo.ResourceType rt on rt.ID = tr.ResourceTypeID
join projects.projecttask pt on pt.taskid = tr.taskid
join projects.jobproject jp on jp.projectid = pt.projectid
join crm.tbljobs j on j.jobid = jp.jobid
left join common.tblSalesPersons sp on sp.SalesPersonID = j.SalesPersonID
left join common.tblSupervisors s on s.SupervisorID = j.SupervisorID
where JobDeleted is null or JobDeleted = 0
order by ResourceType
When converted to Linq it would make
...from j in temp1.DefaultIfEmpty()
join sp in dbc.tblSalesPersons on j.SalesPersonID equals sp.SalesPersonID into temp2
So far so good. But when I do the next left join I though it would just be the same thing but pointing to one of the previous keys as I mentioned earlier so instead of using the sp variable which I've seen several examples of, I use the j variable which is from a previous join:
from sp in temp2.DefaultIfEmpty()
join s in dbc.tblSupervisors on j.SupervisorID equals s.SupervisorID
Here is the full code snippet:
List<ResourceTreeObject> resourceTreeObjects = (
from tr in dbc.TaskResources
join t in dbc.Tasks on tr.TaskID equals t.ID
join rt in dbc.ResourceTypes on tr.ResourceTypeID equals rt.ID
join pt in dbc.ProjectTasks on tr.TaskID equals pt.TaskID
join jp in dbc.JobProjects on pt.ProjectID equals jp.ProjectID
join j in dbc.tblJobs on jp.JobID equals j.JobID into temp1
from j in temp1.DefaultIfEmpty()
join sp in dbc.tblSalesPersons on j.SalesPersonID equals sp.SalesPersonID into temp2
from sp in temp2.DefaultIfEmpty()
join s in dbc.tblSupervisors on j.SupervisorID equals s.SupervisorID
where j.JobDeleted == null || j.JobDeleted == 0
select new ResourceTreeObject
{
TaskResourceID = tr.ID
,
TaskID = tr.TaskID
,
ResourceTypeID = tr.ResourceTypeID
,
ResourceType = rt.Name
,
SkillID = tr.SkillID
,
OrderCodeID = tr.OrderCodeID
,
PermissionID = tr.PermissionID
,
JobID = j.JobID
,
JobNumber = j.JobNumber
,
CustomerName = j.CustomerName
,
Salesperson = sp.FirstName + " " + sp.LastName
,
Supervisor = s.FirstName + " " + s.Surname
}).ToList();
And this results in the wrong query. The last "left join" is treated like an inner join and returns the wrong number of rows. So in essence what I'm asking is, how do I (in LinQ) do two consecutive left outer joins after doing several consecutive inner joins but use the key from one of the previous tables in my left out join?
Also I'm not sure what the correct terminology for inner/outer keys etc. hence the awkward phrasing and title. Perhaps someone could correct that so it would be more beneficial to others. Thank you.