This discussion is archived
5 Replies Latest reply: Nov 8, 2012 5:47 PM by Alex_Keh - Oracle_Product_Manager RSS

Entity framework TPT does not work -> showstopper

972836 Newbie
Currently Being Moderated
Greetings,

I'm experiencing a similar problem that is described here:

[Entity Framework and inheritance - Possible bug in EF?|http://stackoverflow.com/questions/11506683/entity-framework-and-inheritance-possible-bug-in-ef.]

I'm using:

Windows 7 64 bit
ODAC 11.2 Release 5 32 bit
Oracle Express 11.2 on localhost
Visual Studio 2012
Entity framework 5.0
.net 4.5

Create a simple database with TPT inheritance:


CREATE TABLE Person(
Id NUMBER(38, 0) NOT NULL,
Name VARCHAR2(50),
CONSTRAINT PK100 PRIMARY KEY (Id)
)
;
CREATE TABLE Employee(
Id NUMBER(38, 0) NOT NULL,
JobTitle VARCHAR2(50),
CONSTRAINT PK101 PRIMARY KEY (Id)
)
;
ALTER TABLE Employee ADD CONSTRAINT ref_Employee_person
FOREIGN KEY (Id)
REFERENCES Person(Id)
;
CREATE TABLE Superhero(
Id NUMBER(38, 0) NOT NULL,
Superpower VARCHAR2(50),
CONSTRAINT PK102 PRIMARY KEY (Id)
)
;
ALTER TABLE Superhero ADD CONSTRAINT ref_Superhero_person
FOREIGN KEY (Id)
REFERENCES Person(Id)
;
-- Sequence
CREATE SEQUENCE seq_id
INCREMENT BY 1
START WITH 55
MINVALUE 1
MAXVALUE 9999999999999999999999999999
NOCYCLE
NOORDER
CACHE 20
/
CREATE OR REPLACE TRIGGER PERSON_BI BEFORE INSERT ON PERSON FOR EACH ROW
BEGIN IF :NEW.ID IS NULL THEN SELECT SEQ_ID.NEXTVAL INTO :NEW.ID FROM DUAL; END IF; END;
/

Create a console application, add an edmx file, set up inheritance like so:

Diagram

And I set the Id property in Person entity as StoreGeneratedPattern to Identity

Then I created the following program:


static void Main(string[] args)
{
//Employee
var db = new Entities();
EMPLOYEE e = new EMPLOYEE() { ID = 0, JOBTITLE = "Programmer"};
e.NAME = "John Regular Doe";
db.People.Add(e);
//Superhero
SUPERHERO s = new SUPERHERO();
s.NAME = "Superman";
s.SUPERPOWER = "Flight";
db.People.Add(s);
db.SaveChanges();

//Fetch all persons and look at the type
//this is where I get an error:
var allPeople = db.People.ToList();

allPeople.ForEach(x => Console.WriteLine("type: " + x.GetType()));

}

This throws the following error when I fetch into allPeople:


All objects in the EntitySet 'Entities.People' must have unique primary keys. However, an instance of type 'TestingTPTInheritance.EMPLOYEE' and an instance of type 'TestingTPTInheritance.SUPERHERO' both have the same primary key value, 'EntitySet=People;ID=38'.


Looking at the database everything looks fine

-- Person table --
ID     NAME
39     John Regular Doe
40     Superman

-- Employee table --
ID     JOBTITLE
39     Programmer

-- Superhero table --
ID     SUPERPOWER
40     Flight

If I take the select that EF generates in " db.People.ToList();" and run it I get the following result:

C1      C2     NAME      C3      C4
0X0X     40     Superman           
0X0X     39     John Regular Doe     Programmer     

Is this a bug in ODAC. If so is there a workaround or will there be a fix soon?

Edited by: Amplus on 7.11.2012 07:59
  • 1. Re: Entity framework TPT does not work -> showstopper
    15208 Explorer
    Currently Being Moderated
    Thank you for the testcase.
    I tried it and could not reproduce the error at the following line.

    var allPeople = db.People.ToList();

    I also tried the following lines, as described in
    http://stackoverflow.com/questions/11506683/entity-framework-and-inheritance-possible-bug-in-ef,
    and did not encounter any issue. Data in those three test tables were correct.

    var result1 = db.People.OfType<SUPERHERO>().ToList(); //Count=1
    var result2 = db.People.OfType<EMPLOYEE>().ToList(); //Count=1
    var result3 = db.People.OfType<PERSON>().ToList(); //Count=2

    The database I used was 11.1.0.7.0 Enterprise Edition.
    You were using Oracle Express 11.2.
    I have two questions:
    1. Your sequence has START WITH 55. I wonder why in your table there were ID = 39 and 40?
    The error message you got even indicated the ID was 38. It seemed the same ID value 38 was used
    in the INSERTs for employee and superhero tables.

    "db.SaveChanges()" should cause four insert statements.
    #1 insert is for person table and it has returning ID value for #2 insert.
    #2 insert is for employee table.
    #3 insert is for person table and it has returning ID vslue for #4 insert.
    #4 insert is for superhero table.

    The returning ID value in #1 and #3 should be different from the trigger which uses the sequence for ID.

    Below is what I got.
    SQL> select * from person;

    ID NAME
    ---------- -------------------------------
    55 John Regular Doe
    56 Superman

    SQL> select * from employee;

    ID JOBTITLE
    ---------- -------------------------------
    55 Programmer

    SQL> select * from superhero;

    ID SUPERPOWER
    ---------- -------------------------------
    56 Flight

    2. Can you try the following query, which is from "var allPeople = db.People.ToList()", and see if you get the same result?

    Query:

    SELECT
    CASE WHEN (( NOT (("Project1"."C1" = 1) AND ("Project1"."C1" IS NOT NULL))) AND ( NOT (("Project2"."C1" = 1) AND ("Project2"."C1" IS NOT NULL)))) THEN '0X' WHEN (("Project1"."C1" = 1) AND ("Project1"."C1" IS NOT NULL)) THEN '0X0X' ELSE '0X1X' END AS "C1",
    "Extent1"."ID" AS "ID",
    "Extent1"."NAME" AS "NAME",
    CASE WHEN (( NOT (("Project1"."C1" = 1) AND ("Project1"."C1" IS NOT NULL))) AND ( NOT (("Project2"."C1" = 1) AND ("Project2"."C1" IS NOT NULL)))) THEN NULL WHEN (("Project1"."C1" = 1) AND ("Project1"."C1" IS NOT NULL)) THEN "Project1"."JOBTITLE" END AS "C2",
    CASE WHEN (( NOT (("Project1"."C1" = 1) AND ("Project1"."C1" IS NOT NULL))) AND ( NOT (("Project2"."C1" = 1) AND ("Project2"."C1" IS NOT NULL)))) THEN NULL WHEN (("Project1"."C1" = 1) AND ("Project1"."C1" IS NOT NULL)) THEN NULL ELSE "Project2"."SUPERPOWER" END AS "C3"
    FROM "SCOTT"."PERSON" "Extent1"
    LEFT OUTER JOIN (SELECT
         "Extent2"."ID" AS "ID",
         "Extent2"."JOBTITLE" AS "JOBTITLE",
         1 AS "C1"
         FROM "SCOTT"."EMPLOYEE" "Extent2" ) "Project1" ON "Extent1"."ID" = "Project1"."ID"
    LEFT OUTER JOIN (SELECT
         "Extent3"."ID" AS "ID",
         "Extent3"."SUPERPOWER" AS "SUPERPOWER",
         1 AS "C1"
         FROM "SCOTT"."SUPERHERO" "Extent3" ) "Project2" ON "Extent1"."ID" = "Project2"."ID";


    Result I got when executed in ODT Query Window.

    C1     ID     NAME     C2     C3     
    0X0X     55     John Regular Doe     Programmer     <NULL>     
    0X1X     56     Superman     <NULL>     Flight

    Edited by: shsu on Nov 7, 2012 5:44 PM
  • 2. Re: Entity framework TPT does not work -> showstopper
    972836 Newbie
    Currently Being Moderated
    Thank you for the reply shsu

    I was being careless when I copied the error message and sequence, sorry about that. I did run this serveral times after I copied the error message. I recreate the seqence starting with 1 and recreated the problem. The error message is:

    All objects in the EntitySet 'Entities.People' must have unique primary keys. However, an instance of type 'TestingTPTInheritance.EMPLOYEE' and an instance of type 'TestingTPTInheritance.SUPERHERO' both have the same primary key value, 'EntitySet=People;ID=2'.

    The data in the tables is:

    Person:
    ID     NAME
    1      John Regular Doe
    2      Superman

    employee
    ID     JOBTITLE
    1      Programmer

    Superhero
    ID     SUPERPOWER
    2      Flight

    And the generated select is:

    SELECT
    CASE WHEN (( NOT (("Project1"."C2" = 1) AND ("Project1"."C2" IS NOT NULL))) AND ( NOT (("Project2"."C2" = 1) AND ("Project2"."C2" IS NOT NULL)))) THEN '0X' WHEN (("Project1"."C2" = 1) AND ("Project1"."C2" IS NOT NULL)) THEN '0X0X' ELSE '0X1X' END AS "C1",
    CAST( "Extent1"."ID" AS number(19,0)) AS "C2",
    "Extent1"."NAME" AS "NAME",
    CASE WHEN (( NOT (("Project1"."C2" = 1) AND ("Project1"."C2" IS NOT NULL))) AND ( NOT (("Project2"."C2" = 1) AND ("Project2"."C2" IS NOT NULL)))) THEN NULL WHEN (("Project1"."C2" = 1) AND ("Project1"."C2" IS NOT NULL)) THEN "Project1"."JOBTITLE" END AS "C3",
    CASE WHEN (( NOT (("Project1"."C2" = 1) AND ("Project1"."C2" IS NOT NULL))) AND ( NOT (("Project2"."C2" = 1) AND ("Project2"."C2" IS NOT NULL)))) THEN NULL WHEN (("Project1"."C2" = 1) AND ("Project1"."C2" IS NOT NULL)) THEN NULL ELSE "Project2"."SUPERPOWER" END AS "C4"
    FROM "TPT"."PERSON" "Extent1"
    LEFT OUTER JOIN (SELECT
    "Extent2"."JOBTITLE" AS "JOBTITLE",
    CAST( "Extent2"."ID" AS number(19,0)) AS "C1",
    1 AS "C2"
    FROM "TPT"."EMPLOYEE" "Extent2" ) "Project1" ON ( CAST( "Extent1"."ID" AS number(19,0))) = "Project1"."C1"
    LEFT OUTER JOIN (SELECT
    "Extent3"."SUPERPOWER" AS "SUPERPOWER",
    CAST( "Extent3"."ID" AS number(19,0)) AS "C1",
    1 AS "C2"
    FROM "TPT"."SUPERHERO" "Extent3" ) "Project2" ON ( CAST( "Extent1"."ID" AS number(19,0))) = "Project2"."C1"

    With the following result:

    C1      C2     NAME      C3      C4
    0X0X     2      Superman      null      null --> should't there be a "Flight" entry here??
    0X0X     1      John Regular Doe     Programmer     null

    I also tried this:
    var result1 = db.People.OfType<SUPERHERO>().ToList(); //Count=1
    var result2 = db.People.OfType<EMPLOYEE>().ToList(); //Count=1
    var result3 = db.People.OfType<PERSON>().ToList(); //Throws the following error:

    All objects in the EntitySet 'Entities.People' must have unique primary keys. However, an instance of type 'TestingTPTInheritance.EMPLOYEE' and an instance of type 'TestingTPTInheritance.SUPERHERO' both have the same primary key value, 'EntitySet=People;ID=2'.

    The only thing I can think of is that my little console app is picking up the oracle client from oracle express instead of the ODAC driver. Is there an easy way to force it to use the correct driver? I have this in my config file but I'm not sure if it works:

    <oracle.dataaccess.client>
    <settings>
    <add name="DllPath" value="C:\oracle\product\11.2.0\client_3\bin" />
    </settings>
    </oracle.dataaccess.client>

    And I have also referenced Oracle.Data.Access here:

    C:\oracle\product\11.2.0\client_3\odp.net\bin\4\Oracle.DataAccess.dll

    client_3 directory is my ODAC Release 5 installation. I changed the config settings to:

    <oracle.dataaccess.client>
    <settings>
    <add name="DllPath" value="C:\oracle\product\11.2.0\client_3\odp.net\bin\4\Oracle.DataAccess.dll" />
    </settings>
    </oracle.dataaccess.client>

    then the program was able to run this line:

    var allPeople = db.People.ToList();

    But both objects were of type EMPLOYEE. Then I ran this:

    var result1 = db.People.OfType<SUPERHERO>().ToList();

    and get the following error:

    All objects in the EntitySet 'Entities.People' must have unique primary keys. However, an instance of type 'TestingTPTInheritance.EMPLOYEE' and an instance of type 'TestingTPTInheritance.SUPERHERO' both have the same primary key value, 'EntitySet=People;ID=2'.

    The problem must be that I do have multiple versions of Oracle client installed on my computer and it is picking up the wrong one either when I'm generating the edmx file or when the application is connecting to the db. I think my next step will be to uninstall all oracle client instances as well as oracle express and try again from scratch!!
  • 3. Re: Entity framework TPT does not work -> showstopper
    15208 Explorer
    Currently Being Moderated
    Given our generated queries are slightly different (such as CASTs on ID column and column order in SELECT list),
    the query result should be logically same.
    I ran the generated query from you against my 11.1 database and the result is as below.

    C1     C2     NAME     C3     C4     
    0X1X     66     Superman     <NULL>     Flight     
    0X0X     65     John Regular Doe     Programmer     <NULL>     


    You see "Flight" is not missing from "Superman" row.

    If you run the generated query from me against your Oracle Express database 11.2 and "Flight" is still missing, then we
    know there is difference between the two databases we are using.
    The error you got may be related to this difference. You may want to confirm it by trying with a different Oracle database
    other than an Express database.
    Don't forget to replace "SCOTT" with "TPT" in the generated query from me.
  • 4. Re: Entity framework TPT does not work -> showstopper
    972836 Newbie
    Currently Being Moderated
    You are absolutely correct shsu! Here is what I did:

    1. Uninstalled Oracle XE
    2. Deinstalled all oracle clients I had on my machine and cleaned out Oracle registry keys
    3. Installed ODAC Release 5 x64
    4. Installed ODAC Release 5 x32
    5. Ran the same test against our Oracle enterprise 11g developer server -> everything worked fine
    6. Installed Oracle XE
    7. Regenerated the edmx file from database and ran against the enterprise server -> everything worked fine
    8. Created the tables on my Oracle Express, regenerated the edmx -> Got the same error on this line var result1 = db.People.OfType<SUPERHERO>().ToList();
    All objects in the EntitySet 'Entities.People' must have unique primary keys. However, an instance of type 'TestingTPTInheritance.SUPERHERO' and an instance of type 'TestingTPTInheritance.EMPLOYEE' both have the same primary key value, 'EntitySet=People;ID=2'.
    9. I ran your sql agains my XE and you were right the "Flight" is missing from the result:

    C1      ID     NAME      C2      C3
    0X0X     1      John Regular Doe     Programmer     NULL
    0X0X     2      Superman      NULL      NULL

    So this means I won't be able to use Oracle Express for development with EF?

    I find it quite amazing that Oracle XE is giving us a different result from a select than the Enterprise version of Oracle....Should't this be registered as a bug in Oracle XE? The idea was to develop and do unit testing locally on Oracle XE since we are a big team working on the same schema...

    And by the way, I could probably have saved me the trouble of uninstalling all Oracle clients from my system :o)

    Edited by: Amplus on 8.11.2012 14:40
  • 5. Re: Entity framework TPT does not work -> showstopper
    Alex_Keh - Oracle_Product_Manager Expert
    Currently Being Moderated
    This appears to be a bug in XE. Unfortunately, it cannot be patched, even if this is eventually diagnosed as an XE bug, since XE is not officially supported with patching. I apologize.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points