Entity Framework Projection translation failure GroupBy

Question

I've been cleaning up some Entity LINQ Method queries, to try and optimize generated queries in a project, and was attempting to avoid a JOIN, by GROUP BY of a foreign key. But ran into an unexpected projection failure with Entity Framework.

Using the demo database in LINQPAD 7, here's an example of the original query and how I'm attempting to optimize it.

Works:

Albums
    .Select(a => a.Tracks.OrderBy(t => t.Milliseconds).First())
    .Select(g => new { g.Name, g.Composer })
    .Dump();

Runtime Exception:

Tracks.GroupBy(t => t.AlbumId)
    .Select(g => g.OrderBy(t => t.Milliseconds).First())
    .Select(g => new { g.Name, g.Composer })
    .Dump();

Now I understand I can do the following, but it makes a very odd SQL query (that joins to itself) and isn't as clean to read as the failing example.

Tracks.GroupBy(t => t.AlbumId)
    .Select(g => g.OrderBy(t => t.Milliseconds).Select(g => new { g.Name, g.Composer }).First())
    .Dump();

Can anyone explain why Entity Framework doesn't like the GroupBy followed by a selection, and a projection? While a similar join to a table works fine?

The optimal query I'm looking for is:

SELECT Name, Composer
FROM (
    SELECT Name, Composer, ROW_NUMBER() OVER(PARTITION BY AlbumId ORDER BY Milliseconds) AS row
    FROM Track
)
WHERE row <= 1

Answer 1

I have noticed that if you change query order, the exception does not occur...

Change this:

Tracks
    .GroupBy(t => t.AlbumId)
    .Select(g => g.OrderBy(t => t.Milliseconds).First())
    .Select(g => new { g.Name, g.Composer })
    .Dump();

With this:

Tracks
    .OrderBy(x => x.Milliseconds)
    .GroupBy(x => x.AlbumId)
    .Select(x => new { 
        x.FirstOrDefault().Name, 
        x.FirstOrDefault().Composer 
    })

I think it's because you're trying to sort lists within groups, when you should be sorting all results first and then grouping, though I'm not entirely sure. This, if it wasn't linq to sql, would work just fine.