The code represent 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 Entity Framework, it generates only one relation of parent and child categories.
How can I tell to semantically separate the properties, and generate two different relations in SQL Server one for getting all child categories with (child of child relationship(recursive top-down)), and the other for getting all parent categories (parent of parent(recursive bottom-up))? Something like this:
public virtual ICollection<Category> childCategories { get; set;}
public virtual ICollection<Category> parentCategories { get; set;}
I tried it with modelBuilder but from there I can only get one level of detail.
I've had the problem of retrieving all child nodes to a depth of n come up in one of my projects as the classic supervisor/employee self-referencing relationship on an Employee table in my model. As Slauma and Milracle pointed out, EF won't help you to retrieve all nodes to a depth of n under a specified parent. However, I was able to solve this problem using a Breadth-first search algorithm in my repository. Please note, that my goal was not only to retrieve all the child nodes, but to do so quickly, as using recursive LINQ queries was taking more than two minutes for the top levels of management. 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's ID) for each value (employee's supervisor's 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();
}
Also, as a footnote, I was able to increase the efficiency of look-ups in the dictionary with help from this Dictionary optimization article.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With