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 withHasTranslation
.
1. Entity Framework Core: ROW_NUMBER Support
- Follow-up post: Making RowNumber (more) usefull
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-excitingmembers.
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.
Originally published at https://weblogs.thinktecture.com.