I am not sure what your relation database table hierarchical setup is, so I will focus on a typical setup.
The data is stored in a flat structure, usually with a pointer to a parent node. So something like this:
class Data
{
public int Id { get; set; }
public int ParentId { get; set; }
public DataType DataType { get; set; }
public string Title { get; set; }
}
enum DataType
{
Country,
City,
Store
}
To map to a hierarchical dataset, the parent usually contains the children for that node. Something like this:
class Node
{
public Data Data { get; set; }
public int Level { get; set; }
public List<Node>? Children { get; set; }
}
I am not going to create an EF DB + table, so I will emulate a dataset:
List<Data> FlatData = GetData().ToList();
IEnumerable<Data> GetData()
{
for (int i = 0; i < 10; i++)
yield return new Data
{
DataType = DataType.Country,
Id = i,
ParentId = -1,
Title = $"Country {i}"
};
for (int i = 1000; i < 1020; i++)
yield return new Data
{
DataType = DataType.City,
Id = i,
ParentId = random.Next(10),
Title = $"City {i}"
};
for (int i = 2000; i < 2100; i++)
yield return new Data
{
DataType = DataType.Store,
Id = i,
ParentId = random.Next(1000, 1020),
Title = $"Store {i}"
};
}
Now that we have the data, we can build the hierarchical dataset:
List<Node> NodeData = BuildHierarchy(FlatData).ToList();
IEnumerable<Node>? BuildHierarchy(List<Data> flatData, int parentId = -1, int level = 1)
{
foreach (Data data in flatData.Where(item => item.ParentId == parentId))
{
yield return new Node
{
Data = data,
Level = level,
Children = BuildHierarchy(flatData, data.Id, level + 1)?.ToList()
};
}
}
Lastly, we can now look at the hierarchical dataset:
OutputHierarchy(NodeData);
void OutputHierarchy(List<Node> nodeData)
{
foreach (Node node in nodeData)
{
Console.WriteLine(node.Data.Title.PadLeft((node.Level - 1) * 2 +
node.Data.Title.Length));
if (node.Children?.Any() == true)
OutputHierarchy(node.Children);
}
}
and the output:
Country 0
City 1017
Store 2040
Store 2063
Store 2070
Country 1
City 1003
Store 2000
Store 2006
Store 2010
Store 2055
Store 2081
Store 2088
Store 2097
City 1008
Store 2046
Store 2074
Store 2075
Store 2092
Store 2098
Country 2
City 1002
Store 2002
Store 2008
Store 2024
Store 2094
City 1007
Store 2005
Store 2029
Store 2041
Store 2065
Store 2089
City 1011
Store 2011
Store 2028
Store 2038
Store 2042
Store 2044
Store 2072
Store 2077
City 1012
Store 2026
Store 2061
Store 2068
Store 2086
Store 2099
Country 3
City 1010
Store 2020
Store 2027
Store 2033
Store 2034
Store 2052
Store 2093
Country 4
City 1001
Store 2039
Store 2073
Store 2087
City 1015
Store 2051
Store 2064
Store 2071
Store 2095
City 1016
Store 2025
Store 2030
City 1018
Store 2015
Store 2021
Store 2036
Store 2085
Store 2091
Country 5
City 1000
Store 2003
Store 2031
Store 2058
Country 6
City 1006
Store 2032
Store 2035
Store 2037
Store 2048
Store 2059
Store 2067
City 1014
Store 2001
Store 2023
Store 2043
Country 7
City 1005
Store 2012
Store 2016
Store 2018
Store 2022
Store 2054
Store 2056
Store 2057
Store 2078
Country 8
City 1019
Store 2007
Store 2009
Store 2047
Store 2060
Country 9
City 1004
Store 2049
Store 2050
Store 2062
Store 2066
Store 2080
Store 2082
Store 2083
Store 2084
Store 2090
Store 2096
City 1009
Store 2004
Store 2014
Store 2017
Store 2019
Store 2076
Store 2079
City 1013
Store 2013
Store 2045
Store 2053
Store 2069
Here is the complete test Dot Net 7.0 console app:
Random random = new Random();
List<Data> FlatData = GetData().ToList();
List<Node> NodeData = BuildHierarchy(FlatData).ToList();
OutputHierarchy(NodeData);
Console.WriteLine("-- press any key to quit --");
Console.ReadKey();
void OutputHierarchy(List<Node> nodeData)
{
foreach (Node node in nodeData)
{
Console.WriteLine(node.Data.Title.PadLeft((node.Level - 1) * 2 + node.Data.Title.Length));
if (node.Children?.Any() == true)
OutputHierarchy(node.Children);
}
}
IEnumerable<Node>? BuildHierarchy(List<Data> flatData, int parentId = -1, int level = 1)
{
foreach (Data data in flatData.Where(item => item.ParentId == parentId))
{
yield return new Node
{
Data = data,
Level = level,
Children = BuildHierarchy(flatData, data.Id, level + 1)?.ToList()
};
}
}
IEnumerable<Data> GetData()
{
for (int i = 0; i < 10; i++)
yield return new Data
{
DataType = DataType.Country,
Id = i,
ParentId = -1,
Title = $"Country {i}"
};
for (int i = 1000; i < 1020; i++)
yield return new Data
{
DataType = DataType.City,
Id = i,
ParentId = random.Next(10),
Title = $"City {i}"
};
for (int i = 2000; i < 2100; i++)
yield return new Data
{
DataType = DataType.Store,
Id = i,
ParentId = random.Next(1000, 1020),
Title = $"Store {i}"
};
}
class Node
{
public Data Data { get; set; }
public int Level { get; set; }
public List<Node>? Children { get; set; }
}
class Data
{
public int Id { get; set; }
public int ParentId { get; set; }
public DataType DataType { get; set; }
public string Title { get; set; }
}
enum DataType
{
Country,
City,
Store
}
UPDATE
One more tip. If you need to get the path for a node, you can use the following:
void OutputHierarchy(List<Node> nodeData)
{
foreach (Node node in nodeData)
{
StringBuilder sb = new();
sb.Append(node.Data.Title.PadLeft((node.Level - 1) * 2 + node.Data.Title.Length))
.Append(" [")
.Append(GetDataPath(node.Data))
.Append("]");
Console.WriteLine(sb);
if (node.Children?.Any() == true)
OutputHierarchy(node.Children);
}
}
string GetDataPath(Data data)
{
if (data.ParentId == -1)
return data.Title;
return $"{GetDataPath(FlatData.First(parent => parent.Id == data.ParentId))} > {data.Title}";
}
And the output will be:
Country 0 [Country 0]
City 1000 [Country 0 > City 1000]
Store 2015 [Country 0 > City 1000 > Store 2015]
Store 2030 [Country 0 > City 1000 > Store 2030]
Store 2068 [Country 0 > City 1000 > Store 2068]
Store 2086 [Country 0 > City 1000 > Store 2086]
Store 2089 [Country 0 > City 1000 > Store 2089]
City 1013 [Country 0 > City 1013]
Store 2007 [Country 0 > City 1013 > Store 2007]
Store 2043 [Country 0 > City 1013 > Store 2043]
Country 1 [Country 1]
City 1006 [Country 1 > City 1006]
Store 2016 [Country 1 > City 1006 > Store 2016]
Store 2019 [Country 1 > City 1006 > Store 2019]
Store 2044 [Country 1 > City 1006 > Store 2044]
Store 2078 [Country 1 > City 1006 > Store 2078]
Store 2093 [Country 1 > City 1006 > Store 2093]
City 1007 [Country 1 > City 1007]
Store 2003 [Country 1 > City 1007 > Store 2003]
Store 2020 [Country 1 > City 1007 > Store 2020]
Store 2051 [Country 1 > City 1007 > Store 2051]
Store 2052 [Country 1 > City 1007 > Store 2052]
Store 2092 [Country 1 > City 1007 > Store 2092]
Country 2 [Country 2]
City 1004 [Country 2 > City 1004]
Store 2006 [Country 2 > City 1004 > Store 2006]
Store 2027 [Country 2 > City 1004 > Store 2027]
Store 2034 [Country 2 > City 1004 > Store 2034]
Store 2047 [Country 2 > City 1004 > Store 2047]
Store 2058 [Country 2 > City 1004 > Store 2058]
Store 2095 [Country 2 > City 1004 > Store 2095]
City 1010 [Country 2 > City 1010]
Store 2032 [Country 2 > City 1010 > Store 2032]
Store 2039 [Country 2 > City 1010 > Store 2039]
Store 2041 [Country 2 > City 1010 > Store 2041]
Store 2077 [Country 2 > City 1010 > Store 2077]
City 1015 [Country 2 > City 1015]
Store 2001 [Country 2 > City 1015 > Store 2001]
Store 2037 [Country 2 > City 1015 > Store 2037]
Store 2063 [Country 2 > City 1015 > Store 2063]
Store 2070 [Country 2 > City 1015 > Store 2070]
Store 2082 [Country 2 > City 1015 > Store 2082]
City 1019 [Country 2 > City 1019]
Store 2012 [Country 2 > City 1019 > Store 2012]
Store 2029 [Country 2 > City 1019 > Store 2029]
Store 2080 [Country 2 > City 1019 > Store 2080]
Store 2099 [Country 2 > City 1019 > Store 2099]
Country 3 [Country 3]
City 1003 [Country 3 > City 1003]
Store 2000 [Country 3 > City 1003 > Store 2000]
Store 2040 [Country 3 > City 1003 > Store 2040]
Store 2045 [Country 3 > City 1003 > Store 2045]
Store 2055 [Country 3 > City 1003 > Store 2055]
Store 2061 [Country 3 > City 1003 > Store 2061]
Store 2084 [Country 3 > City 1003 > Store 2084]
City 1012 [Country 3 > City 1012]
Store 2026 [Country 3 > City 1012 > Store 2026]
Store 2031 [Country 3 > City 1012 > Store 2031]
Store 2087 [Country 3 > City 1012 > Store 2087]
Store 2088 [Country 3 > City 1012 > Store 2088]
Store 2090 [Country 3 > City 1012 > Store 2090]
Country 4 [Country 4]
City 1005 [Country 4 > City 1005]
Store 2017 [Country 4 > City 1005 > Store 2017]
Store 2018 [Country 4 > City 1005 > Store 2018]
Store 2028 [Country 4 > City 1005 > Store 2028]
Store 2050 [Country 4 > City 1005 > Store 2050]
Store 2059 [Country 4 > City 1005 > Store 2059]
Store 2064 [Country 4 > City 1005 > Store 2064]
Store 2073 [Country 4 > City 1005 > Store 2073]
City 1017 [Country 4 > City 1017]
Store 2011 [Country 4 > City 1017 > Store 2011]
Store 2014 [Country 4 > City 1017 > Store 2014]
Store 2069 [Country 4 > City 1017 > Store 2069]
Store 2083 [Country 4 > City 1017 > Store 2083]
City 1018 [Country 4 > City 1018]
Store 2004 [Country 4 > City 1018 > Store 2004]
Store 2035 [Country 4 > City 1018 > Store 2035]
Store 2053 [Country 4 > City 1018 > Store 2053]
Store 2056 [Country 4 > City 1018 > Store 2056]
Store 2057 [Country 4 > City 1018 > Store 2057]
Store 2060 [Country 4 > City 1018 > Store 2060]
Store 2062 [Country 4 > City 1018 > Store 2062]
Store 2081 [Country 4 > City 1018 > Store 2081]
Store 2097 [Country 4 > City 1018 > Store 2097]
Country 5 [Country 5]
City 1011 [Country 5 > City 1011]
Store 2025 [Country 5 > City 1011 > Store 2025]
Store 2067 [Country 5 > City 1011 > Store 2067]
Store 2074 [Country 5 > City 1011 > Store 2074]
Country 6 [Country 6]
Country 7 [Country 7]
City 1008 [Country 7 > City 1008]
Store 2013 [Country 7 > City 1008 > Store 2013]
Store 2033 [Country 7 > City 1008 > Store 2033]
Store 2046 [Country 7 > City 1008 > Store 2046]
Store 2049 [Country 7 > City 1008 > Store 2049]
Store 2072 [Country 7 > City 1008 > Store 2072]
Store 2096 [Country 7 > City 1008 > Store 2096]
City 1009 [Country 7 > City 1009]
Store 2005 [Country 7 > City 1009 > Store 2005]
Store 2009 [Country 7 > City 1009 > Store 2009]
Store 2021 [Country 7 > City 1009 > Store 2021]
Store 2022 [Country 7 > City 1009 > Store 2022]
Store 2042 [Country 7 > City 1009 > Store 2042]
Store 2048 [Country 7 > City 1009 > Store 2048]
Store 2071 [Country 7 > City 1009 > Store 2071]
Store 2076 [Country 7 > City 1009 > Store 2076]
Store 2085 [Country 7 > City 1009 > Store 2085]
Store 2098 [Country 7 > City 1009 > Store 2098]
Country 8 [Country 8]
City 1001 [Country 8 > City 1001]
Store 2010 [Country 8 > City 1001 > Store 2010]
Store 2024 [Country 8 > City 1001 > Store 2024]
Store 2036 [Country 8 > City 1001 > Store 2036]
Store 2038 [Country 8 > City 1001 > Store 2038]
Store 2054 [Country 8 > City 1001 > Store 2054]
Store 2065 [Country 8 > City 1001 > Store 2065]
Store 2066 [Country 8 > City 1001 > Store 2066]
Store 2075 [Country 8 > City 1001 > Store 2075]
Store 2094 [Country 8 > City 1001 > Store 2094]
City 1002 [Country 8 > City 1002]
Store 2008 [Country 8 > City 1002 > Store 2008]
Store 2079 [Country 8 > City 1002 > Store 2079]
Store 2091 [Country 8 > City 1002 > Store 2091]
City 1014 [Country 8 > City 1014]
Store 2002 [Country 8 > City 1014 > Store 2002]
Country 9 [Country 9]
City 1016 [Country 9 > City 1016]
Store 2023 [Country 9 > City 1016 > Store 2023]
Hopefully, this will point you in the right direction.