linq2sql, one to many through relations

Question

I have OrgUnits, OrgUnitJobs,
Employees, OrgUnitJobEmployee (this one may seem redundant in this context, its here fyi) tables.

org units have jobs defined in OrgUnitJobs (1..n) employee (can also have many jobs) has jobs defined in OrgUnitJobEmployee table. and employee has a PrimaryJob (1..1) in OrgUnitJobs table, so how do I get a list of all the employees who's primary job is in that org unit.

I am using LINQ2SQL ORM, so I have an OrgUnit partial class, I would like to be able to get this employee list within that partial ORM class, without writing another query, or a linq select. I just dont want to go to my OCRepository class and write a GetEmployeeListForOrgUnit(orgUnitId) there, it does not sound right.

Answer 1

Check DataContext.LoadOptions to tweak what's included in the first query. This means it'll execute a join, instead of N subqueries.

In your case, something like:

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Job>(j => j.PrimaryEmployees);
dbContext.LoadOptions = dlo;