Outer Joins with Subsonic 3.0

Question

Does anyone know of a way to do a left outer join with SubSonic 3.0 or another way to approach this problem? What I am trying to accomplish is that I have one table for departments and another table for divisions. A department can have multiple divisions. I need to display a list of departments with the divisions it contains. Getting back a collection of departments which each contain a collection of divisions would be ideal, but I would take a flattened result table too.

Using the LINQ syntax seems to be broken (I am new to LINQ though and may be using it wrong), for example this throws an ArgumentException error:

var allDepartments = from div in Division.All()
    join dept in Department.All() on div.DepartmentId equals dept.Id into divdept
    select divdept;

So I figured I could fall back to using the SubSonic query syntax. This code however generates an INNER JOIN instead of an OUTER JOIN:

List<Department> allDepartments = new Select()
    .From<Department>()
    .LeftOuterJoin<Division>(DepartmentsTable.IdColumn, DivisionsTable.DepartmentIdColumn)
    .ExecuteTypedList<Department>();

Any help would be appreciated. I am not having much luck with SubSonic 3. I really enjoyed using SubSonic 2 and may go back to that if I can't figure out something as basic as a left join.

Answer 1

Getting back a collection of departments which each contain a collection of divisions would be ideal

SubSonic does this for you (if you setup your relationships correctly in the database), just select all Departments:

var depts = Model.Department.All();

There will be a property in each item of depts named Divisions, which contains a collection of Division objects.