title: Converting sql statement that contains 'with' cte to linq
body: <p>I have this piece of code here, been battling with it for hours. basically what this sql statement does is gets <strong>ALL</strong> subfolders of a specified folder (@compositeId). </p>
  
  <pre><code>WITH auto_table (id, Name, ParentID) AS
  (
  SELECT
      C.ID, C.Name, C.ParentID
  FROM Composite_Table AS C
      WHERE C.ID = @compositeId
  
  UNION ALL
  
  SELECT
      C.ID, C.Name, C.ParentID
  FROM Composite_Table AS C
      INNER JOIN auto_table AS a_t ON C.ParentID = a_t.ID
  )
  
  SELECT * FROM auto_table
  </code></pre>
  
  <p>This query would return something like this:</p>
  
  <pre class="lang-none prettyprint-override"><code>Id   |    Name    | ParentId
  1    | StartFolder| NULL
  2    | Folder2    | 1
  4    | Folder3    | 1
  5    | Folder4    | 4
  </code></pre>
  
  <p>Now I want to convert the code to linq. I know it involves some form of recursion but still stuck thanks to the with statement. </p>