What is your question exactly?

How to make a tree so that categories and their children are displayed

Does your solution work?

A mother can have several children, each child has her own children. I don't know how many grandchildren my mother has, I know she only has children.

My only thought is to loop and check if there are children and do the whole function again, unfortunately I'm afraid that's not efficient and correct.

if you fetch all data, you can use LINQ to make your tree in memory

it will be 1 db query

How to do it? I would like my children to be placed correctly.

it'd be a recursive query till you reach the depth you want, is that what you're worried about?

It's more about how to handle it.
I'm thinking here about some lambda that will be executed every time.

there are a couple of ways to handle this - it depends on the scalability/demand here.

at the most simple crude level, you're given an ID of a random entity

so let's find the ultimate parent

Entity FindParent(int id)
{
  var entity = db.Entities.Single(x => x.Id);
  
  if(entity.ParentId is not null)
    return FindParent(entity.ParentId.Value);

  return null;
}

right

it depends on the tree you're expecting here, but you can build it up this way pretty easily.

Won't this cause a lot of database queries?

yes, it will

do you always fetch all data or do you need only a particular parent all the way down?

if you find yourself struggling to get to the ultimate parent, i would consider a strategy to store this on the entities, for each one store what their ultimate parent is and that problem immediately disappears 🙂

with 25 items I'd just fetch all from the db and

var dict = await _context.Items.ToDictionaryAsync(x => x);

foreach(var item in dict.Values)
{
    if (item.ParentId is not null)
    dict[item.ParentId].Children.Add(item);
}

var root = dict.Values.First(x => x.ParentId is null);

Just testing a few solutions, these are tests. There will be multiple categories by default. I want to make wikipedia

I have a strange problem, if there is no where then correctly each model creates include. If I give where CategoryID == null (so that it starts with the main parent), then include will only work once

With "where "

without where

What it depends on? In theory, this would be fine, but unfortunately it fetches all values and then shows the children.

yeah you'd need to

_context.Items
    .Include(x => x.Children)
    .ThenInclude(x => x.Children)
    .ThenInclude(x => x.Children)
    .ThenInclude(x => x.Children)

for infinity

I would like to avoid this, unfortunately I do not know how many children or grandchildren my children have. There may be a case when my family will be very large. I was just looking for a solution with automatic include but unfortunately I couldn't find it anywhere.

you could as Patrick said store the topmost parent for every item. Then load all of those where the topmost parent match into memory and organize them through LINQ or loops

for this to work I would need to know how many generations there are, e.g. the main parent has 5 generations of children.

InvalidOperationException: Cycle detected while auto-including navigations: 'Category.Subcategories'. To fix this issue, either don't configure at least one navigation in the cycle as auto included in `OnModelCreating` or call 'IgnoreAutoInclude' method on the query.

        modelBuilder.Entity<Models.Category>()
            .Navigation(e => e.Subcategories)
            .AutoInclude();

        var query = await this.Context()
            .Categories
            .Where(x => x.CategoryId == null)
            .ToListAsync();

I don't understand the problem
What did I do wrong? I did as in the documentation.

it's maybe thinking that there is a cyclic reference and it might loop forever

Possibly, it's a pity. That would be the best option.

        var query = await this.Context()
            .Categories
            .Include(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            //.Include(x => x.Subcategories)
            .AsNoTrackingWithIdentityResolution()
            .Where(x => x.CategoryId == null)
            .ToListAsync();

Fastest solution, it works and I'll worry about it someday xD

    public async Task<List<DTO.Category>> ThreadTree(Guid thread)
        => await this.Context()
            .Categories
            .Include(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .ThenInclude(x => x.Subcategories)
            .AsNoTrackingWithIdentityResolution()
            .Where(x => x.ThreadId == thread && x.CategoryId == null)
            .Select(x => DTO(x))
            .ToListAsync();

    public static DTO.Category DTO(Models.Category x)
        => new() { 
            Name = x.Name,
            Title = x.Title,
            Categories = x.Subcategories.Select(x => DTO(x)).ToList()
        };

internal class Category
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Title { get; set; } = string.Empty;

    public List<Category>? Categories { get; set; }
}

Can it be simplified somehow?

if you were worried about "many" queries, what you have now is worse 😄

"Bad code" lies in the eye of the beholder

    public async Task<List<DTO.Category>> Tree(string thread)
    {
        var categories = await this.Context()
            .Categories
            .Include(x => x.Details)
            .AsNoTrackingWithIdentityResolution()
            .Where(x => x.Thread.Name == thread)
            .ToListAsync();

        return categories.Where(x => x.CategoryId == null).Select(x => ConvertDTO(x)).ToList();
    }

    public static DTO.Category ConvertDTO(Models.Category x)
    {
        var details = x.Details.FirstOrDefault() ?? new();

        return new()
        {
            Name = x.Name,
            Culture = details.Culture,
            Title = details.Title,
            Description = details.Description,
            Categories = x.Subcategories.Select(x => ConvertDTO(x)).ToList()
        };
    }

As if someone was looking for a solution.