Entity Framework Core – ROW_NUMBER Support

In this article:

pg
Pawel Gerr is architect and consultant at Thinktecture. He focuses on backends with .NET Core and knows Entity Framework inside out.

There are some use cases that force me to use raw T-SQL instead of using LINQ. One of them is the need for ROW_NUMBER. But not anymore…

One again, due to great work of Entity Framework (EF) team we are able to add more capabilities to EF quite easily. At the moment there are (at least) 2 approaches, the first one is by implementing a custom IMethodCallTranslator the other one is by using the method HasDbFunction.

				
					This blog post is all about the usage of the EF function RowNumber. In the following post we will see how to add a custom function to EF by implementing an IMethodCallTranslator and to register it with EF. The last post of this series will show you the 2nd approach that uses the method HasDbFunction.
				
			
  1. Entity Framework Core: ROW_NUMBER Support

  2. Entity Framework Core: Custom Functions (using IMethodCallTranslator)
  3. Entity Framework Core: Custom Functions (using HasDbFunction)

The actual code can be found on Azure DevOps: Thinktecture.EntityFrameworkCore

Activate the RowNumber support

To be able to define a RowNumber in a regular LINQ query we have to install the Nuget package Thinktecture.EntityFrameworkCore.SqlServer first. The package will provide a new extension method AddRowNumberSupport for the SqlServerDbContextOptionsBuilder that we have to call during the configuration of a DbContext.

				
					services
   .AddDbContext<DemoDbContext>(builder => builder
         .UseSqlServer("conn-string", sqlOptions =>
          {
                sqlOptions.AddRowNumberSupport();
          });

				
			

Usage of RowNumber in LINQ

There are 2 method overloads for definition of a RowNumber, one with and one without the PARTITION BY part. In both cases we are using the new extension method overload RowNumber for the DbFunctions.

**RowNumber** with the ORDER BY part only:

				
					// ORDER BY ProductId
var query = dbContext.OrderItems
                     .Select(i => new
                                  {  
                                     ...,
                                     RowNumber = EF.Functions.RowNumber(i.ProductId)
                                  });

// ORDER BY ProductId, OrderId
var query = dbContext.OrderItems
                     .Select(i => new
                                  { 
                                    ...,
                                    RowNumber = EF.Functions.RowNumber(new 
                                                                       {
                                                                          i.ProductId,
                                                                          i.OrderId
                                                                       })
                                  });
				
			

Use another new extension method Descending for changing the default sort order (ascending).

In the example below I’m using _ instead of a property name because the names on the left side of = don’t matter.

				
					// ORDER BY ProductId, OrderId DESC
...
  .Select(i => new
               {
                  ...,
                  RowNumber = EF.Functions.RowNumber(new 
                                                {
                                                  i.ProductId,
                                                  _ = EF.Functions.Descending(i.OrderId)
                                                })
                                      })
				
			

RowNumber with PARTITION BY and ORDER BY:

The second overload of RowNumber has 2 arguments. The first one is for the definition of the PARTITION BY part and the second is for the ORDER BY.

				
					// PARTITION BY ProductId ORDER BY OrderId
...
  .Select(i => new
             {
                ...,
                RowNumber = EF.Functions.RowNumber(i.ProductId, i.OrderId)
             })

// PARTITION BY ProductId, Id ORDER BY OrderId
...
  .Select(i => new
               {
                  ...,
                  RowNumber = EF.Functions.RowNumber(new 
                                                    {
                                                       i.ProductId,
                                                       i.Id
                                                    }, 
                                                    i.OrderId)
               })
				
			

In the next blog post we will see how to introduce a new EF function by implementing IMethodCallTranslator.

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
.NET
pg
While simple value objects wrap single primitives, many domain concepts involve multiple related properties (e.g., a date range's start and end). This article introduces Complex Value Objects in .NET, which group these properties into a cohesive unit. This ensures internal consistency, centralizes validation, and encapsulates behavior. Discover how to implement these for clearer, safer code using the library Thinktecture.Runtime.Extensions, which minimizes boilerplate when handling such related data.
01.07.2025
.NET
pg
Traditional C# enums often fall short when needing to associate data or behavior with constants, or ensure strong type safety. This article explores the "Smart Enum" pattern as a superior alternative. Leveraging the library Thinktecture.Runtime.Extensions and Roslyn Source Generators, developers can easily implement Smart Enums. These provide a robust, flexible, and type-safe way to represent fixed sets of related options, encapsulating both data and behavior directly within the Smart Enum. This results in more maintainable, expressive, and resilient C# code, overcoming the limitations of basic enums.
17.06.2025
.NET
pg
Overusing primitive types like string or int for domain concepts ("primitive obsession") causes bugs from missed validation, like invalid emails or negative monetary values. This article explores Value Objects as a .NET solution. Learn how these self-validating, immutable types prevent entire classes of errors, make code more expressive, and reduce developer overhead. We'll demonstrate creating robust domain models with minimal boilerplate, improving code quality without necessarily adopting full Domain-Driven Design, and see how Roslyn Source Generators make this practical.
03.06.2025