Microsoft.EntityFrameworkCore 8.0.2: Inferring type mapping based on operand size has caused regression for the Oracle EF core provider

indrajitjadeja commented 8 months ago

With reference to the below comment on #32520

@indrajitjadeja the fix in https://github.com/dotnet/efcore/pull/32510 only corrected the length on the type mapping; this is a general type mapping inference fix that isn't SQL Server-specific (even if the original bug manifested specifically in SQL Server). I'm still not clear on how it exactly it affects the Oracle SQL translation - can you please open a new issue with a clear minimal, runnable repro and the SQL generated in 8.0.1 and 8.0.2?

Minimal repro:

using var context = new BlogContext();  
context.Database.EnsureDeleted();  
context.Database.EnsureCreated();  

context.Persons.Add(new() { ThreeCharacterProperty = "AAA", FiveCharacterProperty = "BBBBB" });  
context.SaveChanges();  

var queryData = new[] { "AAA;BBBBB", "AAA;CCCCC" };  
Console.WriteLine(context.Persons.Count(x => queryData.Contains(x.ThreeCharacterProperty + ";" + x.FiveCharacterProperty)));  

public class BlogContext : DbContext  
{  
    public DbSet<Person> Persons { get; set; }  

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  
        => optionsBuilder  
            .UseOracle(@"User Id=scott;Password=tiger;Data Source=inst1")  
            .LogTo(Console.WriteLine, LogLevel.Information)  
            .EnableSensitiveDataLogging();  
}  

public class Person  
{  
    public int Id { get; set; }  
    [Column(TypeName = "char(3)")]  
    public string ThreeCharacterProperty { get; set; }  
    [Column(TypeName = "char(5)")]  
    public string FiveCharacterProperty { get; set; }  
}

Application using Oracle.EntityFrameworkCore 8.21.121 with Oracle.EntityFrameworkCore 8.0.1 generates the following SQL without any error:

SELECT COUNT(*)
FROM "Persons" "p"
WHERE (COALESCE("p"."ThreeCharacterProperty", NULL) || ';') || COALESCE("p"."FiveCharacterProperty", NULL) IN ('AAA;BBBBB', 'AAA;CCCCC')

Application using Oracle.EntityFrameworkCore 8.21.121 with Oracle.EntityFrameworkCore 8.0.2 generates the following SQL:

SELECT COUNT(*)
      FROM "Persons" "p"
      WHERE (COALESCE("p"."ThreeCharacterProperty", NULL) || N';') || COALESCE("p"."FiveCharacterProperty", TO_NCLOB(N'')) IN (TO_NCLOB(N'AAA;BBBBB'), TO_NCLOB(N'AAA;CCCCC'))

which throws the below error:

Unhandled exception. Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00932: inconsistent datatypes: expected CHAR got NCLOB
   at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, Int64 internalInitialJSONFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
   at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
   at lambda_method45(Closure, QueryContext)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at ConsoleApp1.Program.Main() in C:\ConsoleApp1\ConsoleApp1\Program.cs:line 39

Debug information

In the oracle provider, depending on the size parameter, a string CLR type maps to either VARCHAR2/NVARCHAR2 or CLOB/NCLOB store types.

out of the whole binary expression:

x.ThreeCharacterProperty + ";" + x.FiveCharacterProperty

when the left part of the binary expression:

/*left*/ x.ThreeCharacterProperty       /*operand */ +        /*right*/ ";"

requires inferring of type mapping, the right SqlConstantExpression does not have a TypeMapping thus the value of inferredSize becomes null and it calls _typeMappingSource.FindMapping on string CLR type with Unicode and Oracle provider returns NVARCHAR2 as type mapping with 2000 default size.

now when the right part of the binary expression gets evaluated:

/*left*/ x.ThreeCharacterProperty + ";"      /*operand */ +        /*right*/ x.FiveCharacterProperty

the left Sql Binary Expression part already has 2000 as a size and the right SqlColumnEpxression has 5 as a size which sets the inferredSize value to 2005 and it calls _typeMappingSource.FindMapping on String CLR type with Unicode and Oracle provider returns NCLOB as type mapping.

In the case of the Oracle provider with string CLR type, an additional call to the _typeMappingSource.FindMapping method based on the inferred size results in incorrect type mapping combinations, despite the fact that each SqlColumnEpxression has already been assessed with the correct type mapping.

provider and version information

EF Core version: 8.0.2 Database provider: Oracle.EntityFrameworkCore 8.21.121 Target framework: .NET 8.0 Operating system: Windows 11 IDE: Visual Studio 2022 17.7.4

indrajitjadeja commented 8 months ago

@roji @ajcvickers

ajcvickers commented 7 months ago

@indrajitjadeja

In the case of the Oracle provider with string CLR type, an additional call to the _typeMappingSource.FindMapping method based on the inferred size results in incorrect type mapping combinations

Doesn't this indicate an issue with the Oracle provider where the call to FindMapping is returning an invalid mapping?

indrajitjadeja commented 7 months ago

@ajcvickers

Doesn't this indicate an issue with the Oracle provider where the call to FindMapping is returning an invalid mapping?

For the Oracle Provider, the size parameter was the only reason why the FindMapping method call from SqlExpressionFactory.ApplyTypeMappingOnSqlBinary() encounters a problem.

However, the relational layer also calls the FindMapping method for each property when the model properties are initialized and finalized, and the Oracle provider returns appropriate type mappings.

Unlike the SQL Server's VARCHAR2 datatype, which supports max as size, the Oracle VARCHAR2 does not support max as size due to its limited character size support, as explained in this document.

Oracle EF Core Provider has the following combinations based on the size parameter:

For a model property of string CLR type
- When size is not configured and Unicode is true ( the default case for a string property)
- Oracle provider uses the maximum allowed size of 2000 and maps it to NVARCHAR2(2000) datatype
- When size is not configured and Unicode is false
- Oracle provider uses the maximum allowed size of 4000 and maps it to the VARCHAR2 (4000) datatype
- When size is configured >2000 and Unicode is true
- Oracle provider maps it to NCLOB datatype
- When size is configured >4000 and Unicode is false
- Oracle provider maps it to CLOB datatype

For Oracle Provider, the previous behavior (<8.0.2) of inferring the type mapping based on the left and right expressions (usually the first expression in the list) was adequate.

if (UseOldBehavior32325)
{
inferredTypeMapping ??= ExpressionExtensions.InferTypeMapping(left, right);
}

dotnet / efcore