Question

Linq To Sql 如何实现递归查询 树形结构

CREATE TABLE [dbo].[MenuInfo](
[Id] [bigint] PRIMARY KEY IDENTITY(1,1) NOT NULL,[
Name] [varchar](50) NOT NULL,[
Parent] [bigint] NOT NULL,
[Type] [int] NULL,[Url] [varchar](100) NULL,
[Flag] [int] NULL,
[Note] [varchar](200) NULL
)

需求：查询指定节点下的所有节点，即指定节点下的所有子级节点以及子级节点下的子级节点。。。无限递归直到不存在子级节点。

sql 的实现是这样的
WITH temp
AS
(
--父项
SELECT * FROM MenuInfo WHERE Parent = 3
UNION ALL
--递归结果集中的下级
SELECT m.* FROM MenuInfo AS m
INNER JOIN temp AS child
ON m.Parent = child.Id
)
SELECT * FROM temp请高手赐教linq to sql 语句该如何写
分不多，请见谅。。。

Answer 1

--构造测试数据： 只作演示用
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=GetClassID(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> GetClassID(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 => GetClassID(t.Id)));
}

Answer 2

这样的递归建议你在SQL里做成存储过程或函数，运行效率会比linq to sql高得多