多表查询

  1. static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
  2.     .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")
  3.     .Build(); //请务必定义成 Singleton 单例模式
  5. class Topic {
  6.     [Column(IsIdentity = true)]
  7.     public int Id { get; set; }
  8.     public string Title { get; set; }
  9.     public int Clicks { get; set; }
  10.     public DateTime CreateTime { get; set; }
  12.     public int CategoryId { get; set; }
  13.     public Category Category { get; set; }
  14. }
  15. class Category {
  16.     [Column(IsIdentity = true)]
  17.     public int Id { get; set; }
  18.     public string Name { get; set; }
  20.     public int ParentId { get; set; }
  21.     public CategoryType Parent { get; set; }
  22.     public List<Topic> Topics { get; set; }
  23. }
  24. class CategoryType {
  25.     public int Id { get; set; }
  26.     public string Name { get; set; }
  27. }

1、导航属性联表

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

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

2、复杂联表

  1. fsql.Select<Topic, Category, CategoryType>()
  2.   .LeftJoin((a,b,c) => a.CategoryId == b.Id)
  3.   .LeftJoin((a,b,c) => b.ParentId == c.Id)
  4.   .Where((a,b,c) => c.Id > 0)
  5.   .ToList((a,b,c) => new { a,b,c });
  7. //或者
  8. fsql.Select<Topic>().From<Category, CategoryType>((s, b, c) => s
  9.   .LeftJoin(a => a.CategoryId == b.Id)
  10.   .LeftJoin(a => b.ParentId == c.Id))
  11.   .Where((a,b,c) => c.Id > 0)
  12.   .ToList((a,b,c) => new { a,b,c });
  14. //减少定义 a,b,c 写法
  15. fsql.Select<Topic, Category, CategoryType>()
  16.   .LeftJoin(w => w.t1.CategoryId == w.t2.Id)
  17.   .LeftJoin(w => w.t2.ParentId == w.t3.Id)
  18.   .Where(w => w.t3.Id > 0)
  19.   .ToList(w => new { w.t1,w.t2,w.t3 });
  21. //SELECT ...
  22. //FROM `Topic` a
  23. //LEFT JOIN `Category` b ON a.`CategoryId` = b.`Id`
  24. //LEFT JOIN `CategoryType` c ON b.`ParentId` = c.`Id`
  25. //WHERE c. `Id` > 0

3、WithSql

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

4、SQL联表

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

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

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

5、子表Exists

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

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

6、子表In

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

7、子表Join

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

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

提示：子查询 string.Join + ToList 适配了 sqlserver/pgsql/oracle/mysql/sqlite/firebird/达梦/金仓 #405

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

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

9、AsSelect

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

效果等同于：

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

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

10、WhereCascade

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

如：

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

得到的 SQL：

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

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

可应用范围：

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

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

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