Get Flat Linked List from Table

Question

I have a table similar to:

did  diScheduleLinkedID  diScheduleID  
8    47                  71
9    71                  72            
10   45                  67            
11   72                  85            
12   67                  78            
14   85                  92

where The table is like a linked list diScheduleLinkedID is linked to diScheduleID recursively.

I need to query the table (hopefully in Linq) with an ID say (47) and get back:

47,71,72,85,92

Or with ID 45 and get back:

45,67,78

I tried something similar to: .NET - LINQ AsHierarchy() extension method

however it's not working as expected

EDIT I used the following code from the linked article:

var hierachy = tbllinks.Where(w=>w.diScheduleLinkedID.Equals(pnID)).ToList().AsHierarchy(e => e.diScheduleLinkedID, e => e.diScheduleID);

however it's returning an empty list and I can't see any way to filter for the first ID (say 47) in the linked list?

EDIT 2 Was able to do it using the Extension method:

public static IEnumerable<T> Traverse<T>(this IEnumerable<T> source, Func<T, IEnumerable<T>> selector)
{
    foreach (T item in source)
    {
        yield return item;
        IEnumerable<T> children = selector(item);
        foreach (T child in children.Traverse(selector))
        {
            yield return child;
        }
    }
}

and then use:

var result = tblpermit_links.Where(item => item.diScheduleLinkedID == 47)
                 .Traverse(item => tblpermit_links.Where(parent
                                                   => item.diScheduleID == parent.diScheduleLinkedID));

Not sure of the performance hit though! However I will be able to prefilter the table on another field.

Maybe I should use a CTE?

So what exactly did you try so that it didn't work? The article you linked solves almost identical problem. — Andrey, Dec 11 '17 at 17:26
Are you using sql server to store this data? If so, you should check the hierarchyid datatype! — Hackerman, Dec 11 '17 at 17:35
@Hackerman I've never used that datatype - I'll check it out - thanks — Wattcey, Dec 11 '17 at 17:48
@Wattcey ok they actually are solving slightly different problem. Look into this answer https://stackoverflow.com/a/41837737/283676 — Andrey, Dec 11 '17 at 18:03

score 1 · Accepted Answer · answered Dec 11 '17 at 18:50

Your approach will get the data back, but it would do so by querying the table repeatedly. If you have no ability to change the tables, that is the best you can do.

If you can change the structure of the table, and your hierarchies are non-overlapping trees, you could speed things up considerably by adding a hierarchy ID field, like this:

did  diScheduleLinkedID  diScheduleID  hierarchyId
8    47                  71            1
9    71                  72            1
10   45                  67            2
11   72                  85            1
12   67                  78            2
14   85                  92            1

Now you can query the whole tree by supplying hierarchyId of any of its members, and then "link up" the nodes in memory. Of course this would put an additional requirement to maintain the integrity of hierarchyIds on your code.

I decided to go with a combination of my approach above and this extra id column. — Wattcey, Dec 15 '17 at 14:36

Get Flat Linked List from Table

1 Answers1