Loading a Hierarchical data from a self joined table, can be done by several ways.
If you know how deep it will go, then You can easily use PrefetchPaths and SubPaths n levels deep.
Or if it has no limits, then I suggest loading the entire table into a Hashtable or a dictionary where you save each ParentID as the Key with a list of its children entities.
This can be easily ustelized in building a Tree control for instance.
Example:
private Dictionary<int, List<MyEntity>> GetDictionary()
{
Dictionary<int, List<MyEntity>> myDictionary = new Dictionary<int, List<MyEntity>>();
EntityCollection<MyEntity> myEntities = new EntityCollection<MyEntity>();
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(myEntities, null);
foreach (MyEntity myEntity in myEntities)
{
List<MyEntity> children;
if (!myDictionary.TryGetValue(myEntity.ParentId, out children))
{
children = new List<MyEntity>();
myDictionary.Add(myEntity.ParentId, children);
}
if (!children.Contains(myEntity))
{
children.Add(myEntity);
}
}
return myDictionary;
}
private void LoadTree()
{
Dictionary<int, List<MyEntity>> myDictionary = GetDictionary();
TreeView1.Nodes.Clear();
PopulateChildNodes(myDictionary, TreeView1.Nodes, 0);
}
private void PopulateChildNodes(Dictionary<int, List<MyEntity>> myDictionary, TreeNodeCollection childNodes, int parentId)
{
List<MyEntity> children = new List<MyEntity>();
if (myDictionary.TryGetValue(parentId, out children))
{
foreach (MyEntity child in children)
{
TreeNode childNode = new TreeNode(child.Name, child.Id.ToString());
PopulateChildNodes(myDictionary, childNode.ChildNodes, child.Id);
childNodes.Add(childNode);
}
}
}