Get node parent department in Entity Framework

Question

I have an SQL table like this:

DepartmentID is the parent of the department. I built a tree from this table (in an ASP.net project (C #)):

Entries in the tree above:

I need to get parents in this tree.

I can do this in SQL Server like this (for example id=2 , id is the input argument):

 with cte1 as ( select id,name,DepartmentID, 0 AS level from Department where id =2 union all select Department.ID,Department.name,Department.DepartmentID, level+1 from Department inner join cte1 on Department.ID=cte1.DepartmentID ) select * from cte1 

Logout (id = 2 (A))

Logout (id = 4 (A1))

I know that EF does not support cte , but I need to get this result in EF.

It would be very helpful if someone could explain the solution to this problem.

Answer 1

These posts are similar to your question. Please see the following:

writing-recursive-cte-using-entity-framework-fluent-syntax-or-inline-syntax
converting-sql-statement-that-contains-with-cte-to-linq

I think there is no way to write a single LINQ to SQL query that could get everything. However, LINQ does support a method for executing a query (oddly enough, the so-called DataContext.ExecuteQuery). It looks like you can use this to invoke an arbitrary part of SQL and map it back to LINQ.

See this post: common-table-expression-in-entityframework

Answer 2

The easiest way I can imagine is to map the relationships in EF, then get all the departments, and then get the root parent from this list. All of them must be loaded into memory, and EF will take care of the tree structure with the display. Alternatively, you can enable lazy loading and just get the parent, but then with each child or child, the request will be executed by EF during the extraction.

Model

 public class Department { public int Id { get; set; } public string Name { get; set; } public int? DepartmentId { get; set; } public Department ParentDepartment { get; set; } public virtual ICollection<Department> ChildDepartments { get; set; } } 

Display (using free)

 public DbSet<Department> Departments { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { // other mapping code modelBuilder.Entity<Department>() .HasOptional(x => x.ParentDepartment) .WithMany(x => x.ChildDepartments) .HasForeignKey(x => x.DepartmentId); // other mapping code } 

Expected Parent Root Extraction

 using (var context = new YourDbContext()) { var allDepartments = context.Departments.ToList(); // eagerly return everything var rootDepartment = allDepartments.Single(x => x.DepartmentId == null); } 

Retrieving only the root parent and then using lazy loading, note that DbContext must be available for Lazy Loading to work, and must also be included in DbContext

 using (var context = new YourDbContext()) { var rootDepartment = context.Departments.Single(x => x.DepartmentId == null); // do other stuff, as soon as context is disposed you cant lazy load anymore } 

Answer 3

Try one of them,

one -

 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 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, header control column);

Change 1:

3 - In another way, specifying the identifier as input and assuming that the identifier column is unique, so there will always be two values ​​in the array, and, returning the list, the index of the elements actually represents their levels. (they won’t add results because they are the same :)). By the way, 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 no cte in linq, mainly using view, sp is the first choice, but here is the solution, it can be a little push. In any case, this gives a 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 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 level 4)

Hope helps

Answer 4

The following is a simple program class program code.

You can check using different identifiers of the input parameter of the GetParentSet method.

 class Program { static void Main(string[] args) { Program p = new Program(); var result= p.GetParentSet(6); foreach(var a in result) { Console.WriteLine(string.Format("{0} {1} {2}",a.ID,a.Name,a.DepartmentId)); } Console.Read(); } private List<Department> GetParentSet(int id) { List<Department> result = new List<Department>(); //Result set using (RamzDBEntities context = new RamzDBEntities()) { var nodeList = context.Departments.Where(t=>t.ID<=id).ToList(); //Get All the the entries where ID is below or greater than the given to the list var item = nodeList.Where(a => a.ID == id).SingleOrDefault(); //Get the default item for the given ID result.Add(item); //Add it to the list. This will be the leaf of the tree int size = nodeList.Count(); //Get the nodes count for (int i = size; i >= 1;i--) { var newItem= nodeList.Where(j => j.ID == item.DepartmentId).SingleOrDefault(); //Get the immediate parent. This can be done by matching the leaf Department ID against the parent ID if (item!=null && !result.Contains(newItem)) //If the selcted immediate parent item is not null and it is not alreday in the list { result.Add(newItem); //Add immediate parent item to the list } if (newItem.ID == 1) //If the immediate parent item ID is 1 that means we have reached the root of the tree and no need to iterate any more. break; item = newItem; //If the immediate parent item ID is not 1 that means there are more iterations. Se the immediate parent as the leaf and continue the loop to find its parent } } return result; //return the result set } } 

The code itself is self-explanatory. However, the explanation below. Hope this helps!

• First, all records with an identifier lower than or equal to the specified identifier are assigned to the list
• Then get a leaf of the tree and add it to the list called result. This is the first element of our result set.
• We are sorting in descending order. Get the immediate parent of the sheet by equating the parent ID to the department ID of the sheet
• If this immediate parent is not null and it is not already in the list, add it to the list.
• Make the original parent as a sheet and continue the loop so that we can get the parent of the closest parent.
• continue this until you reach the root of the tree.
• If the closest parent id is 1, it means that we have reached the root of the tree and we can break the loop.

Answer 5

Since you created edmx, you have the code created for your DbContext and for your model classes, including departments, for example, in this screenshot.

You should not change them, because they can (will) be overwritten by EF tools in any case with any manipulations with the model. Fortunately, both classes are generated as partial , so the creators thought about people who wanted to configure it safely.

The following is an example for ease of implementation, not for maximum performance. I suggested that the table containing the Departments is not very large, and the levels of nesting in the hierarchy are not very deep.

• Create a new class (* .cs file) in your project and extend your auto-generated Departments class with your custom method or property:

   using System; using System.Collections.Generic; using System.Linq; namespace CustomEF.EFStuff { public partial class Departments { public List<Departments> Hierarchy { get { List<Departments> retVal = new List<Departments>(); retVal.Add(this); using (YourAutoGeneratedContext ctx = new YourAutoGeneratedContext()) { Departments tmp = this; while(tmp.DepartmentID != null) { tmp = ctx.Departments.First(d => d.ID == tmp.DepartmentID); retVal.Add(tmp); } } return retVal; } private set { } } } } 

  When extending a partial class, make sure you put it in the same namespace. In my case, I named my project CustomEF, and I placed the edmx file in a subfolder of EFStuff so that the generator would place the automatically generated class in the CustomEF.EFStuff namespace.

  The above example will allow you to get a hierarchy for any department object, for example.

   int level = 0; foreach(Departments d in someDepartmentObject.Hierarchy) { Console.WriteLine(d.ID.ToString() + ", " + d.DepartmentID.ToString() + ", " + d.Name +", " +(level++).ToString()); } 

• If you also need to get a hierarchy from some code where you have an identifier but not an object, you can additionally create another class (* .cs file) in which you will expand the auto-generated context.

   using System.Collections.Generic; using System.Linq; namespace CustomEF.EFStuff { public partial class YourAutoGeneratedContext { public List<Departments> GetDepartmentHierarchy(int departmentId) { Departments mydep = this.Departments.FirstOrDefault(d => d.ID == departmentId); if (mydep == null) { throw new System.Data.Entity.Core.ObjectNotFoundException("There is no department with ID = " + departmentId.ToString()); } return mydep.Hierarchy; } } } 

  Or in this case, you can completely move the implementation to the Context class without extending the Departments class at all (and you will not need to create an additional instance of your context, you will get this to use).

   using System.Collections.Generic; using System.Linq; namespace CustomEF.EFStuff { public partial class YourAutoGeneratedContext { public List<Departments> GetDepartmentHierarchy(int departmentId) { Departments tmp = this.Departments.FirstOrDefault(d => d.ID == departmentId); if (tmp == null) { throw new System.Data.Entity.Core.ObjectNotFoundException("There is no department with ID = " + departmentId.ToString()); } List<Departments> retVal = new List<Departments>(); retVal.Add(tmp); while (tmp.DepartmentID != null) { tmp = this.Departments.First(d => d.ID == tmp.DepartmentID); retVal.Add(tmp); } return retVal; } } } 

  As another inexperienced use case:

   YourAutoGeneratedContext ctx = new YourAutoGeneratedContext(); level = 0; foreach (Departments currentHier in ctx.GetDepartmentHierarchy(10)) { Console.WriteLine(currentHier.ID.ToString() + ", " + currentHier.DepartmentID.ToString() + ", " + currentHier.Name + ", " + (level++).ToString()); } 

I do not know how much you can trust the data in the database. You might want to implement some checks, including cross-references, to prevent an endless loop.

Please note that formally the term “class extension” can be applied to extension methods, and not to partial classes. I used this word for lack of a better one. Extension methods are what you might want to use if for some reason you need your method / property that returns EF native DbSet<> instead of List<> . In this case, you can take a look at: https://shelakel.co.za/entity-framework-repository-pattern/

Answer 6

An example in EF6 is to get all parents to the root of the node.

 public class Department { [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int Id { get; set; } public string Name { get; set; } public int? ParentId { get; set; } public virtual Department Parent { get; set; } public virtual ICollection<Department> Children { get; set; } private IList<Department> allParentsList = new List<Department>(); public IEnumerable<Department> AllParents() { var parent = Parent; while (!(parent is null)) { allParentsList.Add(parent); parent = parent.Parent; } return allParentsList; } }