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
Is "N+1 Queries" still a performance issue in Entity Framework Core 3?
In a previous post we saw that EF 2.1 is highly susceptible to the N+1 queries problem. After the release of a new version of Entity Framework Core (EF) the first question coming to mind is: "Is it still a big issue in EF 3.1?" And if the answer is no, is there anything else we…
Pawel Gerr
entity framework core
Entity Framework Core 3.0 - "Hidden" GROUP BY Capabilities (Part 2)
In the previous blog post we used a navigational property to work around the limitations of the extension method . The problem is, there is not always such a property we can use for grouping. Especially, when following domain driven design practices, a bidirectional navigation is…
Pawel Gerr
entity framework core
Entity Framework Core 3.0 - "Hidden" GROUP BY Capabilities (Part 1)
With Entity Framework Core 3.0 (EF) the internal implementation of the LINQ query translation has been changed a lot. So, some queries that didn't work previously are working now and some that worked are not working anymore. :) The LINQ extension method is a special one…
Pawel Gerr
entity framework core
Entity Framework Core - Getting more Deadlock Information with named Transactions
Whether in an application in production or in integration tests on CI, the deadlocks are virtually unavoidable. The behavior of a deadlock depends on the database (MS SQL Server, MySQL, etc)  and the isolation level (like Snapshot Isolation). Some of the databases are blocking…
Pawel Gerr