Managing self referencing tables with LINQ to SQL

Using a single self referencing table is a common database pattern for trees of data. As an example of this concept, we can use the Employee table in Northwind. It has a self referential relationship set up using the ReportsTo field. If we drag this into the LINQ to SQL designer, it will infer the self relation and create the appropriate EntitySet/EntityRef relationship exposing it with the Employees (for subordinates of a given employee) and Employee (for the reference to the boss). The ReportsTo property contains the ID of the related property.

Inspecting the attributes used for Employees, we see that the association is mapped as follows:

<Association(Name:="Employee_Employee", Storage:="_Employees", OtherKey:="ReportsTo")> _
Public Property Employees() As EntitySet(Of Employee)

Similarly, Employee is mapped as follows:

<Association(Name:="Employee_Employee", Storage:="_Employee", ThisKey:="ReportsTo", IsForeignKey:=true)> _
Public Property Employee() As Employee

If we wish to traverse the subordinates of a given employee (boss), we can use the following:

Private Sub Main()
   Dim dc As New NWindDataContext
   Dim StartingEmp = (From emp In dc.Employees Where emp.ReportsTo Is Nothing).FirstOrDefault
   Console.WriteLine("{0}{1} {2}", Space(indentLevel * 5), StartingEmp.FirstName, StartingEmp.LastName)
   DisplaySubordniates(StartingEmp)
End Sub

Private indentLevel As Integer
Private Sub DisplaySubordniates(ByVal emp As Employee)
   If emp.Employees.Count > 0 Then
      indentLevel += 1
      For Each emp In emp.Employees
         Console.WriteLine("{0}{1} {2}", Space(indentLevel * 5), emp.FirstName, emp.LastName)
         DisplaySubordniates(emp)
      Next
      indentLevel -= 1
   End If
End Sub

Realize that using this implementation does have a cost. Each time we iterate over the Employee or Employees, we lazy load the resulting set. This can cause a rather chatty interaction with the data source. Because we are working with n levels of heirarchies, we can not optimize this interaction using the load options. If we were to try to add a LoadOptions, we get the following run-time exception: "Cycles not allows in LoadOptions.LoadWith type graph."