Hi,
We are launching a project to migrate our legacy application to the ASP.NET platform. Our new application will be using Entity Framework to connect to our Oracle database. I have downloaded Devart for Oracle 5.2 Beta and gave it a try. The result was not satisfactory, unfortunately. I’m hoping to get some help here. Our database is fairly complicated, with 200 active tables. Like many other legacy systems, the database schema is not perfect but we have to live with it. When I tried to generate an entity model from it, I got a whole bunch of errors and messages. Here are the main ones:
1. The error message says “Schema specified is not valid. Errors: (1,229707) : error 2039: The conceptual side property 'HSS_CODE' has already been mapped to a storage property with type 'decimal'. If the conceptual side property is mapped to multiple properties in the storage model, make sure that all the properties in the storage model have the same type.”
I did some research and found that the data types of the foreign keys in many tables are not completely consistent. In this particular case, the column “HSS_CODE” is defined as NUMBER in the parent table but defined as NUMBER(10) in some child tables. The Devart provider is mapping NUMBER(10) to int64, and NUMBER to decimal, thus the inconsistency. One possible solution is to change our database schema, but our DBA is very uncomfortable with this. So, on the Devart side, is there any possibility to configure which .NET data type to use for each Oracle data type, or to make the Devart provider “ignore” the minor incompatibility between NUMBER and NUMBER(?)?
2. I got about 100 messages like “The relationship 'TGLN.HOSP_PROV_FK' has columns that are not part of the key of the table on the primary side of the relationship which is not supported, the relationship was excluded.”
Looks like this happens as long as a primary key/foreign key contains more than one column.
Thanks
Jerry
Problem handling large Oracle database
The first problem can be solved by manual type remapping (replace Int64 with decimal or decimal with Int64, as you need).
This can be done using any XML Editor you like, simply open the .edmx file and make necessary changes.
Please note that you should change types both in SSDL and CSDL parts of the .edmx file.
As for the second problem, it looks like the one described here:
http://social.msdn.microsoft.com/forums ... 2457cca94/.
If so, nothing can be done.
If this post doesn't make the things clear, please post here the definitions of any two tables with the excluded foreign key, I'll look into it.
As an alternative, try LINQ to Oracle, if it suits your needs. Similar foreign key problem should not appear there.
This can be done using any XML Editor you like, simply open the .edmx file and make necessary changes.
Please note that you should change types both in SSDL and CSDL parts of the .edmx file.
As for the second problem, it looks like the one described here:
http://social.msdn.microsoft.com/forums ... 2457cca94/.
If so, nothing can be done.
If this post doesn't make the things clear, please post here the definitions of any two tables with the excluded foreign key, I'll look into it.
As an alternative, try LINQ to Oracle, if it suits your needs. Similar foreign key problem should not appear there.
1. I thought about changing the data types manually in the .edmx file. However, I don't get a chance to do it. At the end of the wizard, after reporting the error messages, it fails to generate the .edmx file. In fact, it only generates some header information without any entity definitions in it.
2. It's not the same problem as the post you forwarded. In my case, the HOSP table is referencing the PK of the PROV table. It's just that the PK contains two columns. They look completely legitimate and normal. Here are the definitions of the keys:
Primary Key in PROV table:
CONSTRAINT PROV_PK PRIMARY KEY (PROV_CODE, COUN_CODE));
Foreign Key in HOSP table:
CONSTRAINT HOSP_PROV_FK FOREIGN KEY (PROV_CODE, COUN_CODE)
REFERENCES PROV (PROV_CODE,COUN_CODE));
2. It's not the same problem as the post you forwarded. In my case, the HOSP table is referencing the PK of the PROV table. It's just that the PK contains two columns. They look completely legitimate and normal. Here are the definitions of the keys:
Primary Key in PROV table:
CONSTRAINT PROV_PK PRIMARY KEY (PROV_CODE, COUN_CODE));
Foreign Key in HOSP table:
CONSTRAINT HOSP_PROV_FK FOREIGN KEY (PROV_CODE, COUN_CODE)
REFERENCES PROV (PROV_CODE,COUN_CODE));
I have the same problem. I download the latest version of dotConnect for Oracle 5.0.22.0 and generating the edmx I get this error:
The relationship 'ONHOSPITAL.T_AMB_APP_PRESTAZIONI_R01' has columns that are not part of the key of the table on the primary side of the relationship which is not supported, the relationship was excluded
The relationship script is:
ALTER TABLE ONHOSPITAL.T_AMB_APP_PRESTAZIONI ADD (
CONSTRAINT T_AMB_APP_PRESTAZIONI_R01
FOREIGN KEY (TAP_APP_NUMERO, TAP_APP_RIC_CODICE, TAP_AZI_CODICE)
REFERENCES ONHOSPITAL.T_AMB_APPUNTAMENTI (APP_NUMERO,APP_RIC_CODICE,APP_AZI_CODICE)
The script for the master table T_AMB_APPUNTAMENTI is:
CREATE TABLE ONHOSPITAL.T_AMB_APPUNTAMENTI
(
APP_NUMERO NUMBER(5) NOT NULL,
APP_RIC_CODICE VARCHAR2(12 BYTE) NOT NULL,
APP_UNI_CODICE NUMBER(5) NOT NULL,
APP_SED_CODICE NUMBER(5),
APP_DATA_ORA DATE NOT NULL,
APP_DURATA NUMBER(5),
APP_STA_CODICE NUMBER(5) NOT NULL,
APP_DATA_REGISTRAZIONE DATE NOT NULL,
APP_UTE_REGISTRAZIONE NUMBER(10) NOT NULL,
APP_APP_NUMERO NUMBER(5),
APP_ACC_NUMERO NUMBER(8),
APP_TIPO_AGENDA VARCHAR2(1 BYTE),
APP_FASCIA_CUP VARCHAR2(8 BYTE),
APP_DATA_SPOSTAMENTO DATE,
APP_UTE_SPOSTAMENTO NUMBER(10),
APP_DATA_REVOCA DATE,
APP_UTE_REVOCA NUMBER(10),
APP_NOTE VARCHAR2(4000 BYTE),
APP_CODICE NUMBER(8),
APP_AZI_CODICE VARCHAR2(10 BYTE) DEFAULT '080112' NOT NULL,
APP_DATA_ORA_ARROT DATE
);
ALTER TABLE ONHOSPITAL.T_AMB_APPUNTAMENTI ADD (
CONSTRAINT T_AMB_APPUNTAMENTI_PK
PRIMARY KEY
(APP_NUMERO, APP_RIC_CODICE, APP_AZI_CODICE);
And the script for the detail table T_AMB_APP_PRESTAZIONI is:
CREATE TABLE ONHOSPITAL.T_AMB_APP_PRESTAZIONI
(
TAP_APP_NUMERO NUMBER(5) NOT NULL,
TAP_PRE_CODICE NUMBER(5) NOT NULL,
TAP_PRF_CODICE NUMBER(5),
TAP_AZI_CODICE VARCHAR2(10 BYTE) DEFAULT '080112' NOT NULL,
TAP_APP_RIC_CODICE VARCHAR2(12 BYTE) NOT NULL,
TAP_UNIERO_DEFAULT VARCHAR2(1 BYTE)
);
ALTER TABLE ONHOSPITAL.T_AMB_APP_PRESTAZIONI ADD (
CONSTRAINT T_AMB_APP_PRESTAZIONI_PK
PRIMARY KEY
(TAP_APP_NUMERO, TAP_PRE_CODICE, TAP_AZI_CODICE, TAP_APP_RIC_CODICE);
With version 4.75.43.0, I was able to generate edmx without any problem.
So I can't migrate to new version
The relationship 'ONHOSPITAL.T_AMB_APP_PRESTAZIONI_R01' has columns that are not part of the key of the table on the primary side of the relationship which is not supported, the relationship was excluded
The relationship script is:
ALTER TABLE ONHOSPITAL.T_AMB_APP_PRESTAZIONI ADD (
CONSTRAINT T_AMB_APP_PRESTAZIONI_R01
FOREIGN KEY (TAP_APP_NUMERO, TAP_APP_RIC_CODICE, TAP_AZI_CODICE)
REFERENCES ONHOSPITAL.T_AMB_APPUNTAMENTI (APP_NUMERO,APP_RIC_CODICE,APP_AZI_CODICE)
The script for the master table T_AMB_APPUNTAMENTI is:
CREATE TABLE ONHOSPITAL.T_AMB_APPUNTAMENTI
(
APP_NUMERO NUMBER(5) NOT NULL,
APP_RIC_CODICE VARCHAR2(12 BYTE) NOT NULL,
APP_UNI_CODICE NUMBER(5) NOT NULL,
APP_SED_CODICE NUMBER(5),
APP_DATA_ORA DATE NOT NULL,
APP_DURATA NUMBER(5),
APP_STA_CODICE NUMBER(5) NOT NULL,
APP_DATA_REGISTRAZIONE DATE NOT NULL,
APP_UTE_REGISTRAZIONE NUMBER(10) NOT NULL,
APP_APP_NUMERO NUMBER(5),
APP_ACC_NUMERO NUMBER(8),
APP_TIPO_AGENDA VARCHAR2(1 BYTE),
APP_FASCIA_CUP VARCHAR2(8 BYTE),
APP_DATA_SPOSTAMENTO DATE,
APP_UTE_SPOSTAMENTO NUMBER(10),
APP_DATA_REVOCA DATE,
APP_UTE_REVOCA NUMBER(10),
APP_NOTE VARCHAR2(4000 BYTE),
APP_CODICE NUMBER(8),
APP_AZI_CODICE VARCHAR2(10 BYTE) DEFAULT '080112' NOT NULL,
APP_DATA_ORA_ARROT DATE
);
ALTER TABLE ONHOSPITAL.T_AMB_APPUNTAMENTI ADD (
CONSTRAINT T_AMB_APPUNTAMENTI_PK
PRIMARY KEY
(APP_NUMERO, APP_RIC_CODICE, APP_AZI_CODICE);
And the script for the detail table T_AMB_APP_PRESTAZIONI is:
CREATE TABLE ONHOSPITAL.T_AMB_APP_PRESTAZIONI
(
TAP_APP_NUMERO NUMBER(5) NOT NULL,
TAP_PRE_CODICE NUMBER(5) NOT NULL,
TAP_PRF_CODICE NUMBER(5),
TAP_AZI_CODICE VARCHAR2(10 BYTE) DEFAULT '080112' NOT NULL,
TAP_APP_RIC_CODICE VARCHAR2(12 BYTE) NOT NULL,
TAP_UNIERO_DEFAULT VARCHAR2(1 BYTE)
);
ALTER TABLE ONHOSPITAL.T_AMB_APP_PRESTAZIONI ADD (
CONSTRAINT T_AMB_APP_PRESTAZIONI_PK
PRIMARY KEY
(TAP_APP_NUMERO, TAP_PRE_CODICE, TAP_AZI_CODICE, TAP_APP_RIC_CODICE);
With version 4.75.43.0, I was able to generate edmx without any problem.
So I can't migrate to new version
I have the same problem. I download the latest version of dotConnect for Oracle 5.0.22.0 and generating the edmx I get this error:
The relationship 'ONHOSPITAL.T_AMB_APP_PRESTAZIONI_R01' has columns that are not part of the key of the table on the primary side of the relationship which is not supported, the relationship was excluded
The relationship script is:
ALTER TABLE ONHOSPITAL.T_AMB_APP_PRESTAZIONI ADD (
CONSTRAINT T_AMB_APP_PRESTAZIONI_R01
FOREIGN KEY (TAP_APP_NUMERO, TAP_APP_RIC_CODICE, TAP_AZI_CODICE)
REFERENCES ONHOSPITAL.T_AMB_APPUNTAMENTI (APP_NUMERO,APP_RIC_CODICE,APP_AZI_CODICE)
The script for the master table T_AMB_APPUNTAMENTI is:
CREATE TABLE ONHOSPITAL.T_AMB_APPUNTAMENTI
(
APP_NUMERO NUMBER(5) NOT NULL,
APP_RIC_CODICE VARCHAR2(12 BYTE) NOT NULL,
APP_UNI_CODICE NUMBER(5) NOT NULL,
APP_SED_CODICE NUMBER(5),
APP_DATA_ORA DATE NOT NULL,
APP_DURATA NUMBER(5),
APP_STA_CODICE NUMBER(5) NOT NULL,
APP_DATA_REGISTRAZIONE DATE NOT NULL,
APP_UTE_REGISTRAZIONE NUMBER(10) NOT NULL,
APP_APP_NUMERO NUMBER(5),
APP_ACC_NUMERO NUMBER(8),
APP_TIPO_AGENDA VARCHAR2(1 BYTE),
APP_FASCIA_CUP VARCHAR2(8 BYTE),
APP_DATA_SPOSTAMENTO DATE,
APP_UTE_SPOSTAMENTO NUMBER(10),
APP_DATA_REVOCA DATE,
APP_UTE_REVOCA NUMBER(10),
APP_NOTE VARCHAR2(4000 BYTE),
APP_CODICE NUMBER(8),
APP_AZI_CODICE VARCHAR2(10 BYTE) DEFAULT '080112' NOT NULL,
APP_DATA_ORA_ARROT DATE
);
ALTER TABLE ONHOSPITAL.T_AMB_APPUNTAMENTI ADD (
CONSTRAINT T_AMB_APPUNTAMENTI_PK
PRIMARY KEY
(APP_NUMERO, APP_RIC_CODICE, APP_AZI_CODICE);
And the script for the detail table T_AMB_APP_PRESTAZIONI is:
CREATE TABLE ONHOSPITAL.T_AMB_APP_PRESTAZIONI
(
TAP_APP_NUMERO NUMBER(5) NOT NULL,
TAP_PRE_CODICE NUMBER(5) NOT NULL,
TAP_PRF_CODICE NUMBER(5),
TAP_AZI_CODICE VARCHAR2(10 BYTE) DEFAULT '080112' NOT NULL,
TAP_APP_RIC_CODICE VARCHAR2(12 BYTE) NOT NULL,
TAP_UNIERO_DEFAULT VARCHAR2(1 BYTE)
);
ALTER TABLE ONHOSPITAL.T_AMB_APP_PRESTAZIONI ADD (
CONSTRAINT T_AMB_APP_PRESTAZIONI_PK
PRIMARY KEY
(TAP_APP_NUMERO, TAP_PRE_CODICE, TAP_AZI_CODICE, TAP_APP_RIC_CODICE);
With version 4.75.43.0, I was able to generate edmx without any problem.
So I can't migrate to new version
The relationship 'ONHOSPITAL.T_AMB_APP_PRESTAZIONI_R01' has columns that are not part of the key of the table on the primary side of the relationship which is not supported, the relationship was excluded
The relationship script is:
ALTER TABLE ONHOSPITAL.T_AMB_APP_PRESTAZIONI ADD (
CONSTRAINT T_AMB_APP_PRESTAZIONI_R01
FOREIGN KEY (TAP_APP_NUMERO, TAP_APP_RIC_CODICE, TAP_AZI_CODICE)
REFERENCES ONHOSPITAL.T_AMB_APPUNTAMENTI (APP_NUMERO,APP_RIC_CODICE,APP_AZI_CODICE)
The script for the master table T_AMB_APPUNTAMENTI is:
CREATE TABLE ONHOSPITAL.T_AMB_APPUNTAMENTI
(
APP_NUMERO NUMBER(5) NOT NULL,
APP_RIC_CODICE VARCHAR2(12 BYTE) NOT NULL,
APP_UNI_CODICE NUMBER(5) NOT NULL,
APP_SED_CODICE NUMBER(5),
APP_DATA_ORA DATE NOT NULL,
APP_DURATA NUMBER(5),
APP_STA_CODICE NUMBER(5) NOT NULL,
APP_DATA_REGISTRAZIONE DATE NOT NULL,
APP_UTE_REGISTRAZIONE NUMBER(10) NOT NULL,
APP_APP_NUMERO NUMBER(5),
APP_ACC_NUMERO NUMBER(8),
APP_TIPO_AGENDA VARCHAR2(1 BYTE),
APP_FASCIA_CUP VARCHAR2(8 BYTE),
APP_DATA_SPOSTAMENTO DATE,
APP_UTE_SPOSTAMENTO NUMBER(10),
APP_DATA_REVOCA DATE,
APP_UTE_REVOCA NUMBER(10),
APP_NOTE VARCHAR2(4000 BYTE),
APP_CODICE NUMBER(8),
APP_AZI_CODICE VARCHAR2(10 BYTE) DEFAULT '080112' NOT NULL,
APP_DATA_ORA_ARROT DATE
);
ALTER TABLE ONHOSPITAL.T_AMB_APPUNTAMENTI ADD (
CONSTRAINT T_AMB_APPUNTAMENTI_PK
PRIMARY KEY
(APP_NUMERO, APP_RIC_CODICE, APP_AZI_CODICE);
And the script for the detail table T_AMB_APP_PRESTAZIONI is:
CREATE TABLE ONHOSPITAL.T_AMB_APP_PRESTAZIONI
(
TAP_APP_NUMERO NUMBER(5) NOT NULL,
TAP_PRE_CODICE NUMBER(5) NOT NULL,
TAP_PRF_CODICE NUMBER(5),
TAP_AZI_CODICE VARCHAR2(10 BYTE) DEFAULT '080112' NOT NULL,
TAP_APP_RIC_CODICE VARCHAR2(12 BYTE) NOT NULL,
TAP_UNIERO_DEFAULT VARCHAR2(1 BYTE)
);
ALTER TABLE ONHOSPITAL.T_AMB_APP_PRESTAZIONI ADD (
CONSTRAINT T_AMB_APP_PRESTAZIONI_PK
PRIMARY KEY
(TAP_APP_NUMERO, TAP_PRE_CODICE, TAP_AZI_CODICE, TAP_APP_RIC_CODICE);
With version 4.75.43.0, I was able to generate edmx without any problem.
So I can't migrate to new version