Entity Framework Core - Take(1), Single(), First()... Not Working with Oracle Provider (ORA-00933: SQL command not properly ended)

Question

I'm using ef core(2.2.4) with oracle database

oracleProvider: Oracle.EntityFrameworkCore(2.18.0-beta3)

this code:

IQueryable<KeyInfo> queryable = context
                .KeyInfos
                .Where(x => x.MobileNumber == "989191111111")
                .Take(1);

generate this db query:

SELECT "x"."ID", "x"."Key", "x"."MobileNumber", "x"."NationalCode"
FROM "KeyInfo" "x"
WHERE "x"."MobileNumber" = N'989191111111'
FETCH FIRST 1 ROWS ONLY;

running query give me this error:

ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:
Error at Line: 4 Column: 1

is any way to fix it? the correct way is to get the first row with

AND rownum = 1

not

FETCH FIRST 1 ROWS ONLY

and .ToList() works fine with IQueryable

Answer 1

Apparently you are targeting an older Oracle database which doesn't support the newer FETCH FIRST N ROWS ONLY SQL construct.

In order to get the older ROWNUM based SQL translation, you should utilize the optional Action<OracleDbContextOptionsBuilder> oracleOptionsAction parameter of UseOracle method and UseOracleSQLCompatibility extension method with value "11" (the only currently supported values are "11" and "12"):

.UseOracle(connection_string, options => options
    .UseOracleSQLCompatibility("11"))