LINQ to SQL for querying parent and counting children in hierarchy

Question

I would take suggestions from C # and VB.NET.

I use LINQ to query data. I am trying to query the parent and count the child tags.

Here is the column table of my tags:

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

Here are 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 the child tag also has its own child tag

Here are 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)

Question number 2 is optional, but it will be very good if you also give some suggestion. Thank.

Answer 1

:

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

    public int? ParentId { get; set; }      

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

:

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

.

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

:

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

:

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

---

, , :

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

Answer 2

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: :

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

Answer 3

Well, the easiest way is to create a simple structure where you link your tags if they have parents, and then iterate through the children to create a counter for each 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);
    }
}

Using the code above, you will receive information per tag, not just the “parent” tags.