We have a production Oracle database server maintained by our ERP partner.
For some custom development I need to connect to this Oracle database using Entity Framework 6. I have a user that can SELECT any table on the ERP schema and I create views in the schema/user used in my EF context.
The view itself is pretty straightforward, a few joins but all referencing tables on another schema ofcourse.
i.e.:
CREATE TABLE ERP.M_GROUP
(
FILE VARCHAR2(3 BYTE)
, MATFAM VARCHAR2(1 BYTE) NOT NULL
, GROUP VARCHAR2(20 BYTE) NOT NULL
, OMS1 VARCHAR2(60 BYTE)
, OMS2 VARCHAR2(60 BYTE)
, RESTW_FACTOR1_I NUMBER
)
CREATE VIEW EF6CTX.GROUPS AS
SELECT
GROUP Id,
MAX(OMS1) Name
FROM
M_GROUP
WHERE
FILE = 'BAT'
AND MATFAM IN ('B','C','I', 'K')
GROUP BY GROEP
When I connect to my database using Visual Studio's Entity Framework 6 Code First from Database identifing as user EF6CTX I can select this view and my model is created as it should.
But when I try to read these groups..
var ctx = new TestContext();
ctx.Database.Log = Console.WriteLine;
foreach (var group in ctx.GROUPS)
{
Console.WriteLine("Group: {0}", group.NAME);
}
I get this result:
Opened connection at 21/11/2014 15:29:05 +01:00
Started transaction at 21/11/2014 15:29:05 +01:00
create table "EF6CTX"."GROUPS"
(
"ID" varchar2(20 CHAR) not null,
"NAME" varchar2(60 CHAR) null,
constraint "PK_GROUPS" primary key ("ID")
)
-- Executing at 21/11/2014 15:29:05 +01:00
-- Failed in 217 ms with error: ORA-01031: insufficient privileges
The user EF6CTX has no permissions to create a table.. ofcourse. But why is it trying to create a table? It should USE the existing view!