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

Can't test, so all I can do is to suggest trying the alternative LINQ left outer join syntax, e.g. instead of `join table2 in ...` try `from result in context.TABLE2.Where(table2 => table1.TABLE2_ID != null && table1.TABLE2_ID.Value.ToString() == table2.ID && table2.MATCH == "VALUE").DefaultIfEmpty()`. — Ivan Stoev, Jun 25 '18 at 07:39
@IvanStoev I simplified the case for my problem. I tried your code but it results in the same SQL translation. — Daniel Bonhard, Jun 25 '18 at 08:09

score 4 · Accepted Answer · answered Jul 05 '18 at 07:06

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
            };

Entity Framework 6 - ORA-00932 while joining with ToString

1 Answers1