LINQ to SQL 實現 GROUP BY、聚合、ORDER BY

?? 前言

本示例主要實現 LINQ 查詢，先分組，再聚合，最后在排序。示例很簡單，但是使用 LINQ 卻生成了不同的 SQL 實現。

?

1)?? 采用手動編寫 SQL 實現

SELECT ROW_NUMBER() OVER(ORDER BY T.RealTotal DESC) AS SN, * FROM

(

? ??? SELECT (SELECT TradeName FROM UserInfo AS T2 WHERE T2.Id=T1.UserId) AS TradeName, (SELECT UserName FROM UserInfo AS T2 WHERE T2.Id=T1.UserId) AS UserName, SUM(T1.RealTotal) AS RealTotal FROM Orders AS T1

? ??? WHERE 1=1 AND T1.SalesUserId=131 AND T1.PayStatusId=2

? ??? AND (T1.PayTime>='2017-05-01 00:00:00' AND T1.PayTime<='2017-05-31 23:59:59')

? ??? GROUP BY T1.UserId

) AS T

?

2)?? LINQ 實現

var query = (from t1 in DataContext.Orders

where t1.SalesUserId == salesUserId && (t1.PayTime >= mbdt && t1.PayTime <= medt)

?????????????? ?group t1 by t1.UserId into g1

??????????????? select new

??????????????? {

??????????????????? TradeName = (from t2 in DataContext.UserInfo

??????????????????????????????? where t2.id == g1.Key

??????????????????????????????? select t2.TradeName).FirstOrDefault(),

??????????????????? UserName = (from t2 in DataContext.UserInfo

??????????????????????????????? where t2.id == g1.Key

??????????????????????????????? select t2.userName).FirstOrDefault(),

??????????????????? RealTotal = g1.Sum(o => o.RealTotal)

??????????????? }

??????????????? into v1

??????????????? orderby v1.RealTotal descending

??????????????? select v1).AsEnumerable().Select((o, i) =>

??????????????? new CustomOrderAmountRankingModel

??????????????? {

??????????????????? Sn = i + 1,

??????????????????? CustomerShopName = o.TradeName,

??????????????????? RegisterUserName = o.UserName,

??????????????????? OrderAmount = o.RealTotal

??????????????? });

?

3)?? 生成SQL

exec sp_executesql N'SELECT

??? [Project10].[C1] AS [C1],

??? [Project10].[C2] AS [C2],

??? [Project10].[C3] AS [C3],

??? [Project10].[C4] AS [C4]

??? FROM ( SELECT

??????? 1 AS [C1],

??????? [Project9].[C1] AS [C2],

??????? [Project9].[C2] AS [C3],

??????? [Project9].[C3] AS [C4]

??????? FROM ( SELECT

??????????? [Project8].[C1] AS [C1],

??????????? [Project8].[C2] AS [C2],

??????????? (SELECT

??????????????? SUM([Extent4].[RealTotal]) AS [A1]

??????????????? FROM [dbo].[Orders] AS [Extent4]

??????????????? WHERE ([Extent4].[SalesUserId] = @p__linq__0) AND ([Extent4].[PayTime] >= @p__linq__1) AND ([Extent4].[PayTime] <= @p__linq__2) AND ([Project8].[UserId] = [Extent4].[UserId])) AS [C3]

??????????? FROM ( SELECT

??????????????? [Project7].[UserId] AS [UserId],

??????????????? [Project7].[C1] AS [C1],

??????????????? [Project7].[C2] AS [C2]

??????????????? FROM ( SELECT

??????????????????? [Project5].[UserId] AS [UserId],

???? ?????????????? [Project5].[C1] AS [C1],

??????????????????? (SELECT TOP (1)

??????????????????????? [Extent3].[userName] AS [userName]

??????????????????????? FROM [dbo].[UserInfo] AS [Extent3]

??????????????????????? WHERE [Extent3].[id] = [Project5].[UserId]) AS [C2]

??????????????????? FROM ( SELECT

??????????????????????? [Project4].[UserId] AS [UserId],

??????????????????????? [Project4].[C1] AS [C1]

??????????????????????? FROM ( SELECT

??????????????????????????? [Project2].[UserId] AS [UserId],

??????????????????????????? (SELECT TOP (1)

??????????????????????????????? [Extent2].[TradeName] AS [TradeName]

??????????????????????????????? FROM [dbo].[UserInfo] AS [Extent2]

???????????? ?????????????????? WHERE [Extent2].[id] = [Project2].[UserId]) AS [C1]

??????????????????????????? FROM ( SELECT

??????????????????????????????? [Distinct1].[UserId] AS [UserId]

??????????????????????????????? FROM ( SELECT DISTINCT

???????????????????? ?????????????? [Extent1].[UserId] AS [UserId]

??????????????????????????????????? FROM [dbo].[Orders] AS [Extent1]

??????????????????????????????????? WHERE ([Extent1].[SalesUserId] = @p__linq__0) AND ([Extent1].[PayTime] >= @p__linq__1) AND ([Extent1].[PayTime] <= @p__linq__2)

??????????????????????????????? )? AS [Distinct1]

??????????????????????????? )? AS [Project2]

??????????????????????? )? AS [Project4]

??????????????????? )? AS [Project5]

??????????????? )? AS [Project7]

??????????? )? AS [Project8]

??????? )? AS [Project9]

??? )? AS [Project10]

ORDER BY [Project10].[C4] DESC',N'@p__linq__0 bigint,@p__linq__1 datetime2(7),@p__linq__2 datetime2(7)',@p__linq__0=131,@p__linq__1='2017-05-01 00:00:00',@p__linq__2='2017-05-31 23:59:59'

?

4)?? 結果集（兩種實現方式相同）

?

?? 總結：可以看出 LINQ 生成的查詢語句中，在子查詢中使用 WHERE + DICTINCT 實現，并沒有GROUP BY。兩者執行耗時也沒有什么區別，只是 LINQ 使用了過多的派生表，增加了理解的難度性。