联表查询、关联查询、JoinTable、连表查询

Join用法

语法糖1、2和3 在Where OrderBy GroupBy Select用法都一样的,他们区别就在JOIN的方式不一样,其它都一样

语法糖1  

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

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

表和表的左连接  新语法糖  5.0.4.2

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) => 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] o
  Left JOIN [Custom] cus ON ([o].[CustomId] = [cus].[Id])
  Left JOIN [OrderDetail] oritem ON ([o].[Id] = [oritem].[OrderId])
WHERE
  ([o].[Id] = @Id0)

表和Queryable  JOIN 新语法糖  5.0.4.3

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] )

Queryable和表 JOIN  新语法糖  5.0.4.3 

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

更多套娃用法看嵌套查询:

https://www.donet5.com/Home/Doc?typeId=2354

表和内存集合查询

https://www.donet5.com/Home/Doc?typeId=2315

语法糖2  

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

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

缺点:不支持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 ,不要写多了或者写少了

语法糖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,不然会查询出重复列报错 , Select一般写在ToList之前

只用到o表可以 o=> 

用到 i 表 没用到 c表 (o,i)=>   (错误用法 : i=>)

用到 c表  (o,i,c)=>  (错误1 : c=>   错误2 :  (i,c)=>  错误3 :   (o,c)=>)

用例:

//新类
.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();
//更多用法看文档下面

1、返回匿名对象

1.一个一个赋值

var list = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(
        JoinType.Left,st.SchoolId==sc.Id))//语法糖2联表,其他语法糖都可以
      .OrderBy(st=>st.Name)
      .Select((st,sc)=>new{Id=st.Id,Name=st.Name,SchoolName=sc.Name}).ToList();

生成的Sql如下:

SELECT  [st].[ID] AS [id] , 
          [st].[Name] AS [name] , 
          [sc].[Name] AS [schoolName]  FROM [STudent] st 
          Left JOIN School sc ON ( [st].[SchoolId] =[sc].[Id])  ORDER BY [st].[Name]

2.自动主表赋值  表.*

.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是个语法糖

2、返回到新类

1.主表自动填充,从表指定

生成的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

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.高并发功能不建议使用,手写的性能肯定高于自动映射

4、更多用法

Select用法太多,这篇文章主要讲联表查询,更多用法看:

https://www.donet5.com/Home/Doc?typeId=1186

四、导航属性联表

如果有配置过导航, 这个就比较简单了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; }
}
  
/*** 在配好导航后可以: 导航对象.具体属性 进行使用 ***/

//在Where中
 var list = db.Queryable<StudentA>()
         .Where(x =>x.SchoolA.SchoolName=="北大")// 5.0.7.7请升级到 支持了一对一之后在一对?的条件过滤
         .ToList();
          
//在Select中       
var list = db.Queryable<StudentA>()
             .Where(x => x.id>1)  //Where和Select中别名要写一样
             .Select(x =>new { 
                name=x.Name,
                SchoolName= x.SchoolA.SchoolName
             }).ToList();

更多用法:https://www.donet5.com/Home/Doc?typeId=1188


五、联表查询设置别名

https://www.donet5.com/Home/Doc?typeId=2244


六、子查询和嵌套查询

子查询 :https://www.donet5.com/Home/Doc?typeId=2231

嵌套查询: https://www.donet5.com/Home/Doc?typeId=2354


七、超过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();


文档:SqlSugar5.0