【联表查询】.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查询语法一定要牢记于心，才能应对后面更为复杂的形势。

​

一、Join用法 

1.1 语法糖1 

👍优点：好理解，5个表以内的联表非常爽，支持功能全

😒缺点:  联表超过5个以上的表后 (x,b,c...) 会比较难看，语法糖2可以弥补

 场景1：表和表的左连接 LeftJoin<T>()  

//联表查询
var query5 = db.Queryable<Order>().LeftJoin<Custom>((o,cus) => o.CustomId == cus.Id)//多个条件用&&.LeftJoin<OrderDetail> ((o,cus,oritem) => o.Id == oritem.OrderId).Where(o => o.Id == 1)  .Select((o,cus,oritem) => new ViewOrder {Id=o.Id,CustomName = cus.Name }).ToList();  //ViewOrder是一个新建的类，更多Select用法看下面文档//内联用 .InnerJoin
//FullJoin 需要高版本才支持用法一样//注意：Join (a,b)=> 别名用法： 
a,b //正确用法
a,b,c
a,b,c,d a,b //错误用法
a,c
a,d

生成的SQL

SELECT[o].[Id] AS [Id],[cus].[Name] AS [CustomName]
FROM[Order] oLeft JOIN [Custom] cus ON ([o].[CustomId] = [cus].[Id])Left JOIN [OrderDetail] oritem ON ([o].[Id] = [oritem].[OrderId])
WHERE([o].[Id] = @Id0)

 场景2：表和 Queryable  JOIN 

var rigtQueryable = db.Queryable<Custom>().LeftJoin<OrderItem>((o, i) => o.Id == i.ItemId).Select(o => o);var List = db.Queryable<Order>().LeftJoin(rigtQueryable, (c, j) => c.CustomId == j.Id).Select(c => c).ToList();　//SELECT c.* FROM [Order] c Left JOIN //(SELECT o.* FROM [Custom] o Left JOIN [OrderDetail] i ON ( [o].[Id] = [i].[ItemId] )  ) j //ON ( [c].[CustomId] = [j].[Id] )

 场景3：Queryable 和表 JOIN  

var queryable=db.Queryable<Order>();
var list=db.Queryable(queryable).LeftJoin<OrderDetails>((o,d)=>o.id==d.orderid).Select(o=>o).ToList();

1.2 语法糖2  

👍优点1：这种适合联表比较多的比如5个以上的表  JOIN  写起来会比较爽

👍优点2：因为是一个参数更容易封装成方法 例如 Queryable<T,T2>(expression)

👍优点3:   升级到最新支持 Cross Join ,用法 JoinType.CrossJoin ，条件写it=>true

😒缺点：不支持 LeftJoin(queryable) 这种嵌套

单表查询是基于 db.Queryable<T>

//生成的Sql: from [Order]
db.Queryable<Order>

联表查询是基于多个T，例如 db.Queryable<T, T2,T3>  3个T就是3表查询

db.Queryable<Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(JoinType.Left, o.Id == i.OrderId, //左连接 左链接 左联 JoinType.Left, o.CustomId == c.Id 
))
.Select((o,i,c)=>new ViewModel{ name=o.Name ..})
.ToList()//3个T代表3个表查询,并且按顺序排列
//Order  o     
//OrderItem i   关系 JoinType.Left, o.Id == i.OrderId
//Custom c     关系 JoinType.Left, o.CustomId == c.Id      
//那么生成的Sql就是
// FROM [Order] o 
// Left JOIN [OrderItem] i ON ( [o].[Id] = [i].[OrderId] )  
// Left JOIN [Custom] c ON ( [o].[CustomId] = [c].[Id] )

因为多个T的原因所以在  Where 、 Select 、OrderBy、GroupBy 操作上同单表查询稍有差别

💥常见错误：数组超过界限  5个T就是4个JOIN ， 8个T就是7个JOIN ，不要写多了或者写少了

1.3 语法糖3

如果全部是 Inner Join 可以用这种方式直接联表

var list = db.Queryable<Order, OrderItem, Custom>((o, i, c) => o.Id == i.OrderId&&c.Id == o.CustomId).Select((o,i,c)=>new Class1{ Id=o.Id,Name=o.Name,CustomName=c.Name}).ToList(); //Class1是一个新建的类，更多Select用法看下面文档

生成的SQL

SELECT  c.[Name] AS [CustomName],o.[Id] AS [Id],o.[Name] AS [Name]       FROM [Order] o  ,[OrderDetail]  i ,[Custom]  c  WHERE (( [o].[Id] = [i].[OrderId] ) AND ( [c].[Id] = [o].[CustomId] ))

二、Where用法

🤖注意：写在.Select()之前

.Where(o=>o.id==1) //只用到o这样写就行
.Where((o,i)=>i.xx==1) //如果用到i需要这么写
//更多用法:https://www.donet5.com/Home/Doc?typeId=1184

三、OrderBy用法

🤖注意：写在.Select()之前

.OrderBy(o=>o.id) //只用到o这样写就行
.OrderBy((o,i)=>i.xx) //如果用到i需要这么写
//更多用法: https://www.donet5.com/Home/Doc?typeId=2312

四、GroupBy用法

🤖注意：写在.Select()之前

.GroupBy(o=>o.id) //只用到o这样写就行
.GroupBy((o,i)=>i.xx) //如果用到i需要这么写
//更多用法: https://www.donet5.com/Home/Doc?typeId=2243

五、Select 用法

📌Select位置：

1. 正常情况后面一般是 .Where(..).OrderBy(..).Select(..).ToList()
2. 如果 Where 等要写在 Select 后面应该 用 Select(...).MergeTable().Where
3. 别名建议写全，后面方便维扩
4. 例如三表查询：(o,i,c)=>  (不建议 o=> 或者 (o,i)=>)

5.1 手动映射

✍️ Select 写几列 查几列，不多查

//新类
.Select((o,i)=>new 类名{Id=o.Id,Name=o.Name,SchoolName=i.Name}).ToList();
//匿名对象
.Select((o,i)=>new {Id=o.Id,Name=o.Name,SchoolName=i.Name}).ToList();
//更多用法看文档下面

5.2 实体自动映射1

🫶语法最美，先指定自定义字段，true 表示其余字段根据字段名自动映射。

 var list4=db.Queryable<SchoolA>().LeftJoin<StudentA>((x, y) => (x.SchoolId == y.SchoolId)).Select((x,y) => new UnitView01(){Name=x.SchoolName,Count=100},true)//true表示 其余字段自动映射，根据字段名字.ToList();

生成的Sql如下:

SELECT        [x].[ID] AS [id] , --自动[x].[Time] AS [Time] , --自动 [x].[SchoolName] AS [Name] --手动 100 as [Count]  --手动FROM [SchoolA] xLeft JOIN StudentA  y  ON ( [x].[SchoolId] =[y].[SchoolId])

5.3 实体自动映射2

💥说明：通过 x.* 方式实现多表查询

🤖注意： Oracle如果用到Take或者分页 需要改用ToffsetPage()替换

//生成的SQL为 Select o.*, [c].[Name] AS [CustomName]
var oneClass = db.Queryable<Order>().LeftJoin<OrderItem>((o,i)=>o.Id == i.OrderId).LeftJoin<Custom>((o,i,c)=>o.CustomId == c.Id).Where(o=>o.Id>1)
.Select((o,i,c)=> new ViewOrder// 是一个新类
{      //Id是o任意一个属性Id=o.Id.SelectAll(),   //  等于 o.*   （SelectAll建议用一张表，多表会容易重名）CustomName=c.Name   // 等于 [c].[Name] AS [CustomName]
}).ToList()

生成Sql如下

SELECT o.*, [c].[Name] AS [CustomName] FROM  [Order] o Left JOIN [OrderItem] i ON ( [o].[Id] = [i].[OrderId] )  Left JOIN [Custom] c ON ( [o].[CustomId] = [c].[Id] ) WHERE [o].[Id]>1

5.4 实体自动映射3

💥说明：通过约束实现自动映射

比如一个3表查询 Order 、 OrderItem 、 Custom 

需要注意的是 Select 用的是自动填充这样使用方便，高并发的地方还是写成上面那种方式（5.0.5.2性能优化提升）

public class ViewOrder
{public string Name { get; set; } // ORDER表中的name 主表规则【字段名】public string CustomName { get; set; }//查询的是Custom中的的name 从表规则【class+字段名】public string OrderItemPrice { get; set; }//查询的是OrderItem中的name 从表规则【 class+字段名】
}
var viewModel= db.Queryable<Order>().LeftJoin<OrderItem>((o,i)=>o.Id == i.OrderId).LeftJoin<Custom>((o,i,c)=>o.CustomId == c.Id).Select<ViewOrder>().ToList();

sql:

SELECT o.[Name] AS [Name],c.[Name] AS [CustomName],i.[Price] AS [OrderItemPrice] FROM [Order] o Left JOIN [OrderItem] i ON ( [o].[Id] = [i].[OrderId] )  Left JOIN [Custom] c ON ( [o].[CustomId] = [c].[Id] )

💥注意: 

         1. ViewOrder 必须每个列都能匹配到字段，否则就无法按规则匹配，保证每个列都正确

         2. 高并发功能不建议使用，手写的性能肯定高于自动映射

5.5 匿名对象自动映射

说明：自动主表赋值  表.*

.Select<dynamic>((st,sc)=> new  
{ //id是st任意一个属性id=st.Id.SelectAll(), //  st.*  (SelectAll建议只用一张表，不然查询列会有重名)SchoolName=sc.Name // Name as  SchoolName
}).ToList()
//Select st.*,[sc].[Name] AS [schoolName]//.SelectAll等同于SqlFunc.GetSelfAndAutoFill是个语法糖

六、导航属性联表

如果有配置过导航， 这个就比较简单了 Join 都不要写了，懒人可以用

//实体
public class StudentA
{[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]public int StudentId { get; set; }public string Name { get; set; }public int SchoolId { get; set; }[Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一对一 SchoolId是StudentA类里面的public SchoolA SchoolA { get; set; } //不能赋值只能是null}
public class SchoolA
{[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]public int SchoolId { get; set; }public string SchoolName { get; set; }
}/*** 在配好导航后可以: 导航对象.具体属性 进行使用 ***///IncludeLeftJoin 会根据导航生成LeftJoin语句 (IncludeLeftJoin)
var list = db.Queryable<StudentA>()//From StudentA x.IncludeLeftJoin(x=>x.SchoolA) // Left Join SchoolA y on x.SchoolId=y.Id.Where(x =>x.SchoolA.SchoolName=="北大")//Where y.SchoolName='北大'.ToList();//IncludeInnerJoin也是一样//没有IncludeLeftJoin在Where中也可以直接用（SQL是子查询过滤）var list = db.Queryable<StudentA>().Where(x =>x.SchoolA.SchoolName=="北大")//导航对象过滤  .ToList();//没有IncludeLeftJoin在Select中也可以直接用（SQL是子查询实现）   
var list = db.Queryable<StudentA>().Where(x => x.id>1)  //Where和Select中别名要写一样.Select(x =>new { name=x.Name,SchoolName= x.SchoolA.SchoolName}).ToList();//IncludeLeftJoin 会根据导航生成LeftJoin语句 (IncludeLeftJoin)
var list = db.Queryable<StudentA>().IncludeLeftJoin(x=>x.SchoolA) //Left Join SchoolA y on x.SchoolId=y.Id.Where(x =>x.SchoolA.SchoolName=="北大")//y.SchoolName='北大'.ToList();

七、联表查询设置别名

var list1 = db.Queryable<Order>().AS("Order001").LeftJoin<OrderItem>((o,i)=> o.Id == i.OrderId,"OrderItem001")  .LeftJoin<Custom>((o, i, c) => c.Id == o.CustomId,"Custom001") .Where((o,i,c)=> o.TypeId==1).Select((o,i,c)=>new classA() { oid=o.Id , iname=i.Name }).ToList();

八、子查询和嵌套查询

8.1 子查询 

var list= db.Queryable<Student>().Where(it => SqlFunc.Subqueryable<School>().Where(s =>s.Id==it.Id).Any()).ToList();var list= db.Queryable<Student>().Select(st => new{name = st.Name,id = SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id)}).ToList();

8.2 嵌套查询

//Queryable联表      
var q11 = db.Queryable<Order>().Where(it => it.Id>1); 
var q22 = db.Queryable<Order>().Where(it => it.Id > 2); 
var q33 = db.Queryable<Order>().Where(it => it.Id > 3);  
var list= q11.LeftJoin(q22, (x, y) => x.Id == y.Id).LeftJoin(q33, (x, y, z) => x.Id == z.Id).ToList();

九、超过12个表的联表

我们可以通用 Megetable 进行合并成一个表，然后在进行JOIN。

db.Queryable<Order>().LeftJoin<OrderItem>((x, y) => x.id == y.ItemId) .LeftJoin.....省略.LeftJoin.....省略 .....省略.Select((x,y,z,.......省略) => new {xid=x.id,yid=y.ItemId}) .MergeTable()//合并   .LeftJoin<OrderItem>((x,y)=>x.yid==y.ItemId)// 最后一个表不是匿名对象就行.ToList();

十、动态Join条件

 10.1 条件动态

var exp2=Expressionable.Create<Order, Custom>();
exp2.And((o,cus)=>o.CustomId == cus.Id);
exp2.AndIF(Name!=null,(o, cus) => o.Name==Name);var list= db.Queryable<Order>().LeftJoin<Custom>(exp2.ToExpression())//动态拼出来的条件.LeftJoin<OrderDetail>((o, cus, oritem) => o.Id == oritem.OrderId).Where(o => o.Id == 1) .ToList();

10.2  表动态

var list= db.Queryable<Order>().LeftJoinIF<Custom>(条件 ,(o, cus) => o.CustomId == cus.Id)//条件成立才会生成Join这个表.LeftJoin<OrderDetail>((o, cus, oritem) => o.Id == oritem.OrderId).ToList();//InnerJoinIF一样用法//没有SelectIF方案
var fileName = true ? "name" : "name2";
var list=db......Select(it=>new {name=SqlFunc.MappingColumn<string>(fileName)}).ToList();

十一、Cross Join

 Cross join 只支持语法糖二

  var userInfo = db.Queryable<UserInfo001, UserInfo001>((x, y) => new JoinQueryInfos(JoinType.Cross, true)).Select(x=>new { name=x.UserId}).ToList();//SELECT  [x].[UserId] AS [name]  FROM [UserInfo001] [x] Cross JOIN [UserInfo001] [y]

   .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 系列