EF Core 3.19.80 Using Any And FirstOrDefault Generates Wrong Sql

[erhanemre]

Hi,

i have just upgraded all packages in my solutions and i have a strange bug. Some of my queries have exists filter. If i build the query like below, EF core generates 2 where statement in sql command text.

Query:

 IQueryable<TableEntity> query = unitOfWork.Context.Set<TableEntity>();
query = query.Where(q => unitOfWork.Context.GlEntityUsers.Any(t => t.RestrictedFlag == 0 && t.EntityCode == q.ChargeEntityCode));
query = query.Where(p => p.TransactionNo == TransactionNo);
var q1 = query.FirstOrDefault();

Generated Sql Where Statement:

WHERE (((EXISTS (
    SELECT `1`
    FROM "AVLOWN"."GL_ENTITY_USERS" "g2"
    WHERE (((("g2"."RESTRICTED_FLAG" = 0)) AND (("g2"."ENTITY_CODE" = "b"."CHARGE_ENTITY_CODE")))))) AND (("b"."TRANSACTION_NO" = :TransactionNo_0))))
where rownum <= 1 

If i remove Any filter, efcore generates correct command text.

 IQueryable<TableEntity> query = unitOfWork.Context.Set<TableEntity>();
query = query.Where(p => p.TransactionNo == TransactionNo);
var q1 = query.FirstOrDefault();

Generated Sql Where Statement:

WHERE ("b"."TRANSACTION_NO" = :TransactionNo_0)
and rownum <= 1 

is this a bug or am i doing something wrong?

[alexkeh]

This is not a known issue I've seen. Before you upgraded, were you using EF Core 3.1 with one of the Oracle EF Core 3.x betas? Do you have a more complete test case that reproduces the error?

[erhanemre]

Hi,

@alexkeh

Before you upgraded, were you using EF Core 3.1 with one of the Oracle EF Core 3.x betas?

No. Previous Oracle.EntityFrameworkCore package (2.19.90) only supports EF Core below 3.x (>=2.1.14 && < 3.0.0). But main API project were using ASP.NET Core 3.x. İ have added a project which reproduces the error.

https://github.com/erhanemre/CoreAPITest

The things that you should do is to create 2 tables with no relationship and set the database connection string.

create table TEMP_TABLE_1
(
    ID NUMBER(11) NOT NULL,
    ENTITY_CODE VARCHAR2(10) NOT NULL,
    PRIMARY KEY(ID)
)

create table TEMP_TABLE_2
(
    ENTITY_CODE VARCHAR2(10) NOT NULL,
    FLAG NUMBER(3) NOT NULL,
    PRIMARY KEY(ENTITY_CODE)
)

In test service you can see the query.

public async Task<TempTable1> Query1()
{
    IQueryable<TempTable1> query = _context.Set<TempTable1>();
    query = query.Where(i => _context.TempTable2.Any(x => x.Flag == 1 && i.EntityCode == x.EntityCode));
    query = query.Where(i => i.Id == 0);
    var result = await query.FirstOrDefaultAsync();
    return result;
}

I also added NLog package to log and see the queries that efcore generates.

2020-10-25 18:52:35.8585||INFO|Microsoft.Hosting.Lifetime|Application started. Press Ctrl+C to shut down. 
2020-10-25 18:52:35.9341||INFO|Microsoft.Hosting.Lifetime|Hosting environment: Development 
2020-10-25 18:52:35.9541||INFO|Microsoft.Hosting.Lifetime|Content root path: C:\Users\e_diga\source\repos\CoreAPI\CoreAPI 
2020-10-25 18:52:36.8301|10400|WARN|Microsoft.EntityFrameworkCore.Model.Validation|Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data, this mode should only be enabled during development. 
2020-10-25 18:52:39.7805||ERROR|Microsoft.EntityFrameworkCore.Database.Command|2020-10-25 18:52:39.778066 ThreadID:4   (ERROR)   OracleRelationalCommand.ExecuteReaderAsync() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00933: SQL command not properly ended
   at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
   at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) 
2020-10-25 18:52:39.7955|20102|ERROR|Microsoft.EntityFrameworkCore.Database.Command|Failed executing DbCommand (470ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
SELECT "t"."ID", "t"."ENTITY_CODE"
FROM "TEMP_TABLE_1" "t"
WHERE (((EXISTS (
    SELECT 1
    FROM "TEMP_TABLE_2" "t0"
    WHERE (((("t0"."FLAG" = 1)) AND (("t"."ENTITY_CODE" = "t0"."ENTITY_CODE")))))) AND (("t"."ID" = 0))))
where rownum <= 1 
2020-10-25 18:52:40.0006||ERROR|Microsoft.EntityFrameworkCore.Infrastructure|2020-10-25 18:52:39.998464 ThreadID:4   (ERROR)   OracleExecutionStrategy.ExecuteAsync() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00933: SQL command not properly ended
   at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
   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.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken) 

[alexkeh]

I've reproduced the error. I filed bug 32081523 to track the issue and have a dev team member review it.

The SQL generated is related to Oracle DB 11.2 SQL syntax. If you change the SQL compatibility level from "11" to any DB server level higher, it will generate working SQL for your app. It's the 11g type syntax that happens to be incorrectly generated. This is a workaround if you aren't actually using an 11.2 DB.

[EikeSchwass]

@alexkeh hitting the same error. Is this fixed?

[alexkeh]

Not yet. This bug looks like the same issue as documented in the Oracle EF Core README:

• Certain LINQs cannot be executed against Oracle Database 11.2. Let us first imagine an Entity Model with the following entities:

public class Gear { public string FullName { get; set; } public virtual ICollection Weapons { get; set; } }

public class Weapon { public int Id { get; set; } public bool IsAutomatic { get; set; } public string OwnerFullName { get; set; } public Gear Owner { get; set; } }

The following LINQ will not work against Oracle Database 11.2:

dbContext.Gear.Include(i => i.Weapons).OrderBy(o => o.Weapons.OrderBy(w => w.Id).FirstOrDefault().IsAutomatic).ToList();

This is due to LINQ creating the following SQL query:

SELECT "i"."FullName" FROM "Gear" "i" ORDER BY ( Select K0 "IsAutomatic" from( SELECT "w"."IsAutomatic" K0 FROM "Weapon" "w" WHERE ("i"."FullName" = "w"."OwnerFullName") ORDER BY "w"."Id" NULLS FIRST ) "m1" where rownum <= 1 ) NULLS FIRST, "i"."FullName" NULLS FIRST

Within the SELECT statement, there are two nested SELECTs. The generated SQL will encounter a ORA-00904 : "invalid identifier" error with Oracle Database 11.2 since it has a restriction where it does not recognize outer select table alias "i" in the inner nested select query.

I'm asking my dev team to confirm. If it's confirmed, it's unlikely we'll fix the bug at this point. Oracle DB 11.2 is EOL for almost everyone except for a small group of customers. The workaround is to upgrade to a supported DB. The root cause is related to the 11.2 SQL syntax generated.

[alexkeh]

Closing as DB 11.2 is generally out of support.