I have a single table which expresses a possible parent child relationship between Categories. Root categories would not contain a ParentId value, rather null. I think it's also important to point out that it should construct N level of depth.
For example consider the following Sql table.
Category : Id | Name | ParentId
Where ParentId is a relationship back to the Id column of the same table.
Trying to understand if it would be possible to populate the following class?
public class Category
{
public string Id
{
get;
set;
}
public string Name
{
get;
set;
}
public List<Category> Categories
{
get;
set;
}
}
from a method such as :
public List<Category> GetCategories()
{
// construct using dapper.
}
You can get a flat list from DB and assemble in C#:
[TestFixture]
public class Recursion
{
[Test]
public void Test()
{
using (var conn = new SqlConnection(@"Data Source=.\sqlexpress;Integrated Security=true; Initial Catalog=foo"))
{
var flatResult = conn.Query<Category>(@"select '1' as Id, 'Cat 1' as Name, ParentId = null
union all select '2' as Id, 'Cat 2' as Name, '1' as ParentId
union all select '3' as Id, 'Cat 3' as Name, '2' as ParentId
union all select '4' as Id, 'Cat 4' as Name, null as ParentId
union all select '5' as Id, 'Cat 5' as Name, 4 as ParentId");
var tree = BuildTree(flatResult.ToList());
}
}
private static IEnumerable<Category> BuildTree(List<Category> items)
{
items.ForEach(i => i.Categories = items.Where(ch => ch.ParentId == i.Id).ToList());
return items.Where(i => i.ParentId == null).ToList();
}
}