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 SubPrivate 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
Likewise, we can walk the supervisors for any given employee using the following:
Sub DisplayReportsTo(ByVal emp As Employee)Private
If Not emp.Employee Is Nothing Then
Console.Write("Reports to: {0} {1} ", emp.Employee.FirstName, emp.Employee.LastName)
DisplayReportsTo(emp.Employee)
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."