• 主页

# 分享案例：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日

C#

8.5w+

64.0w+

.NET技术 C#