Recursive IQueryable Linq Extensions method

Question

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

    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

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

Answer 1

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.