c# - Entity Framework 6 - ORA-00932 while joining with ToString

Question

I'm testing out Entity Framework 6.2 with an oracle database but facing issues while trying to join on multiple conditions with convertion.

I need to match TABLE2_ID (NUMBER) with TABLE2.ID (VARCHAR2) which are different value types. The problem is that the ToString() method translates into TO_NCLOB instead of TO_NCHAR which would work.

var query = from table1 in context.TABLE1
            join table2 in context.TABLE2 on table1.TABLE2_ID.ToString() equals table2.ID
            select  new
            {
                table1.NAME,
                table2.TEXT
            };

The generated SQL looks as following:

SELECT 
1 AS "C1", 
"Extent1"."NAME" AS "NAME", 
"Extent2"."TEXT" AS "TEXT"
FROM  "USER"."TABLE1" "Extent1"
INNER JOIN "USER"."TABLE2" "Extent2" ON 
  (CASE WHEN ("Extent1"."TABLE2_ID" IS NULL) THEN N'' 

   // At this point I need TO_NCHAR
   ELSE TO_NCLOB("Extent1"."TABLE2_ID") END) = "Extent2"."ID"

This results in:

ORA-00932: inconsistent datatypes: expected NCHAR got NCLOB

I know there are many questions with almost the same exception but only with different use cases, so please have a look how I can solve this problem.
For e.g. is there a way to override the ToString() SQL translation?

MAIN PROBLEM: LINQ to SQL .ToString() converts into TO_NCLOB but I need TO_NCHAR or another solution.

I use Visual Studio 2017 with Oracle Developer Tools for VS2017 12.2.0.11

标签： c#oracleentity-frameworklinqentity-framework-6

Answer 1

Sadly .ToString() isn't implemented that well in Oracle Entity Framework. So I got a workaround by this SO question accepted answer. Installing EntityFramework.Functions Nuget Package and using the TO_NCHAR built in Oracle function did the trick as following:

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

Overriding OnModelCreating in DbContext:

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

And then I can use it like this which works flawless:

var query = from table1 in context.TABLE1
            join table2 in context.TABLE2 on table1.TABLE2_ID.ToNChar() equals table2.ID
            select  new
            {
                table1.NAME,
                table2.TEXT
            };