Converting sql statement that contains 'with' cte to linq

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    | StartFo开发者_Go百科lder| 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.

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.

You could write code (recursive or not) that repeatedly queries the database, until it has all the results.

But I think there is no way to write a single LINQ to SQL query that would get all the results you need in one go, so it's probably best to keep the query in SQL.

There is known plugin 'LinqToDb', which provides methods to get CTE equivalent in Linq

public static List<Composite> GetSubCascading(int compositeId)
{
    List<Composite> compositeList = new List<Composite>();

    List<Composite> matches = (from uf in ctx.Composite_Table
    where uf.Id == compositeId
    select new Composite(uf.Id, uf.Name, uf.ParentID)).ToList();

    if (matches.Any())
    {
        compositeList.AddRange(TraverseSubs(matches));
    }

    return compositeList;
}

private static List<Composite> TraverseSubs(List<Composite> resultSet)
{
    List<Composite> compList = new List<Composite>();

    compList.AddRange(resultSet);

    for (int i = 0; i < resultSet.Count; i++)
    {
        //Get all subcompList of each folder
        List<Composite> children = (from uf in ctx.Composite_Table
        where uf.ParentID == resultSet[i].Id
        select new Composite(uf.Id, uf.Name, uf.ParentID)).ToList();

        if (children.Any())
        {
            compList.AddRange(TraverseSubs(children));
        }
    }

    return compList;
}

//All where ctx is your DataContext