CODEWITHSUNDEEP
c#datatable
Kamil
Kamil

Reputation: 13931

How to select from DataTable with join to same table?

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)?

Upvotes: 3

Views: 1383

Answers (5)

Tim Schmelter
Tim Schmelter

Reputation: 460138

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;

Upvotes: 3

MichaC
MichaC

Reputation: 13380

Another way to do it, this will give you a new object which contains the level and the row item itself. This will work for n number of levels...

        var nodes = table.AsEnumerable();

        //var nodes = new List<TreeNode>();

        var parentId = 0;
        var countLevel = 0;
        var allNods = new List<dynamic>();

        while (nodes.Any(p => p.Field<int>("parentId") == parentId))// && countLevel < 2) 
            // countlevel< 2 only to give you the first 2 levels only...
        {
            var nodesWithLevel = nodes.Where(p => p.Field<int>("parentId") == parentId)
                        .Select(p => new { Level = parentId, Node = p });

            allNods = allNods.Concat<dynamic>(nodesWithLevel).ToList();
            parentId++;
            countLevel++;
        }

The code currently expects that the root nodes have parentId = 0. Could be changed to null, too of cause...

Upvotes: 1

Kaf
Kaf

Reputation: 33809

DataTable level1 = (from t in dtCategories.AsEnumerable()
                    where t.Field<int>("parentId") == 0
                    select t).CopyToDataTable();

DataTable level2 =(from t1 in dtCategories.AsEnumerable()
                        join t2 in dtCategories.AsEnumerable() 
                           on t1.Field<int>("id") equals t2.Field<int>("parentId")
                   where t1.Field<int>("parentId") == 0
                   select t2).CopyToDataTable();

Upvotes: 1

Drew
Drew

Reputation: 272

You have a couple of options to your problem. As proposed by @Ali, you could use recursion like this: 

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;
}

But the problem is that you'll end up iterating though every element and then using recursion on every iteration. If you have absolutely no data relationship between your columns and their level in the tree, besides a parentId, then this is your only solution.

On the other hand, if you do have a relationship, where you have name[level of tree] like Name[A] is tree level 1 and Name[AB] is tree level two with the right node, then iteration through each like:

    foreach (DataRow r in dt.Rows)
    {
        //Pull out the element
        //Check the element's level
       //Add it to the result set if level <= 2
    }

I'd personally prefer to solve the problem by actually building a tree structure or using a SQL WHERE clause, but it's hard to justify the time on it. Depending on where you get this data from, you may also be able to add an additional column which tells you which level the node is in depending on where it's inserted. If it has a grandparent (i.e. two parent nodes) you don't include it in the result set.

Upvotes: 1

Ali Baghdadi
Ali Baghdadi

Reputation: 648

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;
    }

Upvotes: 2

Related Questions