Skip to content
FreeSql 官方文档FreeSql 官方文档
指南
扩展
服务支持
github icon
  • 指南

      • 查询
        • 分页查询
          • 单表查询
            • 多表查询
              • 分组聚合
                • 返回数据 ✨
                  • 延时加载
                    • 贪婪加载 ✨
                      • LinqToSql
                        • WithSql
                          • 树型查询 ✨
                            • 父子导航属性
                              • 1、ToTreeList
                                • 2、AsTreeCte 递归删除
                                  • 3、AsTreeCte 递归查询
                                • DB First
                                  • 表达式函数
                                    • 事务
                                      • 过滤器
                                        • ADO
                                          • AOP✨
                                            • 读写分离
                                              • 分表分库
                                                • 多租户
                                                  • 性能
                                                    • 你不知道的功能 ✨

                                                    树型查询 ✨

                                                    author iconnicyecalendar icon2021年2月5日timer icon大约 4 分钟word icon约 1121 字

                                                    此页内容
                                                    • 父子导航属性
                                                    • 1、ToTreeList
                                                    • 2、AsTreeCte 递归删除
                                                    • 3、AsTreeCte 递归查询

                                                    # 树型查询 ✨

                                                    无限级分类(父子)是一种比较常用的表设计,每种设计方式突出优势的同时也带来缺陷,如:

                                                    • 方法 1:表设计中只有 parent_id 字段,困扰:查询麻烦(本文可解决);
                                                    • 方法 2:表设计中冗余子级 id 便于查询,困扰:添加/更新/删除的时候需要重新计算;
                                                    • 方法 3:表设计中存储左右值编码,困扰:同上;

                                                    方法 1 设计最简单,本文解决它的递归查询问题,让使用透明化。

                                                    # 父子导航属性

                                                    FreeSql 导航属性之中,有针对父子关系的设置方式,如下:

                                                    public class Area
                                                    {
                                                      [Column(IsPrimary = true)]
                                                      public string Code { get; set; }
                                                    
                                                      public string Name { get; set; }
                                                      public string ParentCode { get; set; }
                                                    
                                                      [Navigate(nameof(ParentCode))]
                                                      public Area Parent { get; set; }
                                                      [Navigate(nameof(ParentCode))]
                                                      public List<Area> Childs { get; set; }
                                                    }
                                                    
                                                    1
                                                    2
                                                    3
                                                    4
                                                    5
                                                    6
                                                    7
                                                    8
                                                    9
                                                    10
                                                    11
                                                    12
                                                    13

                                                    定义 Parent 属性,在表达式中可以这样:

                                                    fsql.Select<Area>().Where(a => a.Parent.Parent.Parent.Name == "中国").First();
                                                    
                                                    1

                                                    定义 Childs 属性,在表达式中可以这样(子查询):

                                                    fsql.Select<Area>().Where(a => a.Childs.Any(c => c.Name == "北京")).First();
                                                    
                                                    1

                                                    定义 Childs 属性,还可以使用【级联保存】、【贪婪加载】等等操作。

                                                    fsql.Delete<Area>().Where("1=1").ExecuteAffrows();
                                                    var repo = fsql.GetRepository<Area>();
                                                    repo.DbContextOptions.EnableCascadeSave = true;
                                                    repo.DbContextOptions.NoneParameter = true;
                                                    repo.Insert(new Area
                                                    {
                                                      Code = "100000",
                                                      Name = "中国",
                                                      Childs = new List<Area>(new[] {
                                                        new Area
                                                        {
                                                          Code = "110000",
                                                          Name = "北京",
                                                          Childs = new List<Area>(new[] {
                                                            new Area{ Code="110100", Name = "北京市" },
                                                            new Area{ Code="110101", Name = "东城区" },
                                                          })
                                                        }
                                                      })
                                                    });
                                                    
                                                    1
                                                    2
                                                    3
                                                    4
                                                    5
                                                    6
                                                    7
                                                    8
                                                    9
                                                    10
                                                    11
                                                    12
                                                    13
                                                    14
                                                    15
                                                    16
                                                    17
                                                    18
                                                    19
                                                    20

                                                    # 1、ToTreeList

                                                    配置好父子属性之后,就可以这样用了:

                                                    var t1 = fsql.Select<Area>().ToTreeList();
                                                    Assert.Single(t1);
                                                    Assert.Equal("100000", t1[0].Code);
                                                    Assert.Single(t1[0].Childs);
                                                    Assert.Equal("110000", t1[0].Childs[0].Code);
                                                    Assert.Equal(2, t1[0].Childs[0].Childs.Count);
                                                    Assert.Equal("110100", t1[0].Childs[0].Childs[0].Code);
                                                    Assert.Equal("110101", t1[0].Childs[0].Childs[1].Code);
                                                    
                                                    1
                                                    2
                                                    3
                                                    4
                                                    5
                                                    6
                                                    7
                                                    8

                                                    查询数据本来是平面的,ToTreeList 方法将返回的平面数据在内存中加工为树型 List 返回。

                                                    # 2、AsTreeCte 递归删除

                                                    很常见的无限级分类表功能,删除树节点时,把子节点也处理一下。

                                                    fsql.Select<Area>()
                                                      .Where(a => a.Name == "中国")
                                                      .AsTreeCte()
                                                      .ToDelete()
                                                      .ExecuteAffrows(); //删除 中国 下的所有记录
                                                    
                                                    1
                                                    2
                                                    3
                                                    4
                                                    5

                                                    如果软删除:

                                                    fsql.Select<Area>()
                                                      .Where(a => a.Name == "中国")
                                                      .AsTreeCte()
                                                      .ToUpdate()
                                                      .Set(a => a.IsDeleted, true)
                                                      .ExecuteAffrows(); //软删除 中国 下的所有记录
                                                    
                                                    1
                                                    2
                                                    3
                                                    4
                                                    5
                                                    6

                                                    # 3、AsTreeCte 递归查询

                                                    若不做数据冗余的无限级分类表设计,递归查询少不了,AsTreeCte 正是解决递归查询的封装,方法参数说明:

                                                    参数描述
                                                    (可选) pathSelector路径内容选择,可以设置查询返回:中国 -> 北京 -> 东城区
                                                    (可选) upfalse(默认):由父级向子级的递归查询,true:由子级向父级的递归查询
                                                    (可选) pathSeparator设置 pathSelector 的连接符,默认:->
                                                    (可选) level设置递归层级

                                                    通过测试的数据库:MySql8.0、SqlServer、PostgreSQL、Oracle、Sqlite、Firebird、达梦、人大金仓、南大通用、翰高

                                                    姿势一:AsTreeCte() + ToTreeList

                                                    var t2 = fsql.Select<Area>()
                                                      .Where(a => a.Name == "中国")
                                                      .AsTreeCte() //查询 中国 下的所有记录
                                                      .OrderBy(a => a.Code)
                                                      .ToTreeList(); //非必须,也可以使用 ToList(见姿势二)
                                                    Assert.Single(t2);
                                                    Assert.Equal("100000", t2[0].Code);
                                                    Assert.Single(t2[0].Childs);
                                                    Assert.Equal("110000", t2[0].Childs[0].Code);
                                                    Assert.Equal(2, t2[0].Childs[0].Childs.Count);
                                                    Assert.Equal("110100", t2[0].Childs[0].Childs[0].Code);
                                                    Assert.Equal("110101", t2[0].Childs[0].Childs[1].Code);
                                                    // WITH "as_tree_cte"
                                                    // as
                                                    // (
                                                    // SELECT 0 as cte_level, a."Code", a."Name", a."ParentCode"
                                                    // FROM "Area" a
                                                    // WHERE (a."Name" = '中国')
                                                    
                                                    // union all
                                                    
                                                    // SELECT wct1.cte_level + 1 as cte_level, wct2."Code", wct2."Name", wct2."ParentCode"
                                                    // FROM "as_tree_cte" wct1
                                                    // INNER JOIN "Area" wct2 ON wct2."ParentCode" = wct1."Code"
                                                    // )
                                                    // SELECT a."Code", a."Name", a."ParentCode"
                                                    // FROM "as_tree_cte" a
                                                    // ORDER BY a."Code"
                                                    
                                                    1
                                                    2
                                                    3
                                                    4
                                                    5
                                                    6
                                                    7
                                                    8
                                                    9
                                                    10
                                                    11
                                                    12
                                                    13
                                                    14
                                                    15
                                                    16
                                                    17
                                                    18
                                                    19
                                                    20
                                                    21
                                                    22
                                                    23
                                                    24
                                                    25
                                                    26
                                                    27
                                                    28

                                                    姿势二:AsTreeCte() + ToList

                                                    var t3 = fsql.Select<Area>()
                                                      .Where(a => a.Name == "中国")
                                                      .AsTreeCte()
                                                      .OrderBy(a => a.Code)
                                                      .ToList();
                                                    Assert.Equal(4, t3.Count);
                                                    Assert.Equal("100000", t3[0].Code);
                                                    Assert.Equal("110000", t3[1].Code);
                                                    Assert.Equal("110100", t3[2].Code);
                                                    Assert.Equal("110101", t3[3].Code);
                                                    //执行的 SQL 与姿势一相同
                                                    
                                                    1
                                                    2
                                                    3
                                                    4
                                                    5
                                                    6
                                                    7
                                                    8
                                                    9
                                                    10
                                                    11

                                                    姿势三:AsTreeCte(pathSelector) + ToList

                                                    设置 pathSelector 参数后,如何返回隐藏字段?

                                                    var t4 = fsql.Select<Area>()
                                                      .Where(a => a.Name == "中国")
                                                      .AsTreeCte(a => a.Name + "[" + a.Code + "]")
                                                      .OrderBy(a => a.Code)
                                                      .ToList(a => new {
                                                        item = a,
                                                        level = Convert.ToInt32("a.cte_level"),
                                                        path = "a.cte_path"
                                                      });
                                                    Assert.Equal(4, t4.Count);
                                                    Assert.Equal("100000", t4[0].item.Code);
                                                    Assert.Equal("110000", t4[1].item.Code);
                                                    Assert.Equal("110100", t4[2].item.Code);
                                                    Assert.Equal("110101", t4[3].item.Code);
                                                    Assert.Equal("中国[100000]", t4[0].path);
                                                    Assert.Equal("中国[100000] -> 北京[110000]", t4[1].path);
                                                    Assert.Equal("中国[100000] -> 北京[110000] -> 北京市[110100]", t4[2].path);
                                                    Assert.Equal("中国[100000] -> 北京[110000] -> 东城区[110101]", t4[3].path);
                                                    // WITH "as_tree_cte"
                                                    // as
                                                    // (
                                                    // SELECT 0 as cte_level, a."Name" || '[' || a."Code" || ']' as cte_path, a."Code", a."Name", a."ParentCode"
                                                    // FROM "Area" a
                                                    // WHERE (a."Name" = '中国')
                                                    
                                                    // union all
                                                    
                                                    // SELECT wct1.cte_level + 1 as cte_level, wct1.cte_path || ' -> ' || wct2."Name" || '[' || wct2."Code" || ']' as cte_path, wct2."Code", wct2."Name", wct2."ParentCode"
                                                    // FROM "as_tree_cte" wct1
                                                    // INNER JOIN "Area" wct2 ON wct2."ParentCode" = wct1."Code"
                                                    // )
                                                    // SELECT a."Code" as1, a."Name" as2, a."ParentCode" as5, a.cte_level as6, a.cte_path as7
                                                    // FROM "as_tree_cte" a
                                                    // ORDER BY a."Code"
                                                    
                                                    1
                                                    2
                                                    3
                                                    4
                                                    5
                                                    6
                                                    7
                                                    8
                                                    9
                                                    10
                                                    11
                                                    12
                                                    13
                                                    14
                                                    15
                                                    16
                                                    17
                                                    18
                                                    19
                                                    20
                                                    21
                                                    22
                                                    23
                                                    24
                                                    25
                                                    26
                                                    27
                                                    28
                                                    29
                                                    30
                                                    31
                                                    32
                                                    33
                                                    34

                                                    更多姿势...请根据代码注释进行尝试

                                                    edit icon在 GitHub 上编辑此页open in new window
                                                    上次编辑于: 2022/5/16 下午8:50:28
                                                    贡献者: igeekfan,luoyunchong,luoyunchong
                                                    上一页
                                                    WithSql
                                                    Copyright © 2018-present nicye
                                                    Copyright © 2022 nicye

                                                    该应用可以安装在你的 PC 或移动设备上。这将使该 Web 应用程序外观和行为与其他应用程序相同。它将在出现在应用程序列表中,并可以固定到主屏幕,开始菜单或任务栏。此 Web 应用程序还将能够与其他应用程序和你的操作系统安全地进行交互。

                                                    详情