EF core self referencing include all child

Question

I have one entity like this:

 public string Name { get; set; }
 public string Description { get; set; }
 public Guid? ParentId { get; set; }
 public int Position { get; set; }
 public bool IsVisible { get; set; }

 public virtual Category ParentCategory { get; set; }
 public virtual ICollection<Category> Categories { get; set; }
 public virtual ICollection<Product> Products { get; set; }

In database i insert it like this:

Category1
 {
  Category2
  {
   Category4
  }  
  Category3
 }

How should look entity query to get all Category with ALL children? I traing like this:

  var category = await _context.Categories
                .Include(x => x.Categories)
                .ThenInclude(x=>x.Categories)
                .OrderBy(x => x.Position).FirstAsync(x => x.ParentId == null);

But i think this isn't good way becouse i don't know deep will be this tree.

Does this answer your question? [Eager load a self-referencing table](https://stackoverflow.com/questions/1412544/eager-load-a-self-referencing-table) — panoskarajohn, Jan 13 '20 at 14:17
Sorry i think this is more appropriate -> https://stackoverflow.com/questions/51395852/ef-core-eager-loading-include-sub-categories-self-reference — panoskarajohn, Jan 13 '20 at 14:20
One option is to just get all the rows, then create the tree structure in memory. — juharr, Jan 13 '20 at 14:23

Tufy Duck · Answer 1 · 2020-01-14T06:56:18.307

0

You should try this (this is not best practice)

var categories = await _context.Categories.ToListAsync();

var firstCategory = categories.GroupBy(c=>c.ParentId).FirstOrDefault(c=> c.Key == null).FirstOrDefault();

edited Jan 14 '20 at 06:56

answered Jan 13 '20 at 14:27

Tufy Duck

94
1
7

Doesn't look like an answer. Why it's not a best practise? Code looks wrong, await with ToList() rather than ToListAsync()... How should he try your code when he has an variable amout of parent/child relationships? – user743414 Jan 13 '20 at 14:40
And "No, it isn't possible" its simply not true, he can create a recursive loop and load all childrens – Tiago Silva Jan 13 '20 at 14:44
This one work with some changes (like ToListAsync). I need to check this one https://stackoverflow.com/questions/51395852/ef-core-eager-loading-include-sub-categories-self-reference – Newer Jan 13 '20 at 14:48
@TiagoSilva it's not possible to load it in one query. Of course you can load one by one, more or less intelligently. The lazy loading already fulfills this condition, just access every element, and you are done. (one query cannot contain recursion). – Holger Jan 13 '20 at 14:59
Ok guys, I edited my answer for you :) – Tufy Duck Jan 14 '20 at 06:57

score 0 · Answer 2 · answered Jan 13 '20 at 14:53

Loading the root of the tree, means to get all rows, and the tree structure will be built correctly by EF. For this, you just have to select all.

But we cannot load subtrees, the number of joins to do is fixed - hence the depth of subtrees is fixed.

You found the way yourself if the depth is just 1 or 2 levels. There just isn't a way to represent this in SQL.

It's a query depending on the data returned - that's not possible.

You want to read a part, and depending on the result, you want to read more !

It ends up in a multi-query solution. But this is what lazy loading already is. In the moment you access it, your data will be loaded with a separate query. It's just not optimal, since this is one query per entity, it's not one query per level.

EF core self referencing include all child

2 Answers2