How to use Oracle built in functions in Entity Framework Core?

Question

With EF 6 (and .NET Framework), I was able to map Oracle built in functions by creating a static class like this:

public static class OracleFunctions
{
    [Function(FunctionType.BuiltInFunction, "TO_CHAR")]
    public static string ToChar(this decimal? value) => Function.CallNotSupported<string>();
   
    [Function(FunctionType.BuiltInFunction, "LENGTHB")]
    public static int? LENGTHB(this byte[] value) => Function.CallNotSupported<int?>();
}

And then I added this code to OnModelCreating:

modelBuilder.Conventions.Add(new FunctionConvention(typeof(OracleFunctions)));

However, this syntax does not work for EF Core. I attempted to create a similar static class:

public static class OracleFunctions
{
    public static int LENGTHB(this byte[] input)
    {
        throw new NotSupportedException("This function can only be used in LINQ to Entities queries.");
    }

    public static string ToChar(this decimal? value)
    {
        throw new NotSupportedException("This function can only be used in LINQ to Entities queries.");
    }        
}

And added them in OnModelCreating:

modelBuilder.HasDbFunction(typeof(OracleFunctions).GetMethod(nameof(OracleFunctions.LENGTHB)))
            .HasName("LENGTHB");

modelBuilder.HasDbFunction(typeof(OracleFunctions).GetMethod(nameof(OracleFunctions.ToChar)))
            .HasName("TO_CHAR");

Using the functions like this:

var bar = dbContext.WORKFLOW_DOCUMENT.Where(x => OracleFunctions.LENGTHB(x.Template) > 0).FirstOrDefault();

But it results in the following error:

Oracle.ManagedDataAccess.Client.OracleException: 'ORA-00904: "MySchema"."LENGTHB": invalid identifier https://docs.oracle.com/error-help/db/ora-00904/'

Answer 1

As can be seen in the Oracle error message, the schema name MySchema is being applied to the function name LENGTHB. Removing the following from OnModelCreating will prevent the schema name from being added.

modelBuilder.HasDefaultSchema("MySchema")

With this change, the LENGTHB function, as defined in the original question, now works. The TO_CHAR function now throws a different error. However it may not be needed as there appears to be better .ToString() handling with EF Core and the related Oracle packages. For example, I have not encountered the dreaded

ORA-00932 inconsistent datatypes: expected CHAR got NCLOB

yet when using .ToString() on numeric fields.

A streamlined alternative to defining built in functions (as can be seen here) is adding a static function to your database context:

[DbFunction("LENGTHB")]
public static int? LengthB(byte[] input) => throw new NotSupportedException("This function can only be used in LINQ to Entities queries.");

This can then be called like:

var foo = dbContext.WORKFLOW_DOCUMENT.Where(x => MyTestContext.LENGTHB(x.Template) > 0).FirstOrDefault();

Answer 2

Schema doesn't matter for built-in functions, but you have to tell EF that your function is built-in (by default it is assumed to be user-defined). You do that by ... well, using the surprisingly named IsBuiltIn method of the DbFunctionBuilder class returned by the all HasDbFunction fluent API. e.g.

modelBuilder.HasDbFunction(typeof(OracleFunctions).GetMethod(nameof(OracleFunctions.LENGTHB)))
    .HasName("LENGTHB")
    .IsBuiltIn(); // <--

As you can see, the class has many other methods (HasSchema, HasParameter, HasStoreType, IsNullable and even HasTranslation) which allow you to configure all aspects of the function mapping, not just the name.

DbFunction attribute also has some properties (IsBuiltIn, IsNullable, Name, Schema), but is not so flexible as the fluent API.