EFCore error:- ORA-00904: “m”.“Id”: invalid identifier

Question

I am working with an application using asp.net core 2.2 and efcore database first approach with Oracle database. I am using Oracle.EntityFrameworkCore (2.19.60) nuget package, successfully mapped db model, but when I try to fetch data from DBContext , getting error

ORA-00904: "m"."Id": invalid identifier

Oracle Database version: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

code:

var fetched = await myDatabaseContext.MyTableVersions.ToListAsync();

LinQ is generating following query :

SELECT "m"."Id", "m"."MAJORVERSION" FROM "MyTableVersions" "m"

Since It's not a correct syntax for PL/Sql query so getting error ORA-00904: "m"."Id": invalid identifier.

Is there any way to fix this error? Thanks.

I have searched on the web and found

Bug 30352492 - EFCORE: ORA-00904: INVALID IDENTIFIER

but that issue is related to schema.

Answer 1

The query is perfectly valid (db<>fiddle here), assuming your table looks something like

CREATE TABLE "MyTableVersions"
  ("Id"          NUMBER,
   MAJORVERSION  NUMBER)

However, I suspect your table looks like

CREATE TABLE "MyTableVersions"
  (ID            NUMBER,
   MAJORVERSION  NUMBER)

I don't know what the class looks like that you're trying to fetch into, but I suspect it has a field named Id. If you can change the name of that field to ID (in other words, so that its capitalization matches the capitalization of the related database column) you might find it works then.

Answer 2

Double quotes are something you should avoid in Oracle world.

Your query:

SELECT "m"."Id", "m"."MAJORVERSION" FROM "MyTableVersions" "m"

means that column name is exactly Id (capital I followed by d). Only if that's really so, you should use double quotes. Otherwise, simply remove them:

SELECT m.id, m.MAJORVERSION FROM MyTableVersions m

The same goes for the table name - if it was created using mixed case (and you can't do that without double quotes), you'll have to use double quotes and exactly same letter case always. Otherwise, don't use them.

Oracle is case-insensitive regarding object and column names and are UPPERCASE by default:

SQL> create table test (id number);

Table created.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 ID                                                 NUMBER

SQL> select table_name, column_name
  2  from user_tab_columns
  3  where table_name = 'TEST';

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
TEST                           ID

SQL> insert into test (id) values (1);

1 row created.

SQL>

But, if you use mixed case with double quotes, then use them always:

SQL> create table "teST" ("Id" number);

Table created.

SQL> insert into test (id) values (1);
insert into test (id) values (1)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> insert into "test" (id) values (1);
insert into "test" (id) values (1)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> insert into "teST" (id) values (1);
insert into "teST" (id) values (1)
                    *
ERROR at line 1:
ORA-00904: "ID": invalid identifier


SQL> insert into "teST" ("Id") values (1);

1 row created.

SQL>

---

So: first make sure what table and column names really are, then use them appropriately.

Answer 3

To avoid problems with case-sensitivity, add the option to eliminate EF to add quotes to the generated queries. For instance, if you are using Devart Oracle provider, this is done in the following way:

    public override void ConfigureServices(ServiceConfigurationContext context)
    {
        Configure<AbpDbContextOptions>(options =>
        {
            var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
            config.Workarounds.DisableQuoting = true;
            ...
        }
    }

If you are using official provider - just inherit from DbCommandInterceptor (do quotes replacement with empty character in dbCommand in ...Executing methods), add this interceptor to DbContextOptionsBuilder instance.