C#
C#

help

Root Question Message

Alerin
Alerin11/28/2022
❔ ✅ Tree category EF Core

I Have model:
public class Category
{
    public int Id { get; set; }

    public string Name { get; set; } = string.Empty;
    public string Title { get; set; } = string.Empty;
    
    public ICollection<Category> Subcategories { get; set; } = new List<Category>();
    public int? CategoryId { get; set; }
}

Query:
        var query = await this.Context()
            .Categories
            .Include(x => x.Subcategories)
            .Where(x => x.Id == 1)
            .ToListAsync();

Data base (screen)
How can I get this effect:
Warzone
- BR
- DMZ
-- Mission
--- Legion Tier 1
--- Legion Tier 2
--- Legion Tier 3
etc...
Did I do it right?
Tvde1
Tvde111/28/2022
What is your question exactly?
Alerin
Alerin11/28/2022
How to make a tree so that categories and their children are displayed
Tvde1
Tvde111/28/2022
Does your solution work?
Alerin
Alerin11/28/2022
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.
Alerin
Alerin11/28/2022
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.
Tvde1
Tvde111/28/2022
if you fetch all data, you can use LINQ to make your tree in memory
Tvde1
Tvde111/28/2022
it will be 1 db query
Alerin
Alerin11/28/2022
How to do it? I would like my children to be placed correctly.
patrickk
patrickk11/28/2022
it'd be a recursive query till you reach the depth you want, is that what you're worried about?
Alerin
Alerin11/28/2022
It's more about how to handle it.
I'm thinking here about some lambda that will be executed every time.
patrickk
patrickk11/28/2022
there are a couple of ways to handle this - it depends on the scalability/demand here.
patrickk
patrickk11/28/2022
at the most simple crude level, you're given an ID of a random entity
patrickk
patrickk11/28/2022
so let's find the ultimate parent
patrickk
patrickk11/28/2022
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
patrickk
patrickk11/28/2022
it depends on the tree you're expecting here, but you can build it up this way pretty easily.
Alerin
Alerin11/28/2022
Won't this cause a lot of database queries?
patrickk
patrickk11/28/2022
yes, it will
Tvde1
Tvde111/28/2022
do you always fetch all data or do you need only a particular parent all the way down?
patrickk
patrickk11/28/2022
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 🙂
Tvde1
Tvde111/28/2022
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);
Alerin
Alerin11/28/2022
Just testing a few solutions, these are tests. There will be multiple categories by default. I want to make wikipedia
Alerin
Alerin11/28/2022
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
Alerin
Alerin11/28/2022
With "where "
Alerin
Alerin11/28/2022
without where
Alerin
Alerin11/28/2022
What it depends on? In theory, this would be fine, but unfortunately it fetches all values and then shows the children.
Tvde1
Tvde111/28/2022
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
Alerin
Alerin11/28/2022
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.
Tvde1
Tvde111/28/2022
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
Alerin
Alerin11/28/2022
for this to work I would need to know how many generations there are, e.g. the main parent has 5 generations of children.
Alerin
Alerin11/28/2022
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.
Tvde1
Tvde111/28/2022
it's maybe thinking that there is a cyclic reference and it might loop forever
Alerin
Alerin11/28/2022
Possibly, it's a pity. That would be the best option.
Alerin
Alerin11/28/2022
        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
Alerin
Alerin11/29/2022
    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?
patrickk
patrickk11/29/2022
if you were worried about "many" queries, what you have now is worse 😄
Tvde1
Tvde111/29/2022
"Bad code" lies in the eye of the beholder
Alerin
Alerin12/2/2022
    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.
ContactFrequently Asked QuestionsJoin The DiscordBugs & Feature RequestsTerms & Privacy