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.

Related Articles

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
entity framework core
Entity Framework Core - Isolation of Integration Tests
When working with Entity Framework Core (EF) a lot of code can be tested using the In-Memory database provider but sometimes you want (or have) to go to the real database. For example, you are using not just LINQ but custom SQL statements due to performance reasons or you want to…
Pawel Gerr
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