Self-regulation with the Entity Framework

Question

The code is a small scale of my problem:

public class Category { public Guid CategoryID { get; set; } public string Name { get; set; } public Guid? ParentID { get; set; } public bool IsTop { get; set; } public string Description { get; set; } public virtual Category parentCategory { get; set; } } 

When I use this class in the Entity Framework, it generates only one relation of parent and child categories.

How can I say, to semantically separate properties and generate two different relationships in SQL Server, to get all child categories (child relationships of a child (recursive from top to bottom)) and the other to get all parent categories (parent of a parent (recursive from bottom to top) )? Something like that:

 public virtual ICollection<Category> childCategories { get; set;} public virtual ICollection<Category> parentCategories { get; set;} 

I tried this with modelBuilder, but from there I can only get one level of detail.

Answer 1

I had the problem of extracting all the child nodes to a depth of n in one of my projects, as a classic relationship with the supervisor / employee in the Employee table in my model. As noted by Slauma and Milracle, EF will not help you retrieve all nodes to a depth n under a specific parent. However, I was able to solve this problem using the first-order search algorithm in my repository. Please note that my goal was not only to get all the child nodes, but also so fast, since using recursive LINQ queries took more than two minutes for the upper levels of control. Using this method, it now executes in less than two seconds.

 public IEnumerable<string> GetAllSubordinateEmployeeIdsByUserId(string userId) { // Retrieve only the fields that create the self-referencing relationship from all nodes var employees = (from e in GetAllEmployees() select new { e.Id, e.SupervisorId }); // Dictionary with optimal size for searching Dictionary<string, string> dicEmployees = new Dictionary<string, string>(employees.Count() * 4); // This queue holds any subordinate employees we find so that we may eventually identify their subordinates as well Queue<string> subordinates = new Queue<string>(); // This list holds the child nodes we're searching for List<string> subordinateIds = new List<string>(); // Load the dictionary with all nodes foreach (var e in employees) { dicEmployees.Add(e.Id, e.SupervisorId); } // Get the key (employee ID) for each value (employee supervisor ID) that matches the value we passed in var directReports = (from d in dicEmployees where d.Value == userId select d.Key); // Add the child nodes to the queue foreach (var d in directReports) { subordinates.Enqueue(d); } // While the queue has a node in it... while (subordinates.Count > 0) { // Retrieve the children of the next node in the queue var node = subordinates.Dequeue(); var childNodes = (from e in dicEmployees where e.Value == node select e.Key); if (childNodes.Count() != 0) { // Add the child nodes to the queue foreach (var c in childNodes) { subordinates.Enqueue(c); } } // Add the node from the queue to the list of child nodes subordinateIds.Add(node); } return subordinateIds.AsEnumerable(); } 

In addition, as a footnote, I was able to increase the efficiency of search queries in the dictionary using this article on optimizing the dictionary .