LINQ Join inside Join, how to?

问题:

I am new to LINQ. I am using LINQ to Objects (I think) and the way the data is set up I can't directly get to a piece of data I need. This is the general structure of what I NEED to do:

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

I need a single piece of data from Process.
So far I have figured out how to do a LEFT OUTER JOIN with LINQ using DefaultIfEmpty() but I cannot figure out out to get Process to JOIN with ProjectStatus.
So far I have this (ps is ProjectStatus):

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

but that gives me an error about "ps not in scope on left side of equals".

EDIT
For reference sake, the "join" that I included is not the entire statement. "ProjectStatus" (ps) is joined to the "Project" (pr) and I need "Process" (ec) joined as well.
ec does not have any relationship directly to pr and so it must be joined through ps. Flipping the "on" statements doesn't solve the problem.

EDIT 2
The full 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()

That does not work.
I have also tried taking out that line and just using this:

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

And I have 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()

回答1:

You just need to flip the on statement

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

In regards to the multiple joins you should be able to chain them

回答2:

You just need to swap the ec and ps around

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

To do the left join you need to do the following

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

There are various ways to name the objects but I usually find I write the join to go into the object name with nullable prefixed and then give it the same name again by using the next line from ec in nullablePsec.DefaultIfEmpty()

EDIT: After seeing the full linq query you need to be joining like this

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

Note here the join is on the prps variable name as you are selecting into to prps in the line above it

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

The into prps has changed the variable name that you will be working with in the join query onto the process list.

EDIT 2 It might work a bit better if you write the statement as follows

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()

回答3:

When you do a group join, the variable from the inner sequence goes out of scope and you no longer have access to the individual elements. You need to move the associated DefaultIfEmpty() up if you want that access or not do the group join 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()
            // ...