如何在LINQ到SQL查询中排除特定的关联记录?

Question

假设我有以下数据:

Parent
  > Child 1 (Alive = true)
  > Child 2 (Alive = true)
    > Grandchild 1 (Alive = false)
    > Grandchild 2 (Alive = true)
  > Child 3 (Alive = false)
    > Grandchild 3 (Alive = false)

我想写一个查询来选择Alive为真的所有记录，从而排除Alive为假的任何记录。这是我想弄明白的语法:

from p
in this.People
where p.IsAlive && (p.Children.IsAlive)  && (p.Children.Children.IsAlive)
select p

我希望结果集看起来像这样:

Parent
  > Child 1 (Alive = true)
  > Child 2 (Alive = true)
    > Grandchild 2 (Alive = true)

Answer 1

你可以这样做:1)通过ParentID列创建递归关系表People:

Table name : People
ID  Name        ParentID IsAlive
1   Parent1     Null       1
2   Child1      1          1
3   Child2      1          1
4   GrandChild1 3          1
5   GrandChild2 3          0
6   Child3      1          0
7   GrandChild3 6          0

2)对Alive记录执行如下查询:

from p
in this.People
where p.IsAlive 
select p

请告诉我，这种方式适合你吗?