Dapper Build Object Tree Containing Same Object Type

Question

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

Answer 1

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