【子查询】.NET开源 ORM 框架 SqlSugar 系列

    .NET开源 ORM 框架 SqlSugar 系列

1. 【开篇】.NET开源 ORM 框架 SqlSugar 系列
2. 【入门必看】.NET开源 ORM 框架 SqlSugar 系列
3. 【实体配置】.NET开源 ORM 框架 SqlSugar 系列
4. 【Db First】.NET开源 ORM 框架 SqlSugar 系列
5. 【Code First】.NET开源 ORM 框架 SqlSugar 系列
6. 【数据事务】.NET开源 ORM 框架 SqlSugar 系列
7. 【连接池】.NET开源 ORM 框架 SqlSugar 系列
8. 【查询目录】.NET开源 ORM 框架 SqlSugar 系列
9. 【查询基础】.NET开源 ORM 框架 SqlSugar 系列
10. 【排序用法】.NET开源 ORM 框架 SqlSugar 系列
11. 【分组去重】.NET开源 ORM 框架 SqlSugar 系列
12. 【联表查询】.NET开源 ORM 框架 SqlSugar 系列
13. 【导航查询】.NET开源 ORM 框架 SqlSugar 系列
14. 【子查询】.NET开源 ORM 框架 SqlSugar 系列
15. 【嵌套查询】.NET开源 ORM 框架 SqlSugar 系列

💦万丈高楼平地起，做开发想要技术精进，必须要有扎实的基础功底。基础SQL查询语法一定要牢记于心，才能应对后面更为复杂的形势。

💥SqlSugar 在子查询上支持是非常强大，比如 子查询进行联表、IN、NOT IN 和 SELECT 一列等 。

1、基础教程 🎀

 1.1 API目录

/***只查一列***/
//First: select top 1 id
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 中使用

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();//也可以DTOSqlFunc.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：导航方式

这种已经不属于子查询了，属于另一个查询体系。

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==it.Aid).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_aggvar 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()), ",")//去重复
.SelectStringJoin(it=>SqlFunc.MappingColumn<string>($"distinct {it.UserId}"),".")

8、只查一列 （top 1）

//First:select top 1 id
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=>new{ 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

    .NET开源 ORM 框架 SqlSugar 系列

1. 【开篇】.NET开源 ORM 框架 SqlSugar 系列
2. 【入门必看】.NET开源 ORM 框架 SqlSugar 系列
3. 【实体配置】.NET开源 ORM 框架 SqlSugar 系列
4. 【Db First】.NET开源 ORM 框架 SqlSugar 系列
5. 【Code First】.NET开源 ORM 框架 SqlSugar 系列
6. 【数据事务】.NET开源 ORM 框架 SqlSugar 系列
7. 【连接池】.NET开源 ORM 框架 SqlSugar 系列
8. 【查询目录】.NET开源 ORM 框架 SqlSugar 系列
9. 【查询基础】.NET开源 ORM 框架 SqlSugar 系列
10. 【排序用法】.NET开源 ORM 框架 SqlSugar 系列
11. 【分组去重】.NET开源 ORM 框架 SqlSugar 系列
12. 【联表查询】.NET开源 ORM 框架 SqlSugar 系列
13. 【导航查询】.NET开源 ORM 框架 SqlSugar 系列
14. 【子查询】.NET开源 ORM 框架 SqlSugar 系列
15. 【嵌套查询】.NET开源 ORM 框架 SqlSugar 系列