第一种：5个表以内
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) => new ViewOrder { Id = o.Id, CustomName = cus.Name })
.ToList(); //ViewOrder是一个新建的类，更多Select用法看下面文档
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)
第二种：5个表以上。
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()
第三种
如果全部是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 用法
必写：联表查询必须加上SELECT，不然会查询出重复列报错 , Select一般写在ToList之前
只用到o表可以 o=>
用到 i 表 没用到 c表 (o,i)=> (错误用法 : i=>)
用到 c表 (o,i,c)=> (错误1 : c=> 错误2 : (i,c)=> 错误3 : (o,c)=>)
用例：
//新类
.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、返回匿名对象
1.一个一个赋值
var list = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(
JoinType.Left,st.SchoolId==sc.Id))//语法糖2联表，其他语法糖都可以
.OrderBy(st=>st.Name)
.Select((st,sc)=>new{Id=st.Id,Name=st.Name,SchoolName=sc.Name}).ToList();
|