Devart Forums

Discussion forums for open issues and questions concerning database tools, data access components and developer tools from Devart
https://forums.devart.com/

EF.Core provider generates invalid SQL

https://forums.devart.com/viewtopic.php?f=1&t=34714

Page 1 of 1

EF.Core provider generates invalid SQL

Posted: Wed 14 Dec 2016 13:31
by 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.

Re: EF.Core provider generates invalid SQL

Posted: Fri 16 Dec 2016 20:09
by Shalex
Thank you for the report. We are processing your request.

Re: EF.Core provider generates invalid SQL

Posted: Mon 19 Dec 2016 19:17
by Shalex
We have reproduced the issues and are investigating them. We will notify you about the result.

Re: EF.Core provider generates invalid SQL

Posted: Wed 21 Dec 2016 14:18
by Shalex
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.

Re: EF.Core provider generates invalid SQL

Posted: Thu 22 Dec 2016 18:37
by Shalex
The new build of dotConnect for Oracle 9.2.172 is available for download now: viewtopic.php?f=1&t=34762.

Re: EF.Core provider generates invalid SQL

Posted: Mon 16 Jan 2017 17:16
by 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
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/