'How to implement Row_Number() over (Partition By Order By) in EntityFramework Core 5?

I have a table and fields as following
Table

T {
  RunDate datetime,
  A varchar,
  B varchar,
  C varchar,
  D varchar,
  E varchar,
}

I want to run the Sql with LINQ

SQL

Select 
    t.*
From 
    (
        SELECT *,ROW_NUMBER() OVER (PARTITION BY A,B,C,D ORDER BY RunDate DESC) AS RowNumber
        From T
        Where A == 'xx' && B == 'XX' && RunDate <= 'dddd'
    ) AS t
Where t.RowNumber=1

I tried

LINQ

T.AsNoTracking()
    .Where(x => x.Product == "XXX" && x.ProductSubCode == "AAA" && x.RunDate <= DateTime.Now.AddDays(-1))
    .GroupBy(x => new {x.A,x.B,x.C,x.D})    
    .Select(g => g.OrderByDescending(x => x.RunDate).FirstOrDefault())
    .ToList()

This can only run in EF Core 6 but can't run in EF Core 5. Now I want to know how to implement above SQL in EF Core 5? And it can run in EF Core 6, but will generate a very complex sql, I also want to know there is a better way to solve this case?
LINQ generated SQL

SELECT [t0].[RunDate], [t0].[A], [t0].[B], [t0].[C], [t0].[D], [t0].[E]
FROM (
    SELECT [m].[A], [m].[B], [m].[C], [m].[D]
    FROM [T] AS [m]
    WHERE (([m].[A] = N'XXX') AND ([m].[B] = N'AAA')) AND ([m].[RunDate] <= DATEADD(day, CAST(0.0E0 AS int), GETDATE()))
    GROUP BY [m].[A], [m].[B], [m].[C], [m].[D]
) AS [t]
LEFT JOIN (
    SELECT [t1].[RunDate], [t1].[C], [t1].[D], [t1].[E], [t1].[A], [t1].[B]
    FROM (
        SELECT [m0].[RunDate], [m0].[A], [m0].[B], [m0].[C], [m0].[D], [m0].[E], ROW_NUMBER() OVER(PARTITION BY [m0].[A], [m0].[B], [m0].[C], [m0].[D] ORDER BY [m0].[RunDate] DESC) AS [row]
        FROM [T] AS [m0]
        WHERE (([m0].[A] = N'XXX') AND ([m0].[B] = N'AAA')) AND ([m0].[RunDate] <= DATEADD(day, CAST(0.0E0 AS int), GETDATE()))
    ) AS [t1]
    WHERE [t1].[row] <= 1
) AS [t0] ON ((([t].[A] = [t0].[A]) AND ([t].[B] = [t0].[B])) AND ([t].[C] = [t0].[C])) AND (([t].[D] = [t0].[D]) OR ([t].[D] IS NULL AND [t0].[D] IS NULL))
GO



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source