Try one of these,
1-
int _ID = 2; // ID criteria
List<object> result = new List<object>(); // we will use this to split parent at child, it is object type because we need Level
var departments = entites.Departments.Where(x => x.ID == _ID).SelectMany(t => entites.Departments.Where(f => f.ID == t.DepartmentID),
(child, parent) => new { departmentID = child.DepartmentID, Name = child.Name, ID = child.ID, level = 0,
Parent = new { DepartmentID = parent.DepartmentID, Name = parent.Name, ID = parent.ID, level = 1 }});
// first we check our ID (we take A from where criteria), then with selectmany T represents the Department A, we need
// department A's departmentID to find its parent, so another where criteria that checks ID == DepartmentID, so we got T and the new list
// basically child from first where parent from second where, and object created.
// for showing the results
foreach (var item in departments)
{
result.Add(new { DepartmentID = item.departmentID,ID = item.ID, level= item.level,Name = item.Name}); // child added to list
result.Add(new { DepartmentID = item.Parent.DepartmentID, ID = item.Parent.ID, level = item.Parent.level, Name = item.Parent.Name }); // parent added to list
}
Result;
2-
List<object> childParent = new List<object>();
// basically get the child first
Departments child1 = entites.Departments.Where(x => x.ID == _ID).FirstOrDefault();
// find parent with child object
Departments parent1 = entites.Departments.Where(x => x.ID == child1.DepartmentID).FirstOrDefault();
// create child object with level
childParent.Add(new { child1.DepartmentID, child1.ID,child1.Name , level = 0});
// create parent object with level
childParent.Add(new { parent1.DepartmentID,parent1.ID,parent1.Name, level = 1 });
Result (not the same image, check column Header Text);
Edit 1:
3-
Another way, by giving ID as input and assuming that ID column is unique, so there will be always 2 values at the array and by returning list, the index of items actually represent their levels. (won't add results because they are same :)).Btw you can also use Union instead of Concat.
var ress = list.Where(x=> x.ID ==2)
.SelectMany(x=> list.Where(c=> c.ID == x.ID).Concat(list.Where(s => s.ID == x.DepartmentID))).ToList();
DataTable dt = new DataTable();
dt.Columns.Add("DepartmentID");
dt.Columns.Add("ID");
dt.Columns.Add("Name");
dt.Columns.Add("Level");
for (int i = 0; i < ress.Count(); i++)
{
dt.Rows.Add(ress[i].DepartmentID, ress[i].ID, ress[i].Name, i);
}
dataGridView1.DataSource = dt;
Edit 2
There is not cte in linq, basically using view,sp is the first choise but here is a solution, it might be a little push. Anyway it gives the result.
List<Departments> childParent = new List<Departments>();
// or basically get the child first
Departments child1 = entites.Departments.Where(x => x.ID == 7).FirstOrDefault();
// find parent with child object
Departments parent1 = entites.Departments.Where(x => x.ID == child1.DepartmentID).FirstOrDefault();
// create child object with level
Departments dep = new Departments(); // I add to department class a string level field
dep.DepartmentID = child1.DepartmentID;
dep.ID = child1.ID;
dep.Name = child1.Name;
dep.level = 0; // first item
childParent.Add(dep);
// create parent object with level
dep = new Departments();
dep.DepartmentID = parent1.DepartmentID;
dep.ID = parent1.ID;
dep.Name = parent1.Name;
dep.level = 1; // parent one
childParent.Add(dep);
while (childParent.Select(t => t.DepartmentID).Last() != null) // after added to list now we always check the last one if it's departmentID is null, if null we need to stop searching list for another parent
{
int? lastDepID = childParent.Last().DepartmentID; // get last departmentID
Departments tempDep = entites.Departments.Single(x => x.ID == lastDepID); // find as object
tempDep.level = childParent.Last().level + 1; // increase last level
childParent.Add(tempDep); // add to list
}
(Added another C1 to check 4th level)
Hope helps,
