I'm trying to add ROW_NUMBER functionality to EF Core and filter by it.
After adding custom function, it works fine in Select
but doesn't work in Where
, because of malformed SQL.
Linq:
var query = dbContext.OrderItems
.Select(i => new
{
i.Name,
RowNumber = EF.Functions.RowNumber(i.ProductId)
})
.Where(i => i.RowNumber == 1);
Translates into:
SELECT
i.NAME,
ROW_NUMBER() OVER(ORDER BY i.ProductId) AS RowNumber
FROM
OrderItems AS i
WHERE
ROW_NUMBER() OVER(ORDER BY i.ProductId) = CAST(1 AS bigint)
Error:
Microsoft.Data.SqlClient.SqlException (0x80131904): Windowed functions can only appear in the SELECT or ORDER BY clauses.
To correct this SQL, I need to create a subquery:
SELECT
t.NAME,
t.RowNumber
FROM (
SELECT
i.NAME,
ROW_NUMBER() OVER(ORDER BY i.ProductId) AS RowNumber
FROM
OrderItems AS i
) t
WHERE
t.RowNumber = CAST(1 AS bigint)
I've found an article on how to do this in EF Core 2.
Probably, the easiest way is to introduce a method that gives EF a hint that the previous query should be a sub query. Fortunately, we don't have do much because internally the method AsQueryable (or rather the expression associated with it) does just that.
https://www.thinktecture.com/en/entity-framework-core/making-rownumber-more-useful-in-2-1/
But this approach does nothing in EF Core 3.1
Is there a way to create a subquery?
Looking at the EF Core 3.1 source code, the only way I see to force subquery before applying where
filter is to introduce a query limit (i.e. Skip
and/or Take
).
From the two possible fake limit operators (Skip(0)
and Take(int.MaxValue)
), looks like choosing the later is better, because the former also requires some ordering (even fake).
So the workaround is to insert
.Take(int.MaxValue)
before .Where(...)
.
The generated SQL is not perfect (has fake TOP
clause), but at least is valid.
User contributions licensed under CC BY-SA 3.0