title: Recursive IQueryable Linq Extensions method
body: <p>I'm interested in writing an extension method the IQueryable interface. The method would return all children recursively of the specified selector.</p>
  
  <pre><code>public static class MyExtensions
  {
      public static IQueryable&lt;IRecursion&lt;T&gt;&gt; SelectRecursive&lt;T&gt;(this IQueryable&lt;T&gt; source, Func&lt;T, IQueryable&lt;T&gt;&gt; selector)
      {
          //Code goes here
      }
  
      public interface IRecursion&lt;T&gt;
      {
          int Depth { get; }
  
          T Item { get; }
      }
  }
  </code></pre>
  
  <p>Example usage:</p>
  
  <pre><code>var allChildren = tblCompanies
          .Where(c =&gt; c.pkCompanyID == 38)
          .SelectRecursive(p =&gt; tblCompanies.Where (c =&gt; c.pkCompanyID == p.fkCompToCompID));
  </code></pre>
  
  <p>SQL code generated by function would be something like this.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Can it be done?
  If not possible with a CTE, maybe with SQL 2008 hierarchyid?</p>