Query: Add support for User Defined Scalar Functions

[pmiddleton]

I have added support for user defined scalar functions.

My fork can be found at https://github.com/pmiddleton/EntityFramework in the udf branch.

I am looking for feedback on this feature and if there is interest in it I will submit a pull request.

This update has three main features.

• User defined scalar functions. Query: Additional function mapping capabilities #4319
• The EntityFunctions feature from EF 6 Query: Provide EF.Functions to define methods that correspond to standard SQL operations #2850
• The ability to intercept any dotnet method call and translate it into a function call in the EF store.

Implementation details

I have used a different integration approach with Re-linq vs what EF Core is presently doing. Rather than performing method translation in the VisitMethodCall on the SqlTranslatingExpressionVisitor class, I have moved the translation to earlier in the transformation pipeline by useing an implementation of IExpressionTransformer which is called when Re-linq parses the original query. This transformer replaces method calls with DbFunctionExpressions. The DbFunctionExpressions are then translated into SqlFunctionExpressions by the SqlTranslatingExpressionVisitor.

By moving the method transformation to earlier in the Re-linq process, and introducing the DbFunctionExpression, we are now able to translate method calls inside from clauses. This is not possible in the current implementation. This ability will allow us to use functions as data sources and therefore implement Table Valued Functions for database which support this feature.

There is a working proof of concept for this feature inside of the TVF branch of my repository. The item holding up that feature is missing support in EF Core for representing the return results of a TVF. The proof of concept registers all TVF results objects as Entities. This is not the correct solution, but it proves out that the approach for TVF code will work. What is needed to finalize the code is a lightweight representation of TVF results. The wip complex type branch appears to be a promising approach, but the TVF results are not tied to a single Entity, so something closely related to complex types is needed. Discussion needs to happen around the proper way to represent the results of TVF in the model.

Function Setup

Methods can be marked as being translatable in two ways. For user defined functions you can use the DbFunctionAttribute. You can also use the fluent api method DbFuction which is located on the ModelBuilder class. For intercepting method calls on existing classes or adding a custom EntityFunction extension method you need to use the fluent api.

User defined function

To create a UDF you just need to define a dotnet function which has the same signature as the database function. You then register the dotnet method using either the DbFunctionAttribute or the fluent api. You can then use that function in your queries. Calls to it will be translated to an equivalent call in the database.

By default if you are using the DbFunctionAttribute your methods must be defined on your dbContext. You can provide an alternate class for hosting your functions by using the fluent api ModelBuilder.LoadDbFunctions method.

By default the database function name will be the name of the dotnet function. There are parameters on the attribute and fluentApi to set a different function name and for setting the function schema, for providers which need this information.

EntityFunctions

The EntityFunctions can be accessed from the EF class's Function property.

They are implemented as extension methods. This design allows different providers to add provider specific functions without impacting other providers.

There is a core set of functions which I have started to define for all relational providers which is a subset of what was defined in EF 6. These methods all have client side implementations, so if a provider does not support the method it will fall back to client side evaluation.

Method Interception

EF Core has support for intercepting a limited number of methods, mainly methods on the string, datetime, and math class in the Sql Server provider and a bit less in the Sqlite provider. Today to intercept these methods you need to implement and register a custom IMethodCallTranslator in the EF Core source. End users cannot override methods at runtime.

By using the DbFunction fluent api these methods can be registered for interception at model building time. I have done this for all the existing Sql Server and Sqlite IMethodTranslators, which are registered by the system at startup so the end user does not need to make any code changes.

I have left in place the IMethodCall translator interface, and the default implementations in the relational provider. In order to use the new functionality the EF provider must fully switch from IMethodCall to DbFunctions, so I left this in place for backwards compatibility for existing providers.

Limitations

Only scalar functions which accept and return simple datatypes are supported. The functions can use any dotnet primitive, string, datetime, decimal, guid, and enums.

The Sqlite provider does not support UDFs due to lacking support in the underlying Sqlite provider for calling custom functions.

Examples

Say you have the following UDF in Sql Server.

create function [dbo].[Foo] (@bar nvarchar(max))
returns nvarchar(max)
as
begin
	return 'foo' + @bar
end

You can call this by first defining a method on our dbContext.

public class MyContext : DbContext
{
	[DbFunction(Schema = "dbo")]
	public static string Foo(string bar)
	{ throw new NotImplementedException() ;}
}

Alternatily you can remove the DbFunction attribute and register with the fluent api like this.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
	modelBuilder.DbFunction(typeof(MyContext), nameof(MyContext.Foo)) ;		
}

We can then use that method in a linq query.

context.Widgets.Select(w => context.Foo(w.Name)) ;

from w in context.Widgers
select context.Foo(w.Name)

For more examples of how to use the fluent api see the class SqlServerDbFunctionInitalizer in the Microsoft.EntityFrameworkCore.SqlServer project.

[divega]

Second iteration PR #8507 was merged today. Thanks @pmiddleton and feel free to update this issue with any changes from the original proposal!

[aaronhudon]

I find the documentation on DbFunctions fairly sparse...
@pmiddleton what is the procedure of data type mapping for the UDF?
For example, I have a UDF that accepts a DATE, however when the TSQL is generated, EFCore define the parameters as DATETIME2(7).

E.g. see below. I would like EFCore to know that the parameter type for this function is DATE

        [DbFunction("udf_GetBlah")]
        public static int GetBlah(DateTime? d)
        {
            throw new NotImplementedException();
        }

[smitpatel]

@aaronhudon - See #13752