Problem: I wanted to create a database model that would allow me to store tree structure of categories and sub categories. This flat table I wanted to render as a UL/LI tree list, so that style it with CSS and use jQuery to add the tree view like behavioral. So here is what I came up with.
Let's start with the DB desgin:
The DB columns are: id, name, parentId; where if parentId is null, there is no it means this row is the parent.
Now comes the C# LINQ code:
public class categoryTree
{
public IQueryable<Category> categories;
public string getTree()
{
StringBuilder sb = new StringBuilder();
sb.Append("<ul class=\"list\">");
foreach (var s in this.categories)
{
if (s.parentId == null)
{
sb.Append("<li>");
sb.Append(s.name);
sb.Append(this.getChildren(s.id));
sb.Append("</li>");
}
}
sb.Append("</ul>");
return sb.ToString();
}
//child self referencing loop
private string getChildren(int parentId)
{
StringBuilder sb = new StringBuilder();
//child categories
var childCategories = from m in this.categories
where m.parentId == parentId
orderby m.name
select m;
//check if there are nay children
if (childCategories.Count() > 0)
{
sb.Append("<ul>");
foreach (var s in childCategories)
{
sb.Append("<li>");
sb.Append(s.name);
//call itself for children
sb.Append(this.getChildren(s.id));
sb.Append("</li>");
}
sb.Append("</ul>");
}
return sb.ToString();
}
}
And this is how you can use this code:
var allCategories = from m in dataContext.Categories
orderby m.name
select m;
categoryTree q = new categoryTree();
q.categories = allCategories;
string tree = q.getTree();