Workaround needed for EF Core performing GroupBy operations in memory instead of in SQL

c# entity-framework-core


I'm working in Entity Framework Core 1.1.0 (and upgrading is not an option at this point, due to breaking changes in later versions). My query is of the following form:

var q = db.MyTable
            .GroupBy(t => new { t.Field1 })
            .Select(g => new
                MaxField2 = g.Max(x => x.Field2)

In test code this works well and returns the expected data. But when deployed to a real environment, with real data, it times out. Why? Well, I put a sniffer on the SQL server, and here's the actual SQL:

SELECT [t].[Field1], [t].[Field2], [t].[Field3], [t].[Field4], [t].[Field5]
FROM [dbo].[MyTable] AS [t]
ORDER BY [t].[Field1]

Oh. Well that would explain it. EF is only compiling the query up to the .GroupBy() into SQL, thus attempting to load the entire contents of the table (some 17 million records at this time of writing) into memory, and the rest of the grouping and ordering is supposed to be done in memory.

Any suggestions how to rework this query so that the heavy lifting is done in SQL?

7/19/2017 9:05:41 AM

Accepted Answer

As @xanatos points out, this is is not supported in EF Core 1.1.0 (and not even 2.0.0). There is, however, a workaround, using literal SQL:

var q = db.MyTable
        .FromSql("select t.* from " +
                 "  (select distinct Field1 from MyTable) t0 " +
                 "cross apply " +
                 "  (select top 1 t.* from MyTable t " +
                 "  where t.Field1 = t0.Field1 " +
                 "  order by t.Field2 desc) t")                     
        .Select(t => new
            MaxField2 = t.Field2

Not the solution I'd hoped for, but it works a charm.

7/19/2017 12:26:36 PM

Popular Answer

Not supported in EF Core 1.1.0:

LINQ's GroupBy() operators can sometimes be translated to SQL's GROUP BY clauses, in particular when aggregate functions are applied in the projection.

Sadly it won't be supported even in EF Core 2.0.0.

Related Questions


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