Recursive IQueryable Linq Extensions Method

Question

I am interested in writing an IQueryable interface extension method. The method will return all recursive data 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; }
    }
}

Usage example:

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

The SQL code generated by the function will 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 this be done? If this is not possible with CTE, perhaps with a SQL 2008 hierarchy?

Answer 1

This is not possible in L2S. However, you can expand the query to a certain constant depth, if that is enough for you. This will lead to an unpleasant forest of associations.

Since your set of "companies" is probably not very large, try downloading all of them and doing this on the client side.

Answer 2

See the following:

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