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

Question

To simplify my current situation, let's say I have 2 tables (inherited, so I can’t touch the diagram 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 identifiers refer to the same table.

Ideally, I would like to have a Node class that has a Children property that returns the correct list? Results should be equivalent.

 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 

What is the best way to model this in Linq to Sql?

Answer 1

Here is what I came up with (works too!)
Open the created Node class Node 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 a relationship between the two tables. I renamed the property in the Node class to ChildLinks.
• (This led me to the wall for several hours). In order for LINQ to join the work with composite keys, you need the Name, as well as the Order of each part. See How to join using composite keys.

One of these days I am going to complete a set of LINQ articles Getting Started :)