common table expression - Converting sql statement that contains 'with' cte to linq

Question

I have this piece of code here, been battling with it for hours. basically what this sql statement does is gets ALL subfolders of a specified folder (@compositeId).

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

This query would return something like this:

Id   |    Name    | ParentId
1    | StartFolder| NULL
2    | Folder2    | 1
4    | Folder3    | 1
5    | Folder4    | 4

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.

Answer 1

There is no Linq to SQL equivalent that can do that (in an efficient manner). Best solution would be to call a SP/View/UDF from Linq containing that statement.