语法糖1、2和3 在Where OrderBy GroupBy Select用法都一样的,他们区别就在JOIN的方式不一样,其它都一样
优点:好理解,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=2354
表和内存集合查询
https://www.donet5.com/Home/Doc?typeId=2315
优点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 ,不要写多了或者写少了
如果全部是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] ))
注意:写在.Select()之前
.Where(o=>o.id==1) //只用到o这样写就行 .Where((o,i)=>i.xx==1) //如果用到i需要这么写 //更多用法:https://www.donet5.com/Home/Doc?typeId=1184
注意:写在.Select()之前
.OrderBy(o=>o.id) //只用到o这样写就行 .OrderBy((o,i)=>i.xx) //如果用到i需要这么写 //更多用法: https://www.donet5.com/Home/Doc?typeId=2312
注意:写在.Select()之前
.GroupBy(o=>o.id) //只用到o这样写就行 .GroupBy((o,i)=>i.xx) //如果用到i需要这么写 //更多用法: https://www.donet5.com/Home/Doc?typeId=2243
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(); //更多用法看文档下面
语法最美,新功能(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])
说明:通过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表查询 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();
更多用法:https://www.donet5.com/Home/Doc?typeId=1188
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();
更多:https://www.donet5.com/Home/Doc?typeId=2231
//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();
更多:https://www.donet5.com/Home/Doc?typeId=2354
我们可以通用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();
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();
2016 © donet5.comApache Licence 2.0