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"