How to select from DataTable with join to same table?

Question

I have DataTable object, which holds some "tree data structure". Data is not stored in any database, I just use DataTable to manipulate data without SQL server.

My data looks like this (indents are only for better reading here):

DataTable dtCategories = GetCategoriesAsDataTable();

id    name    parentId
int   string  int
----------------------
 1    One         0
 2      OneA      1
 3      OneB      1
 4    Two         0
 5      TwoA      4
 6      TwoB      4
 7        TwoAA   5
 8        TwoAB   5

So far - I was thinking about selecting first level with "where parentId = 0" and putting this to separate DataTable, like this:

DataTable dtFirstLevel = dtCategories.Select("[parentId] = 0");

// and after this - create DataTable for second level
// but I don't know how can I use "IN" clause here
DataTable dtSecondLevel = dtCategories.Select(?????????);

1. How can I select only first 2 levels of tree?
2. How can I select this without SQL server (by using only data objects)?

Answer 1

Maybe this helps:

var rows = table.AsEnumerable();
var parents = rows.Where(r => !r.Field<int?>("parentId").HasValue);
var children = rows.Where(r => r.Field<int?>("parentId").HasValue);
var secondLevel = from parent in parents
                  join child in children
                  on parent.Field<int>("id") equals child.Field<int?>("parentId").Value
                  select child;
var both = parents.Concat(secondLevel).CopyToDataTable();

Note that i've used Nullable<int> instead of 0 for a parent since that is more readable and less prone of errors. Here is your sample data:

var table = new DataTable();
table.Columns.Add("id", typeof(int));
table.Columns.Add("name", typeof(string));
table.Columns.Add("parentId", typeof(int));
table.Rows.Add(1, "One", (int?)null);
table.Rows.Add(2, "OneA", 1);
table.Rows.Add(3, "OneB", 1);
table.Rows.Add(4, "Two", (int?)null);
table.Rows.Add(5, "TwoA", 4);
table.Rows.Add(6, "TwoB", 4);
table.Rows.Add(7, "TwoAA", 5);
table.Rows.Add(8, "TwoAB", 5);

Result:

1   One 
4   Two 
2   OneA    1
3   OneB    1
5   TwoA    4
6   TwoB    4

Since you want to stay with 0 instead of int?:

var parents = rows.Where(r =>  r.Field<int>("parentId") == 0);
var children = rows.Where(r => r.Field<int>("parentId") != 0);
var secondLevel = from parent in parents
                  join child in children
                  on parent.Field<int>("id") equals child.Field<int>("parentId")
                  select child;

Answer 2

I think this function might help you figure out the level of tree of each entry so you can use it in your selection:

    public int level(DataTable dt, DataRow row)
    {
        int parentid = int.Parse(row[2].ToString());
        if (parentid == 0)
            return 1;
        else
            return 1 + level(dt, GetDataRow(dt,parentid ));
    }

    public DataRow GetDataRow(DataTable dt, int id)
    {
        foreach (DataRow r in dt.Rows)
        {
            if (int.Parse(r[0].ToString()) == id) return r;
        }
        return null;
    }