c# - 如何拆分列的第二个实例

问题描述

对于大多数查询，我正在尝试从 EntityFramework Core 转换为 Dapper。我有一个 sql 表（树结构）定义为：

[Table("bma_ec_categories")]
public class Category
{
    [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 ICollection<Category> Children { get; set; }

    [Column("title")] 
    [StringLength(150)] 
    public string Title { get; set; }

    [Column("description")]
    [StringLength(250)]
    public string Description { get; set; }
    
    [Column("keywords")]
    [StringLength(250)]
    public string Keywords { get; set; }

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

    [Column("banner_group_id")] 
    public int? BannerGroupId { get; set; }
    
    [Column("inactive")] 
    public bool? Inactive { get; set; }
    
    [Column("issitecategory")] 
    public bool Issitecategory { get; set; }
    
    [Column("sort_order")] 
    public int? SortOrder { get; set; }
}

它是一个tree. 我也有一个dto定义为：

public class CategoryDto
{
    [Key]
    public int CategoryId { get; set; }

    public int ParentId { get; set; }

    public string Name { get; set; }

    public string Title { get; set; }

    public string Description { get; set; }

    public string Keywords { get; set; }

    public string PageContent { get; set; }

    public int? BannerGroupId { get; set; }

    public bool? Inactive { get; set; }

    public bool Issitecategory { get; set; }

    public int? SortOrder { get; set; }

    public ICollection<CategoryDto> Children { get; set; }
}

我Item Service的这个 Dapper 查询大部分是从 EF Core 复制的：

    public IEnumerable<CategoryDto> GetCategoriesWithDapperUnbuffered()
    {
        var plainResult =  _dapper.GetCategoriesUnbuffered();

        var categoryDtos = plainResult.ToList();

        var lookup = categoryDtos.Where(x => x.ParentId != 0).ToLookup(x => x.ParentId);

        foreach (var c in categoryDtos)
        {
            if (lookup.Contains(c.CategoryId))
            {
                c.Children = lookup[c.CategoryId]
                    .OrderBy(x => x.SortOrder)
                    .ThenBy(x => x.Name)
                    .ToList();
            }
        }

        var dto = categoryDtos
            .Where(x => x.ParentId == 0)
            .OrderBy(x => x.SortOrder)
            .ThenBy(x => x.Name)
            .ToList();

        return dto;
    }

...在哪里GetCategoriesUnbuffered：

    public IEnumerable<CategoryDto> GetCategoriesUnbuffered()
    {
        string sql = "SELECT * FROM [dbo].[bma_ec_categories]";

        using (DbConnection db = _connectionFactory.CreateConnection())
        {
            var categories = db.Query<CategoryDto>(sql, buffered: false).ToList();

            return categories;
        }
    }

我还可以展示一个 Dapper Fluent 映射。这个查询确实有效。但我试图通过在原始查询中使用 Dapper 的多映射功能来进一步优化。这是我到目前为止所拥有的：

    public IEnumerable<CategoryDto> GetCategoriesMultiMapping()
    {
        string sql = "SELECT * FROM [dbo].[bma_ec_categories] AS [A] INNER JOIN [dbo].[bma_ec_categories] AS [B] ON [A].[category_id] = [B].[parent_category_id];";

        using (DbConnection db = _connectionFactory.CreateConnection())
        {
            var categoryDictionary = new Dictionary<int, CategoryDto>();

            var list = db.Query<CategoryDto, CategoryDto, CategoryDto>(
                    sql,
                    (category, children) =>
                    {
                        if (!categoryDictionary.TryGetValue(category.ParentId, out var categoryEntry))
                        {
                            categoryEntry = category;
                            categoryEntry.Children = new List<CategoryDto>();
                            categoryDictionary.Add(categoryEntry.ParentId, categoryEntry);
                        }

                        categoryEntry.Children.Add(children);
                        return categoryEntry;
                    },
                    splitOn: "category_id,category_id", buffered: false)
                .Distinct()
                .ToList();

            return list;
        }
    }

这将返回第一个类别，并将所有其他类别作为其子类别。我在尝试splitOn第二个实例时遇到了麻烦category_id。我尝试改编 Dapper's Example - Query Multi-Mapping (One to Many)中的一对多示例。任何帮助将不胜感激。

标签： c#sql-servertreedapper

解决方案

如果我真的理解你想把这个表当作一棵树来读，一种方法是在我所做的 SQL 服务器上使用递归查询。

我在相同的情况下使用了存储过程，您可以在 SQL 服务器上使用 sp 或函数，运行命令来运行您的 sp。

我为您创建了一个简单的示例；

 DECLARE @CatTree TABLE(category_id int,parent_category_id int,category_name varchar(50) )

 INSERT INTO @CatTree (category_id,parent_category_id,category_name) 
 VALUES (1,null,'shoes'),(2,1,'outdoor shoes'),(3,1,'winter shoes'),(4,3,'water proof shoes'),(5,3,'snow shoes')




 ;WITH TopCat AS (
                SELECT 
                    CAST(category_id AS VARCHAR(MAX))  ID_Path,
                    parent_category_id,
                    category_id,
                    category_name
                
                FROM @CatTree where parent_category_id is null

            UNION ALL

             SELECT  
                r.ID_Path + ' > ' + CAST(t.category_id AS VARCHAR(MAX)),
                t.parent_category_id,
                t.category_id,
                t.category_name
            
            FROM @CatTree t 
            INNER JOIN TopCat r ON  t.parent_category_id = r.category_id
            ) 
            
  select * from TopCat

第二个解决方案；如果您想在后端自引用表上创建树是您的关键。您必须为您的模型添加一个关系，然后指向自己，然后流利的 api 或 migraiton 都是有用的

public class Category: BaseEntity
{
    public Guid Id{ get; set; }

    public string Name { get; set; }

    public Guid? TopCategoryId{ get; set; }

    public virtual ICollection<Category> TopCat{ get; set; }
}

这可能是错误的我没有尝试，我只是写在我的脑海里。那么您必须将您的关系添加到您的数据注释或 fluentapi

builder.HasMany(pt => pt.Category).WithOne(p => p.TopCat)

Entity Framework Core 自引用表

c# - 如何拆分列的第二个实例

问题描述

解决方案

推荐阅读