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

@PauloCampez yes and so tried FirstOrDefault and did'nt work — Mojtaba Khooryani, May 28 '19 at 11:45

Ivan Stoev · Accepted Answer · 2019-05-28T17:02:33.453

60

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"))

edited May 28 '19 at 17:02

answered May 28 '19 at 13:16

Ivan Stoev

195,425
15
312
343

There's any support to Oracle 10 ? I'm trying to make Entity Framework Core works with Oracle 10, but without success.. – Edson Rodrigues Oct 31 '19 at 10:46
@EdsonRodrigues According to the [EF Core Database Providers](https://learn.microsoft.com/en-us/ef/core/providers/?tabs=dotnet-core-cli) page, Supported database engines by Oracle.EntityFrameworkCore are *Oracle DB 11.2 onwards*. The page also shows a provider by DevArt supporting *Oracle DB 9.2.0.4 onwards*, but it's paid product. – Ivan Stoev Oct 31 '19 at 11:52
Hi @IvanStoev, I'm facing the same issue. Can you please guide me that how can I add this `OracleCompatibilty` while using `DI`. Here is the code which I'm using; `services.AddDbContext(options => options.UseOracle( configuration.GetConnectionString("connectionString"),oracleServerOptions => oracleServerOptions.CommandTimeout(60)), ServiceLifetime.Singleton);` – Arsman Ahmad May 10 '22 at 08:26
@ArsmanAhmad The fluent method `UseOracleSQLCompatibility` should show on your `oracleServerOptions` variable, e.g. `oracleServerOptions => oracleServerOptions.CommandTimeout(60)..UseOracleSQLCompatibility("11")` – Ivan Stoev May 10 '22 at 10:13
thats great. done. – Zia Ur Rahman Nov 22 '22 at 07:29

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

1 Answers1

Linked