sqlsugar联表查询

作者: 李炳坤发布时间： 2024-04-07 183 分类专栏: 后端开发

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

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,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] 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=2315

语法糖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 ，不要写多了或者写少了

语法糖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位置：

正常情况后面一般是 .Where(..).OrderBy(..).Select(..).ToList()

如果Where等要写在Select后面应该用Select(...).MergeTable().Where

别名建议写全，后面方便维扩

例如三表查询：(o,i,c)=> (不建议 o=> 或者 (o,i)=>)

手动映射

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

实体自动映射1

语法最美，新功能(5.1.3.35)

 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] x
          Left JOIN StudentA  y  ON ( [x].[SchoolId] =[y].[SchoolId])

实体自动映射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

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

匿名对象自动映射

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

.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();

六、子查询和嵌套查询

子查询

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();

嵌套查询

//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条件

条件动态

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();

表动态

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();

转载自:联表查询、关联查询、JoinTable、连表查询、Left Join - SqlSugar 5x - .NET果糖网 (donet5.com)

所属分类：后端开发

全球开源创新大会 GOSIM 2024 欧洲站5月开启！

四种ORM框架的对比

李炳坤

183

访问

App定制开发

小程序/公众号开发

嵌入式开发（物联网）

区块链开发

软件系统开发

人工智能新技术

其他产品&工业控制

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

Join用法

语法糖1

语法糖2

语法糖3

Where用法

OrderBy用法

GroupBy用法

Select 用法

手动映射

实体自动映射1

实体自动映射2

实体自动映射3

匿名对象自动映射

四、导航属性联表

五、联表查询设置别名

六、子查询和嵌套查询

子查询

嵌套查询

七、超过12个表的联表

八、动态Join条件

条件动态

表动态

全球开源创新大会 GOSIM 2024 欧洲站5月开启！

四种ORM框架的对比

小当家，帮您把生意越做越大！

联系我们/

Contact us

联系电话

腾讯QQ

添加微信