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;
}
}
這里是采用的是左聯連的查詢用法，如有什么不足不處，歡迎大神指正。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/279123.html
標籤：C#
下一篇：如何在專案中創建類的快捷方式？