Whats the best way to model parent child relationships stored in a join table in LinqToSql?

Question

To simplify my current situation lets say I have 2 tables (legacy so can't touch the schema to play better with Linq)

node
Columns: key_field1, key_field2, [lots of other fields]

node_children
Columns: parent_key_field1, parent_key_field2, child_key_field1, child_key_field2

node_children is similar to the has_and_belongs_to_many join table from Rails... except that both ids refer to the same table.

Ideally I would like to have a Node Class which has a Property Children, which returns the right List? Results should be equivalent to

select * from node 
join node_children 
on node.key_field1 = node_children.child_key_field1 and node.key_field2 = node_children.child_key_field2
where node_children.parent_key_field1 = @paramX and node_children.parent_key_field2 = @paramY

Whats the best way to model this in Linq to Sql?

Answer 1

This is what I came up with (works too!)
Open up the ORM generated Node class MyDatabase.cs

partial class Node
{
  public IEnumerable<Node> Children
  {
    get
    {
      MyDatabaseDataContext dc = new MyDatabaseDataContext();
      return from link in this.ChildLinks
          join node in dc.Nodes on new { Site = link.child_key_field1, ID = link.child_key_field2 } equals new { Site = node.key_field1, ID = node.key_field2 }
          select node;

    }
  }
}

• The ORM designer automatically adds an association between the two tables. I renamed the property on the Node class to ChildLinks.
• (This drove me up the wall for a couple of hours) For the LINQ join to work with composite keys, you need the Name as well as Order of each part to match. See How to: Join by Using Composite Keys

One of these days, I'm gonna complete the LINQ Getting Started set of articles :)