SqlSugar 5.联表查询

用到的几个实体类

使用 CodeFirst 创建表

public class Custom
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        
        public string Name { get; set; }
    }
    
    
public class Order
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        
        public string Name { get; set; }
        
        public decimal Price { get; set; }
        
        [SugarColumn(IsNullable = true)]
        
        public DateTime CreateTime { get; set; }
        
        [SugarColumn(IsNullable = true)]
        public int CustomId { get; set; }
    }
    

public class OrderDetail
    {
        [SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int ItemId { get; set; }
        
        public int OrderId { get; set; }
        
        public decimal? Price { get; set; }
        
        [SqlSugar.SugarColumn(IsNullable = true)]
        public DateTime? CreateTime { get; set; }
        
        [SugarColumn(IsIgnore = true)]
        public Order Order { get; set; }
    }
    
    
public class ViewOrder : Order
    {
        public string CustomName { get; set; }
    }

1.Join用法

1.1 语法糖1

优点：好理解，五个表以内的联表比较推荐

1.1.1 表和表的连接

List<ViewOrder> list = db.Queryable<Order>()
                .LeftJoin<Custom>((o, cus) => o.CustomId == cus.Id) //若是多个条件 用 &&
                .LeftJoin<OrderDetail>((o, cus, oritem) => o.Id == oritem.OrderId)
                 .Where((o, cus, oritem) => o.Id == 4 && oritem.Price == 50)
                .Select((o, cus) => new ViewOrder { Id = o.Id, CustomName =cus.Name })
                .ToList();
                
            //    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` = '4')

若将 .Select((o, cus) => new ViewOrder { Id = o.Id, CustomName =cus.Name }) 修改成.Select((o, cus) => new ViewOrder()) 那就是查询所有字段

1.1.2 表和Queryable的连接 (主表左连了一个由子查询组合的表)

var rightQueryable = db.Queryable<Custom>()
                .LeftJoin<OrderDetail>((c, o) => c.Id == o.ItemId)
                .Select(c => c);

var list = db.Queryable<Order>()
               .LeftJoin(rightQueryable, (o, r) => o.CustomId == r.Id)
               .Where(o=>o.Id==4)
               .Select(o => o)
               .ToList();


//SELECT o.* FROM `Order` o 
//Left JOIN(SELECT c.*FROM `Custom` c Left JOIN `OrderDetail` o ON( `c`.`Id` = `o`.`ItemId` )   ) r ON ( `o`.`CustomId` = `r`.`Id` )
//WHERE( `o`.`Id` = '4')

1.1.3 Queryable和表的连接 (以一个子查询为主表关联其他表)

var queryable = db.Queryable<Order>();

var list = db.Queryable(queryable)
            .LeftJoin<Custom>((o, c) => o.CustomId == c.Id)
            .LeftJoin<OrderDetail>((o, c, od) => o.Id == od.OrderId).Select(o => o)
            .Where(o => o.Id == 4)
            .ToList();
            
//SELECT o.* FROM  
//(SELECT * FROM  (SELECT `Id`,`Name`,`Price`,`CreateTime`,`CustomId` FROM `Order`  ) t ) o 
//Left JOIN `Custom` c ON ( `o`.`CustomId` = `c`.`Id` )  
//Left JOIN `OrderDetail` od ON ( `o`.`Id` = `od`.`OrderId` )   
//WHERE ( `o`.`Id` = '4' )

1.2 语法糖2

• 优点：五个表以上用这种方式比较快捷
• 缺点：不能套子查询( leftjoin(queryable) )

var list = db.Queryable<Order, Custom, OrderDetail>((o, c, od) => new JoinQueryInfos(
            JoinType.Left, o.CustomId == c.Id,
            JoinType.Left, o.Id == od.OrderId
            ))
            .Select((o, c, od) => new { Id = o.Id, Name = o.Name, Cname = c.Name, Price = od.Price, CreateTime = od.CreateTime})
            .ToList();

//SELECT  `o`.`Id` AS `Id` , `o`.`Name` AS `Name` , `c`.`Name` AS `Cname` , `od`.`Price` AS `Price` , `od`.`CreateTime` AS `CreateTime`  FROM `Order` o
//Left JOIN `Custom` c ON( `o`.`CustomId` = `c`.`Id` )  
//Left JOIN `OrderDetail` od ON( `o`.`Id` = `od`.`OrderId` )

1.3 语法糖3

• ‘inner join 可以用这种方式’

 var list = db.Queryable<Order, Custom, OrderDetail>((o, c, od) => o.CustomId == c.Id && o.Id == od.OrderId)
               .Select((o, c, od) => new { Id = o.Id, Name = o.Name, Cname = c.Name, Price = od.Price, CreateTime = od.CreateTime })
               .ToList();
               
//SELECT  `o`.`Id` AS `Id` , `o`.`Name` AS `Name` , `c`.`Name` AS `Cname` , `od`.`Price` AS `Price` , `od`.`CreateTime` AS `CreateTime`  
//FROM `Order` o  ,`Custom`  c ,`OrderDetail`  od
//WHERE(( `o`.`CustomId` = `c`.`Id` ) AND( `o`.`Id` = `od`.`OrderId` ))

2.Where用法

注意：写在 Select() 之前

.Where(o => o.Id == 4)
.Where((o, cus, oritem) => o.Id == 4 && oritem.Price == 50)

3.OrderBy用法

注意：写在 Select() 之前

.OrderBy(st => st.StudentId) 
.OrderBy((st, sc) => sc.SchoolId, OrderByType.Desc)

4.GroupBy用法

注意：写在 Select() 之前

.GroupBy(it => it.Name)
.GroupBy((st, sc) => sc.SchoolId)

5.Select用法

连表必须加上 Select 不然会查询出重复列而报错，一般 Select() 写在 ToList() 之前

基础用法

//新类
.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.1 返回匿名对象

5.1.1 一个一个字段赋值

参考 "1.2 语法糖2" 中 Select的写法

5.1.2 自动主表赋值 表.*

• o.Id是表的任意一个属性， SelectAll是查询全部 o.*
• .SelectAll等同于SqlFunc.GetSelfAndAutoFill是个语法糖

 var list = db.Queryable<Order, Custom, OrderDetail>((o, c, od) => new 
        JoinQueryInfos(
                JoinType.Left, o.CustomId == c.Id,
                JoinType.Left, o.Id == od.OrderId
                ))
                 .Select((o, c, od) => new { 
                 d = o.Id.SelectAll(), 
                 CName = c.Name, 
                 Time = od.CreateTime })
                .ToList();
                
//SELECT o.*, `c`.`Name` AS `CName` , `od`.`CreateTime` AS `Time`  FROM `Order` o
//Left JOIN `Custom` c ON ( `o`.`CustomId` = `c`.`Id` )
//Left JOIN `OrderDetail` od ON ( `o`.`Id` = `od`.`OrderId` )

5.2 返回到新类

参考 "1.1.1 表和表的连接" 中 Select的写法

5.3 自动映射机制

Select用的是自动填充这样使用方便，高并发的地方还是写成上面那种方式

var list = db.Queryable<Order, Custom, OrderDetail>((o, c, od) => new 
        JoinQueryInfos(
                JoinType.Left, o.CustomId == c.Id,
                JoinType.Left, o.Id == od.OrderId
                ))
                 .Select<ViewOrder>()
                .ToList();

//SELECT c.`Name` AS `CustomName`,o.`Id` AS `Id`,o.`Name` AS `Name`,o.`Price` AS `Price`,o.`CreateTime` AS `CreateTime`,o.`CustomId` AS `CustomId` 
//FROM `Order` o
//Left JOIN `Custom` c ON( `o`.`CustomId` = `c`.`Id` )  
//Left JOIN `OrderDetail` od ON( `o`.`Id` = `od`.`OrderId` )

5.4 更多用法

• Select用法太多，这篇文章主要讲联表查询
• 更多用法看官网

6.Full Join用法

文档参考：联表查询