LINQ to SQL to query parent and count childs in hierarchy

Question

I'd take both C# and VB.NET suggestion.

I'm using LINQ to query data. I'm trying to query the parent and count the child tags.

Here's my Tags table column:

TagId (int primary)
TagName
ParentId (int Allow NULL referred to TagId column)

Here's some sample data:

TagId, TagName, ParentId

1, Web Design, NULL
2, HTML, 1
3, Programming, NULL
4, CSS 3, 1

Question 1: In my query result, I want to query all parent Tags with the sum of child tags. Something like the following:

Web Design (2 sub tags)
Programming (0 sub tags)

Question 2: If a child tag also, has its own child tag

Here's some sample data:

TagId, TagName, ParentId

1, Web Design, NULL
2, HTML, 1
3, Programming, NULL
4, CSS 3, 1
5, HTML 4, 2
6, HTML 5, 2

Desired query result:

Web Design (4 sub tags)
Programming (0 sub tags)

The question number 2 is optional but it'll be very good if you also give some suggestion. Thank you.

Answer 1

public static IEnumerable<T> SelectDescendents<T>(this IEnumerable<T> source, Func<T, IEnumerable<T>> selector)
 {
  foreach(var item in source)
  {
   yield return item;
   foreach(T item2 in SelectDescendents(selector(item), selector))
    yield return item2;
  }
 }

Edit: Usage:

Tags.where(t => t.tagID == 1).SelectDescendents(d => 
  Tags.where(t.parentID == d.TagId)).Count();

Answer 2

Definition:

class Tag
{
    public int Id { get; set; }
    public string TagName { get; set; }

    public int? ParentId { get; set; }      

    public IEnumerable<Tag> Children { get; set; }
}

Data:

int id = 0;
var tags = new[]
{
    new Tag
    {
        Id = ++id,
        TagName = "Parent", 
        ChildNodes = new[]
        {
            new Tag { TagName = "Child1", Id = ++id, ParentId = 1  },
            new Tag { TagName = "Child2", Id = ++id, ParentId = 1  }
        }
    }
    new Tag
    {
        Id = ++id,
        TagName = "NoChildren"
    }
};

i.e.

1, Parent, null
2, Child1, 1
3, Child2, 1
4, NoChildren, null

Code:

 var q = from tag in tags
         where tag.ParentId == null
         select new
         {
             Name = tag.TagName,
             ChildrenCount = tag.Children.Count()
         };

foreach (var entry in q)
{
    Console.WriteLine("{0} ({1} sub tags)", entry.Name, entry.ChildrenCount);
}

Output:

Parent (2 sub tags)
NoChildren (0 sub tags)

---

In case of complex hierarchy, to get all child nodes recursively use can use next extensions method:

public static IEnumerable<Tag> GetChildTags(this Tag tag)
{
    var children = tag.Children ?? Enumerable.Empty<Tag>();
    return children.SelectMany(c => GetChildTags(c)).Concat(children);
}

Answer 3

Ok, the easiest way is to create a simple structure where you connects your tags if they have parents, and then iterates through the childrens to produce the count per tag.

class Tag
{
    public Tag(int id, int? parentId, string tag)
    {
        Id = id;
        ParentId = parentId;
        TagName = tag;
    }

    public int Id { get; set; }
    public int? ParentId { get; set; }
    public string TagName { get; set; }
}

class TagNode
{
    public Tag Node { get; set; }
    public IList<TagNode> ChildNodes { get; set; }
    public int ChildNodeCount()
    {
        int count = 0;
        if (ChildNodes != null)
        {
            foreach (var node in ChildNodes)
            {
                count += node.ChildNodeCount();
            }
            count += ChildNodes.Count;
        }
        return count;
    }
}

class Program
{
    static void Main(string[] args)
    {
        var tags = new List<Tag>();
        tags.Add(new Tag(1, null, "Web design"));
        tags.Add(new Tag(2, null, "Programming"));
        tags.Add(new Tag(3, 1, "HTML"));
        tags.Add(new Tag(4, 1, "CSS 3"));
        tags.Add(new Tag(5, 3, "HTML 4"));
        tags.Add(new Tag(6, 3, "HTML 5"));

        IList<TagNode> nodes = tags.Select(y => new TagNode { Node = y, ChildNodes = new List<TagNode>() }).ToList();
        foreach (var node in nodes)
        {
            if (node.Node.ParentId.HasValue)
                ConnectNodeToParent(nodes, node);
        }

        // print all nodes
        Console.WriteLine("=== ALL NODES ===");
        nodes.ToList().ForEach(PrintNode);

        // print root nodes
        Console.WriteLine(Environment.NewLine + "=== ROOT NODES ===");
        nodes.Where(y => y.Node.ParentId.HasValue == false).ToList().ForEach(PrintNode);

        Console.ReadLine();
    }

    private static void PrintNode(TagNode node)
    {
        Console.WriteLine("Tag id: {0}, Tag name: {1}, Tag count: {2}", node.Node.Id, node.Node.TagName, node.ChildNodeCount());
    }

    private static void ConnectNodeToParent(IList<TagNode> nodes, TagNode node)
    {
        var parentNode = nodes.Where(y => y.Node.Id == node.Node.ParentId.Value).Single();
        parentNode.ChildNodes.Add(node);
    }
}

With the code above you get the information per tag and not just the "parent" tags.