Hi. Is there a way to have scaffold-dbcontext pick up a primary key on a materializied view? I'm just getting results that have .HasNoKey() and a complaint that it can't scaffold a foreign key because it can't find the primary key. We get this message:
Could not scaffold the foreign key 'DATAMSTR.TEST_PR_MVW(SAMPLE_ID)'. A key for 'SAMPLE_ID' was not found in the principal entity type 'TestSampMvw'.
Here is the DDL to create the views. This is in .net 6 using Oracle.EntityFrameworkCore 7.21.9 and Oracle.ManagedDataAccess.Core 3.21.90. Thanks.
CREATE MATERIALIZED VIEW test_samp_mvw TABLESPACE "SIRDATA" BUILD IMMEDIATE USING INDEX REFRESH FORCE ON DEMAND USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE AS select distinct sa.sample_id, sa.station_id, sa.sample_fr_date, sa.wada_exclude_indr from envi_sample sa inner join envi_station st on sa.station_id = st.station_id inner join station_cwa_group scg on st.station_id = scg.station_id inner join station_cwa_category scc on st.station_id = scc.station_id where sa.sample_fr_date > to_date('2013-01-01', 'YYYY-MM-DD') and st.station_status_desc_e = 'Active' and scg.cwa_group_id = 15 and scc.cwa_category_id <> 32 ;
CREATE MATERIALIZED VIEW test_pr_mvw TABLESPACE "SIRDATA" BUILD IMMEDIATE USING INDEX REFRESH FORCE ON DEMAND USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE AS select distinct pr.parameter_result_id, pr.sample_id, sa.station_id, pr.parameter_cd, pr.parameter_name_e, pr.parameter_name_f, pr.parameter_flag, pr.parameter_flag_cd, pr.parameter_result_value, pr.parameter_result_noofdec, pr.unit_desc_e, pr.unit_desc_f, pr.unit_cd from envi_parameter_result pr inner join envi_sample sa on pr.sample_id = sa.sample_id inner join envi_station st on sa.station_id = st.station_id inner join station_cwa_group scg on st.station_id = scg.station_id inner join station_cwa_category scc on st.station_id = scc.station_id where sa.sample_fr_date > to_date('2013-01-01', 'YYYY-MM-DD') and st.station_status_desc_e = 'Active' and scg.cwa_group_id = 15 and scc.cwa_category_id <> 32;
CREATE UNIQUE INDEX PK_test_samp_mvw ON test_samp_mvw (sample_ID ASC) TABLESPACE SIRINDX;
ALTER TABLE test_samp_mvw ADD CONSTRAINT PK_test_samp_mvw PRIMARY KEY (Sample_ID);
CREATE UNIQUE INDEX PK_test_pr_mvw ON test_pr_mvw (parameter_result_ID ASC) TABLESPACE SIRINDX;
ALTER TABLE test_pr_mvw ADD CONSTRAINT PK_test_pr_mvw PRIMARY KEY (parameter_result_ID);
create index ak_test_pr_mvw on test_pr_mvw (sample_id ASC);
ALTER TABLE test_pr_mvw ADD (CONSTRAINT R_test_pr_mvw_01 FOREIGN KEY (sample_id) REFERENCES test_samp_mvw (sample_ID));