linq 行转列

表结构如下：

 

id          NAME                 result
----------- -------------------- ------
1           jim                  胜
2           jim                  胜
3           jim                  负
4           Tom                  胜
5           Tom                  负
6           Sam                  负
7           Sam                  负

 

想得到的结果是：

 

NAME                 胜           负
-------------------- ----------- -----------
jim                  2           1
Sam                  0           2
Tom                  1           1

 

SQL可以这样来实现：

 

SELECT t1.name,

  (SELECTCOUNT(1)FROM temp t2 WHERE t2.NAME=t1.NAME AND t2.result='胜')AS'胜',

  (SELECTCOUNT(1)FROM temp t3 WHERE t3.NAME=t1.NAME AND t3.result='负')AS'负'

FROM

(SELECT NAME FROM temp GROUPBY NAME ) AS t1
name                胜          负
-------------------- ----------- -----------
jim                 2           1
Sam                 0           2
Tom                 1           1
(3row(s) affected)

 

用LINQ怎么来写呢？

 

var query=from t in Temps
    group t by t.NAMEinto m
    selectnew
    {
      NAME=m.Key,
      胜=m.Count(n=>n.Result=="胜"),
      负=m.Count(n=>n.Result=="负")
    };

 

这是LINQ翻译成的SQL语句:

 

-- Region Parameters

DECLARE

@p0 NVarChar(1) = N''

DECLARE

@p1 NVarChar(1) = N''
-- EndRegion


SELECT
[t1]
[NAME], (
SELECT COUNT(*)
FROM [temp] AS
[t2]
WHERE ([t2]
[result] = @p0) AND ((([t1]
[NAME] IS NULL) AND ([t2]
[NAME] IS NULL)) OR (([t1]
[NAME] IS NOT NULL) AND ([t2]
[NAME] IS NOT NULL) AND ([t1]
[NAME] = [t2]
[NAME])))
) AS [], (
SELECT COUNT(*)
FROM [temp] AS
[t3]
WHERE ([t3]
[result] = @p1) AND ((([t1]
[NAME] IS NULL) AND ([t3]
[NAME] IS NULL)) OR (([t1]
[NAME] IS NOT NULL) AND ([t3]
[NAME] IS NOT NULL) AND ([t1]
[NAME] = [t3]
[NAME])))
) AS
[]
FROM
(
SELECT [t0]
[NAME]
FROM [temp] AS
[t0]
GROUP BY [t0]
[NAME]
) AS
[t1]