有以下sql陳述句：
select p.BranjobId
,p.Jobno
,c.Jobna
,p.Planqty
,jobqty = count(p.Jobno)
,lackqty = p.Planqty - count(p.Jobno)
,Overtime = sum(p.Overtime)
from BranJob p
inner join JobSet c on p.jobno = c.Jobno
where p.Branno = @branno
group by p.BranjobId,p.Jobno,c.Jobna,p.Planqty
請教大神，怎么實作在EF方法實作它的寫法？
BranJob和JobSet已經 存在物體映射關系。
uj5u.com熱心網友回復：
好象搞出來了，測驗結果也沒啥問題，不知道還有沒有調優的可能。
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
select new
{
p.BranjobId,
p.Jobno,
a.Jobna,
p.Planqty,
p.Overtime
}
into x
group x by new
{
x.BranjobId,
x.Jobno,
x.Jobna,
x.Planqty,
x.Overtime
}
into y
select new
{
y.Key.BranjobId,
y.Key.Jobno, //職位編號
y.Key.Jobna, //職位名稱
y.Key.Planqty, //定編人數
Jobqty = y.Count(), //實際人數
Lackqty = y.Key.Planqty - y.Count(), //欠缺人數
Overtime = y.Sum(s => s.Overtime) //固定加班小時
}).ToList();
return LinqToDataTable(BranjobObj);
}
}
catch(Exception ex)
{
throw ex;
}
}
跟蹤結果：
exec sp_executesql N'SELECT
[GroupBy1].[K1] AS [BranjobId],
[GroupBy1].[K2] AS [Jobno],
[GroupBy1].[K5] AS [Jobna],
[GroupBy1].[K3] AS [Planqty],
[GroupBy1].[A1] AS [C1],
[GroupBy1].[K3] - [GroupBy1].[A2] AS [C2],
[GroupBy1].[A3] AS [C3]
FROM ( SELECT
[Extent1].[BranjobId] AS [K1],
[Extent1].[Jobno] AS [K2],
[Extent1].[Planqty] AS [K3],
[Extent1].[Overtime] AS [K4],
[Extent2].[Jobna] AS [K5],
COUNT(1) AS [A1],
COUNT(1) AS [A2],
SUM([Extent1].[Overtime]) AS [A3]
FROM [dbo].[BranJob] AS [Extent1]
INNER JOIN [dbo].[JobSet] AS [Extent2] ON [Extent1].[Jobno] = [Extent2].[Jobno]
WHERE [Extent1].[Branno] = @p__linq__0
GROUP BY [Extent1].[BranjobId], [Extent1].[Jobno], [Extent1].[Planqty], [Extent1].[Overtime], [Extent2].[Jobna]
) AS [GroupBy1]',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'009'
輸出結果也是正確的，只是感覺效率低的瞞多，感覺性能還不如直接sql陳述句執行。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/270382.html
標籤：C#
上一篇：求解決 CS0433: 型別“ASP.usercontrols_templateresource_templateprocolumn_ascx”
下一篇：撰寫windows服務時，mainwindowtitle無法獲取存在主視窗的應用程式主視窗標題，請問是什么原因，有沒有什么解決辦法？