-1
| ID | PrID | Name |
|------------------|
|  1 | null | N1   |
|  2 |    1 | N2   |
|  3 |    2 | N3   |
|  4 |    3 | N4   |

Hello, I have following self-referenced datatable. My input parameter is N1 and I need to find it's last child N4. Please help to write LINQ query. Thanks!

user3347046
  • 11
  • 1

3 Answers3

0

This question is very similar. Basically you need to write an extension method as LINQ was not designed to handle scenarios like this.

Walking a hierarchy table with Linq

Community
  • 1
  • 1
Ryan Bennett
  • 3,404
  • 19
  • 32
0

Not the best LINQ candidate, however, this recursive extension should work:

public static IEnumerable<T> Traverse<T>(this IEnumerable<T> source, Func<T, IEnumerable<T>> fnRecurse)
{
    foreach (T item in source)
    {
        yield return item;

        IEnumerable<T> seqRecurse = fnRecurse(item);
        if (seqRecurse != null)
        {
            foreach (T itemRecurse in Traverse(seqRecurse, fnRecurse))
            {
                yield return itemRecurse;
            }
        }
    }
}

Here's your sample data:

DataTable tbldata = new DataTable();
tbldata.Columns.Add("ID", typeof(int));
tbldata.Columns.Add("PrID", typeof(int));
tbldata.Columns.Add("Name", typeof(string));
tbldata.Rows.Add(1, null, "N1");
tbldata.Rows.Add(2, 1, "N2");
tbldata.Rows.Add(3, 2, "N3");
tbldata.Rows.Add(4, 3, "N4");

This query returns your desired result:

string parentName = "N1";
IEnumerable<DataRow> rows = tbldata.AsEnumerable();
DataRow parentRow = rows.FirstOrDefault(r => r.Field<string>("Name") == parentName);
DataRow lastChild = null;
if(parentRow != null)
{
    int parentID = parentRow.Field<int>("ID");
    lastChild = rows.Where(r => r.Field<int?>("PrID") == parentID)
        .Traverse(parent => rows
            .Where(child => child.Field<int?>("PrID") == parent.Field<int>("ID")))
        .LastOrDefault();
}

Output: 

Console.Write(String.Join(",", lastChild.ItemArray)); // 4,3,N4
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Side-note: this approach does not depend on the order as the sample data suggests (the desired row is already the last). It jumps from parent to child recursively. – Tim Schmelter Feb 24 '14 at 15:33
-1

This is probably undoable in LINQ.

You can either create a stored procedure that will use WHILE loop to find last child or you can pull whole table to your application and resolve it using loop like

Item i = FetchFirst();
while (i.GetNext() != null)
    i = i.GetNext();

Then i will store result. GetNext() loads next item, FetchFirst() loads N1 item.

Migol
  • 8,161
  • 8
  • 47
  • 69