EF6迁移至EFCore后相同LINQ查询生成不同SQL引发超时问题

问题背景 #

近期将旧项目从EF6、.NET Framework 4.8升级到EFCore、.NET 5.0，迁移完成后发现部分在EF6中运行正常的查询在EFCore中出现SQL超时问题。将EF6和EFCore的DbContext连接都导入LINQPad，对比分析二者生成的SQL语句。

实体类定义 #

[Table("Asset")]
public class Asset
{
    public Asset()
    {
        MonitoringLogs = new HashSet<MonitoringLog>();
    }

    [Key]
    public int AssetId { get; set; }

    public virtual ICollection<MonitoringLog> MonitoringLogs { get; set; }

}
[Table("MonitoringLog")]
public class MonitoringLog
{
    [Key]
    public int MonitoringLogId { get; set; }

    public DateTime LogUTCDateTime { get; set; }

    public string OtherProperty { get; set; }

    [ForeignKey(nameof(Asset))]
    public int AssetId { get; set; }
    public virtual Asset Asset { get; set; }
}

测试LINQ语句 #

this.Assets.SelectMany(r => r.MonitoringLogs.OrderByDescending(t => t.LogUTCDateTime).Take(1)).Dump();

生成SQL对比 #

EFCore生成SQL #

SELECT [t0].[MonitoringLogId], [t0].[AssetId], [t0].[LogUTCDateTime], [t0].[OtherProperty]
FROM [Asset] AS [a]
INNER JOIN (
    SELECT [t].[MonitoringLogId], [t].[AssetId], [t].[LogUTCDateTime], [t].[OtherProperty]
    FROM (
        SELECT [m].[MonitoringLogId], [m].[AssetId], [m].[LogUTCDateTime], [m].[OtherProperty], ROW_NUMBER() OVER(PARTITION BY [m].[AssetId] ORDER BY [m].[LogUTCDateTime] DESC) AS [row]
        FROM [MonitoringLog] AS [m]
    ) AS [t]
    WHERE [t].[row] <= 1
) AS [t0] ON [a].[AssetId] = [t0].[AssetId]

EFCore采用按AssetId分区的ROW_NUMBER()窗口函数，再筛选行号为1的记录，该实现方案在大数据集下性能不佳，容易触发SQL超时。

EF6生成SQL #

SELECT 
    [Limit1].[MonitoringLogId] AS [MonitoringLogId], 
    [Limit1].[LogUTCDateTime] AS [LogUTCDateTime], 
    [Limit1].[OtherProperty] AS [OtherProperty], 
    [Limit1].[AssetId] AS [AssetId]
    FROM  [dbo].[Asset] AS [Extent1]
    CROSS APPLY  (SELECT TOP (1) [Project1].[MonitoringLogId] AS [MonitoringLogId], [Project1].[LogUTCDateTime] AS [LogUTCDateTime], [Project1].[OtherProperty] AS [OtherProperty], [Project1].[AssetId] AS [AssetId]
        FROM ( SELECT 
            [Extent2].[MonitoringLogId] AS [MonitoringLogId], 
            [Extent2].[LogUTCDateTime] AS [LogUTCDateTime], 
            [Extent2].[OtherProperty] AS [OtherProperty], 
            [Extent2].[AssetId] AS [AssetId]
            FROM [dbo].[MonitoringLog] AS [Extent2]
            WHERE [Extent1].[AssetId] = [Extent2].[AssetId]
        )  AS [Project1]
        ORDER BY [Project1].[LogUTCDateTime] DESC ) AS [Limit1]

EF6采用CROSS APPLY加TOP(1)的实现方案，相同逻辑下性能更优，小数据集下二者差异不明显，大数据集下性能差距显著。

补充说明 #

上述示例为从大型业务项目中提取的精简复现场景，可复现的完整测试代码、表结构、测试数据已开源。

内容的提问来源于stack exchange，提问作者Dawood Awan