EF6 and EFCore context generate different SQL scripts causing performance issue in EFCore

I have recently been upgrading an old solution from EF6 to EFCore and Net48 to Net5.0.

So after migrating to EFCore, I have noticed some of the queries performing alright in EF6 are now SQL timeout in EFCore.

I imported both the old EF6 and EFCore DbContext connections into LINQPad, and investigated the generated SQL.

These are my Entities:

[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; }

}

This is my LINQ Query:

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

In EFCore the generated SQL is:

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]
GO

It seems to generate a ROW_NUMBER() with a Partition by AssetId. Then filters out the record with RowNumber == 1. Now I don't see why all this is necessary when TOP (1) ORDER BY LogUTCDateTime can solve this.

Execution Plan: https://www.brentozar.com/pastetheplan/?id=H1a3J6-dY

While the SQL generated in EF6 is:

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]

Execution Plan: https://www.brentozar.com/pastetheplan/?id=SJLQxTbdY

With small datasets, there is not much impact on the Query - but with large data sets this is causing an SQL Timeout.

Here is a Repo with both EF6 and EFCore context, the Repo also includes the SQL script to generate the DB/Tables and create some sample data:

https://github.com/mdawood1991/EFIssue

NOTE: The above example is an extracted example from my large solution.