Oracle Entity Framework - Call custom function (EDIT_DISTANCE)

Question

I am using Entity Framework 6 and Oracle Database 11g (ODP.NET Manage Driver).

How to call UTL_MATCH.EDIT_DISTANCE function in LINQ query?

Answer 1

There is an awesome Library to help with mapping Database functions and Stored Procedures to Entity Framework.

Install the Nuget package

- Install-Package EntityFramework.Functions

Create Extension Methods for functions:

public static class OracleFunctions
{
   [Function(FunctionType.BuiltInFunction, "TO_NUMBER")]
   public static int? ToNumber(this string value) => Function.CallNotSupported<int?>();
}

Map it on your EntityFramework Context:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   modelBuilder.Conventions.Add(new FunctionConvention(typeof(OracleFunctions)));
}

Call your newly mapped "ToNumber()" function in your LINQ queries:

.FirstOrDefault(p => p.Id == "209706".ToNumber());

And bobs your Uncle.

Unfortunately, for Oracle functions that resides in a different schema, like UTL_MATCH.EDIT_DISTANCE it will not work. You are supposed to be able to set the schema, but it seems like it is not currently working with Oracle or something. But for other fucntions like SOUNDEX etc. this should work fine.

You can read the Documentation for EntityFramework.Functions here

Answer 2

I agree with Gerrie Pretorius and if you used database first model , you must add a function to edmx file like below

   <Function Name="TO_NUMBER" Aggregate="false" BuiltIn="true" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" ReturnType="number">
          <Parameter Name="valueStr" Type="varchar" Mode="In" />
        </Function>

Then add a static function to OracleFunctions class like below

[EdmFunction("Model.Store", "TO_CHAR")]
public static int? ToNumber(this string value) {
    throw new NotSupportedException("this function is not supported");
}

After that you can use ToNumber function freely.