联表查询
用到的几个实体类
使用 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用法太多，这篇文章主要讲联表查询- 更多用法看官网
