Entity Framework Core – Making RowNumber (More) Useful

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.

In this article:

pg
Pawel Gerr is architect consultant at Thinktecture. He focuses on backends with .NET Core and knows Entity Framework inside out.
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.

Free
Newsletter

Current articles, screencasts and interviews by our experts

Don’t miss any content on Angular, .NET Core, Blazor, Azure, and Kubernetes and sign up for our free monthly dev newsletter.

EN Newsletter Anmeldung (#7)
Related Articles
AI
sg
One of the more pragmatic ways to get going on the current AI hype, and to get some value out of it, is by leveraging semantic search. This is, in itself, a relatively simple concept: You have a bunch of documents and want to find the correct one based on a given query. The semantic part now allows you to find the correct document based on the meaning of its contents, in contrast to simply finding words or parts of words in it like we usually do with lexical search. In our last projects, we gathered some experience with search bots, and with this article, I'd love to share our insights with you.
17.05.2024
Angular
SL-rund
If you previously wanted to integrate view transitions into your Angular application, this was only possible in a very cumbersome way that needed a lot of detailed knowledge about Angular internals. Now, Angular 17 introduced a feature to integrate the View Transition API with the router. In this two-part series, we will look at how to leverage the feature for route transitions and how we could use it for single-page animations.
15.04.2024
.NET
KP-round
.NET 8 brings Native AOT to ASP.NET Core, but many frameworks and libraries rely on unbound reflection internally and thus cannot support this scenario yet. This is true for ORMs, too: EF Core and Dapper will only bring full support for Native AOT in later releases. In this post, we will implement a database access layer with Sessions using the Humble Object pattern to get a similar developer experience. We will use Npgsql as a plain ADO.NET provider targeting PostgreSQL.
15.11.2023