0

Is there any way I can get a chain of entities from EF that represents a chain from a Child all the way up to the root Parent entity in a self-referencing hierarchy? I could of course do this with a while loop, such as:

var chain = new List<Node>();
var thisNode = db.Nodes.First(n => n.Id == [whatever]);
do {
    chain.Add(thisNode);
    thisNode = thisNode.ParentId == null ? null : thisNode.Parent;
} while (thisNode != null);

But I think this would result in one query to the DB for each level in the hierarchy. While we're talking about an admittedly small performance gain here, is there a way I can express this logic as a single LINQ query to get the whole chain of companies from a given child all the way up to a parent where the parent meets a particular condition? Or do I need to use a loop like this?

Jez
  • 27,951
  • 32
  • 136
  • 233
  • No, as asked and answered many times before, LINQ doesn't have any tooling for hierarchical queries. But you can load all data at once and rely on relationship fixup: https://stackoverflow.com/a/41837737/861716 – Gert Arnold Jul 13 '18 at 12:13

1 Answers1

0

Maybe this is a bit of a "cheat" answer, but I actually achieved what I wanted with the awesome AutoMapper. I guess behind-the-scenes, it's doing the recursive work for me. My EF entity has a Parent navigation property, my DTO to map to looks a little like this:

public class CompanyInChainDTO
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public CompanyInChainDTO Parent { get; set; }
}

... and this code causes AutoMapper to create a DTO chain from the child with the given ID up to the root parent (ie. the one with no Parent):

Mapper.Initialize(conf =>
{
    conf.CreateMap<EF.Company, CompanyInChainDTO>().MaxDepth(100);
});

using (var context = new EfContext())
{
    var child = context.Companies.First(comp => comp.Id == [whatever]);

    CompanyInChainDTO childDto = Mapper.Map<CompanyInChainDTO>(child);

    return childDto;  // Returns the child DTO with full Child -> Parent chain!
}

Very cool - I didn't know AutoMapper followed EF navigation properties by default. Notice how I also use MaxDepth(100) for safety to prevent infinite recursion just in case there is no root parent company!

Jez
  • 27,951
  • 32
  • 136
  • 233
  • It surely simplifies the code, but it conceals the fact that still n + 1 queries are fired. – Gert Arnold Jul 13 '18 at 12:53
  • True, but I'm not sure there's any more efficient way to do it with EF. – Jez Jul 13 '18 at 12:57
  • No, indeed there isn't. If you want to extract one branch of an entire hierarchy you need SQL views based on hierarchical queries using CTEs. – Gert Arnold Jul 13 '18 at 13:04