Convert sql statement containing 'c' to linq

Question

Hey, I have this code here, he fought with it for hours. basically what this sql statement does is get ALL subfolders of a specific 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 will return something like this:

• Id | Name | ParentId
• 1 | StartFolder | Null
• 2 | Folder2 | one
• 4 | Folder3 | one
• 5 | Folder4 | 4

Now I want to convert the code to linq. I know this is due to some form of recursion, but still stuck thanks to statement c. Help?

Answer 1

There is no Linq to SQL equivalent that can do this (in an efficient way). A better solution would be to call SP / View / UDF from Linq containing this statement.

Answer 2

You can write code (recursive or not) that repeatedly queries the database until it gets all the results.

But I think there is no way to write a single LINQ to SQL query that will get all the results you need at a time, so it’s best to save the query in SQL.

Answer 3

 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