Yet another example of a situation when there was nothing wrong with Entity Framework design, but if you don't watch for certain things, the performance of the query will be dismal.

I had roughly this piece of code:

var grouppedEvents = query  
    .GroupBy(g => g.MessagePatternId)
    .OrderByDescending(s => s.Count())

... and I was thinking this is equivalent to

select top 100 l.MessagePatternId, count(*)  
from Logs l  
group by l.MessagePatternId  
order by count(*) desc  

but the query was taking several minutes and here's what SQL Profiler trace looked like (abbreviated):

    -- stuff
    FROM ( SELECT        
        CASE WHEN (-- stuff
        FROM   (SELECT TOP (100) [Project1].[C1] AS [C1], [Project1].[MessagePatternId] AS [MessagePatternId], [Project1].[C2] AS [C2]
            FROM ( SELECT 
                [GroupBy1].[A1] AS [C1], 
                [GroupBy1].[K1] AS [MessagePatternId], 
                1 AS [C2]
                FROM ( 
                    [Extent1].[MessagePatternId] AS [K1], 
                    COUNT(1) AS [A1]
                    FROM [dbo].[Logs] AS [Extent1]
                    WHERE ([Extent1].[TimeLogged] >= '2016-01-25 00:00:00') AND ([Extent1].[TimeLogged] <= '2016-01-25 23:59:59')
                    GROUP BY [Extent1].[MessagePatternId]
                )  AS [GroupBy1]
            )  AS [Project1]
            ORDER BY [Project1].[C1] DESC ) AS [Limit1]
        LEFT OUTER JOIN [dbo].[Logs] AS [Extent2] ON ([Extent2].[TimeLogged] >= @p__linq__0) AND ([Extent2].[TimeLogged] <= @p__linq__1) AND (([Limit1].[MessagePatternId] = [Extent2].[MessagePatternId]) OR (([Limit1].[MessagePatternId] IS NULL) AND ([Extent2].[MessagePatternId] IS NULL)))
    )  AS [Project2]
    ORDER BY [Project2].[C1] DESC, [Project2].[MessagePatternId] ASC, [Project2].[C3] ASC

Essentially it was trying to join to the same table by some very odd fields, but why?

It was simply because LINQ GroupBy returns IQueryable<IGrouping<int, Log>> and LINQ has to return all the rows from the table.

It turned out to be easy to fix by projection:

var grouppedEvents = query  
    .GroupBy(g => g.MessagePatternId)
    .OrderByDescending(s => s.Count())
    .Select(s => new {MessageId = s.Key, Count = s.Count()})

With that LINQ no longer needs to bring all the rows and the query turns out to be close to what I have written by hand, it has a couple of extra SELECTs, but performance-wise it is close..