分享案例:EF【LINQ用法】多表联动内嵌子查询Count分组用法

morliz子轩 自由职业 ERP程序员  2021-04-22 03:15:12
SQL原型:

select a.BranjobId
,a.Jobno
,b.Jobna
,Planqty
,Jobqty = (select count(Manno) from BgMan c where a.Branno = c.Branno and a.Jobno = c.Jobno) --人事档案表
,Overtime = sum(a.Overtime)

from BranJob a --部门职位表
left join JobSet b on a.Jobno =b.Jobno --职位表
where a.Branno = '006'

group by a.BranjobId,a.Branno,a.Jobno,b.Jobna,Planqty
order by a.BranjobId


EF联动查询用法:

/// <summary>
/// 获取部门职务信息
/// </summary>
/// <param name="branno">部门编号</param>
/// <returns></returns>
public static DataTable GetBranjob(string branno)
{
try
{
using(CusProContext db = new CusProContext())
{
var BranjobObj = (from p in db.BranJob
where p.Branno == branno

join a in db.JobSet on p.Jobno equals a.Jobno into t1
from a in t1.DefaultIfEmpty()

join b in db.BgMan on new { p.Branno, p.Jobno } equals new { b.Branno, b.Jobno } into t2
from c in t2.DefaultIfEmpty()

group c by new
{
p.BranjobId,
p.Jobno,
a.Jobna,
p.Planqty,
p.Overtime
} into grp

select new
{
grp.Key.BranjobId,
grp.Key.Jobno,
grp.Key.Jobna,
grp.Key.Planqty,
Jobqty = grp.Count(x => x.Manno != null),
Lackqty = grp.Key.Planqty - grp.Count(x => x.Manno != null),
grp.Key.Overtime
}).ToList();

return LinqToDataTable(BranjobObj);
}
}
catch(Exception ex)
{
throw ex;
}
}


这里是采用的是左联连的查询用法,如有什么不足不处,欢迎大神指正。
...全文
150 点赞 收藏 2
写回复
2 条回复
morliz子轩 04月23日
谢谢 版主大大
回复 点赞
兔子党-顾问 04月23日
感谢分享,虽然没看出问题,但不影响我发帖赞一下。
回复 点赞
发动态
发帖子
C#
创建于2007-09-28

8.5w+

社区成员

64.0w+

社区内容

.NET技术 C#
社区公告
暂无公告