在 Entity Framework 中，如何用 LInq 表达式生成带聚合函数的 SQL，同时分组以及排序

先给大家说一下我的需求。

我要生成的 SQL 语句如下：

select r.UserID, r.UserName, max(StudentPoint) as ResultStudentPoint, max(CreateTime) as ResultCreateTime  from CR_UserRecite r     
where PaperId='90F8533E-B2E0-42E2-8122-3A3FE3DDB49B'
group by r.UserID,r.UserName order by ResultStudentPoint desc, ResultCreateTime desc

其中 PaperID 是需要动态查询了，这里为了演示就写死了。

经过在 Entity Framework 4.1 Code First 中尝试，写出如下的代码，满足了需求。

            // 其中 context 是 System.Data.Entity.DbContext 的类， TodayRank 是自己定义的类型
            // 下面代码是意思就是搜索特定 PagerID 的 UserRecite ，根据 r.UserID, r.UserName 分组，取每个人的最高分，根据最高分（降序）和时间（降序）排序
            IQueryable<TodayRank> query = from r in context.UserRecites
                                          where r.PaperID == new Guid("90f8533e-b2e0-42e2-8122-3a3fe3ddb49b&a=1")
                                          group r by new { r.UserID, r.UserName } into result
                                          let scoreMax = result.Max(n => n.StudentPoint)
                                          let createdTimeMax = result.Max(n => n.CreateTime)
                                          orderby scoreMax descending, createdTimeMax descending
                                          select new TodayRank()
                                          {
                                              UserID = result.Key.UserID,
                                              UserName = result.Key.UserName,
                                              Score = scoreMax,
                                              CreateTime = createdTimeMax
                                          };
            List<TodayRank> ltTodayRanks = query.Take(10).ToList();

 

最后生成的 SQL 基本上也是满足需求的，只是代码比较多！

看来 EF 还蛮够智能的，赞！

exec sp_executesql N'SELECT TOP (10) 
[Project3].[UserID] AS [UserID], 
[Project3].[UserName] AS [UserName], 
[Project3].[C2] AS [C1], 
[Project3].[C3] AS [C2]
FROM 
(
    SELECT 
    [Project2].[C1] AS [C1], 
    [Project2].[UserID] AS [UserID], 
    [Project2].[UserName] AS [UserName], 
    CAST( [Project2].[C1] AS decimal(19,0)) AS [C2], 
    [Project2].[C2] AS [C3]
    FROM 
    ( 
        SELECT 
        [Project1].[C1] AS [C1], 
        [Project1].[UserID] AS [UserID], 
        [Project1].[UserName] AS [UserName], 
        (
            SELECT 
            MAX( [Extent2].[CreateTime] ) AS [A1]
            FROM [dbo].[CR_UserRecite] AS [Extent2]
            WHERE ( [Extent2].[PaperID] = @p__linq__0 ) AND 
            (
                [Project1].[UserID] = [Extent2].[UserID]
            ) 
            AND 
            (
                ( [Project1].[UserName] = [Extent2].[UserName] ) 
                OR 
                (
                    ( [Project1].[UserName] IS NULL ) 
                    AND ( [Extent2].[UserName] IS NULL )
                )
            )
        ) AS [C2]
        FROM 
        ( 
            SELECT 
            [GroupBy1].[A1] AS [C1], 
            [GroupBy1].[K1] AS [UserID], 
            [GroupBy1].[K2] AS [UserName]
            FROM 
            ( 
                SELECT 
                [Extent1].[UserID] AS [K1], 
                [Extent1].[UserName] AS [K2], 
                MAX( [Extent1].[StudentPoint] ) AS [A1]
                FROM [dbo].[CR_UserRecite] AS [Extent1]
                WHERE [Extent1].[PaperID] = @p__linq__0
                GROUP BY [Extent1].[UserID], [Extent1].[UserName]
            )  
            AS [GroupBy1]
            
        )  
        AS [Project1]
    )  
    AS [Project2]
)  
AS [Project3]
ORDER BY [Project3].[C1] DESC, 
[Project3].[C3] DESC',N'@p__linq__0 uniqueidentifier',@p__linq__0='90F8533E-B2E0-42E2-8122-3A3FE3DDB49B'

 

 

谢谢浏览！