8 Replies Latest reply: Oct 17, 2012 11:58 PM by Alex.Keh .Product.Manager-Oracle RSS

    Oracle and EntityFramework Inharitance problem

    967949
      I try to connect Oracle and Entity Framework.

      And a lot of things even turned out.

      However faced such complex problem - when loading entities dauthers entities are loaded too. There are some exceptions. Though with MSSQL there are no such problems.

      I do not know what to do.

      Somebody faced a similar problem?
        • 1. Re: Oracle and EntityFramework Inharitance problem
          Tridus
          Daughter entities? You mean the relationships?

          What code are you using to do the loading, and what exceptions are you getting?
          • 2. Re: Oracle and EntityFramework Inharitance problem
            967949
            Not exact. Relationships by Id and primary keys. I mean inheritance. For example, Bird -> Eagle. Bird is parent.
            • 3. Re: Oracle and EntityFramework Inharitance problem
              967949
              I load birds. For example, var bird = <loading birds>. And I wonder why the type of bird is eagle, not bird.
              • 4. Re: Oracle and EntityFramework Inharitance problem
                967949
                I can send you test example. If you can help me.
                • 5. Re: Oracle and EntityFramework Inharitance problem
                  967949
                  ObjectContext.Birds.Where(w => w.Name != "Seagul").FirstOrDefault()
                  • 6. Re: Oracle and EntityFramework Inharitance problem
                    Tridus
                    I don't use that kind of inheritance in EF and thus won't be of much help. Sorry.
                    • 7. Re: Oracle and EntityFramework Inharitance problem
                      967949
                      And who can help me? I am sure, that I am not the first human who faced such problem=)
                      • 8. Re: Oracle and EntityFramework Inharitance problem
                        Alex.Keh .Product.Manager-Oracle
                        With the test case you provided, both ODP.NET and SqlClient returned the same results. See the EF generated SQL below for both Oracle and SQL Server, as well as the query results.

                        We used Model First to generate the DDL for creating the SQL Server schema to run against. The DDL is at the very bottom.

                        ODP.NET
                        -------
                        var a = context.DynamicObjects.ToList();

                        SELECT
                        CASE WHEN ( NOT (("Project1"."C1" = 1) AND ("Project1"."C1" IS NOT NULL))) THEN '0X' ELSE '0X0X' END AS "C1",
                        "Extent1"."ID" AS "ID",
                        "Extent1"."VALUE" AS "VALUE",
                        CASE WHEN ( NOT (("Project1"."C1" = 1) AND ("Project1"."C1" IS NOT NULL))) THEN NULL ELSE "Project1"."NAME" END AS "C2"
                        FROM "DBO_TESTODPNET"."DYNAMICOBJECTS" "Extent1"
                        LEFT OUTER JOIN (SELECT
                             "Extent2"."ID" AS "ID",
                             "Extent2"."NAME" AS "NAME",
                             1 AS "C1"
                             FROM "DBO_TESTODPNET"."DYNAMICOBJECTS_DYNAMICREFATTRI" "Extent2" ) "Project1" ON "Extent1"."ID" = "Project1"."ID";



                        C1 ID VALUE C2
                        ---- -------------------------------- ---------------- ----------------
                        0X0X 1B5020F6EAA243708B6A565FEC20F2CA value1 name1




                        var count = context.DynamicObjects.Count(x => !(x is DynamicRefAttribute));


                        SELECT
                        "GroupBy1"."A1" AS "C1"
                        FROM ( SELECT
                             COUNT(1) AS "A1"
                             FROM "DBO_TESTODPNET"."DYNAMICOBJECTS" "Extent1"
                             LEFT OUTER JOIN (SELECT
                                  "Extent2"."ID" AS "ID",
                                  1 AS "C1"
                                  FROM "DBO_TESTODPNET"."DYNAMICOBJECTS_DYNAMICREFATTRI" "Extent2" ) "Project1" ON "Extent1"."ID" = "Project1"."ID"
                             WHERE ( NOT (CASE WHEN ( NOT (("Project1"."C1" = 1) AND ("Project1"."C1" IS NOT NULL))) THEN '0X' ELSE '0X0X' END LIKE '0X0X%'))
                        ) "GroupBy1";



                        C1
                        ----------
                        0





                        SQL Client:
                        ----------
                        DECLARE @myid uniqueidentifier = NEWID();
                        insert into dbo.Parents VALUES (@myid, 'value1');
                        insert into dbo.Parents_Child VALUES ('Name1', @myid);



                        var a = context.Parents.ToList();

                        SELECT
                        CASE WHEN ( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) THEN '0X' ELSE '0X0X' END AS [C1],
                        [Extent1].[Id] AS [Id],
                        [Extent1].[Value] AS [Value],
                        CASE WHEN ( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) THEN CAST(NULL AS varchar(1)) ELSE [Project1].[Name] END AS [C2]
                        FROM [dbo].[Parents] AS [Extent1]
                        LEFT OUTER JOIN (SELECT
                             [Extent2].[Name] AS [Name],
                             [Extent2].[Id] AS [Id],
                             cast(1 as bit) AS [C1]
                             FROM [dbo].[Parents_Child] AS [Extent2] ) AS [Project1] ON [Extent1].[Id] = [Project1].[Id]



                        c1     Id                         Value     C2
                        0X0X     289ACAF8-2EF7-458D-B42D-A4F51A26387A     value1     Name1


                        var count = context.Parents.Count(x => !(x is Child));

                        SELECT
                        [GroupBy1].[A1] AS [C1]
                        FROM ( SELECT
                             COUNT(1) AS [A1]
                             FROM [dbo].[Parents] AS [Extent1]
                             LEFT OUTER JOIN (SELECT
                                  [Extent2].[Id] AS [Id],
                                  cast(1 as bit) AS [C1]
                                  FROM [dbo].[Parents_Child] AS [Extent2] ) AS [Project1] ON [Extent1].[Id] = [Project1].[Id]
                             WHERE NOT (CASE WHEN ( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) THEN '0X' ELSE '0X0X' END LIKE '0X0X%')
                        ) AS [GroupBy1]



                        C1
                        0



                        -- --------------------------------------------------
                        -- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
                        -- --------------------------------------------------

                        SET QUOTED_IDENTIFIER OFF;
                        GO
                        USE [linq];
                        GO
                        IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
                        GO

                        -- --------------------------------------------------
                        -- Dropping existing FOREIGN KEY constraints
                        -- --------------------------------------------------

                        IF OBJECT_ID(N'[dbo].[FK_Child_inherits_Parent]', 'F') IS NOT NULL
                        ALTER TABLE [dbo].[Parents_Child] DROP CONSTRAINT [FK_Child_inherits_Parent];
                        GO

                        -- --------------------------------------------------
                        -- Dropping existing tables
                        -- --------------------------------------------------

                        IF OBJECT_ID(N'[dbo].[Parents]', 'U') IS NOT NULL
                        DROP TABLE [dbo].[Parents];
                        GO
                        IF OBJECT_ID(N'[dbo].[Parents_Child]', 'U') IS NOT NULL
                        DROP TABLE [dbo].[Parents_Child];
                        GO

                        -- --------------------------------------------------
                        -- Creating all tables
                        -- --------------------------------------------------

                        -- Creating table 'Parents'
                        CREATE TABLE [dbo].[Parents] (
                        [Id] uniqueidentifier NOT NULL,
                        [Value] nvarchar(16) NULL
                        );
                        GO

                        -- Creating table 'Parents_Child'
                        CREATE TABLE [dbo].[Parents_Child] (
                        [Name] nvarchar(16) NULL,
                        [Id] uniqueidentifier NOT NULL
                        );
                        GO

                        -- --------------------------------------------------
                        -- Creating all PRIMARY KEY constraints
                        -- --------------------------------------------------

                        -- Creating primary key on [Id] in table 'Parents'
                        ALTER TABLE [dbo].[Parents]
                        ADD CONSTRAINT [PK_Parents]
                        PRIMARY KEY CLUSTERED ([Id] ASC);
                        GO

                        -- Creating primary key on [Id] in table 'Parents_Child'
                        ALTER TABLE [dbo].[Parents_Child]
                        ADD CONSTRAINT [PK_Parents_Child]
                        PRIMARY KEY CLUSTERED ([Id] ASC);
                        GO

                        -- --------------------------------------------------
                        -- Creating all FOREIGN KEY constraints
                        -- --------------------------------------------------

                        -- Creating foreign key on [Id] in table 'Parents_Child'
                        ALTER TABLE [dbo].[Parents_Child]
                        ADD CONSTRAINT [FK_Child_inherits_Parent]
                        FOREIGN KEY ([Id])
                        REFERENCES [dbo].[Parents]
                        ([Id])
                        ON DELETE NO ACTION ON UPDATE NO ACTION;
                        GO

                        -- --------------------------------------------------
                        -- Script has ended
                        -- --------------------------------------------------