LINQ Join in Join, how?

Question

I am new to LINQ. I am using LINQ to Objects (I think) and a way to tune data. I cannot directly get the part of the data that I need. This is the general structure of what I need:

FROM Project
    LEFT OUTER JOIN TechnologySectors
    LEFT OUTER JOIN SelectedAgencies
    LEFT OUTER JOIN ProjectStatus
        JOIN Process

I need a separate piece of data from the process. So far I have figured out how to make a LEFT OUTER JOIN with LINQ using DefaultIfEmpty(), but I cannot figure out how to get the process to connect to ProjectStatus.
So far I have this (ps - ProjectStatus):

join ec in this._Process.GetProcessList() on ps.ProcessID equals ec.ProcessID into psec

but it gives me the error that "ps is not in scope on the left side equals".

EDIT
For reference, the “connection” that I turned on is not the whole operator. ProjectStatus (ps) joins Project (pr), and I also need Process (ec).
ec is not directly related to pr, and therefore it needs to be combined through ps. Turning the on statements over does not solve the problem.

EDIT 2
Complete LINQ query:

from pr in this._projectRepo.GetAllProjects()
join tr in this._techRepo.GetTechnologySectors() on pr.TechnologySectorID equals tr.TechnologySectorID into prtr
join ev in this._ecEnvRepo.GetAllSelectedAgencies() on pr.ID equals ev.ID into prev
join ps in this._ecProjectStatRepo.GetAllECProjectStatus() on pr.ID equals ps.ID into prps
***THIS LINE***join ec in this._ecProcessRepo.GetProcessList() on ps.ProcessID equals ec.ProcessID into psec
from tr in prtr.DefaultIfEmpty()
from ev in prev.DefaultIfEmpty()
from ps in prps.DefaultIfEmpty()
from ec in psec.DefaultIfEmpty()

This does not work.
I also tried to take out this line and just used this:

from ec in this._ecProcessRepo.GetProcessList() where (ec.ProcessID == ps.ProcessID)

And I tried using this instead of the ps and ec lines:

from ps in this._ecProjectStatRepo.GetAllECProjectStatus() where (ps.ID == pr.ID)
join ec in this._ecProcessRepo.GetProcessList() on ps.ProcessID equals ec.ProcessID into psec
from ec in psec.DefaultIfEmpty()

Answer 1

You just need to flip the statement on

join ec in this._Process.GetProcessList() on ec.ProcessID equals ps.ProcessID into psec

As for multiple joins, you should be able to bind them

Answer 2

ec ps

ec in this._Process.GetProcessList() on 
    ps.ProcessID equals ec.ProcessID into psec

,

ec in this._Process.GetProcessList() on 
    ps.ProcessID equals ec.ProcessID into nullablePsec
from ec in nullablePsec.DefaultIfEmpty()

, , , , , from ec in nullablePsec.DefaultIfEmpty()

: linq

ec in this._Process.GetProcessList() on 
    prps.ProcessID equals ec.ProcessID into psec

, prps, prps

join ps in this._ecProjectStatRepo.GetAllECProjectStatus() on pr.ID equals ps.ID into prps

into prps , , .

2 ,

from pr in this._projectRepo.GetAllProjects()
join tr in this._techRepo.GetTechnologySectors() on pr.TechnologySectorID equals tr.TechnologySectorID into prtr
from tr in prtr.DefaultIfEmpty()
join ev in this._ecEnvRepo.GetAllSelectedAgencies() on pr.ID equals ev.ID into prev
from ev in prev.DefaultIfEmpty()
join ps in this._ecProjectStatRepo.GetAllECProjectStatus() on pr.ID equals ps.ID into prps
from ps in prps.DefaultIfEmpty()
join ec in this._ecProcessRepo.GetProcessList() on ps.ProcessID equals ec.ProcessID into psec
from ec in psec.DefaultIfEmpty()

Answer 3

When you do a group join, the variable out of the inner sequence goes out of scope and you no longer have access to individual elements. You need to move the linked DefaultIfEmpty()up if you want this access or group not to be included in the first place.

var query = from pr in this._projectRepo.GetAllProjects()
            join tr in this._techRepo.GetTechnologySectors()
                on pr.TechnologySectorID equals tr.TechnologySectorID
                into prtr
            join ev in this._ecEnvRepo.GetAllSelectedAgencies()
                on pr.ID equals ev.ID
                into prev
            join ps in this._ecProjectStatRepo.GetAllECProjectStatus()
                on pr.ID equals ps.ID
                 into prps
            from ps in prps.DefaultIfEmpty()
            // you need to resolve `ps == null` issues here
            let key = ps == null ? -1 : ps.ProcessID
            join ec in this._ecProcessRepo.GetProcessList()
                on key equals ec.ProcessID
                into psec
            from tr in prtr.DefaultIfEmpty()
            from ev in prev.DefaultIfEmpty()
            from ec in psec.DefaultIfEmpty()
            // ...