How to convert this dynamic sql script into LinqToSql?

Question

How to convert this dynamic sql script into LinqToSql?

-- Create sample table

Create Table TEST
(DATES Varchar(6),
 EMPNO Varchar(5),
 STYPE Varchar(1),
 AMOUNT Int)

-- Insert sample data

Insert TEST Select '200605',  '02436',     'A',        5
Union All Select '200605',  '02436',     'B',        3
Union All Select '200605',  '02436',     'C',        3 
Union All Select '200605',  '02436',     'D',        2
Union All Select '200605',  '02436',     'E',        9
Union All Select '200605',  '02436',     'F',        7
Union All Select '200605',  '02436',     'G',        6
Union All Select '200605',  '02438',     'A',        7
Union All Select '200605',  '02438',     'B',        8
Union All Select '200605',  '02438',     'C',        0 
Union All Select '200605',  '02438',     'D',        3
Union All Select '200605',  '02438',     'E',        4
Union All Select '200605',  '02438',     'F',        5
Union All Select '200605',  '02438',     'G',        1
GO



-- the sql script which I want to convert into LinqToSql

Declare @S Varchar(1000)
Set @S=''

Select @S=@S+',SUM(Case STYPE When '''+STYPE+''' Then AMOUNT Else 0 End) As '+STYPE 
From (Select Distinct STYPE From TEST) A Order By STYPE

Set @S='Select DATES,EMPNO'+@S+' From TEST Group By DATES,EMPNO Order By DATES,EMPNO'
EXEC(@S)
GO

Answer 1

You don't need dynamic sql to compute those sums. This query computes every number you need.

SELECT Dates, EmpNo, SType, SUM(Amount)
FROM Temp
GROUP BY Dates, EmpNo, SType
ORDER BY Dates, EmpNo, SType

and it is easily translated into LinqToSql.

var query = 
  from rec in db.Temps
  group rec by new {rec.Dates, rec.EmpNo, rec.SType} into g
  order by g.Key.Dates, g.Key.EmpNo, g.Key.SType
  select new
  {
    Dates = g.Key.Dates,
    EmpNo = g.Key.EmpNo,
    SType = g.Key.SType,
    TheSum = g.Sum()
  };

Once you have those sums in memory, then you can do whatever result shaping you please.