【转】C#递归查询

转自：http://www.cnblogs.com/no27/p/6673893.html

一、sql

--构造测试数据： 只作演示用 
CREATE TABLE [dbo].[Tim_LinqTable]( 
[Id] int PRIMARY KEY IDENTITY(1,1) NOT NULL, 
[Name] [varchar](50) NOT NULL, 
[Parent] int NOT NULL, 
) 
GO 
   
INSERT INTO [Tim_LinqTable]   
SELECT 'A',0 UNION ALL 
SELECT 'A1',1 UNION ALL 
SELECT 'A2',1 UNION ALL 
SELECT 'B1',2 UNION ALL 
SELECT 'B2',3 UNION ALL 
SELECT 'C1',4 UNION ALL 
SELECT 'C2',4 UNION ALL 
SELECT 'D1',5 UNION ALL 
SELECT 'D2',5 UNION ALL 
SELECT 'D3',5  
GO 
   
WITH temp 
AS 
( 
SELECT * FROM [Tim_LinqTable]  WHERE Parent = 3 
UNION ALL 
SELECT m.* FROM [Tim_LinqTable]  AS m 
INNER JOIN temp AS child ON m.Parent = child.Id 
) 
SELECT * FROM temp 
GO 
   
--查询 Parent=3 的所有子数据结果如下: 
Id          Name                                               Parent 
----------- -------------------------------------------------- ----------- 
5           B2                                                 3 
8           D1                                                 5 
9           D2                                                 5 
10          D3                                                 5 
   
(4 row(s) affected) 

二、实现

//好，下边来看看用C#怎么实现上边的SQL语句吧： 
void Main() 
{ 
     var query=GetSonID(3); 
     Console.WriteLine("Id\tName\tParent"); 
     query.ToList().ForEach(q=>Console.WriteLine("{0}\t{1}\t{2}",q.Id,q.Name,q.Parent)); 
     /*
        Id       Name       Parent
        5        B2         3
        8        D1         5
        9        D2         5
            10        D3         5
     */ 
} 
public IEnumerable<Tim_LinqTable> GetSonID(int p_id) 
{ 
    var query = from c in this.Tim_LinqTables 
           where c.Parent  == p_id 
           select c; 
            
    return  query.ToList().Concat(query.ToList().SelectMany(t => GetSonID(t.Id)));               
} 

或者：

public static List<db_userinfo> findallchildren(int parentid)
{
    var list = (from c in ctdt.db_userinfo
                where c.parent_id == parentid
                select c).ToList();
    List<db_userinfo> tmpList = new List<db_userinfo>(list);
    foreach (db_userinfo single in temp)
    {
        List<db_userinfo> tmpChildren = findallchildren(single.user_id);
        if (single.PB_Level == 1)//如果循环到最后一层退出
        {
            return list;
        }
        if (tmpChildren.Count != 0)
        {
            list.AddRange(tmpChildren);
        }
    }
    return list;
}