Linq2SQL and Resursive Tree output

Question

Consider a Tree-like structure, where the tree consists only of one table, daTree, with the fields intID, intParentID and strValue. The field intParentID is optional, a null value means that the entry is the root of a tree (there might be many).

I want all entries to be returned by Linq to SQL in the form

Root1
  Root1-Child1
  Root1-Child2
    Root1-Child2-Child1
    ...
Root2

and therefore wrote the following enumerator

IEnumerable<daTree> RecGetTree(daTree tree, DataContext context) {
    int? parentID = null;
    if (tree != null) {
        parentID = tree.intID;
        yield return tree; //emit current tree
    }
    IEnumerable<daTree> trees = context.daTrees
        .Where(t => t.intParent == parentID)
        .OrderBy(t => t.strName)
        .SelectMany(t => RecGetTree(t, context));
    foreach(var child in trees)
        yield return child;
}

which is initially called with null and the Linq2SQL Data Context.

My problem is, that an Exception occurs at yield return child;:

first chance exception of type 'System.NotSupportedException' occurred in System.Data.Linq.dll Additional information: Method 'System.Collections.Generic.IEnumerable`1[MyProject.daTree] RecGetTree(MyProject.daTree, MyProject.DataContext)' has no supported translation to SQL.

Do you have any idea how I could rewrite the code to get it to work? I am using VS 2013 and .Net 4.5.

Albin Sunnanbo · Accepted Answer · 2014-07-31 09:23:42Z

1

You can force evaluation of the LINQ expression earlier

IEnumerable<daTree> trees = context.daTrees
    .Where(t => t.intParent == parentID)
    .OrderBy(t => t.strName).ToList() // <-- here
    .SelectMany(t => RecGetTree(t, context));

It won't be particularly efficient, but it should at least work.

answered Jul 31, 2014 at 9:23

Albin Sunnanbo

47.2k8 gold badges72 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

muffel Over a year ago

Thank you, that works! Do you have any proposal how an efficient solution might look like?

Albin Sunnanbo Over a year ago

To get it efficient you could either cache the whole daTrees table if it is a reasonably small table, otherwise you need to resort to a CTE server side and you can't express a CTE with LINQ directly.

Collectives™ on Stack Overflow

Linq2SQL and Resursive Tree output

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related