如何针对1台同时返回父母和孩子使用LINQ [英] How to return both parent and child using LINQ against 1 table

问题描述

一直在寻找一个解决方案，但一直没能找到一个这么远。<​​/ P>

我相当确信它可能有一个LINQ呼叫，但有麻烦的工作吧。

我有以下的数据结构

 标识的ParentId名称ValidFlag

1空母1 1
2 null父2 1
3 null父3 0
4 1名儿童1 1
5 1名儿童2 1
6 2子3 1
7 2子4 1
8 3名5 1
9 3孩子6 1
 

现在我想要做的是恢复所有有效的父母和他们的孩子，让这种情况下，我会回来的一切，除了n = 3（父3）。

有没有一种简单的方法来使用LINQ做到这一点？即时猜测是有的，但我LinqIQ是非常有限的，我知道的基本知识，但是除此之外，我需要多大的帮助。

这是为ToLookup（）或联合或案件没有？

更新：

要像我没有这样做更具体的，这两种类型的记录是在同一个表，我希望所有的记录，如果可能的返回是否其父母或孩子在1查询。

它不是简单，只是选择的所有记录，其中ValidFlag = 1的源数据库是一个烂摊子，并获得所有记录的唯一途径是找到了合法的父母则发现所有的孩子为有效的父母。我知道我可以做一个简单的查询到所有有效父记录返回然后做一个单独的查询来发现那些父母所有的孩子，但组合成1 LINQ查询是我失败了，我希望这是可能的。在这种情况下，可能的是有无效父母的有效子条目，因此需要对这个问题

解决方案

这应该做的伎俩，（编辑：见下文为不使用distinct版）

 （从父母集合
所有的集合
哪里
    parents.ValidFlag == 1＆安培;＆安培;
    parents.ParentId == NULL和放大器;＆安培;
    all.ValidFlag == 1＆安培;＆安培;
    （all.ParentId == NULL || all.ParentId == parents.Id）
全选）.Distinct（）;
 

以上code应该有希望产生一些非常相似 它本身看起来像SQL，可能与异常的 不同这可能会导致它返回多个数据，实际上是 需要过滤的客户端上。一些可能成为 问题如果有大量的数据，主要是如果有很多家长 因为它会返回那些重复）

下面是重新设计没有明显的来电查询

 从父母集合//父母只是用来决定要哪个孩子
从所有集合//这是我们实际上会抓住我们的数据
哪里
    parents.ValidFlag == 1＆安培;＆安培; //只包括父母是否有效
    parents.ParentId == NULL和放大器;＆安培; //那是父母
    all.ValidFlag == 1＆安培;＆安培; //仅包含有效条目
    （
        （all.ParentId == NULL和放大器;＆安培; all.Id == parents.Id）|| //如果条目是一个家长，配以本身来限制返回到1
        all.ParentId == parents.Id //否则，PARENTID应与有效的家长之一。
    ）
全选
 

Been looking for a solution for this but haven't been able to find one so far.

I'm fairly sure its possible with one linq call but having trouble working it out.

I have the following data structure

Id      ParentId     Name       ValidFlag

1       NULL         parent 1   1
2       NULL         parent 2   1
3       NULL         parent 3   0
4       1            child 1    1
5       1            child 2    1
6       2            child 3    1
7       2            child 4    1
8       3            child 5    1
9       3            child 6    1

Now what i want to do is return all valid parents and their children so this case i would return everything except Id = 3 (parent 3).

Is there a simple way to do this with LINQ? Im guessing there is but my LinqIQ is very limited, i know the basics but beyond that i need much help.

Is this a case for ToLookup() or Union or neither?

Update:

To be more specific as I've failed to do so, both types of records are in the same table, i want to return all records whether its a parent or child in the 1 query if possible.

Its not as simple as just selecting all records where ValidFlag = 1. The source database is a mess and the only way to get all records is to find the "valid" parents then find all children for the "valid" parents. I know i can just do a simple query to return all valid parent records then do a separate query to find all child of those parents, but combining into 1 LINQ query is where i fail, i was hoping that is possible. In this case it is possible that there are valid child entries of invalid parents, hence need for the question

解决方案

This should do the trick, (edit: see below for version that doesn't use Distinct.)

(from parents in collection
from all in collection
where
    parents.ValidFlag == 1 &&
    parents.ParentId == null &&
    all.ValidFlag == 1 &&
    (all.ParentId == null || all.ParentId == parents.Id)
select all).Distinct();

The above code should hopefully generate something quite similar to what it itself looks like in SQL, maybe with the exception of the distinct which might cause it to return more data that is actually needed to be filtered on the client. Something that might become an issue if there's a lot data, chiefly if there's a lot of parents because it will return duplicates of those)

Here is the query reworked without the distinct call

from parents in collection // parents is only used to decide which children to get
from all in collection // this is where we will actually grab our data from
where
    parents.ValidFlag == 1 &&  // only include parents that are valid
    parents.ParentId == null && // and that are parents
    all.ValidFlag == 1 &&  // only include entries that are valid
    (
        (all.ParentId == null && all.Id == parents.Id) ||  // If entry is a parent, match with itself to limit returns to 1
        all.ParentId == parents.Id // otherwise, parentid should match one of the valid parents.
    )
select all

这篇关于如何针对1台同时返回父母和孩子使用LINQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！