EF.Core provider generates invalid SQL

[azabluda]

Version 9.2.162 generates invalid SQL for the following LINQ statements

Code: Select all

dbContext.Set<User>()
    .Select(u => u.Name)
    .Contains("Hello");
=====================
Result Message:	
Test method DevArt.Tests.dotConnect.UnitTest.DbContext_Select_Contains threw exception: 
Devart.Data.Oracle.OracleException: ORA-00932: inconsistent datatypes: expected - got NCLOB

Result StackTrace:	
at Devart.Data.Oracle.b8.d()
   at Devart.Data.Oracle.bm.e()
   at Devart.Data.Oracle.bm.a()
   at Devart.Data.Oracle.e.a(bm A_0, Int32 A_1)
   at Devart.Data.Oracle.e.a(Int32 A_0, s A_1)
   at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at Devart.Data.Oracle.Entity.ay.a(CommandBehavior A_0)
   at Devart.Common.Entity.c.b(CommandBehavior A_0)
   at Devart.Data.Oracle.Entity.ay.b(CommandBehavior A_0)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)
   at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](Func`2 operation, Func`2 verifySucceeded, TState state)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, Func`2 operation, TState state)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.GetResult[TResult](IEnumerable`1 valueBuffers)
   at lambda_method(Closure , QueryContext )
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass20_0`1.<CompileQueryCore>b__0(QueryContext qc)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.Contains[TSource](IQueryable`1 source, TSource item)

Code: Select all

dbContext.Set<User>()
    .Where(u => u.OwnedFolders.Take(2).Count() == 2)
    .ToList();
=====================
Result Message:	
Test method DevArt.Tests.dotConnect.UnitTest.DbContext_Select_Where_Take_Count threw exception: 
Devart.Data.Oracle.OracleException: ORA-00904: "u"."Id": invalid identifier

Result StackTrace:	
at Devart.Data.Oracle.b8.d()
   at Devart.Data.Oracle.bm.e()
   at Devart.Data.Oracle.bm.a()
   at Devart.Data.Oracle.e.a(bm A_0, Int32 A_1)
   at Devart.Data.Oracle.e.a(Int32 A_0, s A_1)
   at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at Devart.Data.Oracle.Entity.ay.a(CommandBehavior A_0)
   at Devart.Common.Entity.c.b(CommandBehavior A_0)
   at Devart.Data.Oracle.Entity.ay.b(CommandBehavior A_0)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)
   at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](Func`2 operation, Func`2 verifySucceeded, TState state)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, Func`2 operation, TState state)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__15`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

I pushed the corresponding unit tests to my repo.

[Shalex]

Thank you for the report. We are processing your request.

[Shalex]

We have reproduced the issues and are investigating them. We will notify you about the result.

[Shalex]

Code: Select all

Test method DevArt.Tests.dotConnect.UnitTest.DbContext_Select_Contains threw exception: 
Devart.Data.Oracle.OracleException: ORA-00932: inconsistent datatypes: expected - got NCLOB

The bug with setting parameter type when generating the IN expression with subselect in EF Core is fixed. We will notify you when the corresponding build of dotConnect for Oracle is available for download.

Code: Select all

Test method DevArt.Tests.dotConnect.UnitTest.DbContext_Select_Where_Take_Count threw exception: 
Devart.Data.Oracle.OracleException: ORA-00904: "u"."Id": invalid identifier

This is a known Oracle feature. It does not recognize the fields from the outer query in the subquery. We are looking for a solution. There is no any timeframe at the moment.

[Shalex]

The new build of dotConnect for Oracle 9.2.172 is available for download now: viewtopic.php?f=1&t=34762.

[Shalex]

dotConnect for Oracle v9.2.187 includes the following improvements:

• The SQL generation for subselects, when using paging and aggregate functions, in EF Core is optimized
• The bug with duplicating conditions when using paging in EF Core for Oracle 11g and below is fixed