Scaffold-DbContext filter with schemas and tables mixed not working #73
Description
I have a huge Oracle 11 db with a lot of schemas/tables.
When I run Scaffold-DbContext passing -Schemas and -Tables parameters all tables in all schemas are generated; -Tables filter is ignored.
I made a test installation of Oracle 11 using official docker image and I created an empty DB with sample schemas and tables:
CREATE USER SCHEMAA IDENTIFIED BY SCHEMAAPASSWORD;
/
GRANT CONNECT TO SCHEMAA;
/
GRANT CONNECT, RESOURCE, DBA TO SCHEMAA;
/
GRANT CREATE SESSION TO SCHEMAA;
/
CREATE USER SCHEMAB IDENTIFIED BY SCHEMABPASSWORD;
/
GRANT CONNECT TO SCHEMAB;
/
GRANT CONNECT, RESOURCE, DBA TO SCHEMAB;
/
GRANT CREATE SESSION TO SCHEMAB;
/
CREATE TABLE SCHEMAA.CUSTOMERS (
ID NUMBER(10,0) NOT NULL,
NAME VARCHAR2(250) NOT NULL,
LASTNAME VARCHAR2(250) NOT NULL,
PRIMARY KEY (ID)
)
/
CREATE TABLE SCHEMAB.EMPLOYEES (
ID NUMBER(10,0) NOT NULL,
NAME VARCHAR2(250) NOT NULL,
LASTNAME VARCHAR2(250) NOT NULL,
PRIMARY KEY (ID)
)
/
CREATE TABLE SCHEMAB.SALARIES (
ID NUMBER(10,0) NOT NULL PRIMARY KEY,
PAYDATE DATE NOT NULL,
AMOUNT NUMBER(14,6) NOT NULL,
CURRENCY VARCHAR2(50) NOT NULL,
EMPLOYEE_ID NUMBER(10,0) NOT NULL,
FOREIGN KEY (EMPLOYEE_ID) REFERENCES SCHEMAB.EMPLOYEES(ID)
)
/Then, I run the following Scaffold-DbContext command:
Scaffold-DbContext "DATA SOURCE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=XE)));PASSWORD=<<REDACTED>>;PERSIST SECURITY INFO=True;USER ID=SYSTEM" Oracle.EntityFrameworkCore -Force -Schemas SCHEMAA, SCHEMAB -Tables CUSTOMERS,EMPLOYEESThe expected result is that only CUSTOMERS and EMPLOYEES table are generated but when the command finishes execution also SALARIES appears in my .NET project
Since provider is not open sourced, to understand what's going wrong, I enabled sql tracing on db and probably I found the bugged query:
SELECT t.table_name name, t.owner schema FROM all_tables t WHERE t.table_name <> '__EFMigrationsHistory' AND (t.owner IN (:s0, :s1)
OR t.table_name IN (:twiths0, :twiths1) AND CONCAT(t.owner, CONCAT(N'.', t.table_name)) IN (:sdott0, :sdott1))Replacing parameters with values passed from command line:
SELECT t.table_name name, t.owner schema FROM all_tables t WHERE t.table_name <> '__EFMigrationsHistory' AND (t.owner IN ('SCHEMAA', 'SCHEMAB')
OR t.table_name IN ('EMPLOYEES', 'CUSTOMERS') AND CONCAT(t.owner, CONCAT(N'.', t.table_name)) IN ('SCHEMAA.CUSTOMERS', 'SCHEMAB.EMPLOYEES'));| NAME | SCHEMA |
|---|---|
| CUSTOMERS | SCHEMAA |
| EMPLOYEES | SCHEMAB |
| SALARIES | SCHEMAB |
I think to problem is OR condition that need to be switched to an AND:
SELECT t.table_name name, t.owner schema FROM all_tables t WHERE t.table_name <> '__EFMigrationsHistory' AND (t.owner IN ('SCHEMAA', 'SCHEMAB')
AND t.table_name IN ('EMPLOYEES', 'CUSTOMERS') AND CONCAT(t.owner, CONCAT(N'.', t.table_name)) IN ('SCHEMAA.CUSTOMERS', 'SCHEMAB.EMPLOYEES'));| NAME | SCHEMA |
|---|---|
| EMPLOYEES | SCHEMAB |
| CUSTOMERS | SCHEMAA |