EFCore Bug Report

[Simonare]

Code: Select all

            
    var result = await dbContext.RiscgroupMs
        .Include(x => x.RiscgroupDs)
        .Where(x => x.RiscgroupDs.Any(d => d.RgdCustRefno == 93315))
        .Skip(0).Take(10)
        .ToListAsync();

The code piece provided above produces the sql below which has problem on `ORDER BY` statement

[09:55:44 ERR] Failed executing DbCommand (102ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
SELECT "t".RGM_NO, "t".RGM_CONFIRM_REQ_REFNO, "t".RGM_CONFIRM_STATUS, "t".RGM_CONFIRMDATE, "t".RGM_CONFIRMUSER, "t".RGM_CUST_REFNO, "t".RGM_DESC, "t".RGM_INSERTDATE, "t".RGM_INSERTUSER, "t".RGM_IS_OUR, "t".RGM_MARK_FOR_DELETE, "t".RGM_RISC_AMT, "t".RGM_UPDATEDATE, "t".RGM_UPDATEUSER, "r1"."RgdRowid", "r1"."RgdConfirmReqRefno", "r1"."RgdConfirmStatus", "r1"."RgdConfirmdate", "r1"."RgdConfirmuser", "r1"."RgdCustRefno", "r1"."RgdInsertdate", "r1"."RgdInsertuser", "r1"."RgdMNo", "r1"."RgdMarkForDelete", "r1"."RgdRank", "r1"."RgdRiscAmt", "r1"."RgdUpdatedate", "r1"."RgdUpdateuser"
FROM (
SELECT "r".RGM_NO, "r".RGM_CONFIRM_REQ_REFNO, "r".RGM_CONFIRM_STATUS, "r".RGM_CONFIRMDATE, "r".RGM_CONFIRMUSER, "r".RGM_CUST_REFNO, "r".RGM_DESC, "r".RGM_INSERTDATE, "r".RGM_INSERTUSER, "r".RGM_IS_OUR, "r".RGM_MARK_FOR_DELETE, "r".RGM_RISC_AMT, "r".RGM_UPDATEDATE, "r".RGM_UPDATEUSER
FROM CSBINSPLUS.RISCGROUP_M "r"
WHERE EXISTS (
SELECT 1
FROM "RiscgroupDs" "r0"
WHERE (("r".RGM_NO = "r0"."RgdMNo") AND "r0"."RgdMNo" IS NOT NULL) AND (("r0"."RgdCustRefno" = 93315) AND "r0"."RgdCustRefno" IS NOT NULL))
ORDER BY (SELECT 1)
) "t"
LEFT JOIN "RiscgroupDs" "r1" ON "t".RGM_NO = "r1"."RgdMNo"
ORDER BY "t".RGM_NO, "r1"."RgdRowid"

Here is the stack trace

[09:55:44 ERR] An exception occurred while iterating over the results of a query for context type 'DevartBugProject1.BugDbContext'.
Devart.Data.Oracle.OracleException (0x80004005): ORA-00923: FROM keyword not found where expected
at ? .( , Int32 )
at ? .? ?(Int32 , )
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.aj.a(CommandBehavior A_0)
at Devart.Common.Entity.cr.d(CommandBehavior A_0)
at Devart.Data.Oracle.Entity.aj.b(CommandBehavior A_0)
at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
Devart.Data.Oracle.OracleException (0x80004005): ORA-00923: FROM keyword not found where expected
at ? .( , Int32 )
at ? .? ?(Int32 , )
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.aj.a(CommandBehavior A_0)
at Devart.Common.Entity.cr.d(CommandBehavior A_0)
at Devart.Data.Oracle.Entity.aj.b(CommandBehavior A_0)
at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

The platform is : .Net Core 3.0
Packages:
<PackageReference Include="Devart.Data.Oracle" Version="9.9.872" />
<PackageReference Include="Devart.Data.Oracle.EFCore" Version="9.9.872" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.0.0" />

COnfiguration String Used :

Code: Select all

 optionsBuilder.UseOracle(
                        @"User Id=xxxxxx;Password=xxxxxx;Server=xxxxxxx;Direct=True;Service Name=xxx;Persist Security Info=True;License Key=xxxx");

I can upload test project which reproduces bug.

[Shalex]

Please send us a small test project for reproducing the error.

[Simonare]

The test project is sent to you via given link.

[Shalex]

Thank you for the test project. We will notify you when the issue is fixed.

[Shalex]

The issue is fixed. The internal build with the fix is available at https://download.devart.com/nuget_oracle_9_9_883.zip.

[Shalex]

The bug with the redundant empty ORDER BY clause in a subquery, when paging is used, in EF Core 3 is fixed: viewtopic.php?f=1&t=39576.