Creating tree from the db

Question

I have DataTable consists of rows which have fields called recordId,recordName,parentId. What I want to do is to show parent children relation of the records but I cannot create a concrete logic on how to build such a tree. I tried to get the records into a model like below:

public class Records
{
    public int recordId{ get; set; }
    public string recordName{ get; set; }
    public List<Records> ChildRecords{ get; set; }

}

Then I started with the records which have parentId==0(These are all the children of the root). Then I recursively tried to get the child records of those, however, I stuck at some points several times.

Is there anyone who can show me a way to create that family tree.

Edit:

lets say DB entry: (1 A 0,2 B 0,3 C 1, 4 D 1, 5 E 3,6 F 2,7 G 6,8 H 7)

A -> (C->E,D)

B ->(F->G->H)

What I tired is basically getting the children of the root. Then based on the acquired id I got to the last children of the parent.

Ex: for A first I got A and then I went through C, then got its child E. After that I tried to keep track of where to put D and messed up.

Answer 1

namespace TestConsoleApp
{
    public class Record
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int? ParentId { get; set; }
        public List<Record> ChildRecords { get; set; }

    }

    class Program
    {
        static void Main(string[] args)
        {
            var RecordsStore = new List<Record>();

            //Get the standalone recoreds
            var standaloneRecords = RecordsStore.Where(x => x.ParentId == null).Select(x => new Record
            {
                Id = x.Id,
                Name = x.Name,
                ChildRecords = new List<Record>()
            });

            //Traverse
            List<Record> records = new List<Record>();
            foreach (var item in standaloneRecords)
                records.Add(Traverse(item, RecordsStore));

        }


        private static Record Traverse(Record parent, List<Record> records)
        {
            parent.ChildRecords = records.Where(x => x.ParentId == parent.Id)
                                              .Select(x => new Record
                                              {
                                                  Id = x.Id,
                                                  Name = x.Name,
                                                  ChildRecords = new List<Record>()
                                              }).ToList();
            foreach (var item in parent.ChildRecords)
                Traverse(item, records);
            return parent;
        }
    }  
}

You can do something like this

Answer 2

You can declare your model class like this

    public class Record
    {
        [Key]
        public int recordId{ get; set; }
        public string recordName{ get; set; }

        [ForeignKey("ParentRecordDetails")]
        public int ParentId {get;set;}
        public Record ParentRecordDetails {get;set;}

    }

and then write self join in LINQ to recursively get tree structure

Reference: https://bitlush.com/blog/recursive-hierarchical-joins-in-c-sharp-and-linq