我希望编写一个扩展方法来处理IQueryable接口。该方法将递归返回指定选择器的所有子节点。
public static class MyExtensions
{
public static IQueryable<IRecursion<T>> SelectRecursive<T>(this IQueryable<T> source, Func<T, IQueryable<T>> selector)
{
//Code goes here
}
public interface IRecursion<T>
{
int Depth { get; }
T Item { get; }
}
}
示例用法:
var allChildren = tblCompanies
.Where(c => c.pkCompanyID == 38)
.SelectRecursive(p => tblCompanies.Where (c => c.pkCompanyID == p.fkCompToCompID));
函数生成的SQL代码可能是这样的。
WITH CompanyCTE(ID, parentID, depth) AS
(
SELECT
pkCompanyID,
fkCompToCompID,
0
FROM
tblCompany
UNION ALL
SELECT
tblCompany.pkCompanyID,
tblCompany.fkCompToCompID,
CompanyCTE.depth + 1
FROM
tblCompany
JOIN CompanyCTE ON tblCompany.fkCompToCompID = CompanyCTE.ID
)
SELECT
tblCompany.*, --Item
CompanyCTE.depth --Depth
FROM
CompanyCTE
JOIN tblCompany ON CompanyCTE.ID = tblCompany.pkCompanyID
WHERE
parentID = 38
能完成吗? 如果不能使用CTE,则可能可以使用SQL 2008的层次结构ID。