c# - 按多个相关实体计数的 Entity Framework 顺序 - 效率

Question

所以我有一个包含这些相关实体的主题。

 - List<Posts>
 - List<Votes>
 - List<Views>

我有以下查询。我想根据特定日期期间内 3 个相关实体的数量提取和排序热门主题。

var topics = _context.Topic
.OrderByDescending(x => x.Posts.Count(c => c.DateCreated >= from && c.DateCreated <= to))
 .ThenByDescending(x => x.Votes.Count(c => c.DateCreated >= from && c.DateCreated <= to))
.ThenByDescending(x => x.Views.Count(c => c.DateCreated >= from && c.DateCreated <= to))
.Take(amountToShow)
.ToList();

我正在寻找执行上述操作的最有效查询？我正在做的是使用 EntityFramework 执行此操作的最佳方法吗？还是我遗漏了什么？

感谢任何帮助。

Answer 1

如果您将上述代码放入LINQPad , 或者用 profiler 检查一下，你会发现它可能会生成类似下面的 SQL:

SELECT TOP @amountToShow [t0].[id] --and additional columns
FROM [Topic] AS [t0]
ORDER BY (
    SELECT COUNT(*)
    FROM [Posts] AS [t1]
    WHERE ([t1].DateCreated >= @from AND [t1].DateCreated <= @to) 
        AND ([t1].[topidId] = [t0].[id])
    ) DESC, (
    SELECT COUNT(*)
    FROM [Votes] AS [t2]
    WHERE ([t2].DateCreated >= @from AND [t2].DateCreated <= @to) 
        AND ([t2].[topicId] = [t0].[id])
    ) DESC , (
    SELECT COUNT(*)
    FROM [Views] AS [t3]
    WHERE ([t3].DateCreated >= @from AND [t3].DateCreated <= @to) 
        AND ([t3].[topicId] = [t0].[id])
    ) DESC
GO

您可以尝试稍微重写 SQL 以 GROUP 子查询的结果并将它们 LEFT JOIN 到原始表，这似乎确实快了 2 倍数据库本身:

SELECT TOP @amountToShow [t0].[id] --etc
FROM [Topic] AS [t0]
LEFT JOIN 
    (SELECT topicId, COUNT(*) AS num FROM Posts p 
    WHERE [p].DateCreated >= @from AND .DateCreated <= @to 
    GROUP BY topicId) [t1]
ON t0.id = t1.topicId
LEFT JOIN 
    (SELECT topicId, COUNT(*) AS num FROM Votes vo 
    WHERE [vo].DateCreated >= @from AND [vo].DateCreated <= @to 
    GROUP BY topidId) [t2]
ON t0.id = t2.topicId
LEFT JOIN 
    (SELECT topicId, COUNT(*) AS num FROM Views vi 
    WHERE [vi].DateCreated >= @from AND [vi].DateCreated <= @to 
    GROUP BY topicId) [t3]
ON t0.id = t3.topicId
ORDER BY t1.num DESC, t2.num DESC, t3.num DESC

但是让 LINQ 生成这样的代码充其量是不确定的。执行 LEFT JOIN 并不是它的强项，使用现有的技术可能会生成使用 CROSS APPLY 和/或 的 SQL OUTER APPLY 而不是，并且可能比您当前的代码慢或慢。

如果您担心速度，您可以考虑将经过微调的 SQL 放入 View 中，这样您就知道正在使用的查询是您想要的查询。

还要记住，您或其他人以后必须返回并维护此代码。您当前的 linq 语句非常简单易懂。复杂的查询将更难维护，将来需要做更多的工作才能改变。