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

   .NET开源 ORM 框架 SqlSugar 系列

• 【开篇】.NET开源 ORM 框架 SqlSugar 系列
  
• 【入门必看】.NET开源 ORM 框架 SqlSugar 系列
  
• 【实体设置】.NET开源 ORM 框架 SqlSugar 系列
  
• 【Db First】.NET开源 ORM 框架 SqlSugar 系列
  
• 【Code First】.NET开源 ORM 框架 SqlSugar 系列
  
• 【数据变乱】.NET开源 ORM 框架 SqlSugar 系列
  
• 【毗连池】.NET开源 ORM 框架 SqlSugar 系列
  
• 【查询目次】.NET开源 ORM 框架 SqlSugar 系列
  
• 【查询根本】.NET开源 ORM 框架 SqlSugar 系列
  
• 【排序用法】.NET开源 ORM 框架 SqlSugar 系列
  
• 【分组去重】.NET开源 ORM 框架 SqlSugar 系列
  
• 【联表查询】.NET开源 ORM 框架 SqlSugar 系列
  
• 【导航查询】.NET开源 ORM 框架 SqlSugar 系列
  
• 【子查询】.NET开源 ORM 框架 SqlSugar 系列
  
• 【嵌套查询】.NET开源 ORM 框架 SqlSugar 系列
  

---

  💦万丈高楼平地起，做开辟想要技能精进，必须要有踏实的根本功底。根本SQL查询语法肯定要牢记于心，才华应对背面更为复杂的形势。
  

​
一、Join用法 

1.1 语法糖1 

👍优点：好明白，5个表以内的联表非常爽，支持功能全
😒缺点:  联表高出5个以上的表后 (x,b,c...) 会比力丢脸，语法糖2可以增补
 场景1：表和表的左毗连 LeftJoin<T>()  

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

复制代码

天生的SQL

1. SELECT
2.   [o].[Id] AS [Id],
3.   [cus].[Name] AS [CustomName]
4. FROM
5.   [Order] o
6.   Left JOIN [Custom] cus ON ([o].[CustomId] = [cus].[Id])
7.   Left JOIN [OrderDetail] oritem ON ([o].[Id] = [oritem].[OrderId])
8. WHERE
9.   ([o].[Id] = @Id0)

复制代码

 场景2：表和 Queryable  JOIN 

1. var rigtQueryable = db.Queryable<Custom>()
2.     .LeftJoin<OrderItem>((o, i) => o.Id == i.ItemId)
3.         .Select(o => o);
4.   
5. var List = db.Queryable<Order>()
6.     .LeftJoin(rigtQueryable, (c, j) => c.CustomId == j.Id)
7.     .Select(c => c).ToList();　
8. //SELECT c.* FROM [Order] c Left JOIN
9. //(SELECT o.* FROM [Custom] o Left JOIN [OrderDetail] i ON ( [o].[Id] = [i].[ItemId] )  ) j
10. //ON ( [c].[CustomId] = [j].[Id] )

复制代码

 场景3：Queryable 和表 JOIN  

1. var queryable=db.Queryable<Order>();
2. 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>

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

复制代码

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

1. db.Queryable<Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
2.     JoinType.Left, o.Id == i.OrderId, //左连接 左链接 左联
3.     JoinType.Left, o.CustomId == c.Id
4. ))
5. .Select((o,i,c)=>new ViewModel{ name=o.Name ..})
6. .ToList()
8. //3个T代表3个表查询,并且按顺序排列
9. //Order  o     
10. //OrderItem i   关系 JoinType.Left, o.Id == i.OrderId
11. //Custom c     关系 JoinType.Left, o.CustomId == c.Id      
12. //那么生成的Sql就是
13. // FROM [Order] o
14. // Left JOIN [OrderItem] i ON ( [o].[Id] = [i].[OrderId] )  
15. // 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 可以用这种方式直接联表

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

复制代码

天生的SQL

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

复制代码

二、Where用法

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

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

复制代码

三、OrderBy用法

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

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

复制代码

四、GroupBy用法

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

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

复制代码

五、Select 用法

📌Select位置：

• 正常环境背面一样平常是 .Where(..).OrderBy(..).Select(..).ToList()
  
• 如果 Where 等要写在 Select 背面应该 用 Select(...).MergeTable().Where
  
• 别名发起写全，背面方便维扩
  
• 比方三表查询：(o,i,c)=>  (不发起 o=> 大概 (o,i)=>)
  

5.1 手动映射

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

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

复制代码

5.2 实体主动映射1

🫶语法最美，先指定自界说字段，true 表现别的字段根据字段名主动映射。

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

复制代码

天生的Sql如下:

1. SELECT        [x].[ID] AS [id] , --自动
2.           [x].[Time] AS [Time] , --自动
3.           [x].[SchoolName] AS [Name] --手动
4.           100 as [Count]  --手动
5.           FROM [SchoolA] x
6.           Left JOIN StudentA  y  ON ( [x].[SchoolId] =[y].[SchoolId])

复制代码

5.3 实体主动映射2

💥阐明：通过 x.* 方式实现多表查询
🤖注意： Oracle如果用到Take大概分页 须要改用ToffsetPage()更换

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

复制代码

天生Sql如下

1. SELECT o.*, [c].[Name] AS [CustomName]
2.               FROM  [Order] o
3.               Left JOIN [OrderItem] i ON ( [o].[Id] = [i].[OrderId] )  
4.               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性能优化提升）

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

复制代码

sql:

1. SELECT
2.           o.[Name] AS [Name],
3.           c.[Name] AS [CustomName],
4.           i.[Price] AS [OrderItemPrice]
5.           FROM [Order] o
6.           Left JOIN [OrderItem] i ON ( [o].[Id] = [i].[OrderId] )  
7.           Left JOIN [Custom] c ON ( [o].[CustomId] = [c].[Id] )

复制代码

💥注意: 
         1. ViewOrder 必须每个列都能匹配到字段，否则就无法按规则匹配，包管每个列都准确
         2. 高并发功能不发起使用，手写的性能肯定高于主动映射

5.5 匿名对象主动映射

阐明：主动主表赋值  表.*

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

复制代码

六、导航属性联表

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

1. //实体
2. public class StudentA
3. {
4.     [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
5.     public int StudentId { get; set; }
6.     public string Name { get; set; }
7.     public int SchoolId { get; set; }
8.     [Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一对一 SchoolId是StudentA类里面的
9.     public SchoolA SchoolA { get; set; } //不能赋值只能是null
10.   
11. }
12. public class SchoolA
13. {
14.     [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
15.     public int SchoolId { get; set; }
16.     public string SchoolName { get; set; }
17. }
18.    
19. /*** 在配好导航后可以: 导航对象.具体属性 进行使用 ***/
22. //IncludeLeftJoin 会根据导航生成LeftJoin语句 (IncludeLeftJoin)
23. var list = db.Queryable<StudentA>()//From StudentA x
24.          .IncludeLeftJoin(x=>x.SchoolA) // Left Join SchoolA y on x.SchoolId=y.Id
25.          .Where(x =>x.SchoolA.SchoolName=="北大")//Where y.SchoolName='北大'
26.          .ToList();//IncludeInnerJoin也是一样
28.   
29. //没有IncludeLeftJoin在Where中也可以直接用（SQL是子查询过滤）
30. var list = db.Queryable<StudentA>()
31.          .Where(x =>x.SchoolA.SchoolName=="北大")//导航对象过滤  
32.          .ToList();
33.            
34. //没有IncludeLeftJoin在Select中也可以直接用（SQL是子查询实现）   
35. var list = db.Queryable<StudentA>()
36.              .Where(x => x.id>1)  //Where和Select中别名要写一样
37.              .Select(x =>new {
38.                 name=x.Name,
39.                 SchoolName= x.SchoolA.SchoolName
40.              }).ToList();
41.               
42. //IncludeLeftJoin 会根据导航生成LeftJoin语句 (IncludeLeftJoin)
43. var list = db.Queryable<StudentA>()
44.          .IncludeLeftJoin(x=>x.SchoolA) //Left Join SchoolA y on x.SchoolId=y.Id
45.          .Where(x =>x.SchoolA.SchoolName=="北大")//y.SchoolName='北大'
46.          .ToList();

复制代码

七、联表查询设置别名

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

复制代码

八、子查询和嵌套查询

8.1 子查询 

1. var list= db.Queryable<Student>()
2.             .Where(it => SqlFunc.Subqueryable<School>().Where(s =>s.Id==it.Id).Any())
3.             .ToList();
5. var list= db.Queryable<Student>()
6.         .Select(st => new{
7.                      name = st.Name,
8.                      id = SqlFunc.Subqueryable<School>()
9.                            .Where(s => s.Id == st.Id)
10.                            .Select(s => s.Id)
11.             }).ToList();

复制代码

8.2 嵌套查询

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

复制代码

九、高出12个表的联表

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

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

复制代码

十、动态Join条件

 10.1 条件动态

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

复制代码

10.2  表动态

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

复制代码

十一、Cross Join

 Cross join 只支持语法糖二

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

复制代码

   .NET开源 ORM 框架 SqlSugar 系列

• 【开篇】.NET开源 ORM 框架 SqlSugar 系列
  
• 【入门必看】.NET开源 ORM 框架 SqlSugar 系列
  
• 【实体设置】.NET开源 ORM 框架 SqlSugar 系列
  
• 【Db First】.NET开源 ORM 框架 SqlSugar 系列
  
• 【Code First】.NET开源 ORM 框架 SqlSugar 系列
  
• 【数据变乱】.NET开源 ORM 框架 SqlSugar 系列
  
• 【毗连池】.NET开源 ORM 框架 SqlSugar 系列
  
• 【查询目次】.NET开源 ORM 框架 SqlSugar 系列
  
• 【查询根本】.NET开源 ORM 框架 SqlSugar 系列
  
• 【排序用法】.NET开源 ORM 框架 SqlSugar 系列
  
• 【分组去重】.NET开源 ORM 框架 SqlSugar 系列
  
• 【联表查询】.NET开源 ORM 框架 SqlSugar 系列
  
• 【导航查询】.NET开源 ORM 框架 SqlSugar 系列
  
• 【子查询】.NET开源 ORM 框架 SqlSugar 系列
  
• 【嵌套查询】.NET开源 ORM 框架 SqlSugar 系列