How can I change my query or my Tree Node Model to include Grand-Children and Great-Grand-Children

Question

I have an Entity Model that is basically a Tree Node:

[Table("bma_ec_categories")]
public class Category : INotifyPropertyChanged
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] 
    [Column("category_id")]
    public int CategoryId { get; set; }

    [Column("parent_category_id")]
    public int? ParentId { get; set; }

    [Required]
    [Column("category_name")]
    [StringLength(50)]
    public string Name { get; set; }

    public Category Parent { get; set; }

    public ICollection<Category> Children { get; set; }
    
    public event PropertyChangedEventHandler PropertyChanged;

    [NotifyPropertyChangedInvocator]
    protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
    {
        PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
    }
}

In my DbContext I have:

modelBuilder.Entity<Category>()
            .HasOne(p => p.Parent)
            .WithMany(c => c.Children)
            .HasForeignKey(k => k.ParentId);

I have one query that uses Category:

    public async Task<IEnumerable<EcommerceItemDto>> GetAllItemsAsync(User user, string category = "All", int page = 0, int pageSize = 9999)
    {
        IQueryable<Category> categories;

        if (category == "All")
        {
            categories = _context.Categories
                .Include(c => c.Children)
                .Include(p => p.Parent)
                .AsNoTrackingWithIdentityResolution();

        }
        else
        {
            categories = _context.Categories
                .Where(n => n.Name == category)
                .Include(c => c.Children)
                .Include(p => p.Parent)
                .AsNoTrackingWithIdentityResolution();
        }

        var p1 = new SqlParameter("@Custnmbr", SqlDbType.Char, 15) {Value = user.Custnmbr};
        var dto = await _context.EcommerceItems
            .FromSqlRaw($"SELECT * FROM [cp].[GetEcommerceItemsView] WHERE [CustomerNumber] = @Custnmbr",p1)
            .Include(x => x.Category)
            .Include(i => i.Images.OrderByDescending(d => d.Default))
            .OrderBy(i => i.ItemNumber)
            .Where(c => categories.Contains(c.Category) || categories.Any(x => x.Children.Contains(c.Category)))
            .Skip(page * pageSize)
            .Take(pageSize)
            .AsNoTracking()
            .ProjectTo<EcommerceItemDto>(_mapper.ConfigurationProvider)
            .ToListAsync();

        return dto;
    }

No problems if the method is called with the default of "All", however if an actual Category Name is used I only will be getting the one Parent and the immediate Children. What can I do to change my query or my Model to include Grand-Children and Great-Grand-Children etc?

UPDATE

I was asked to add my EcommerceItemDto Model:

public class EcommerceItemDto
{
    [Key]
    public string ItemNumber { get; set; }

    public string ItemDescription { get; set; }

    [Column(TypeName = "text")]
    public string ExtendedDesc { get; set; }

    public bool? Featured { get; set; }

    public string CategoryName { get; set; }

    [Column(TypeName = "numeric(19, 5)")]
    public decimal Price { get; set; }
    
    [Column(TypeName = "numeric(19, 5)")]
    public decimal QtyOnHand { get; set; }        

    public ICollection<EcommerceItemImagesDto> Images { get; set; }
}

I have twenty or so metadata properties that I didn't include to save space.

Answer 1

After a comment from @SvyatoslavDanyliv, I found this web article. Using it as an example, I added this to my DbContext:

public Task<List<Category>> AllChildren(string category) =>
        Categories.FromSqlRaw(
                @"WITH organization (category_id, category_name, title, parent_category_id, below) AS (
                            SELECT category_id, category_name, title, parent_category_id, 0
                            FROM dbo.bma_ec_categories    
                            WHERE bma_ec_categories.category_name = {0}         
                            UNION ALL
                    SELECT
                        e.category_id
                       ,e.category_name
                       ,e.title
                       ,e.parent_category_id
                       ,o.below + 1
                    FROM dbo.bma_ec_categories e
                    INNER JOIN organization o
                        ON o.category_id = e.parent_category_id)
                    SELECT * FROM organization", category)
            .AsNoTrackingWithIdentityResolution()
            .ToListAsync();

I changed my query to:

    public async Task<IEnumerable<EcommerceItemDto>> GetAllItemsAsync(User user, string category = "All", int page = 0, int pageSize = 9999)
    {
        List<Category> categories;

        if (category == "All")
        {
            categories = await _context.Categories
                .AsNoTrackingWithIdentityResolution()
                .ToListAsync();
        }
        else
        {
            categories = await _context.AllChildren(category);
        }

        var p1 = new SqlParameter("@Custnmbr", SqlDbType.Char, 15) {Value = user.Custnmbr};
        var dto = await _context.EcommerceItems
            .FromSqlRaw($"SELECT * FROM [cp].[GetEcommerceItemsView] WHERE [CustomerNumber] = @Custnmbr",p1)
            .Include(x => x.Category)
            .Include(i => i.Images.OrderByDescending(d => d.Default))
            .OrderBy(i => i.ItemNumber)
            .Where(c => categories.Contains(c.Category))
            .Skip(page * pageSize)
            .Take(pageSize)
            .AsNoTracking()
            .ProjectTo<EcommerceItemDto>(_mapper.ConfigurationProvider)
            .ToListAsync();

        return dto;
    }

Everything is working now and I am happy.