Let’s take a query from one of the previous posts and add a
WHERE clause:
var query = dbContext.OrderItems
.Select(i => new
{
...,
RowNumber = EF.Functions.RowNumber(i.ProductId)
})
.Where(i => i.RowNumber == 1);
When executing the query we get a SqlException because the SQL statement is not valid.
SELECT
...,
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)
The ROW_NUMBER is not just in SELECT but in WHERE as well because EF cannot know that the main query should be put into a sub query before accessing RowNumber, i.e. something like:
SELECT ...
FROM
(
SELECT
...,
ROW_NUMBER() OVER(ORDER BY i.ProductId) AS RowNumber
FROM
OrderItems AS i
) t
WHERE
t.RowNumber = CAST(1 AS bigint)
Probably, the easiest way is to introduce a method that gives EF a hint that the previous query should be a sub query. Something like:
var query = dbContext.OrderItems
.Select(i => new
{
...,
RowNumber = EF.Functions.RowNumber(i.ProductId)
})
.AsSubQuery()
.Where(i => i.RowNumber == 1);
Fortunately, we don’t have do much because internally the method AsQueryable (or rather the expression associated with it) does just that. We will just (shamelessly ab)use it:
public static class MyQueryableExtensions
{
private static readonly MethodInfo _asQueryableMethodInfo
= typeof(Queryable)
.GetMethods(BindingFlags.Public | BindingFlags.Static)
.Single(m => m.Name == nameof(Queryable.AsQueryable)
&& m.IsGenericMethod);
public static IQueryable<TEntity> AsSubQuery<TEntity>(
this IQueryable<TEntity> source)
{
if (source == null)
throw new ArgumentNullException(nameof(source));
if (!(source.Provider is EntityQueryProvider))
return source;
var methodCall = Expression.Call(
null,
_asQueryableMethodInfo.MakeGenericMethod(typeof(TEntity)),
source.Expression);
return source.Provider.CreateQuery<TEntity>(methodCall);
}
}
Having the method AsSubQuery we get the expected results.
