做商城网站的,ip形象设计,免费中介系统房产软件,网络服务启动失败前言 本示例主要实现 LINQ 查询#xff0c;先分组#xff0c;再聚合#xff0c;最后在排序。示例很简单#xff0c;但是使用 LINQ 却生成了不同的 SQL 实现。 1) 采用手动编写 SQL 实现 SELECT ROW_NUMBER() OVER(ORDER BY T.RealTotal DESC) AS SN, * FROM ( SELECT (S…Ø 前言 本示例主要实现 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.IdT1.UserId) AS TradeName, (SELECT UserName FROM UserInfo AS T2 WHERE T2.IdT1.UserId) AS UserName, SUM(T1.RealTotal) AS RealTotal FROM Orders AS T1 WHERE 11 AND T1.SalesUserId131 AND T1.PayStatusId2 AND (T1.PayTime2017-05-01 00:00:00 AND T1.PayTime2017-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 NSELECT [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,Np__linq__0 bigint,p__linq__1 datetime2(7),p__linq__2 datetime2(7),p__linq__0131,p__linq__12017-05-01 00:00:00,p__linq__22017-05-31 23:59:59 4) 结果集两种实现方式相同 Ø 总结可以看出 LINQ 生成的查询语句中在子查询中使用 WHERE DICTINCT 实现并没有GROUP BY。两者执行耗时也没有什么区别只是 LINQ 使用了过多的派生表增加了理解的难度性。转载于:https://www.cnblogs.com/abeam/p/6970722.html
