5 Replies Latest reply: Nov 8, 2012 7:47 PM by Alex.Keh .Product.Manager-Oracle RSS

    Entity framework TPT does not work -> showstopper

    972836
      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
          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
            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
              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
                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 .Product.Manager-Oracle
                  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.