按多个相关实体计数排列的实体框架顺序 - 效率

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 中，或者用探查器检查它，你会发现它可能会生成类似于以下 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和/或OUTER APPLY的SQL来代替，并且可能比你当前的代码一样慢或更慢。

如果您担心速度，可以考虑将微调的 SQL 放入视图中，以便知道正在使用的查询是您想要的查询。

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