Optimize the LINQ to Entities group
I have this query in LINQ to Entities.
var query = (from s in db.ForumStatsSet
where s.LogDate >= date1 && s.LogDate <= date2
group s by new { s.Topic.topicID, s.Topic.subject, s.Topic.Forum.forumName, s.Topic.datum, s.Topic.Forum.ForumGroup.name, s.Topic.Forum.forumID } into g
orderby g.Count() descending
select new TopicStatsData
{
TopicId = g.Key.topicID,
Count = g.Count(),
Subject = g.Key.subject,
ForumGroupName = g.Key.name,
ForumName = g.Key.forumName,
ForumId = g.Key.forumID
});
I know this is a kind of "evil" request, but it is only used in the admin interface. But the SQL he created is absolutely terrifying. Look at this child.
exec sp_executesql N'SELECT TOP (50)
[Project6].[C1] AS [C1],
[Project6].[TopicId] AS [TopicId],
[Project6].[C4] AS [C2],
[Project6].[subject] AS [subject],
[Project6].[name] AS [name],
[Project6].[forumName] AS [forumName],
[Project6].[C2] AS [C3]
FROM ( SELECT
[Project5].[TopicId] AS [TopicId],
[Project5].[subject] AS [subject],
[Project5].[forumName] AS [forumName],
[Project5].[name] AS [name],
1 AS [C1],
CAST( [Project5].[forumID] AS int) AS [C2],
[Project5].[C1] AS [C3],
[Project5].[C2] AS [C4]
FROM ( SELECT
[Project4].[TopicId] AS [TopicId],
[Project4].[forumID] AS [forumID],
[Project4].[subject] AS [subject],
[Project4].[forumName] AS [forumName],
[Project4].[name] AS [name],
[Project4].[C1] AS [C1],
(SELECT
COUNT(cast(1 as bit)) AS [A1]
FROM [dbo].[tForumStats] AS [Extent14]
LEFT OUTER JOIN [dbo].[tTopic] AS [Extent15] ON [Extent14].[TopicId] = [Extent15].[topicID]
LEFT OUTER JOIN [dbo].[tForum] AS [Extent16] ON [Extent15].[forumID] = [Extent16].[forumID]
LEFT OUTER JOIN [dbo].[tForum] AS [Extent17] ON [Extent15].[forumID] = [Extent17].[forumID]
LEFT OUTER JOIN [dbo].[tForum] AS [Extent18] ON [Extent15].[forumID] = [Extent18].[forumID]
LEFT OUTER JOIN [dbo].[tForumGroup] AS [Extent19] ON [Extent18].[forumGroupID] = [Extent19].[forumGroupID]
LEFT OUTER JOIN [dbo].[tForum] AS [Extent20] ON [Extent15].[forumID] = [Extent20].[forumID]
LEFT OUTER JOIN [dbo].[tForumGroup] AS [Extent21] ON [Extent20].[forumGroupID] = [Extent21].[forumGroupID]
WHERE ([Extent14].[LogDate] >= @p__linq__25) AND ([Extent14].[LogDate] = @p__linq__25) AND ([Extent6].[LogDate] = @p__linq__25) AND ([Extent1].[LogDate]
I do not as anyone to explain that query but it would be great to get some tips on how to optimze the query so that it just do a simple regular join. Something like this works as fine if I write the SQL myself.
SELECT COUNT (*) AS NumberOfViews, s.topicid AS topicId, t.subject AS TopicSubject, g. [Name] AS ForumGroupName, f.forumName AS ForumName FROM tForumStats s join tTopic t on s.topicid = t.topicid join tForum f on f.forumid = t.forumid JOIN tForumGroup g ON f.forumGroupID = g.forumGroupID WHERE s. [LogDate] between @ date1 AND @ date2 group by s.topicid, t.subject, f.Forumname, t.Datum, g. [name] order by count (*) desc
Btw, I LOVE this site. Amazing design and usability! Hope it works well to help :)
Instead of joining all tables in a group, you can join these tables yourself. Can you try this;
from s in db.ForumStatsSet
join t in db.Topics on t.TopicId == s.TopicId
join f in db.Forums on f.ForumId == t.ForumId
join fg in db.ForumGroups on fg.ForumGroupId == f.ForumGroupId
where s.LogDate >= date1 && s.LogDate <=
group s by new { t.TopicId, t.subject, f.forumName, t.datum, fg.name, f.forumID } into g
orderby g.Count() descending
select new TopicStatsData
{
TopicId = g.Key.topicID,
Count = g.Count(),
Subject = g.Key.subject,
ForumGroupName = g.Key.name,
ForumName = g.Key.forumName,
ForumId = g.Key.forumID
});
ps: there may be some errors, but logically it should be correct!
I think the problem is with the grouping construct. Try to fetch the data first (so no need to go through the properties) and then group by the retrieved data.
IOW, try writing LINQ as you would for SQL.