5

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.

sch55
  • 49
  • 1
  • 2

4 Answers4

3

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.

Magnus
  • 45,362
  • 8
  • 80
  • 118
  • Yeah but I have to boycott all SPs and actual SQL code. No choice in this case – sch55 May 04 '11 at 19:55
  • Why?! Why write bad code when you already have a good solution. You know that Linq generates SQL in the end anyway? I usually use Linq for most of my data access queries, but when high performance is needed I always write the SQL directly. Sql queries in code against your db context can map to Linq entities fine. – Magnus May 04 '11 at 22:00
  • Ah, I definitely get your point now, thanks alot for the tip! – sch55 May 04 '11 at 22:20
1

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.

svick
  • 236,525
  • 50
  • 385
  • 514
0

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

Prakash
  • 1
  • 1
-1
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
sch55
  • 49
  • 1
  • 2