有没有办法优化这个LINQ到实体查询？

Question

我被要求制作一份由相当复杂的 SQL 查询驱动的报告，该查询针对一个 SQL Server 数据库。由于报告所在的站点已经使用了 Entity Framework 4.1，所以我决定尝试使用 EF 和 LINQ 来编写查询：

var q = from r in ctx.Responses
                    .Where(x => ctx.Responses.Where(u => u.UserId == x.UserId).Count() >= VALID_RESPONSES)
                    .GroupBy(x => new { x.User.AwardCity, x.Category.Label, x.ResponseText })
         orderby r.FirstOrDefault().User.AwardCity, r.FirstOrDefault().Category.Label, r.Count() descending
         select new
         {
             City = r.FirstOrDefault().User.AwardCity,
             Category = r.FirstOrDefault().Category.Label,
             Response = r.FirstOrDefault().ResponseText,
             Votes = r.Count()
         };

这个查询统计选票，但仅限于提交了一定数量的最低要求票数的用户。

从性能角度来看，这种方法是完全失败的，因此我们转而使用ADO.NET，查询运行得非常快。我确实查看了使用SQL Profiler生成的LINQ SQL，尽管像往常一样看起来很糟糕，但我没有看到任何提示如何优化LINQ语句以使其更有效率。

以下是纯TSQL版本：

WITH ValidUsers(UserId)
AS
(
    SELECT UserId
    FROM Responses
    GROUP BY UserId
    HAVING COUNT(*) >= 103
)
SELECT d.AwardCity
    , c.Label
    , r.ResponseText
    , COUNT(*) AS Votes
FROM ValidUsers u
JOIN Responses r ON r.UserId = u.UserId
JOIN Categories c ON r.CategoryId = c.CategoryId
JOIN Demographics d ON r.UserId = d.Id
GROUP BY d.AwardCity, c.Label, r.ResponseText
ORDER BY d.AwardCity, s.SectionName, COUNT(*) DESC

我想知道的是：这个查询太复杂了，EF和LINQ无法有效地处理它，还是我错过了什么技巧？

- Paul Keister

---

我猜测所有的FirstOrDefault都会引起这个问题。你尝试在groupby之前添加 let response = r.First() 吗？或者交换Select和OrderBy的位置？就像这样https://dev59.com/aW445IYBdhLWcg3wJ298#5013740 - jessehouwing

有没有像User.Responses这样的导航属性？ - Gert Arnold

@jessehouwing 使用 let response 可以显著提高性能，尽管 LINQ 版本仍比 ADO.NET 慢得多。如果您将此作为答案输入，我至少会点赞。我在使用 Jon Skeet 的选择和排序交换策略时遇到了问题，主要是我无法弄清如何在这个结构中获取计数。 - Paul Keister

我也很难理解这个查询。如果您分享纯SQL代码，可能会有所帮助。 - jessehouwing

@GertArnold 是的，我确认添加导航属性也可以提高三倍效率。 - Paul Keister

Answer 1

使用 let 关键字来减少 r.First() 的数量可能会提高性能。但这可能还不够。

 var q = from r in ctx.Responses
                .Where()
                .GroupBy()
     let response = r.First()
     orderby response.User.AwardCity, response.Category.Label, r.Count() descending
     select new
     {
         City = response.User.AwardCity,
         Category = response.Category.Label,
         Response = response.ResponseText,
         Votes = r.Count()
     };

- jessehouwing

---

我将此标记为答案，因为它的效果与@GertArnold提出的导航属性解决方案大致相同，但Gert尚未将其评论发布为答案（抱歉Gert，我会给你点赞）。应该注意的是，即使应用了两种优化，ADO.NET仍然更快，但修订后的LINQ比原来快了几个数量级。 - Paul Keister

Answer 2

也许这个改变可以提高性能，通过移除 where 子句中的嵌套 SQL 查询来实现。

首先获取每个用户的投票，并将它们放入一个字典中。

var userVotes = ctx.Responses.GroupBy(x => x.UserId )
                             .ToDictionary(a => a.Key.UserId,  b => b.Count());

var cityQuery = ctx.Responses.ToList().Where(x => userVotes[x.UserId] >= VALID_RESPONSES)
               .GroupBy(x => new { x.User.AwardCity, x.Category.Label, x.ResponseText })
               .Select(r => new
                       {
                           City = r.First().User.AwardCity,
                           Category = r.First().Category.Label,
                           Response = r.First().ResponseText,
                           Votes = r.Count()
                       })
               .OrderByDescending(r => r.City, r.Category, r.Votes());

- Esteban Elverdin

---

我已经尝试过这种方法了。以下是错误信息：LINQ to Entities 不识别 'Int32 get_Item(Int32)' 方法，因此无法将此方法转换为存储表达式。 - Paul Keister

我觉得我忘了在ctx.Responses之后添加ToList()，导致了ctx.Responses.ToList().Where(...)。 - Esteban Elverdin

我认为核心问题在于 EF LINQ 驱动程序无法在构建实体 SQL 时使用对外部字典的引用。我非常确定我逐字复制并粘贴了您的解决方案，我所做的只是修复了第一个 ToDictionary lambda 的语法。 - Paul Keister

我使用内存对象进行了测试，结果很好。我认为添加ToList将会把所有记录都加载到内存中，然后您就可以使用字典了。我会尝试复制您的场景。 - Esteban Elverdin