EF.Core provider generates invalid SQL

EF.Core provider generates invalid SQL

Postby azabluda » Wed 14 Dec 2016 13:31

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.
azabluda
 
Posts: 29
Joined: Thu 10 Sep 2009 14:45

Re: EF.Core provider generates invalid SQL

Postby Shalex » Fri 16 Dec 2016 20:09

Thank you for the report. We are processing your request.
Shalex
Devart Team
 
Posts: 7531
Joined: Thu 14 Aug 2008 12:44

Re: EF.Core provider generates invalid SQL

Postby Shalex » Mon 19 Dec 2016 19:17

We have reproduced the issues and are investigating them. We will notify you about the result.
Shalex
Devart Team
 
Posts: 7531
Joined: Thu 14 Aug 2008 12:44

Re: EF.Core provider generates invalid SQL

Postby Shalex » Wed 21 Dec 2016 14:18

azabluda wrote:
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.

azabluda wrote:
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
Devart Team
 
Posts: 7531
Joined: Thu 14 Aug 2008 12:44

Re: EF.Core provider generates invalid SQL

Postby Shalex » Thu 22 Dec 2016 18:37

The new build of dotConnect for Oracle 9.2.172 is available for download now: http://forums.devart.com/viewtopic.php?f=1&t=34762.
Shalex
Devart Team
 
Posts: 7531
Joined: Thu 14 Aug 2008 12:44

Re: EF.Core provider generates invalid SQL

Postby Shalex » Mon 16 Jan 2017 17:16

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
Shalex
Devart Team
 
Posts: 7531
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle