title: Optimize Group By in LINQ to Entities
body: <p>I have this query in LINQ to Entities.</p>
  
  <pre><code>        var query = (from s in db.ForumStatsSet
                       where s.LogDate &gt;= date1 &amp;&amp; s.LogDate &lt;= 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
                       });
  </code></pre>
  
  <p>I know it is kind of an "Evil" query but it is only used in a admin interface. But the SQL it generated is absolutely horrifying. Have a look at this baby.</p>
  
  <pre><code>
  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] </pre>
  
  <p>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.</p>
  
  <pre>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
  </code></pre>
  
  <p>Btw, i LOVE this site. Amazing design and usability! Hope it works good to get some help to :)</p>