Foreign Keys only included if connected as constraint owner

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
khn@atp.dk
Posts: 1
Joined: Wed 28 Nov 2018 08:01

Foreign Keys only included if connected as constraint owner

Post by khn@atp.dk » Wed 28 Nov 2018 08:27

Hi,

When making a "ADO.NET Entity Data Model" (.edmx) in Visual Studio 2017 using database first approach on an Oracle DB, we have problems with foreign keys. If the connection credentials used to import database tables is different from the owner of any foreign key constraints between the tables, the constrains are not imported into the model.

It is a major problem for us as we are not allowed to connect to our database using the owning user when developing on our local environments. There we have to use personal, kerberos based users.

Note that this is not an issue when using Devart EF Core Model (.dfml). FKs are imported correctly using the exact same user as when failing on edmx models. However, we cannot use dotConnect specific models as our build server is Azure DevOps hosted so installing dotConnect on it is not possible.

From what we can see, it seems to be (at least) the following statement executed by VS / EF when importing tables:

Code: Select all

SELECT "Extent3"."Ordinal",
                         0 AS C1,
                         "Extent3"."ConstraintId",
                         6 AS C2,
                         "Extent3"."FromColumnId",
                         6 AS C3,
                         "Extent3"."ToColumnId"
                    FROM (  /*SForeignKeys {62BB3537-F32C-43b2-9B99-72AF5882622F} {BFD32F32-EEE4-4826-9725-C489B0EE1ACC}*/
                            SELECT    FKCOLS.OWNER
                                   || '.'
                                   || FKCOLS.TABLE_NAME
                                   || '.'
                                   || FKCOLS.CONSTRAINT_NAME
                                   || '.'
                                   || FKCOLS.COLUMN_NAME
                                      AS "Id",
                                   (SELECT    RCOLS.OWNER
                                           || '.'
                                           || RCOLS.TABLE_NAME
                                           || '.'
                                           || RCOLS.COLUMN_NAME
                                              AS "RColumnId"
                                      FROM SYS.ALL_CONS_COLUMNS RCOLS
                                     WHERE     RCOLS.OWNER = FKCON.R_OWNER
                                           AND RCOLS.CONSTRAINT_NAME =
                                                  FKCON.R_CONSTRAINT_NAME
                                           AND RCOLS.POSITION = FKCOLS.POSITION)
                                      AS "ToColumnId",
                                      FKCOLS.OWNER
                                   || '.'
                                   || FKCOLS.TABLE_NAME
                                   || '.'
                                   || FKCOLS.COLUMN_NAME
                                      AS "FromColumnId",
                                      FKCOLS.OWNER
                                   || '.'
                                   || FKCOLS.CONSTRAINT_NAME
                                      AS "ConstraintId",
                                   FKCOLS.POSITION AS "Ordinal"
                              FROM SYS.ALL_CONS_COLUMNS FKCOLS
                                   JOIN SYS.ALL_CONSTRAINTS FKCON
                                      ON (    FKCOLS.OWNER = FKCON.OWNER
                                          AND FKCOLS.CONSTRAINT_NAME =
                                                 FKCON.CONSTRAINT_NAME)
                                   LEFT OUTER JOIN USER_TAB_PRIVS P
                                      ON (    FKCOLS.OWNER = P.OWNER
                                          AND P.GRANTEE = USER
                                         AND P.TABLE_NAME = FKCOLS.TABLE_NAME
                                          AND (   P."PRIVILEGE" = 'SELECT'
                                               OR P."PRIVILEGE" = 'EXECUTE'))
                             WHERE     FKCON.CONSTRAINT_TYPE = 'R'
                                   AND (FKCON.OWNER = USER OR P.GRANTEE = USER)
                          ORDER BY FKCOLS.OWNER,
                                   FKCOLS.CONSTRAINT_NAME,
                                   FKCOLS.TABLE_NAME) "Extent3"
Using Visual Studio 2017 v 15.8.8 and dotConnect for Oracle v. 9.6.621

Shalex
Site Admin
Posts: 8691
Joined: Thu 14 Aug 2008 12:44

Re: Foreign Keys only included if connected as constraint owner

Post by Shalex » Wed 05 Dec 2018 17:21

khn@atp.dk wrote:
Wed 28 Nov 2018 08:27
When making a "ADO.NET Entity Data Model" (.edmx) in Visual Studio 2017 using database first approach on an Oracle DB, we have problems with foreign keys. If the connection credentials used to import database tables is different from the owner of any foreign key constraints between the tables, the constrains are not imported into the model.
1. We cannot reproduce the issue with dotConnect for Oracle v9.6.646 at the moment.

Please create two schemas for a simple test:

Code: Select all

CONNECT SYSTEM/manager@orcl12c

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;

CREATE TABLE SCOTT.DEPT (
DEPTNO NUMBER(4) CONSTRAINT SCOTT.PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13)
);
CREATE TABLE SCOTT.EMP (
EMPNO NUMBER(4) CONSTRAINT SCOTT.PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(4) CONSTRAINT SCOTT.FK_DEPTNO REFERENCES SCOTT.DEPT
);

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT2 IDENTIFIED BY TIGER2;
ALTER USER SCOTT2 DEFAULT TABLESPACE USERS;
ALTER USER SCOTT2 TEMPORARY TABLESPACE TEMP;
GRANT SELECT ON SCOTT.DEPT TO SCOTT2;
GRANT SELECT ON SCOTT.EMP TO SCOTT2;
Then use credentials SCOTT2/TIGER2 with "ADO.NET Entity Data Model" (.edmx) in Visual Studio 2017. Can you reproduce the problem with missing SCOTT.FK_DEPTNO in this case?
khn@atp.dk wrote:
Wed 28 Nov 2018 08:27
Note that this is not an issue when using Devart EF Core Model (.dfml). FKs are imported correctly using the exact same user as when failing on edmx models. However, we cannot use dotConnect specific models as our build server is Azure DevOps hosted so installing dotConnect on it is not possible.
2. You are working with EF6, aren't you? As a workaround, please use "Devart Entity Model" (*.edml) in this scenario:
a) generate the *.ssdl, *.csdl, *.msl files on your development workstation. For this, set Metadata Artifact Processing property of your model to "Copy To Output Directory"
b) add *.ssdl, *.csdl, *.msl to the project on your build server
c) set Build Action=Embedded resource in the properties of *.ssdl, *.csdl, *.msl
d) compile your MyApplication.dll and open it in JustDecompile ( https://www.telerik.com/products/decompiler.aspx ) to find out the actual names of resources to specify them in connection string

For example, after I added DataModel1.csdl, DataModel1.ssdl, DataModel1.msl to MyApplication, the names of resources included the "MyApplication." prefix, the result connection string was the following:

Code: Select all

connectionString="metadata=res://MyApplication/MyApplication.DataModel1.csdl|res://MyApplication/MyApplication.DataModel1.ssdl|res://MyApplication/MyApplication.DataModel1.msl;...

Shalex
Site Admin
Posts: 8691
Joined: Thu 14 Aug 2008 12:44

Re: Foreign Keys only included if connected as constraint owner

Post by Shalex » Thu 10 Jan 2019 18:53

Refer to viewtopic.php?f=1&t=38262:
  • The bug with accessing FKs, when the SELECT permission is granted through roles, by EDM Wizard is fixed
  • The performance of EDM Wizard is improved

Post Reply