Working with Linq2Sql with multiple joins and various sorts

Question

Hello all, I have another Linq2Sql question I'm hoping I can get help with. In addition to working through to an answer, I'd love to get some "insight" to your thought process. Linq2Sql is still far from natural for me to work with, some of it has to do with the many ways in which it's syntax can be generated.

OK, what I'm trying to solve, the table structure:

---------
Candidate
---------
Id
Name
CompanyId
etc..

------------
CandidateJob
------------
Id
CandidateId (foreign key to Candidate table)
JobId (foreign key to Job table)

-----------
Job
-----------
Id
Title

--------------------
CandidateJobActivity
--------------------
Id
CandidateJobId (foreign key to CandidateJob table)
ActivityType
CreatedOn
CreatedBy

I'm trying to get variations of the following data.

1) Given a Candidate.CompanyId, get a list of ALL the Candidates (from the Candidate table where the CompanyId = x), along with all the Jobs that each candidate has been assigned to (tracked in CandidateJobs and each candidate can have multiple jobs), with appropriate Job meta data like Title (from the Job table) and the MOST recent Activity (CandidateJobActivity). It should return all Candidates, even if they haven't been assigned to jobs. It should return ALL he Jobs that the candidate is assigned to. Each Candidate should be listed only once. The Query can totally return a hierarchial structure.

The result "might" be flat, or hierarchial like:

CandidateName="Todd",
CandidateId=5,
Jobs= [ { Title="Job Title 1", MostRecentActivity="TypeA", ActivityCreatedOn=dateHEre },
        { Title="Job Title 2", MostRecentActivity="Type2", ActivityCreatedOn=dateHere} ]

2) I'd like to know the thought process in composing the query -- if possible, because I'm going to have many variations of this query, like returning only the TOP 5 with most recent CandidateJobActivity. Or, return only those that HAVE jobs assigned at all, etc...so I'd really like to be able to get my head around Linq. (I've also purchased LinqPad to help facilitate the experimentation.)

3) I don't mind seeing different solutions using the various Linq syntaxes.

I can edit this to clarify more.

BTW, this is about as far as I've gotten:

  var y = (from c in Candidates.Where(x => x.AgencyId == 6)
         from jc in JobCandidates.Where(j => j.CandidateId == c.Id).DefaultIfEmpty()
         from cja in CandidateJobActivities.Where( k => k.JobCandidateId == jc.Id).OrderByDescending(x=>x.CreatedOn)
         from j in Jobs.Where (r => r.Id == jc.JobId)
        select new {c.FirstName, c.LastName, jc.JobId, cja.ActivityType, cja.CreatedOn}
);

y.Dump();

Thanks a bunch.