Entity Framework does not work with TPT / TPH

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
jpl
Posts: 13
Joined: Fri 12 Sep 2008 14:47

Entity Framework does not work with TPT / TPH

Post by 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

Post by 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

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 15 Sep 2008 09:08

We have implemented the typed NULLs support. It will be available in the next build.

jpl
Posts: 13
Joined: Fri 12 Sep 2008 14:47

Post by jpl » Mon 15 Sep 2008 09:17

Good. Have you an idea of when it will be available (days, weeks or months) ?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 15 Sep 2008 10:39

The new build will be available in 1-2 weeks.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by 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

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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

Post Reply