1、读取用户和部门两个表的左连接:
var sg = db.Users.GroupJoin(db.Departments, u => u.DepartmentId, d => d.DepartmentId, (u,d) => new { u, d }).Select(o=>o).ToList();
注意:上面将返回所用用户信息和对应的部门信息(即用户部门ID信息缺少,那么用户列表也会显示)
2、读取指定返回列表字段的左连接信息:
var GJoinList = db.Sys_User.GroupJoin(db.Sys_Department, u => u.DepartmentId, d => d.DepartmentId, (u,d) => new { UserId=u.UserId, Account=u.Account, RealName=u.RealName, EnabledMark=u.EnabledMark, DeleteMark=u.DeleteMark,DepartmentName = d.FirstOrDefault(x=>x.DepartmentId==u.DepartmentId).FullName}).Select(o=>o);
3、读取连接表:
var sg = db.Users.Join(db.Departments, u => u.DepartmentId, d => d.DepartmentId, (u,d) => new { u, d }).Select(o=>o).ToList();
注意:这里将只显示用户里DepartmentId
和部门表里DepartmentId
相等的信息,如果用户没有部门ID则此条用户信息不会显示
var data = db.CRM_OrderDetails.Join(db.CRM_Order, d => d.OrderId, o => o.OrderId, (d, o) => new { d, o }).Select(p => new ProductsListModel() {
OrderId =p.o.OrderId,
OrderCode=p.o.OrderCode,
CustomerName=p.o.CustomerName,
ProductName=p.d.ProductName,
UnitId=p.d.UnitId,
Qty=p.d.Qty,
Price=p.d.Price,
Amount=p.d.Amount,
TaxAmount=p.d.TaxAmount,
TaxCostAmount=p.d.TaxCostAmount,
CreateTime=p.d.CreateTime,
EndTime=p.d.EndTime,
Description=p.d.Description
}).Where(expression).OrderBy(orderbyExpression);
Linq Left Join
与Inner Join
Linq语法与lambda
表达式
Left Join
获取数据:
Linq语法如下:
var sg = (from g in dc.sgroup
join gu in dc.sgroupuser on g.gKey equals gu.gKey into l
from lgu in l.DefaultIfEmpty()
select new { g, lgu }).ToList();
Lambda表达式如下:
var sg = dc.sgroup.GroupJoin(dc.sgroupuser, g => g.gKey, gu => gu.gKey, (g, gu) => new { g, gu }).Select(o=>o).ToList() ;
注意:
Linq 与Lambda
表达式取出的结果有所不同.Linq取出的结果的记录数与Sql中的Left Join
的结果相同,而Lambda
表达式取出的记录数是sgroup
表中的记录数,sgroupuser
对应的记录是以对象集合存在于结果中
附:
下面是Inner Join
:
Linq语法如下:
var sg = (from g in dc.sgroup
join gu in dc.sgroupuser on g.gKey equals gu.gKey
select new { g, gu }).ToList();
Lambda
表达式如下:
var sg = dc.sgroup.Join(dc.sgroupuser, g => g.gKey, gu => gu.gKey, (g, gu) => new { g, gu }).Select(o=>o).ToList() ;
注意:
上面最后都用到了ToList()
方法 , 用ToList()
是为了一次性将数据取到本地.