'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 |
|---|
