EF Core在内存而非SQL中执行GroupBy操作所需的解决方法 [英] Workaround needed for EF Core performing GroupBy operations in memory instead of in SQL

问题描述

我正在使用Entity Framework Core 1.1.0（由于在以后的版本中发生重大更改，因此目前无法进行升级）。我的查询具有以下形式：

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
            {
                g.Key.Field1,
                MaxField2 = g.Max(x => x.Field2)
            })
            .ToList();

在测试代码中，此方法效果很好，并返回了预期的数据。但是，当部署到具有真实数据的真实环境时，它会超时。为什么？好吧，我在SQL服务器上放了一个嗅探器，下面是实际的SQL：

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]

哦。好吧，这可以解释它。 EF仅将查询 .GroupBy（）编译到SQL中，从而尝试加载表的 entire 内容（大约1700万记录的时间）写入内存，其余的分组和排序应该在内存中完成。

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?

推荐答案

@xanatos指出，EF Core 1.1.0不支持此功能。 （甚至不是2.0.0）。但是，有一种使用文字SQL的解决方法：

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
        {
            t.Field1,
            MaxField2 = t.Field2
        })
        .ToList();

不是我希望的解决方案，但它很有魅力。

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

这篇关于EF Core在内存而非SQL中执行GroupBy操作所需的解决方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！