ORA-00932 exception: inconsistent datatypes: expected CHAR ; got NCLOB

[kakone]

With EF Core 8.0.2 and Oracle.EntityFrameworkCore 8.21.121, when I concatenate non unicode strings, I got an ORA-00932 exception.

var author = (from b in dbContext.Books select b.AuthorFirstName + " " + b.AuthorLastName).FirstOrDefault();

The generated SQL is

SELECT (COALESCE("b"."AuthorFirstName", NULL) || N' ') || COALESCE("b"."AuthorLastName", TO_NCLOB(N''))

instead of

SELECT (COALESCE("b"."AuthorFirstName", NULL) || ' ') || COALESCE("b"."AuthorLastName", NULL)

This is a sample project that reproduces the problem : Oracle00932

[alexkeh]

Which Oracle DB version are you using?

Did this problem occur with an earlier Oracle EF Core version? If so, which version?

Or is this new code and you've encountered this problem for the first time?

If you can share the ODP trace, that would be helpful. Thanks!

[kakone]

Which Oracle DB version are you using?

I use Oracle 19c

Did this problem occur with an earlier Oracle EF Core version? If so, which version?

I just tested the different versions. In fact, the problem come from Microsoft.EntityFrameworkCore 8.0.2. It works fine with Microsoft.EntityFrameworkCore 8.0.1.

I think the problem is introduced with #32520.

This is the ODP trace : ORACLE00932.EXE_PID_30880_DATE_2024_02_22_TIME_18_11_45_552844.trc.txt

[oberreiterm]

I concur this issue.

Same setup as mentioned here, Oracle 19c with Oracle.EntityFrameworkCore 8.21.121.

Source code:

var list = new List<string>(){ "TEST1$$1", "TEST2$$1"};
var result = context.SampleTable.Where(a => list.Contains(a.Code1 + "$$" + a.Code2)).ToList();

public partial class SampleTable
{

    [Required]
    [Column("CODE1")]
    [StringLength(30)]
    public string Code1 { get; set; }

    [Required]
    [Column("CODE2")]
    [StringLength(30)]
    public string Code2 { get; set; }

    ...
}

Outputted SQL:

SELECT * FROM "SAMPLE_TABLE" "v" WHERE ("v"."CODE1" || N'$$') || "v"."CODE2" IN (TO_NCLOB(N'TEST1$$1'), TO_NCLOB(N'TEST2$$1'))

Expected SQL:

SELECT * FROM "SAMPLE_TABLE" "v" WHERE ("v"."CODE1" || N'$$') || "v"."CODE2" IN (N'TEST1$$1', N'TEST2$$1')

Downgrading to EF Core 8.0.1 as suggested by @kakone solves the issue for now.

[alexkeh]

Thanks @kakone and @oberreiterm. I've filed bug 36345282 to have the Oracle team diagnose whether the problem is the Oracle EF Core provider or EF Core itself.

[alexkeh]

After some analysis, Oracle believes the bug is caused by EF Core relational layer changes in 8.0.2.

The MS code change was not expected in the EF Core relational layer, which introduced this regression.

Oracle raised a concern about the design change decision on GitHub.

Meanwhile, as a workaround, developers that encounter this problem can set the below switch, which will reverse the changes the MS team has applied:

AppContext.SetSwitch("Microsoft.EntityFrameworkCore.Issue32325", true);

var list = new List<string>(){ "TEST1$$1", "TEST2$$1"};
var result = context.SampleTable.Where(a => list.Contains(a.Code1 + "$$" + a.Code2)).ToList();

[alexkeh]

MS Base bug: dotnet/efcore#33218

[alexkeh]

Closing as the problem requires a change to EF Core itself, not in the Oracle EF Core layer nor ODP.NET. The bug resolution will be tracked via the MS base bug.

[alexkeh]

The ODP.NET team has implemented a workaround fix at the Oracle EF Core provider level. It will be available with version 23.6.

[Schoof-T]

@alexkeh We can't upgrade to version Oracle.EntityFrameworkCore 8.23 because we are still on Oracle 21. Is it possible to fix this in the 8.21 as well?

[MT-private]

@Schoof-T IMHO you do not have to upgrade Oracle. You can set a compatibility version during initialization. See UseOracleSQLCompatibility in oracleOptionsAction provided by UseOracle-Extension.

[alexkeh]

@Schoof-T Why is it you can't upgrade the client side to use Oracle.EntityFrameworkCore 8.23?

[Schoof-T]

@Schoof-T Why is it you can't upgrade the client side to use Oracle.EntityFrameworkCore 8.23?

Because of this: https://stackoverflow.com/questions/78847924/oracle-entityframework-can-no-longer-handle-boolean-using-ef
The boolean conversion causes errors on 8.23, 8.21 works perfectly.

[alexkeh]

@Schoof-T Set UseOracleSQLCompatibility(OracleSQLCompatibility.DatabaseVersion19) or UseOracleSQLCompatibility(OracleSQLCompatibility.DatabaseVersion21) to indicate which DB version you are using after upgrading to version 8.23. That ensures Oracle EF Core will not use functionality not available in the DB version you are connecting to.

More info about UseOracleSQLCompatibility(enum version).