14 Replies Latest reply: Sep 21, 2012 4:19 PM by 15208 RSS

    ODAC 11.2.0.3 incorrect EDM mapping

    932861
      I'm using ODAC 11.2.0.3 and use Model-first to generate my edmx, context and entities.

      It all works fine when using LINQ to fetch and manipulate data.

      I've got a table named MEMBER with a field ID of type number(10) (not null). The ID field gets translated to Int32.

      Now the problem. When I use a custom query to fetch data the field is recognized as a Int64 so I get an conversion error.

      Here an example:
      var sql = "SELECT * FROM MEMBER";
      var members = ctx.Member.SqlQuery(sql).ToList();

      This throws the following exception: The 'ID' property on 'MEMBER' could not be set to a 'Int64' value. You must set this property to a non-null value of type 'Int32'.

      Why does it think the ID must be an Int64? The docs (http://docs.oracle.com/cd/E11882_01/win.112/e18754/featLINQ.htm) specify the mapping from a number(10) to an Int32, and even the auto generated Context following this tutorial: http://download.oracle.com/oll/obe/EntityFrameworkOBE/EntityFrameworkOBE.htm) generate the ID field as Int32.

      I just need to fetch data using SQL for performance issues.
        • 1. Re: ODAC 11.2.0.3 incorrect EDM mapping
          932861
          I still need help solving this problem. Is there anybody from Oracle out there who knows how to solve this? Is it a bug or is there any known workaround for it?
          • 2. Re: ODAC 11.2.0.3 incorrect EDM mapping
            932667
            Perhaps the documentation is incorrect? The maximum value of NUMBER(10,0) (9,999,999,999) is greater than the maximum value of INT32 (2,147,483,647), so it would make sense that it's an INT64.
            • 3. Re: ODAC 11.2.0.3 incorrect EDM mapping
              Alex.Keh .Product.Manager-Oracle
              One workaround is to use Decimal.

              var sql = "SELECT * FROM MEMBER";
              var members = ctx.ExecuteStoreQuery<decimal>(sql).ToList();
              foreach (decimal val in members)
              Console.WriteLine(val.ToString());

              Most likely, you are running into Bug 13559540, which is also described here:
              Bug: ExecuteStoreQuery does not map correctly (ODP.NET 11.2.0.2.50 Beta 3)

              There is a fix for this bug and it is being incorporated in an upcoming Oracle patch.
              • 4. Re: ODAC 11.2.0.3 incorrect EDM mapping
                932861
                Thank you for the reply.

                I can't say if it is the same problem. It looks a bit like it though.

                I don't know how to apply your workaround. My context is of the type System.Data.Entity.DbContext and doesn't have an ExecuteStoreQuery option.
                My context is generated by the ADO.NET Entity Data Model wizard, as described in http://download.oracle.com/oll/obe/EntityFrameworkOBE/EntityFrameworkOBE.htm

                Is there a way I get informed when the patch is available? I'm now just using EF to get all the data, which is a lot slower, but it works for the moment.
                • 5. Re: ODAC 11.2.0.3 incorrect EDM mapping
                  Alex.Keh .Product.Manager-Oracle
                  There is no mechanism for being informed when Oracle patches are released. They are generally on a regular release schedule. For example, Oracle patchsets are released generally once a year. The last 11.2 patchset was released around the end of last year, which puts the next scheduled patchset at around the end of this year. The bug fix is being incorporated into that Oracle patchset, 11.2.0.4.

                  If there is an urgent customer need for a fix sooner than that, customers can request the next bundled patch (every 3-4 weeks) or a one-off patch to be released.
                  • 6. Re: ODAC 11.2.0.3 incorrect EDM mapping
                    953147
                    Just wondering if anyone solved this problem? I am also hitting this problem and it is causing me great pain.
                    • 7. Re: ODAC 11.2.0.3 incorrect EDM mapping
                      932861
                      Is there any update available? Where can I track the bug myself? I still have this problem (after months). I created a workaround 3 months ago, but the database size increased exponentially and now each query is taking more then 20 minutes (compared to 5 to 10 seconds on MS SQL server...).
                      • 8. Re: ODAC 11.2.0.3 incorrect EDM mapping
                        Alex.Keh .Product.Manager-Oracle
                        The bug was patched back in June as part of the Oracle DB 11.2.0.3 Patch 7 on My Oracle Support. For your reference, all bugs and their status can be tracked by logging in to My Oracle Support (MOS).

                        https://support.oracle.com

                        Your DBA should either be able access MOS or provide you access.

                        However, today is the day we released a new ODAC version on OTN, which has all fixes up to and including Patch 10. You can download that version here:
                        http://www.oracle.com/technetwork/topics/dotnet/downloads/index.html
                        • 9. Re: ODAC 11.2.0.3 incorrect EDM mapping
                          932861
                          Hi Alex,

                          Where can I find this bug in support.oracle.com? I've got a MOS account, but can't find it in there.

                          Furthermore, I've installed the new 11.2.0.3.20 driver, but still have the same problem.
                          I've updated the references to orace.dataaccess.dll and updated the Model.edmx from the database. Is there anything more I should do?

                          The only way I get this to work is to manually edit the edmx, change the Precision of the number fields from 10 to 11, the entity types from Int32 to In64 and cast every long to int in my application. But this is offcourse a very "dirty" and potential dangerous method.
                          • 10. Re: ODAC 11.2.0.3 incorrect EDM mapping
                            Alex.Keh .Product.Manager-Oracle
                            If the new ODP.NET version doesn't fix the bug for you, you're likely encountering a different bug then. My recommendation is to open a service request with Oracle Support to diagnose the bug and then Oracle can fix it for you.
                            • 11. Re: ODAC 11.2.0.3 incorrect EDM mapping
                              932861
                              Thank you for your reply.

                              I submitted a service request. I will post the outcome here when (if) it comes available.
                              • 12. Re: ODAC 11.2.0.3 incorrect EDM mapping
                                15208
                                Using ODAC 11.2 Release 5 (11.2.0.3.20), I cannot reproduce the error you described.
                                Or maybe I have missed something.

                                1. created a test table
                                create table member (c1 number(1) primary key, id number(10) not null);

                                2. Insert test data
                                insert into member values(1, 1234);
                                insert into member values(2, 4567);
                                commit;

                                3. Use the EDM wizard to create a model based on member table.
                                No custom type mapping in the app.config.

                                CSDL section in the edmx file:
                                ...
                                <EntityType Name="MEMBER">
                                <Key>
                                <PropertyRef Name="C1" />
                                </Key>
                                <Property Name="C1" Type="Int16" Nullable="false" />
                                <Property Name="ID" Type="Int32" Nullable="false" />
                                </EntityType>
                                ...

                                4. Test code

                                using (Entities ctx = new Entities())
                                {
                                var sql = "SELECT * FROM MEMBER";
                                var members = ctx.MEMBERs.SqlQuery(sql).ToList();
                                foreach (var v in members)
                                {
                                Console.WriteLine(v.C1.ToString());
                                Console.WriteLine(v.ID.ToString());
                                }
                                }

                                5. Output

                                1
                                1234
                                2
                                4567

                                Edited by: shsu on Sep 20, 2012 3:48 PM
                                • 13. Re: ODAC 11.2.0.3 incorrect EDM mapping
                                  932861
                                  It looks if you do exactly the same as me. I can't tell why is does work with your setup. There must be some small difference.

                                  I created a ticket at Oracle support with a complete .net solution and DDL file to reproduce the problem. Jenny at Oracle support was very helpful and tested everything. She was able to reproduce the problem and filed a bug for me: Bug 14642733 - EF: CALLING SQLQUERY.TOLIST() THROW INVALIDCASTEXCEPTION

                                  So far I'm really pleased with all your help. I will update this thread for reference usage if I get an update to this bug report.

                                  For now I created a workaround using T4 files to generate BulkLoad functions that use a oracaledatareader and creates and fills Entity instances with all the data. It's not perfect as I can't use lamba expressions for the selections or use joins. But I'm now at least able to fetch large amounts of data.
                                  • 14. Re: ODAC 11.2.0.3 incorrect EDM mapping
                                    15208
                                    I found the difference.
                                    In your test, ID is a key column. In mine, it is not.
                                    Yes, when ID is a key column, the error is reproduced.

                                    CSDL:
                                    <Key>
                                    <PropertyRef Name="ID" />
                                    </Key>