联表查询、关联查询、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()

.LeftJoin((o,cus) => o.CustomId == cus.Id)//多个条件用&&

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

.LeftJoin((o, i) => o.Id == i.ItemId)

.Select(o => o);

var List = db.Queryable()

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

var list=db.Queryable(queryable).LeftJoin((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(expression)

优点3: 升级到最新支持Cross Join,用法JoinType.CrossJoin，条件写it=>true

缺点：不支持LeftJoin(queryable) 这种嵌套

单表查询是基于db.Queryable

//生成的Sql: from [Order]

db.Queryable联表查询是基于多个T，例如 db.Queryable 3个T就是3表查询

db.Queryable((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((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=1184OrderBy用法注意：写在.Select()之前

.OrderBy(o=>o.id) //只用到o这样写就行

.OrderBy((o,i)=>i.xx) //如果用到i需要这么写

//更多用法: https://www.donet5.com/Home/Doc?typeId=2312GroupBy用法注意：写在.Select()之前

.GroupBy(o=>o.id) //只用到o这样写就行

.GroupBy((o,i)=>i.xx) //如果用到i需要这么写

//更多用法: https://www.donet5.com/Home/Doc?typeId=2243Select 用法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()

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

.LeftJoin((o,i)=>o.Id == i.OrderId)

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

.LeftJoin((o,i)=>o.Id == i.OrderId)

.LeftJoin((o,i,c)=>o.CustomId == c.Id)

.Select().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((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()//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()

.Where(x =>x.SchoolA.SchoolName=="北大")//导航对象过滤

.ToList();

//没有IncludeLeftJoin在Select中也可以直接用（SQL是子查询实现）

var list = db.Queryable()

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

.IncludeLeftJoin(x=>x.SchoolA) //Left Join SchoolA y on x.SchoolId=y.Id

.Where(x =>x.SchoolA.SchoolName=="北大")//y.SchoolName='北大'

.ToList();更多用法：https://www.donet5.com/Home/Doc?typeId=1188

五、联表查询设置别名var list1 = db.Queryable().AS("Order001")

.LeftJoin((o,i)=> o.Id == i.OrderId,"OrderItem001")

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

.Where(it => SqlFunc.Subqueryable().Where(s =>s.Id==it.Id).Any())

.ToList();

var list= db.Queryable()

.Select(st => new{

name = st.Name,

id = SqlFunc.Subqueryable()

.Where(s => s.Id == st.Id)

.Select(s => s.Id)

}).ToList();更多：https://www.donet5.com/Home/Doc?typeId=2231

嵌套查询//Queryable联表

var q11 = db.Queryable().Where(it => it.Id>1);

var q22 = db.Queryable().Where(it => it.Id > 2);

var q33 = db.Queryable().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();更多：https://www.donet5.com/Home/Doc?typeId=2354

七、超过12个表的联表我们可以通用Megetable进行合并成一个表，然后在进行JOIN

db.Queryable()

.LeftJoin((x, y) => x.id == y.ItemId)

.LeftJoin.....省略

.LeftJoin.....省略

.....省略

.Select((x,y,z,.......省略) => new {xid=x.id,yid=y.ItemId})

.MergeTable()//合并

.LeftJoin((x,y)=>x.yid==y.ItemId)// 最后一个表不是匿名对象就行

.ToList();八、动态Join条件 条件动态var exp2=Expressionable.Create();

exp2.And((o,cus)=>o.CustomId == cus.Id);

exp2.AndIF(Name!=null,(o, cus) => o.Name==Name);

var list= db.Queryable()

.LeftJoin(exp2.ToExpression())//动态拼出来的条件

.LeftJoin((o, cus, oritem) => o.Id == oritem.OrderId)

.Where(o => o.Id == 1)

.ToList(); 表动态var list= db.Queryable()

.LeftJoinIF(条件 ,(o, cus) => o.CustomId == cus.Id)//条件成立才会生成Join这个表

.LeftJoin((o, cus, oritem) => o.Id == oritem.OrderId)

.ToList();

//InnerJoinIF一样用法

//没有SelectIF方案

var fileName = true ? "name" : "name2";

var list=db......Select(it=>new {

name=SqlFunc.MappingColumn(fileName)

})

.ToList();九、Cross JoinCross join只支持语法糖二

var userInfo = db.Queryable((x, y) => new JoinQueryInfos(

JoinType.Cross, true

))

.Select(x=>new {

name=x.UserId

})

.ToList();

//SELECT [x].[UserId] AS [name] FROM [UserInfo001] [x] Cross JOIN [UserInfo001] [y]