Previous month:
May 2019
Next month:
August 2019

July 2019

Entity Framework Core: Custom Functions (using HasDbFunction)

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.


Entity Framework Core: Custom Functions (using IMethodCallTranslator)

In the previous blog post (Entity Framework Core: ROW_NUMBER Support) we saw how to use the custom function RowVersion with Entity Framework Core (EF). Now, we will look into the implementation.

In the next and the last post of this series we will look ath the 2nd approach that uses the method HasDbFunction in combination with HasTranslation.

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

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

Define a new extension method for DbFunctions

At first we define an extension method RowNumber to be used in a LINQ query. The method itself contains no logic in it because it won't be executed in .NET but translated to SQL.

Actually, it does not matter what class the extension method is defined for but I'm following the pattern the EF team uses for their functions like the methods DateDiffDay or Like.

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

Having the extension method we are now able to define a RowNumber but this will lead to errors during the execution of the query because EF doesn't know what to do with it.

// Expected output: ROW_NUMBER() OVER(ORDER BY ProductId)
// but will throw an error at this point
...
.Select(i => new
{
...,
RowNumber = EF.Functions.RowNumber(i.ProductId)
})

Provide custom implementation of IMethodCallTranslator

EF needs some help to be able to translate the newly implemented method RowNumber. For that we create a new class and implement the method Translate of the interface IMethodCallTranslator

Expression Translate(MethodCallExpression methodCallExpression);

Our implementation is going to be called with different method call expressions so we need to check whether the method is the one we are interested in or not. For easier check we will save the MethodInfo of RowNumber to a variable.

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

In Translate we check the method being passed in and if it's not the RowNumber we return the methodCallExpression unchanged, otherwise we extract the ORDER BY columns.

   public Expression Translate(MethodCallExpression methodCallExpression)
{
if (methodCallExpression.Method != _rowNumberMethod)
       return null;

var orderByParams = ExtractParams(methodCallExpression.Arguments[1]);

Luckily, EF translated the parameter(s) provided to RowVersion (like i.ProductId) to expressions EF understands (like ColumnExpression). The only thing we do is to check whether we have 1 or more "columns".

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();
}

Back to method Translate.

After having the ORDER BY expressions we return a new custom expression RowNumberExpression that is responsible for generation of the SQL.

      return new RowNumberExpression(orderByParams);
}

The RowNumberExpression derives from Expression and has 2 methods of interest: Accept for rendering SQL and VisitChildren for leting all kind of visitors to visit "our" inner expressions.

First, we implement the method VisitChildren and other few not-that-exciting members.

public class RowNumberExpression : Expression
{
private readonly IReadOnlyCollection<Expression> _orderBy;

public override ExpressionType NodeType => ExpressionType.Extension;
public override Type Type => typeof(long);
public override bool CanReduce => false;

public RowNumberExpression(IReadOnlyCollection<Expression> orderBy)
{
_orderBy = orderBy;
}

protected override Expression VisitChildren(ExpressionVisitor visitor)
{
var visitedOrderBy = visitor.VisitExpressions(_orderBy);

if (ReferenceEquals(_orderBy, visitedOrderBy))
return this;

return new RowNumberExpression(visitedOrderBy);
}

The method VisitExpressions inside VisitChildren is a helper method that iterates over the expressions and calls visitor.Visit(expression). The implementation can be found in ExpressionVisitorExtensions.cs on Azure DevOps.

The remaining method Accept is responsible for the generation of the SQL fragment. Yet again, this method is called with different kind of visitors but we are interested in IQuerySqlGenerator only. Having the SQL generator we let it generate some static SQL fragments and the ORDER BY columns.

protected override Expression Accept(ExpressionVisitor visitor)
{
if (!(visitor is IQuerySqlGenerator))
return base.Accept(visitor);

visitor.Visit(new SqlFragmentExpression("ROW_NUMBER() OVER( ORDER BY "));

RenderColumns(visitor, _orderBy);

visitor.Visit(new SqlFragmentExpression(")"));

return this;
}

private static void RenderColumns(ExpressionVisitor visitor,
IEnumerable<Expression> columns)
{
var insertComma = false;

foreach (var column in columns)
{
if (insertComma)
visitor.Visit(new SqlFragmentExpression(", "));

visitor.Visit(column);
insertComma = true;
}
}

Now, the extension method RowNumber can be translated by the SqlServerRowNumberTranslator to SQL. In theory... because we have to register the translater with EF.

Register custom IMethodCallTranslator with EF

An implementation of IMethodCallTranslator can be registered using an implementation of IMethodCallTranslatorPlugin that has just 1 property Translators.

public class SqlServerMethodCallTranslatorPlugin : IMethodCallTranslatorPlugin
{
public IEnumerable<IMethodCallTranslator> Translators { get; }

public SqlServerMethodCallTranslatorPlugin()
{
Translators = new List<IMethodCallTranslator>
{
new SqlServerRowNumberTranslator()
};
}
}

Now, the plugin has to get into the dependency injection (DI) of the EF. For that we need to implement IDbContextOptionsExtension to get to an instance of IServiceCollection.

Please note that EF has its own DI container by default.

public class SqlServerDbContextOptionsExtension : IDbContextOptionsExtension
{
public string LogFragment => "'RowNumberSupport'=true";

public bool ApplyServices(IServiceCollection services)
{
services.AddSingleton<IMethodCallTranslatorPlugin,
SqlServerMethodCallTranslatorPlugin>();
return false;
}

public long GetServiceProviderHashCode()
{
return 0;
}

public void Validate(IDbContextOptions options)
{
}
}

Last but not least, we need an easy method to add RowNumber support to a DbContext. Due to the fact that this is a SQL Server feature we implement an extension method for SqlServerDbContextOptionsBuilder instead of the general DbContextOptionsBuilder.

public static class SqlServerDbContextOptionsBuilderExtensions
{
public static SqlServerDbContextOptionsBuilder AddRowNumberSupport(
this SqlServerDbContextOptionsBuilder sqlServerOptionsBuilder)
{
var infrastructure = (IRelationalDbContextOptionsBuilderInfrastructure)
sqlServerOptionsBuilder;
var builder = (IDbContextOptionsBuilderInfrastructure)
infrastructure.OptionsBuilder;

// if the extension is registered already then we keep it
      // otherwise we create a new one
var extension = infrastructure.OptionsBuilder.Options
.FindExtension<SqlServerDbContextOptionsExtension>()
?? new SqlServerDbContextOptionsExtension();
builder.AddOrUpdateExtension(extension);

return sqlServerOptionsBuilder;
}
}

Summary

Although there are quite a few components we need to implement a custom function, in the end the amount of code is not that big. Especially, if you consider that the last part (registration with EF) is reusable for all your custom EF features you need.

In the last post of this series we will look at the 2nd approach that requires less code and is less flexible.

UPDATE

  • IMethodCallTranslator.Translate must return null if the method is not the one we wait for so EF continues on with other translators.

Entity Framework Core: ROW_NUMBER Support

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.