C#•2y ago

❔ ✅ 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; }
}

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();

        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?

31 Replies

Tvde1•2y ago

What is your question exactly?

AlerinOP•2y ago

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

Tvde1•2y ago

Does your solution work?

AlerinOP•2y ago

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.

Tvde1•2y ago

if you fetch all data, you can use LINQ to make your tree in memory it will be 1 db query

AlerinOP•2y ago

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

Patrick•2y ago

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

AlerinOP•2y ago

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

Patrick•2y ago

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;
}

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.

AlerinOP•2y ago

Won't this cause a lot of database queries?

Patrick•2y ago

yes, it will

Tvde1•2y ago

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

Patrick•2y ago

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•2y ago

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);

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);

AlerinOP•2y ago

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

AlerinOP•2y ago

With "where "

AlerinOP•2y ago

without where

AlerinOP•2y ago

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

Tvde1•2y ago

yeah you'd need to

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

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

for infinity

AlerinOP•2y ago

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•2y ago

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

AlerinOP•2y ago

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

AlerinOP•2y ago

https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager#model-configuration-for-auto-including-navigations

Eager Loading of Related Data - EF Core

Eager loading of related data with Entity Framework Core

AlerinOP•2y ago

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.

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();

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

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

        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•2y ago

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

AlerinOP•2y ago

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();

        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()
        };

    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; }
}

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?

Patrick•2y ago

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

Tvde1•2y ago

"Bad code" lies in the eye of the beholder

Accord•2y ago

Closed!

AlerinOP•2y ago

    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()
        };
    }

    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.

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ ✅ Tree category EF Core