In this Article

In the previous post (Custom Functions - using IMethodCallTranslator) we looked at the more flexible approach that requires some boilerplate code. For the usage of a custom function in a project, especially if there are no plans to make the function public then the sacrifice of some (not required) flexibility to reduce the amount of code is fully acceptable.

  1. Entity Framework Core: ROW_NUMBER Support

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

As in the previos post we will use the extension method RowVersion with the ORDER BY part only as an example. The actual code can be found on Azure DevOps: Thinktecture.EntityFrameworkCore

Create a static method "RowNumber"

For this approach the method RowNumber must a static method containing the ORDER BY parameters only, i.e. the method cannot be an extension method for DbFunctions like before.

public static class DbFunctionsExtensions
{
   // will throw at runtime because EF tries to translate DbFunctions as well
   public static long RowNumber(this DbFunctions _, object orderBy)
   {
      throw new InvalidOperationException("...");
   }

   // works as expected
   public static long RowNumber(object orderBy)
   {
      throw new InvalidOperationException("This method is for use with Entity Framework Core only and has no in-memory implementation.");
   }
}

Due to new method signature of RowNumber the usage is slightly different as well.

...
  .Select(i => new
             {
                ...,
                RowNumber = DbFunctionsExtensions.RowNumber(i.ProductId)
             })

Introduction of custom function to EF

In the previous approach we had to implement IMethodCallTranslator, IMethodCallTranslatorPlugin and IDbContextOptionsExtension to introduce a new function to EF during the configuration of the (SqlServer)DbContextOptionsBuilder. This time we will skip the IMethodCallTranslator and use the ModelBuilder during OnModelCreating.

The configuration of a new function is encapsulated in an extension method AddRowNumberSupport.

public class DemoDbContext : DbContext
{
   ...

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
      ...

      modelBuilder.AddRowNumberSupport();
   }
}

I was not totally honest with you at the beginning. It is true that we don't need custom implementation of IMethodCallTranslator (SqlServerRowNumberTranslator) but we need some code from it. I will pretend that the SqlServerRowNumberTranslator does not exist and copy the required code to the extension method AddRowNumberSupport

Like before we fetch a MethodInfo of the method RowNumber first.

public static class ModelBuilderExtensions
{
   private static readonly MethodInfo _rowNumberMethod 
            = typeof(DbFunctionsExtensions)
              .GetMethod(nameof(DbFunctionsExtensions.RowNumber), 
                         new[] { typeof(object) });

Then we use HasDbFunction and HasTranslation to introduce the function to EF. Inside of HasTranslation we (re)use the RowNumberExpression from the previous post.

   public static ModelBuilder AddRowNumberSupport(this ModelBuilder modelBuilder)
   {
      modelBuilder.HasDbFunction(_rowNumberMethod)
                  .HasTranslation(expressions =>
                               {
                                  var orderByParams = ExtractParams(expressions.First());

                                  return new RowNumberExpression(orderByParams);
                               });

      return modelBuilder;
   }

The method ExtractParams is the same as in the SqlServerRowNumberTranslator.

   private static ReadOnlyCollection<Expression> ExtractParams(Expression parameter)
   {
      if (parameter is ConstantExpression constant
          && constant.Value is IEnumerable<Expression> enumerable)
      {
         return enumerable.ToList().AsReadOnly();
      }

      return new List<Expression> { parameter }.AsReadOnly();
   }

Summary

This approach is easier to grasp and the difference in the amout of code is not that big to base a decision on that alone. Use the approach you like the best because the change from one approach to another is easy and requires very litte time.

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