Entity Framework does not work with TPT / TPH

Entity Framework does not work with TPT / TPH

Postby jpl » Fri 12 Sep 2008 14:58

Hello,

I am in trouble with EF. The queries produced by the provider (4.75.42) ar not well formed if you are in a TPH / TPT pattern. The cause is that EF builds queries something like this one through the OraDirect .NET provider :

Code: Select all
SELECT * FROM(
          SELECT NULL AS C1 FROM USER.TABLE1
          UNION ALL
          SELECT NULL AS C1 FROM USER.TABLE1
          )
          UNION ALL
   SELECT COLUMN2 FROM USER.TABLE1



If you query such a table :

Code: Select all
CREATE TABLE "USER"."TABLE1"
   ( "COLUMN1" VARCHAR2(4000 BYTE),
"COLUMN2" NUMBER
   )


you will encounter a "Expression must have same datatype as corresponding expression" error because the NULL is not cast as NUMBER.

This is a big issue because I cannot find a way to model my database such as this kind of error won't occur. As far as I can see, you cannot claim you support EF at this stage. My project is frozen and I spent a lot of time finding the cause of the issue while I expected to get a provider with true EF support.
jpl
 
Posts: 13
Joined: Fri 12 Sep 2008 14:47

Complement

Postby jpl » Fri 12 Sep 2008 16:10

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 :

Code: Select all

       
         
         
         
         
         
         
           
           
         

         
           
           
         

         
           
           
         

         
           
           
         

       

       
         
           
         

         
         
       

       
         
           
         

         
       

       
         
           
         

         
         
       

       
         
           
         

         
       

       
         
           
         

         
         
       

       
         
         
         
           
             
           

           
             
           

         

       

       
         
         
         
           
             
           

           
             
           

         

       

       
         
         
         
           
             
           

           
             
           

         

       

       
         
         
         
           
             
           

           
             
           

         

       

     


The CSDL content :

Code: Select all

       
         
         

       
         

       
         
           
         

         
         
         

       
         

       
         
         

       
         

       


The mapping content :
Code: Select all

       
         
           
             
               
               
             

           

         
             
               
             

           

             
               
               
               

           
             
               
               
               

             
               
               

         

     


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
jpl
 
Posts: 13
Joined: Fri 12 Sep 2008 14:47

Postby Shalex » Mon 15 Sep 2008 09:08

We have implemented the typed NULLs support. It will be available in the next build.
Shalex
Devart Team
 
Posts: 7293
Joined: Thu 14 Aug 2008 12:44

Postby jpl » Mon 15 Sep 2008 09:17

Good. Have you an idea of when it will be available (days, weeks or months) ?
jpl
 
Posts: 13
Joined: Fri 12 Sep 2008 14:47

Postby Shalex » Mon 15 Sep 2008 10:39

The new build will be available in 1-2 weeks.
Shalex
Devart Team
 
Posts: 7293
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Mon 29 Sep 2008 13:26

The new build of OraDirect .NET version 4.75.43 is available!
Please visit http://www.devart.com/forums/viewtopic.php?t=12982
Shalex
Devart Team
 
Posts: 7293
Joined: Thu 14 Aug 2008 12:44

Postby AndreyR » Fri 18 Sep 2009 06:42

We have changed the default behaviour, but there is a way to avoid breaking of the existing project.
More information is available in this post:
http://www.devart.com/forums/viewtopic.php?t=15752
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle