如何更改查询或树节点模型以包含 Grand-Children 和 Great-Grand-Children [英] How can I change my query or my Tree Node Model to include Grand-Children and Great-Grand-Children

问题描述

我有一个实体模型，它基本上是一个树节点:

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

在我的 DbContext 中，我有:

In my DbContext I have:

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

我有一个使用 Category 的查询:

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

如果使用默认的All"调用方法没有问题，但是如果使用实际的类别名称，我只会得到一个 Parent 和直接的 Children.我该怎么做才能更改我的查询或模型以包含 Grand-Children 和 Great-Grand-Children etc?

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?

更新

我被要求添加我的 EcommerceItemDto 模型:

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.

推荐答案

在@SvyatoslavDanyliv 发表评论后，我发现了这个网站 文章.以它为例，我将其添加到我的 DbContext 中:

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

我将查询更改为:

    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.

这篇关于如何更改查询或树节点模型以包含 Grand-Children 和 Great-Grand-Children的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！