CODEWITHSUNDEEP
c#linqjoin
hmoe
hmoe

Reputation: 13

How to do a Linq Left Outer Join with a middle table?

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.

Upvotes: 1

Views: 310

Answers (1)

madreflection
madreflection

Reputation: 4957

Your LINQ translation is just a little off.

The SQL has an inner join on crm.tbljobs followed by outer joins on common.tblSalesPerson and common.tblSupervisors.

The LINQ has outer joins on dbc.tblJobs and dbc.tblSalesPersons followed by an inner join on dbc.tblSupervisors.

into temp1 ... from j in in temp1.DefaultIfEmpty() makes the outer join happen on the table introduced prior to the into, which is dbc.tblJobs.

So it should be:

...
// inner join
join j in dbc.tblJobs on jp.JobID equals j.JobID

// left outer join
join sp in dbc.tblSalesPersons on j.SalesPersonID equals sp.SalesPersonID into salesPersons
from sp in salesPersons.DefaultIfEmpty()

// left outer join
join s in dbc.tblSupervisors on j.SupervisorID equals s.SupervisorID into supervisors
from s in supervisors.DefaultIfEmpty()
...

I changed temp1 and temp2 to more meaningful names to demonstrate what they represent in the outer join syntax. Note the relationship and relative position of dbc.tblSalesPersons to salesPersons, for example.

One more thing to remember is that sp and s can be null, so make sure you check for that before accessing their FirstName, LastName, and Surname properties.

Upvotes: 1

Related Questions