Create a subquery to filter on windowed function value

c# ef-core-3.1 entity-framework-core entity-framework-core-3.1

Question

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?

1
1
3/4/2020 1:52:03 PM

Accepted Answer

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.

1
3/4/2020 7:28:25 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow