Recursive IQueryable Linq Extensions method

I'm interested in writing an extension method the IQueryable interface. The method would return all children recursively of the specified selector.

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; }
    }
}

Example usage:

var allChildren = tblCompanies
        .Where(c => c.pkCompanyID == 38)
        .SelectRecursive(p => tblCompanies.Where (c => c.pkCompanyID == p.fkCompToCompID));

SQL code generated by function would be something like this.

WITH CompanyCTE(ID, parentID, depth) AS
(
    SELECT
        pkCompanyID, 
        fkCompToCompID,
        0
    FROM 
        tblCompany

    UNION ALL

    SELEC开发者_如何学GoT
        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

Can it be done? If not possible with a CTE, maybe with SQL 2008 hierarchyid?

This is not possible in L2S. You can however expand the query to a certain constant depth if that is enough for you. This will result in a nasty forest of joins.

As your set of "companies" is probably not very large, try loading all of them and do this client-side.

See these:

http://jasonrowe.com/2009/09/19/recursive-linq-query-example/