极客教程

除了联表，还有子查询 Where Exists，和 Select 子表：

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、子表Exists

sql = 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))

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

2、子表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)))

3、子表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

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

4、子表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)
});

5、AsSelect

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

效果等同于：

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

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