Group Optimization In LINQ to Entities, by

entity-framework linq linq-to-entities sql

Question

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 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.


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 good to get some help to :)

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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

Instead of joining all tables in group by you can join the given tables by 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!

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

Popular Answer

I think the problem is with the grouping construct. Try extract the data first (so there is not need to go via properties), then group by the extracted data.

IOW, try write the LINQ like would for the SQL.

leppie

Related Questions

In LINQ to Entities, group by weeks

You should be able to force the query to use LINQ to Objects rather than LINQ to Entities for the grouping, using a call to the ...AsEnumerable... extension method....Try the following:...DateTime firstDay = GetFirstDayOfFirstWeekOfYear(); var userTimes = from t in context.TrackedTimes.Where(myPredicateHere).AsEnumerable() group t by new {...
c# entity-framework linq linq-to-entities
asked by Noldorin

LINQ Optimization Entity Framework's Any() method

Try adding index to the database table "SettingGroups", by Group & Category....BTW, does this produce similar sql?...var ok = context.SettingGroups.Any(sg => sg.Group==settingGroup && sg.Category==settingCategory);
entity-framework linq performance
asked by Tuomas Hietanen

Join two tables using LINQ to Entities, then group and total the columns from each tables.

First of all, you should setup navigation properties for relevant foreign keys (for example, 1:N relation of target to transactions, as well as from transaction to products). If setup correctly, the database design/EF schema should take most of the work from your hands. After that you can go:...(from target in targets where target.Year == year &&...
c# entity-framework linq linq-to-entities
asked by Femaref

The LINQ expression node type 'Invoke' is not supported in the entity framework's LINQ to Entities.

This problem can be solved using the AsExpandable() method present in LINQKIT by Joe Albahari. He's the same creator of ...PredicateBuilder... that I see you're using....If querying with Entity Framework, change the last line to this:...return objectContext.Products.AsExpandable().Where(predicate);...You can grab LINQKIT DLL ...here... or install i...
c# entity-framework expression linq predicate
asked by Leniel Maccaferri

LINQ groups by then orders result groups

In fact, you don't want to group by short code, you want to order by them. So the following query should do the trick:...rawData.Provider.CreateQuery<PDFDocument>(qb.rootExperession) .ToList() .OrderBy(b => b.ShortCode) .ThenBy(b => b.UploadDate) .ToList() ...Edit... If you really want to use a GroupBy, you can do so this way:... rawData.Provider.C...
.net c# entity-framework linq
asked by Falanwe

LINQ to Entities group by and Count()

group Key can't be null...var results = ctx.Rs.Where(r => r.QK != null) .GroupBy(r => r.QK) .Select(gr => new { Key = (int)gr.Key, Count = gr.Count() } .ToList(); ...PS....Mostly, You don't need 'JOIN' syntax in Entity Framework. see: ...Loading Related Entities...Writing descriptive-meaningful variable names would significantly improve...
c# count entity-framework group-by linq
asked by aifarfa

Entity Framework with Linq, inner Join, Group By, Order By

From data you provided, I think query should look like...from z in db.Zeiterfassung join f in db.Firma on z.FirmenID equals f.ID join t in db.Taetigkeit on z.TaetigkeitID equals t.ID select new { f.Name, t.Taetigkeit, z.Zeit) into x group x by new { x.Taetigkeit, f.Name } into g select new { CompanyName = g.Key.Name, SkillName = g.Key.Taetigk...
c# entity-framework linq linq-to-entities sql
asked by Sergey Berezovskiy

Entity Framework - Linq query with order by and group by

It's method syntax (which I find easier to read) but this might do it...Updated post comment...Use ....FirstOrDefault()... instead of ....First()...With regard to the dates average, you may have to drop that ordering for the moment as I am unable to get to an IDE at the moment...var groupByReference = context.Measurements ...
c# entity-framework linq mysql
asked by NinjaNye

LINQ to SQL - order by, group by and order by each group with skip and take

Just re-index your list results and remove the index before returning....var query = grades.GroupBy(student => student.Name) .Select(group => new { Name = group.Key, Students = group.OrderByDescending(x => x.Grade) }) .OrderBy(group => group.Students.FirstOrDefault().Grade) ...
c# entity-framework-6 linq linq-to-sql
asked by David

Entity Framework: How to optimize this below linq query?

Simple. DO not load all the data....Teacher -> Fraction -> TestRPepetition multiplies the amount odf data you pull....Ef is meant to pull the data you need, now load a lot of related data into memory IN CASE YOU MAY NEED IT ONE DAY....Pull the minimum amount of data you need in this moment, go back to the database when you need more. Optimize from ...
.net c# entity-framework-6 linq performance
asked by TomTom

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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