SqlSugar在子查询上支持是非常强大,比如 子查询进行联表、IN、NOT IN和SELECT一列等
1、基础教程
1.1 API目录
*****只查一列*****
//First: SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id)
//max例1: SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Max(s => s.Id)
//max例2: SqlFunc.Subqueryable<School>()..Where(s=>s.Id==st.Id).Select(s=>SqlFunc.AggregateMax(s.Id))
//min:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Min(s => s.Id)
//avg:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Avg(s => s.Id)
//count:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Count()
//sum:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Sum(s => s.num)
//去重后汇总数量
//SqlFunc.Subqueryable<School>().Where(s => z.Id == st.Id).Select(s=>SqlFunc.AggregateDistinctCount(s.Name))
//逗号分割列
//SqlFunc.Subqueryable<Order>().Where(z=>z.Id==it.Id).SelectStringJoin(z => z.Name, ",")
//完整用例:看标题8
*****返回List或者实体*****
新功能:5.1.3.36
//SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList()
//SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList(it=>new Class(){ Name=it.Name})
//更多看标题4
*****返回单个实体*****
//SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).First()
//更多看标题9
*****IN、Not in、Exists、Not Exists*****
//看标题2
1.2 where中使用
var getAll = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left,st.Id==sc.Id) )
.Where(st => st.Id == SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id))
.ToList();
/*生成的MYSQL语句,如果是SqlServer就是TOP 1
SELECT `st`.`ID`,`st`.`SchoolId`,`st`.`Name`,`st`.`CreateTime`
FROM `STudent` st Left JOIN `School` sc ON ( `st`.`ID` = `sc`.`Id` )
WHERE ( `st`.`ID` =(SELECT `Id` FROM `School` WHERE ( `Id` = `st`.`ID` ) limit 0,1))
*/
1.3 在select中使用
var getAll = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left,st.Id==sc.Id ))
.Select(st => new{
name = st.Name,
id = SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id)
}).ToList();
2、IN和NOT IN的操作
2.1 Exists
Exists 和 in 区别在于 Exists 扩展性更强,支持多个字段 (简单的说In能实现的Exists都能实现)
//等同于IN
var getAll7 = db.Queryable<Student>()
.Where(it => SqlFunc.Subqueryable<School>().Where(s =>s.Id==it.Id).Any()).ToList();
/*生成的SQL(等于同于it.id in(select id from school)只是写法不一样
SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` it
WHERE (EXISTS ( SELECT * FROM `School` WHERE ( `Id` = `it`.`ID` ) ))
//等同于NOT IN
var getAll8 = db.Queryable<Student>().Where(it =>
SqlFunc.Subqueryable<School>().Where(s => s.Id == it.Id).NotAny()).ToList();
/*生成的SQL
SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` it
WHERE (NOT EXISTS ( SELECT * FROM `School` WHERE ( `Id` = `it`.`ID` ) ))
//搜索关键词:exits
2.2 In和 Not In (5.0.7.5)
在有GroupBy的情况下==自动转成 in
db.Queryable<Order>()
.Where(it=>it.Id==SqlFunc.Subqueryable<Custom>().GroupBy(z=>z.OrderId).Select(z=>z.OrderId)).ToList()
//Sql
//SELECT [Id],[Name] FROM [Order]
// WHERE [Id] in ((SELECT [OrderId] FROM [Custom] GROUP BY [OrderId]))
2.3 分页In
db.Queryable<Order>().In(it=>it.Id,db.Queryable<Order>().Skip(1).Take(100).Select(it=>it.Id)).ToList();
3、子查询中使用Sql
最新版本才支持
string sql=" (select top 1 id from [Order] ) ";
var list = db.Queryable<Order>()
.Select(it => new
name=it.Name,
customName= SqlFunc.MappingColumn<string>(sql)
//老版本MappingColumn(default(string),sql)
}).ToList();
//如果存在SQL拼接将SQL提取到外面在传进来,有些VS拼接会有问题
Sql代码如下:
SELECT
[Name] AS [name] ,
(select top 1 id from [Order] ) AS [customName]
FROM [Order]
4、ToList()集合对象
方式1:简单方式(
5.1.3.38
)
直接在Select中进行ToList ,
性能: 大于循环数倍 ,大部分情况性能小于ThenMapper方式和导航 ,写法简单性能也说的过去
//请升级到 5.1.3.36+
db.Queryable<Order>()
.Select(it => new
CustomId = it.CustomId,
OrderId = it.Id,
OrderName = it.Name,
CustomList = SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList()
.ToList();
//也可以DTO
SqlFunc.Subqueryable<Custom>()
.Where(c=> c.Id == it.CustomId).
ToList(c=> new Class(){ Name=c.Name}) //转成DTO
//注意:where ToList里面 c 的别名要一致
//只查List<string>这种集合
SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList(c=>c.Name)//最新版本
//自动DTO 5.1.4.70
SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList<DTO>()
SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList(it=>new DTO(){},true);//和Queryable类似
方式2:性能、功能、跨库
该功能在同类框架中性能比较好不会生成垃圾SQL ,
在现有结果进行一对多操作
var treeRoot=db.Queryable<Tree>().ToList();//可以联表查询,只要返回List中有关联字段就可以
//填充子对象
//原理: 数据库一次读取 In (list[0].id,list[1].id,list[2].id...) 内存分配到对应Child
//所以无论集合多少条记录 ThenMapper 只会操作一次库
db.ThenMapper(treeRoot, item =>
//用例1:简单填充
//参数解释:子表的ParentId和主表的Id 进行关联查询(有Select写到SetContext前面)
//可以额外加Where写SetContext前面,但是不能带有item对象,item要写SetContext里面
item.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => item.Id, item).ToList();
//用例2:可以多属性
//item.Child=xxxx
//item.Parent=xxxx
//用例3:类型转换类转成DTO (注意:Select中的关联字段要赋值)
//item.Child = db.Queryable<Tree>()
// .Select(it=>new ViewTree(){ParentId=x.ParentId,Id=i.Id,Name=it.Name })
// .SetContext(x => x.ParentId, () => item.Id, item).ToList();
//用例4:联表填充
//item.Child=db.Queryable<Tree>()
//.LeftJoin<Tree>((i,y)=>i.ParentId==y.Id)
//.Select((i,y)=>new viewmodel(){ Parentid=i.Parentid..........})
//.SetContext(x => x.ParentId, () => item.Id, item).ToList();
var treeRoot=await db.Queryable<Tree>().ToListAsync();
await db.ThenMapperAsync(treeRoot, async item =>
//给集合赋值 参数解释 子表的ParentId和主表的Id 进行关联查询
item.Child =await db.Queryable<Tree>().SetContextAsync(x=>x.ParentId,()=>item.Id, item);
//无限层级
var treeRoot=db.Queryable<Tree>().Where(it => it.Id == 1).ToList();
//第一层
db.ThenMapper(treeRoot, item =>
item.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => item.Id, item).ToList();
//第二层
db.ThenMapper(treeRoot.SelectMany(it=>it.Child), it =>
it.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => it.Id, it).ToList();
var mydb=db.GetConnection(1);
mydb.ThenMapper(root,item=>{...});
方式3:导航方式
这种已经不属于子查询了,属于另一个查询体系,详细功能:
https://www.donet5.com/Home/Doc?typeId=1188
5、子查询在Join
5.0.5.4+
var list = db.Queryable<Order>().Where(it =>
SqlFunc.Subqueryable<A>()
.LeftJoin<B>((i,y)=>i.ItemId==y.ItemId)
.InnerJoin<C>((i,y,z) => i.ItemId == z.ItemId)
.Where((i,y,z)=>i.ItemId==1)
.Any()
).ToList();
//新版本支持了Subquery.join使用 Where<T1,T2>((x,y)=> x.id==y.id)
6、子查询使用别名
如果点不出AS请升级
SqlFunc.Subqueryable<A>().AS("A01") //这样查询的就是 A01表
7、字符串拼接
5.1.1-preview14 逗号拼接
//sqlerver : FOR XML PATH
//mysql sqlite : group_concat
//oracle : listagg
//pgsql :string_agg
var list= db.Queryable<Order>()
.Select(it => new
//names="名字1,名字2,名字3"
names=SqlFunc.Subqueryable<custom>().
Where(z=>z.oid==it.id)
.SelectStringJoin(z => z.Name, ",")
.ToList();
//多字段 不能有null相加 , 字段有null需要加上 z.Name??""
.SelectStringJoin(z => SqlFunc.MergeString(z.Name,"-",z.Id.ToString()), ",")
8、只查一列 (top 1)
//First:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id)
//max例1:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Max(s => s.Id)
//max例2:SqlFunc.Subqueryable<School>().Where(s=>s.Id==st.Id).Select(s=>SqlFunc.AggregateMax(s.Id))
//min:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Min(s => s.Id)
//avg:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Avg(s => s.Id)
//count:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Count()
//sum:SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Sum(s => s.num)
//去重后汇总数量
//SqlFunc.Subqueryable<School>().Where(s => z.Id == st.Id).Select(s=>SqlFunc.AggregateDistinctCount(s.Name))
//逗号分割列
//SqlFunc.Subqueryable<Order>().Where(z=>z.Id==it.Id).SelectStringJoin(z => z.Name, ",")
Select(it=>
id=it.id,
schoolname=SqlFunc.Subqueryable<School>().Where(s=>s.Id==it.ShoolId).Select(s=>s.Name)
//对应的Sql:
//schoolname=(SELECT `Name` FROM `School` `s` WHERE (`s`.`Id`=`it`.`ShoolId`)limit 0,1)
//如果要查整个对象:看标题9
9、子查询 First()整个对象
//需要升级5.1.4.59
var list= db.Queryable<Order>()
.Select(it => new
CustomId = it.CustomId,
OrderId = it.Id,
OrderName = it.Name,
CustomList = SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).First()
.ToList();
//可以转DTO
SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).First(c=>new xxx(){ yy=c.id })
//如果只查一个字段:标题8
10、嵌套查询
https://www.donet5.com/Home/Doc?typeId=2354