The Entity Framework 6. X realize linkage count and group by SQL query-CodePudding

Has the following SQL statement:

 
The select p.B ranjobId 
, p.J obno 
The SAN Antonio obna 
, p.P lanqty 
, jobqty=count (p.J obno) 
, lackqty=p.P lanqty - count (p.J obno) 
, Overtime=sum (p.O vertime) 

The from BranJob p 
Inner join JobSet c on p.j obno=SAN Antonio obno 
Where p.B ranno=@ branno 

Group by p.B ranjobId, p.J obno, SAN Antonio obna, p.P lanqty

Consult a great god, and how to implement written in EF method to realize it?

BranJob and JobSet entity existing mapping relation,

CodePudding user response:

Seems to get out, test results do not have what problem, also don't know is there the possibility of tuning,

 
Public static DataTable GetBranjob (string branno) 
{
Try 
{
Using (CusProContext db=new CusProContext ()) 
{
Var BranjobObj=(from p in the db. BranJob 
Where p.B ranno==branno 
Join a db in JobSet on p.J obno equals a. obno 
Select new 
{
P.B ranjobId, 
P.J obno, 
A. obna, 
P.P lanqty, 
P.O vertime 
} 

Into x 
Group x by new 
{
X.B ranjobId, 
X.J obno, 
X.J obna, 
X.P lanqty, 
X.o. vertime 
} 

Into y 
Select new 
{
Y.K ey BranjobId, 
Y.K. Ey Jobno,//position number 
Y.K ey Jobna,//job title 
Y.K ey Planqty,//the number of personnel allocation 
Jobqty=y.C mount (),//actual number 
Lackqty=y.K ey. Planqty - y.C mount (),//defective number 
Overtime=y.S um (s=& gt; S.O vertime)//fixed overtime hours 
}). ToList (); 

Return LinqToDataTable (BranjobObj); 
} 
} 
The catch (Exception ex) 
{
Throw the ex. 
} 
}

Tracking results:

 
The 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] 
The FROM (SELECT 
[Extent1]. [BranjobId] AS [K1], 
[Extent1]. [Jobno] AS [K2], 
[Extent1]. [Planqty] AS [K3], 
[Extent1]. [Overtime] AS [K4], 
[Extent2]. [Jobna] AS [K5], 
COUNT (1) the AS [A1], 
COUNT (1) the AS [A2], 
The SUM ([Extent1]. [Overtime]) AS [A3] 
The 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'

The output is correct, just feel like low efficiency from many, feel less direct SQL statement execution performance,