Search code examples
c#oraclelinqentity-frameworkodp.net

Oracle Entity Framework - Call custom function (EDIT_DISTANCE)

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?

Solution

  • 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