在LinqToSql的联接表中存储的父子关系建模的最佳方法是什么? [英] Whats the best way to model parent child relationships stored in a join table in LinqToSql?

问题描述

为简化我的当前情况，我们假设我有2个表(旧版，因此无法触摸该模式以更好地与Linq配合使用)

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与Rails中的has_and_belongs_to_many联接表相似...除了两个ID都引用同一个表之外.

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

理想情况下，我希望有一个具有属性Children的Node类，该类返回正确的List? 结果应等于

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

在Linq to Sql中对此建模的最佳方法是什么?

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

推荐答案

这是我想出的(也可以使用！)
打开ORM生成的Node类MyDatabase.cs

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;

    }
  }
}

• ORM设计器会自动在两个表之间添加关联.我将Node类的属性重命名为ChildLinks.
• (这使我陷入困境了几个小时)，要使LINQ联接与复合键配合使用，您需要每个零件的名称和顺序才能匹配.请参见如何:使用组合键进行联接
• 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

这些天之一，我要完成LINQ入门文章集:)

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

这篇关于在LinqToSql的联接表中存储的父子关系建模的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！