FreeSql （十九）多表查询
多表查询，常用的有联表 LeftJoin/InnerJoin/RightJoin ，这三个方法在上篇文章已经介绍过。
除了联表，还有子查询 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、集合属性
fsql.Select<Category>()
.Where(a => a.Topics.Any(b => b.Title.Contains("xx"))) //v3.2.600 以下使用 a.Topics.AsSelect()
.ToList();
效果等同于：
fsql.Select<Category>()
.Where(a => fsql.Select<Topic>().Any(b => b.Title.Contains("xx")))
.ToList();
将集合属性快速转换为 ISelect 进行子查询操作。
系列文章导航
-
（十九）多表查询
