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

      • 查询
        • 分页查询
          • 单表查询
            • 多表查询
              • 1、导航属性联表
                • 2、复杂联表
                  • 3、WithSql
                    • 4、SQL联表
                      • 5、子表Exists
                        • 6、子表In
                          • 7、子表Join
                            • 8、子表First/Count/Sum/Max/Min/Avg
                              • 9、集合属性
                                • 10、WhereCascade
                                • 分组聚合
                                  • 返回数据 ✨
                                    • 延时加载
                                      • 贪婪加载 ✨
                                        • LinqToSql
                                          • WithSql
                                            • 树型查询 ✨
                                            • DB First
                                              • 表达式函数
                                                • 事务
                                                  • 过滤器
                                                    • ADO
                                                      • AOP✨
                                                        • 读写分离
                                                          • 分表分库
                                                            • 多租户
                                                              • 性能
                                                                • 你不知道的功能 ✨

                                                                多表查询

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

                                                                此页内容
                                                                • 1、导航属性联表
                                                                • 2、复杂联表
                                                                • 3、WithSql
                                                                • 4、SQL联表
                                                                • 5、子表Exists
                                                                • 6、子表In
                                                                • 7、子表Join
                                                                • 8、子表First/Count/Sum/Max/Min/Avg
                                                                • 9、集合属性
                                                                • 10、WhereCascade

                                                                # 多表查询

                                                                static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
                                                                    .UseConnectionString(FreeSql.DataType.MySql, "Data Source=127.0.0.1;Port=3306;User ID=root;Password=root;Initial Catalog=cccddd;Charset=utf8;SslMode=none;Max pool size=10")
                                                                    .Build(); //请务必定义成 Singleton 单例模式
                                                                
                                                                class Topic {
                                                                    [Column(IsIdentity = true)]
                                                                    public int Id { get; set; }
                                                                    public string Title { get; set; }
                                                                    public int Clicks { get; set; }
                                                                    public DateTime CreateTime { get; set; }
                                                                
                                                                    public int CategoryId { get; set; }
                                                                    public Category Category { get; set; }
                                                                }
                                                                class Category {
                                                                    [Column(IsIdentity = true)]
                                                                    public int Id { get; set; }
                                                                    public string Name { get; set; }
                                                                
                                                                    public int ParentId { get; set; }
                                                                    public CategoryType Parent { get; set; }
                                                                    public List<Topic> Topics { get; set; }
                                                                }
                                                                class CategoryType {
                                                                    public int Id { get; set; }
                                                                    public string Name { get; set; }
                                                                }
                                                                
                                                                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

                                                                # 1、导航属性联表

                                                                fsql.Select<Topic>()
                                                                  .LeftJoin(a => a.Category.Id == a.CategoryId)
                                                                  .LeftJoin(a => a.Category.Parent.Id == a.Category.ParentId)
                                                                  .Where(a => a.Category.Parent.Id > 0)
                                                                  .ToList();
                                                                //SELECT a.`Id`, a.`Title`, a.`Clicks`, a.`CreateTime`, a.`CategoryId`, a__Category.`Id` as6, a__Category.`Name`, a__Category.`ParentId`
                                                                //FROM `Topic` a
                                                                //LEFT JOIN `Category` a__Category ON a__Category.`Id` = a.`CategoryId`
                                                                //LEFT JOIN `CategoryType` a__Category__Parent ON a__Category__Parent.`Id` = a__Category.`ParentId`
                                                                
                                                                1
                                                                2
                                                                3
                                                                4
                                                                5
                                                                6
                                                                7
                                                                8
                                                                9

                                                                提示:正确配置 【导航关系】后,不需要手工调用 LeftJoin

                                                                # 2、复杂联表

                                                                fsql.Select<Topic, Category, CategoryType>()
                                                                  .LeftJoin((a,b,c) => a.CategoryId == b.Id)
                                                                  .LeftJoin((a,b,c) => b.ParentId == c.Id)
                                                                  .Where((a,b,c) => c.Id > 0)
                                                                  .ToList((a,b,c) => new { a,b,c });
                                                                
                                                                //或者
                                                                fsql.Select<Topic>().From<Category, CategoryType>((s, b, c) => s
                                                                  .LeftJoin(a => a.CategoryId == b.Id)
                                                                  .LeftJoin(a => b.ParentId == c.Id))
                                                                  .Where((a,b,c) => c.Id > 0)
                                                                  .ToList((a,b,c) => new { a,b,c });
                                                                  
                                                                //减少定义 a,b,c 写法
                                                                fsql.Select<Topic, Category, CategoryType>()
                                                                  .LeftJoin(w => w.t1.CategoryId == w.t2.Id)
                                                                  .LeftJoin(w => w.t2.ParentId == w.t3.Id)
                                                                  .Where(w => w.t3.Id > 0)
                                                                  .ToList(w => new { w.t1,w.t2,w.t3 });
                                                                  
                                                                //SELECT ...
                                                                //FROM `Topic` a
                                                                //LEFT JOIN `Category` b ON a.`CategoryId` = b.`Id`
                                                                //LEFT JOIN `CategoryType` c ON b.`ParentId` = c.`Id`
                                                                //WHERE c. `Id` > 0
                                                                
                                                                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

                                                                经验:一对多,分表只取最后一条记录open in new window

                                                                # 3、WithSql

                                                                fsql.Select<Topic, Category, CategoryType>()
                                                                  .WithSql(
                                                                      "select * from Topic where id=@id1",
                                                                      "select * from Category where id=@id2",
                                                                      null, //不设置 CategoryType 对应的 SQL
                                                                      new { id1 = 10, id2 = 11, id3 = 13 }
                                                                  )
                                                                  .LeftJoin((a,b,c) => a.CategoryId == b.Id)
                                                                  .LeftJoin((a,b,c) => b.ParentId == c.Id)
                                                                  .ToList();
                                                                //SELECT ...
                                                                //FROM ( select * from Topic where id=@id1 ) a
                                                                //LEFT JOIN ( select * from Category where id=@id2 ) b ON a.`CategoryId` = b.`Id`
                                                                //LEFT JOIN `CategoryType` c ON b.`ParentId` = c.`Id`
                                                                
                                                                1
                                                                2
                                                                3
                                                                4
                                                                5
                                                                6
                                                                7
                                                                8
                                                                9
                                                                10
                                                                11
                                                                12
                                                                13
                                                                14

                                                                提示:ISelect.ToSql 可与 WithSql 配合使用

                                                                # 4、SQL联表

                                                                fsql.Select<Topic>()
                                                                  .LeftJoin("Category b on b.Id = a.CategoryId and b.Name = @bname", new { bname = "xxx" })
                                                                  .ToList();
                                                                //SELECT a.`Id`, a.`Title`, a.`Clicks`, a.`CreateTime`, a.`CategoryId`
                                                                //FROM `Topic` a
                                                                //LEFT JOIN Category b on b.Id = a.CategoryId and b.Name = @bname
                                                                
                                                                1
                                                                2
                                                                3
                                                                4
                                                                5
                                                                6

                                                                延伸问题:SQL联表 b 表的字段如何在 ToList 中指定?

                                                                .ToList(a => new {
                                                                  bid = Convert.ToInt32("b.Id"),
                                                                  bName = "b.Name"
                                                                })
                                                                
                                                                1
                                                                2
                                                                3
                                                                4

                                                                # 5、子表Exists

                                                                fsql.Select<Topic>()
                                                                  .Where(a => fsql.Select<Topic>().As("b").Where(b => b.Id == a.Id).Any())
                                                                  .ToList();
                                                                //SELECT a.`Id`, a.`Title`, a.`Clicks`, a.`CreateTime`, a.`CategoryId`
                                                                //FROM `Topic` a
                                                                //WHERE (exists(SELECT 1
                                                                //    FROM `Topic` b
                                                                //    WHERE (b.`Id` = a.`Id`)
                                                                //    limit 0,1))
                                                                
                                                                1
                                                                2
                                                                3
                                                                4
                                                                5
                                                                6
                                                                7
                                                                8
                                                                9

                                                                提示:由于子查询的实体类与上层相同,使用 As("b") 指明别名,以便区分

                                                                # 6、子表In

                                                                fsql.Select<Topic>()
                                                                  .Where(a => fsql.Select<Topic>().As("b").ToList(b => b.Id).Contains(a.Id))
                                                                  .ToList();
                                                                //SELECT a.`Id`, a.`Title`, a.`Clicks`, a.`CreateTime`, a.`CategoryId`
                                                                //FROM `Topic` a
                                                                //WHERE (((a.`Id`) in (SELECT b.`Id`
                                                                //    FROM `Topic` b)))
                                                                
                                                                1
                                                                2
                                                                3
                                                                4
                                                                5
                                                                6
                                                                7

                                                                # 7、子表Join

                                                                v1.8.0+ string.Join + ToList 实现将子查询的多行结果,拼接为一个字符串,如:"1,2,3,4"

                                                                fsql.Select<Topic>().ToList(a => new {
                                                                  id = a.Id,
                                                                  concat = string.Join(",", fsql.Select<StringJoin01>().ToList(b => b.Id))
                                                                });
                                                                //SELECT a.`Id`, (SELECT group_concat(b.`Id` separator ',') 
                                                                //    FROM `StringJoin01` b) 
                                                                //FROM `Topic` a
                                                                
                                                                1
                                                                2
                                                                3
                                                                4
                                                                5
                                                                6
                                                                7

                                                                提示:子查询 string.Join + ToList 适配了 sqlserver/pgsql/oracle/mysql/sqlite/firebird/达梦/金仓/南大/翰高 #405open in new window

                                                                # 8、子表First/Count/Sum/Max/Min/Avg

                                                                fsql.Select<Category>().ToList(a => new  {
                                                                  all = a,
                                                                  first = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).First(b => b.Id),
                                                                  count = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Count(),
                                                                  sum = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Sum(b => b.Clicks),
                                                                  max = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Max(b => b.Clicks),
                                                                  min = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Min(b => b.Clicks),
                                                                  avg = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Avg(b => b.Clicks)
                                                                });
                                                                
                                                                1
                                                                2
                                                                3
                                                                4
                                                                5
                                                                6
                                                                7
                                                                8
                                                                9

                                                                # 9、集合属性

                                                                fsql.Select<Category>()
                                                                  .Where(a => a.Topics.Any(b => b.Title.Contains("xx"))) //v3.2.600 以下使用 a.Topics.AsSelect()
                                                                  .ToList();
                                                                
                                                                1
                                                                2
                                                                3

                                                                效果等同于:

                                                                fsql.Select<Category>()
                                                                  .Where(a => fsql.Select<Topic>().Any(b => b.Title.Contains("xx")))
                                                                  .ToList();
                                                                
                                                                1
                                                                2
                                                                3

                                                                将集合属性快速转换为 ISelect 进行子查询操作。

                                                                # 10、WhereCascade

                                                                多表查询时,像isdeleted每个表都给条件,挺麻烦的。WhereCascade使用后生成sql时,所有表都附上这个条件。

                                                                如:

                                                                fsql.Select<t1>()
                                                                .LeftJoin<t2>(...)
                                                                .WhereCascade(x => x.IsDeleted == false)
                                                                .ToList();
                                                                
                                                                1
                                                                2
                                                                3
                                                                4

                                                                得到的 SQL:

                                                                SELECT ...
                                                                FROM t1
                                                                LEFT JOIN t2 on ... AND (t2.IsDeleted = 0) 
                                                                WHERE t1.IsDeleted = 0
                                                                
                                                                1
                                                                2
                                                                3
                                                                4

                                                                实体可附加表达式时才生效,支持子表查询。单次查询使用的表数目越多收益越大。

                                                                可应用范围:

                                                                • 子查询,一对多、多对多、自定义的子查询;
                                                                • Join 查询,导航属性、自定义的Join查询;
                                                                • Include/IncludeMany 的子集合查询;

                                                                暂时不支持【延时属性】的广播;

                                                                此功能和【过滤器】不同,用于单次多表查询条件的传播;

                                                                edit icon在 GitHub 上编辑此页open in new window
                                                                上次编辑于: 2022/5/1 下午3:36:16
                                                                贡献者: IGeekFan,luoyunchong,luoyunchong
                                                                上一页
                                                                单表查询
                                                                下一页
                                                                分组聚合
                                                                Copyright © 2018-present nicye
                                                                Copyright © 2022 nicye

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

                                                                详情