In the blog post ROW_NUMBER Support we saw how to implement a custom function and in the previous post Improved Value Conversion Support we realized that inserting and selecting custom types is one thing but using them for filtering is something totally different.

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.

Related Articles

entity framework core
Do Not Waste Performance by Not Using Temp Tables With Entity Framework Core
It has been a while since I released my article about the usage of temp tables in Entity Framework (v6). Meanwhile, Microsoft has released a completely rewritten version of its O/R mapper so my old approach is no longer applicable. But before we learn about a new one, let us…
Pawel Gerr
entity framework core
Better Entity Framework Core Performance by Reading Execution Plans
Both a LINQ query and an SQL statement are descriptions that state which data should be fetched, but not how.. Sure, when reading LINQ or SQL, we can make assumptions about the performance but not in every case. Some queries are either too fancy or too big to grasp, so our…
Pawel Gerr
entity framework core
Unnecessary Fuzzy Searches may hurt your Entity Framework Core Performance
After talking about performance issues like N+1 Queries and the Cartesian Explosion that made its comeback in Entity Framework Core 3, we will today look at a performance issue that is not tied to any Entity Framework version but is rather a general one. What do I mean by…
Pawel Gerr
entity framework core
The performance issue "Cartesian Explosion" made its comeback in Entity Framework Core 3
In Entity Framework Core 3.0/3.1 the SQL statement generation underwent significant changes. As we have seen in the previous post these changes removed both the implicit client-side evaluation and the N+1 Query Problem (which is good!). Unfortunately, these changes (re)introduced…
Pawel Gerr