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.