We have a database version 12.1 and a web service on .NET 8.
This code worked correctly until Oracle.EntityFrameworkCore version 8.21.170.
await dataContext.Certificates.Where(x => x.IsActive == true)
Which generated the following SQL:
SELECT "b".*
FROM "USERS"."CERTIFICATES" "b"
WHERE "b"."IS_ACTIVE" <> 0
After upgrading to 8.23.50 - 8.23.70, we are getting an error:
CoreEventId.QueryIterationFailed[10100] (Microsoft.EntityFrameworkCore.Query)
An exception occurred while iterating over the results of a query for context type 'CertManagerServer.DataContext'.
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "FALSE": недопустимый идентификатор
https://docs.oracle.com/error-help/db/ora-00904/
at Oracle.ManagedDataAccess.Client.OracleCommand.<>c.<ExecuteDbDataReaderAsync>b__232_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
Вызвано исключение: "Oracle.ManagedDataAccess.Client.OracleException" в System.Private.CoreLib.dll
Because EntityFramework generates the following SQL:
SELECT "b".*
FROM "USERS"."CERTIFICATES" "b"
WHERE "b"."IS_ACTIVE" <> False
//IS_ACTIVE - number(1)
I think this is due to database compatibility? Since the boolean type was added in Oracle version 23? How can we ensure compatibility with 12.1?