As complement, here is an example that produces the error :
Database :
Code: Select all
CREATE TABLE "USER"."ENTITY_BASE"
( "ENTITY_BASE_ID" NUMBER(8,0) NOT NULL ENABLE,
"ENTITY_NAME" VARCHAR2(100 BYTE),
CONSTRAINT "ENTITY1_PK" PRIMARY KEY ("ENTITY_BASE_ID")
)
CREATE TABLE "USER"."D1_ENTITY1"
( "D1_ENTITY1_ID" NUMBER(8,0) NOT NULL ENABLE,
"NUMBER_VALUE" NUMBER,
CONSTRAINT "ENTITY2_PK" PRIMARY KEY ("D1_ENTITY1_ID"),
CONSTRAINT "D1_ENTITY1_BASE" FOREIGN KEY ("D1_ENTITY1_ID")
REFERENCES "USER"."ENTITY_BASE" ("ENTITY_BASE_ID")
)
CREATE TABLE "USER"."D1_ENTITY2"
( "D1_ENTITY2_ID" NUMBER(8,0) NOT NULL ENABLE,
CONSTRAINT "ENTITY3_PK" PRIMARY KEY ("D1_ENTITY2_ID"),
CONSTRAINT "D1_ENTITY2_BASE" FOREIGN KEY ("D1_ENTITY2_ID")
REFERENCES "USER"."ENTITY_BASE" ("ENTITY_BASE_ID")
)
CREATE TABLE "USER"."D2_ENTITY3"
( "D2_ENTITY3_ID" NUMBER(8,0) NOT NULL ENABLE,
"NUMBER_VALUE" NUMBER,
CONSTRAINT "D2_ENTITY3_PK" PRIMARY KEY ("D2_ENTITY3_ID"),
CONSTRAINT "D2_ENTITY3_BASE" FOREIGN KEY ("D2_ENTITY3_ID")
REFERENCES "USER"."D1_ENTITY2" ("D1_ENTITY2_ID")
)
CREATE TABLE "USER"."D2_ENTITY4"
( "D2_ENTITY4_ID" NUMBER(8,0) NOT NULL ENABLE,
CONSTRAINT "D2_ENTITY4_PK" PRIMARY KEY ("D2_ENTITY4_ID"),
CONSTRAINT "D2_ENTITY4_BASE" FOREIGN KEY ("D2_ENTITY4_ID")
REFERENCES "USER"."D1_ENTITY2" ("D1_ENTITY2_ID")
)
The SSDL content :
The CSDL content :
The mapping content :
The conceptuel model is like this ( => for generalization) :
Code: Select all
D1_ENTITY_1 => ENTITY_BASE
D1_ENTITY_2 => ENTITY_BASE
D2_ENTITY_4 => D1_ENTITY_2
D2_ENTITY_3 => D1_ENTITY_2
The code for querying the model :
Code: Select all
using (dal.references.Entities entitiesTest=new dal.references.Entities()){
var query = from dal.references.ENTITY_BASE entity in entitiesTest.ENTITY_BASE select entity;
dal.references.ENTITY_BASE ent = query.First();
}
and finally the SQL produced that fails :
Code: Select all
SELECT
CASE WHEN (( NOT (("UnionAll2".C5 = 1) AND ("UnionAll2".C5 IS NOT NULL))) AND ( NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)))) THEN '0X' WHEN (((("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) AND ( NOT (("UnionAll2".C4 = 1) AND ("UnionAll2".C4 IS NOT NULL)))) AND ( NOT (("UnionAll2".C6 = 1) AND ("UnionAll2".C6 IS NOT NULL)))) THEN '0X0X' WHEN (("UnionAll2".C4 = 1) AND ("UnionAll2".C4 IS NOT NULL)) THEN '0X0X0X' WHEN (("UnionAll2".C5 = 1) AND ("UnionAll2".C5 IS NOT NULL)) THEN '0X1X' ELSE '0X0X1X' END AS C1,
"Extent1".ENTITY_BASE_ID AS ENTITY_BASE_ID,
"Extent1".ENTITY_NAME AS ENTITY_NAME,
CASE WHEN (( NOT (("UnionAll2".C5 = 1) AND ("UnionAll2".C5 IS NOT NULL))) AND ( NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)))) THEN NULL WHEN (((("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) AND ( NOT (("UnionAll2".C4 = 1) AND ("UnionAll2".C4 IS NOT NULL)))) AND ( NOT (("UnionAll2".C6 = 1) AND ("UnionAll2".C6 IS NOT NULL)))) THEN NULL WHEN (("UnionAll2".C4 = 1) AND ("UnionAll2".C4 IS NOT NULL)) THEN "UnionAll2".C2 WHEN (("UnionAll2".C5 = 1) AND ("UnionAll2".C5 IS NOT NULL)) THEN NULL END AS C2,
CASE WHEN (( NOT (("UnionAll2".C5 = 1) AND ("UnionAll2".C5 IS NOT NULL))) AND ( NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)))) THEN NULL WHEN (((("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) AND ( NOT (("UnionAll2".C4 = 1) AND ("UnionAll2".C4 IS NOT NULL)))) AND ( NOT (("UnionAll2".C6 = 1) AND ("UnionAll2".C6 IS NOT NULL)))) THEN NULL WHEN (("UnionAll2".C4 = 1) AND ("UnionAll2".C4 IS NOT NULL)) THEN NULL WHEN (("UnionAll2".C5 = 1) AND ("UnionAll2".C5 IS NOT NULL)) THEN "UnionAll2".C3 END AS C3
FROM USER.ENTITY_BASE "Extent1"
LEFT OUTER JOIN (SELECT
"Extent2".D1_ENTITY2_ID AS D1_ENTITY2_ID,
1 AS C1
FROM USER.D1_ENTITY2 "Extent2" ) "Project1" ON "Extent1".ENTITY_BASE_ID = "Project1".D1_ENTITY2_ID
LEFT OUTER JOIN (SELECT
"UnionAll1".D2_ENTITY3_ID AS C1,
"UnionAll1".NUMBER_VALUE AS C2,
"UnionAll1".C1 AS C3,
"UnionAll1".C2 AS C4,
"UnionAll1".C3 AS C5,
"UnionAll1".C4 AS C6
FROM (SELECT
"Extent3".D2_ENTITY3_ID AS D2_ENTITY3_ID,
"Extent3".NUMBER_VALUE AS NUMBER_VALUE,
NULL AS C1,
1 AS C2,
0 AS C3,
0 AS C4
FROM USER.D2_ENTITY3 "Extent3"
UNION ALL
SELECT
"Extent4".D2_ENTITY4_ID AS D2_ENTITY4_ID,
NULL AS C1,
NULL AS C2,
0 AS C3,
0 AS C4,
1 AS C5
FROM USER.D2_ENTITY4 "Extent4") "UnionAll1"
UNION ALL
SELECT
"Extent5".D1_ENTITY1_ID AS D1_ENTITY1_ID,
NULL AS C1,
"Extent5".NUMBER_VALUE AS NUMBER_VALUE,
0 AS C2,
1 AS C3,
0 AS C4
FROM USER.D1_ENTITY1 "Extent5") "UnionAll2" ON "Extent1".ENTITY_BASE_ID = "UnionAll2".C1