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

                                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 使用了过多的派生表，增加了理解的难度性。