实体框架核心-Take(1)，Single()，First()...无法与Oracle Provider一起使用(ORA-00933:SQL命令未正确结束) [英] Entity Framework Core - Take(1), Single(), First()... Not Working with Oracle Provider (ORA-00933: SQL command not properly ended)

问题描述

我正在将ef core(2.2.4)与oracle数据库一起使用

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

oracleProvider:Oracle.EntityFrameworkCore(2.18.0-beta3)

oracleProvider: Oracle.EntityFrameworkCore(2.18.0-beta3)

此代码:

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

生成此数据库查询:

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

AND rownum = 1

不是

仅先查询1行

FETCH FIRST 1 ROWS ONLY

和.ToList()可与IQueryable一起使用

and .ToList() works fine with IQueryable

推荐答案

显然，您的目标是不支持较新的FETCH FIRST N ROWS ONLY SQL构造的旧Oracle数据库.

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

为了获得较旧的基于ROWNUM的SQL转换，您应该使用UseOracle方法和UseOracleSQLCompatibility扩展方法的可选Action<OracleDbContextOptionsBuilder> oracleOptionsAction参数，其值为"11"(当前唯一支持的值为"11" "和"12"):

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

这篇关于实体框架核心-Take(1)，Single()，First()...无法与Oracle Provider一起使用(ORA-00933:SQL命令未正确结束)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！