LINQ to SQL 模拟实现 ROW_NUMBER() OVER(ORDER BY ...) 的功能

Ø  前言

本来是想使用 LINQ 实现类似 SQL: ROW_NUMBER() OVER(ORDER BY …) 的功能,但是貌似 LINQ 不支持,反正没找到解决办法,无奈使用了LINQ Select() 方法实现。

 

1)   需求,需要实现一下 SQL:

SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY T.TotalMoney DESC) AS SN, * FROM

(

    SELECT T2.Name, SUM(T2.Amount) AS TotalAmount, SUM(T2.TotalMoney) AS TotalMoney FROM Orders AS T1

    LEFT JOIN OrderDetail AS T2 ON(T2.OrderId = T1.Id)

    WHERE 1=1

    AND T1.SalesUserId = 105

    AND T1.PayStatusId = 2

    AND (T1.OrderStatusId >=2 AND T1.OrderStatusId <= 8)

    GROUP BY T2.Name

) AS T

 

2)   使用 LINQ Select() 方法

var result = (from t1 in DataContext.Orders

              join t2 in DataContext.OrderDetail on t1.Id equals t2.OrderId into temp1

              from t12 in temp1.DefaultIfEmpty()

              where t1.SalesUserId == salesUserId && t1.PayStatusId == (int)OrderPayStates.Paid

                  && (t1.OrderStatusId >= (int)OrderStates.Undelivered

                  && t1.OrderStatusId <= (int)OrderStates.ReturnComplete)

              group t12 by t12.Name into group1

              select new

              {

                  Name = group1.Key,

                  TotalAmount = group1.Sum(o => o.Amount),

                  TotalMoney = group1.Sum(o => o.TotalMoney)

              }).OrderByDescending(o => o.TotalMoney).Take(10).AsEnumerable()

              .Select((obj, index) => new

              {

                  Name = obj.Name,

                  TotalAmount = obj.TotalMoney,

                  TotalMoney = obj.TotalMoney,

                  SN = index + 1

              }).ToList();

l  注意:必须加上 AsEnumerable() 方法,且 AsEnumerable() Select() 方法都为延迟查询方法。

 

3)   生成SQL:

exec sp_executesql N'SELECT TOP (10)

    [Project1].[C3] AS [C1],

    [Project1].[Name] AS [Name],

    [Project1].[C1] AS [C2],

    [Project1].[C2] AS [C3]

    FROM ( SELECT

        [GroupBy1].[A1] AS [C1],

        [GroupBy1].[A2] AS [C2],

        1 AS [C3],

        [GroupBy1].[K1] AS [Name]

        FROM ( SELECT

            [Extent2].[Name] AS [K1],

            SUM([Extent2].[Amount]) AS [A1],

            SUM([Extent2].[TotalMoney]) AS [A2]

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

            LEFT OUTER JOIN [dbo].[OrderDetail] AS [Extent2] ON [Extent1].[Id] = [Extent2].[OrderId]

            WHERE ([Extent1].[SalesUserId] = @p__linq__0) AND (2 = [Extent1].[PayStatusId]) AND ([Extent1].[OrderStatusId] >= 2) AND ([Extent1].[OrderStatusId] <= 8)

            GROUP BY [Extent2].[Name]

        )  AS [GroupBy1]

    )  AS [Project1]

ORDER BY [Project1].[C2] DESC',N'@p__linq__0 bigint',@p__linq__0=105

 

4)   最终结果(这是我想要的)

clip_image001

转载于:https://www.cnblogs.com/abeam/p/6947140.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值