25

Explanation why this question is different to: EF - multiple includes to eager load hierarchical data. Bad practice?

  1. the possible duplicate is an opinion based question if this is a bad practice or not whereas my question tends to get the technical solution on how to do it, independent of the opinion if it is a good practice or not. I leave this decision up to the product owner, requirement engineer, project manager and the costumer who wants that feature.
  2. The given answers either explain why it is a bad practice or use an approach which is not working for me (using Include() and ThenInclude() produces a hard coded depth whereas I need a flexible depth).

In the current project (a .NET core web api) I try to load a hierarchy from a self referencing table.

After googling a lot I was surprised that such a task (which I thought would be trivial) seems not to be trivial.

Well, I have this table to form my hierarchy:


CREATE TABLE [dbo].[Hierarchy] (
    [Id]        INT           IDENTITY (1, 1) NOT NULL,
    [Parent_Id] INT           NULL,
    [Name]      NVARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Hierarchy_Hierarchy] FOREIGN KEY ([Parent_Id]) REFERENCES [dbo].[Hierarchy] ([Id])
);

In the web api I try to return the complete hierarchy. One maybe special thing (that could help) is the fact that I want to load the complete table.

I also know that I could use eager loading and the navigation property (Parent and InverseParent for children)


_dbContext.Hierarchy.Include(h => h.InverseParent).ThenInclude(h => h.InverseParent)...

The problem with that is that this would load a hard coded depth (e.g. six levels if I use 1 Include() and 5 ThenInclude()) but my hierarchy has a flexible depth.

Can anyone help me out by giving me some code how to load the full table (e.g. into memory in an optimal scenario with 1 DB call) and then make the method return the full hierarchy?

Community
  • 1
  • 1
monty
  • 7,888
  • 16
  • 63
  • 100
  • What have you tried yourself? And code in what language (there are not language tags)? – Sefe Jan 24 '17 at 12:15
  • @Sefe Entity Framework Core is obviously C# (which I thought would be clear by writint "a .NET core web api". What I have so far and tried my self is also inserted as code snippet. – monty Jan 24 '17 at 12:49
  • 1
    People are filtering their questions by tags. You need to add the correct tags to your question. – Sefe Jan 24 '17 at 12:57
  • 1
    @Sefe thx for the advice. I added more tags. My last questions were answered although they were tagged only with two tags. Any suggestions realting my question? – monty Jan 24 '17 at 13:29
  • May be it will help http://patrickdesjardins.com/blog/how-to-load-hierarchical-structure-with-recursive-with-entity-framework-5 – Artiom Jan 24 '17 at 13:40
  • Can you post the sample entity model? Without that, I could understand what the `Parent` property is (something like `public Hierarchy Parent { get; set; }`), but not sure about `InverseParent`. Do you mean something like `public ICollection Children { get; set; }`? – Ivan Stoev Jan 24 '17 at 19:33
  • @IvanStoev i scaffolded the model class from an existing database (database first approach). InverseParent is exactly what you have written. I don't know why they named it InverseParent instead of children. Maybe because inverseX is a more flexible naming convention than childrenX. – monty Jan 25 '17 at 06:32
  • Take [linq2db.EntityFrameworkCore](https://github.com/linq2db/linq2db.EntityFrameworkCore) extension. Use [CTE](https://linq2db.github.io/articles/sql/CTE.html) – Alexander Petrov Oct 27 '21 at 11:34

1 Answers1

39

In fact loading the whole hierarchy is quite easy thanks to the so called EF (Core) relationship fixup.

Let say we have the following model:

public class Hierarchy
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Hierarchy Parent { get; set; }
    public ICollection<Hierarchy> Children { get; set; }
}

Then the following code

var hierarchy = db.Hierarchy.Include(e => e.Children).ToList();

will load the whole hierarchy with correctly populated Parent and Children properties.

The problem described in the referenced posts arise when you need to load just part of the hierarchy, which is hard due to the lack of CTE like support in LINQ.

Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343
  • 6
    The `Include` may also not required because the target type of the navigation is the same type which is already loaded so relationship fixup will populate it regardless. – Smit Jan 30 '17 at 21:40
  • @Smit Indeed! I realized that later when answering similar EF6 question. Will update when have some time. – Ivan Stoev Jan 30 '17 at 23:47
  • This provides a full hierarchy from all hierarchy records (which mean you will get a duplicate result). we cannot ask for starting from orphan hierarchy and deep down to its entire hierarchy. – Jongz Puangput Mar 02 '19 at 04:00
  • 2
    Any reasons for this to not work with a `.Where`, or I'm missing something? – eja May 16 '19 at 21:14
  • 4
    @eja The reason is that if you use `Where`, it won't load some parents / children. `Include` doesn't help because it's not recursive. – Ivan Stoev May 17 '19 at 07:41
  • @IvanStoev how are your relationships defined in the model above? can you also post dbcontext? – pantonis Sep 27 '19 at 06:37
  • @pantonis Nothing special, just add a `DbSet` for the above model. It would create conventional one-to-many optional relationship to self - basically the same as `CREATE TABLE` in the question w/o underscore in "Parent_Id" column ("ParentId"). – Ivan Stoev Sep 27 '19 at 11:11
  • @IvanStoev I tried your answer but it does not seem to correctly populate Parent and children hierachy. I can see a child under a parent but then this child is also added on its own – pantonis Sep 27 '19 at 11:53
  • 4
    @pantonis That's because the code is loading the whole table into a flat list. To get a tree (i.e. a list of root nodes), you'd apply `Where` to take just the root items, but *after* loading all entities and their navigation properties. e.g. `var tree = db.Hierarchy.Include(e => e.Children).ToList().Where(e => e.Parent == null).ToList();` – Ivan Stoev Sep 27 '19 at 12:05
  • 1
    Thanks that did the trick. I was adding where before the list – pantonis Sep 27 '19 at 12:11
  • 1
    @IvanStoev, what do you think about EF Core 3 - relationship fixup is kind of not working. I couldn't find any information in breaking chages or the issue tracker on GitHub. Now I am able to get only one level deeper when trying to get inverse parents using the proposed solution. It was OK with EF Core 2... Do you have any idea what is happening? – Ivaylo Oct 30 '19 at 08:17
  • 4
    @IvayloDimitrov The above works in 3.0 even w/o `Include` for tracking queries, but seems to be broken for no-tracking queries. Most likely by the following breaking change [No-tracking queries no longer perform identity resolution](https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#notrackingresolution). – Ivan Stoev Oct 30 '19 at 09:24
  • 1
    @IvanStoev yes, you are right! Queries should be tracked to keep the same behaviour. Will dig deeper to confirm this would be from the identity resolution or something else. Anyway, thank you vety much! – Ivaylo Oct 30 '19 at 10:51
  • 1
    @IvayloDimitrov I just noticed that setting the Where after the ToList() causes the entire table to load in server and then filtering applies on that. Imagine the impact on a table with hundreds of thousands of rows – pantonis May 13 '20 at 09:05
  • @pantonis Here and in similar post I started with the word "whole" in bold. So call it whole, entire or full, it should be clear that it loads the unfiltered table in memory - that's the trick which makes it work. If you need server side filtered hierarchical subset, this is NOT for you. – Ivan Stoev May 13 '20 at 11:51
  • 1
    @IvanStoev you wrote whole hierarchy not whole table. A hierarchy might only involve a number of rows in a specific table – pantonis May 13 '20 at 13:33
  • @pantonis I'm not native English speaker, hence might be missing nuances. OP is using "complete hierarchy" and then "One maybe special thing (that could help) is the fact that I want to load the complete table.". So by the word "whole" I mean exactly that. Feel free to edit my post and replace "**whole** hierarchy" with the proper term. – Ivan Stoev May 13 '20 at 13:42
  • @IvanStoev do you have any idea to make my treeview work with n-depth without tracking entities. Without tracking it is 10x faster to render my view. – ibubi Jun 01 '20 at 13:05
  • 1
    @ibubi Unfortunately the whole trick works because of tracking. Eventually you could do the fix-up manually by using temporary `Dictionary` or something. – Ivan Stoev Jun 01 '20 at 13:26
  • @IvanStoev Great answer. Btw, `Closure Tables` are ideal for this kind of things and I've been using them (pure SQL) for years. Unfortunately, I lack experience with EF to implement it using EF Core. Do you think it's possible? – scrnjakovic Apr 20 '21 at 17:56
  • 1
    For no-tracking queries was added AsNoTrackingWithIdentityResolution in EF Core 5 which populates Parent and Children properties. It works without Include – Oleg Lukash Apr 22 '21 at 20:45
  • This is fantastic and solved my query. But does anyone know how to use this in such a way that it'll pull back the children of each node sorted in a particular order? I tried `var hierarchy = db.Hierarchy.Include(e => e.Children.OrderBy( c => c.Name ) ).ToList();` but I get a Linq error saying the field can't be mapped. :( Or can I assume that because this is just using fix-up, that's not possible and I should do the hierarchy mapping/sorting myself? – Webreaper Jun 01 '22 at 16:18
  • @Webreaper `OrderBy` inside include was added recently (EFC 5.0 if I recall correctly), and in general should (probably) work. Field cannot be mapped could be from something else, can you run `db.Hierarchy.ToList()` w/o error? But even if ordering inside `Include` doesn't work, you can do it afterwards client side - the hierarchy structure in memory will be populated by EF Core, you can iterate the result (recursively) and sort the "children" lists of each "parent". – Ivan Stoev Jun 01 '22 at 16:32