CODEWITHSUNDEEP
linq-to-sql
Gishu
Gishu

Reputation: 136613

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

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?

Upvotes: 0

Views: 274

Answers (1)

Gishu
Gishu

Reputation: 136613

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 :)

Upvotes: 1

Related Questions