Group Optimization In LINQ to Entities, by

entity-framework linq linq-to-entities sql

Question

I have this LINQ to Entities query.

        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 am aware that this query is rather "Evil," however it is only used in the admin interface. However, the SQL that it produced is utterly horrible. Look at this little one.


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] 

Although I don't need anybody to explain that query, it would be amazing to have some pointers on how to make it more efficient such that it just does a straightforward normal join. When I create the SQL myself, something similar still works well.

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

Also, I ADORE this website. Amazing usability and design! I hope getting some aid goes well.

1
1
9/26/2010 5:53:40 PM

Accepted Answer

You may connect the specified tables independently instead of joining all the tables in group by. Could 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 could be some mistakes, but it makes sense!

5
11/25/2008 2:32:14 PM

Popular Answer

I believe the grouping concept to be the issue. Instead of going through properties, try extracting the data first, then grouping by it.

In other words, try writing the LINQ as you would the SQL.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow