14 Replies Latest reply on Sep 21, 2012 9:19 PM by 15208

    ODAC incorrect EDM mapping

      I'm using ODAC 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 incorrect EDM mapping
          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 incorrect EDM mapping
            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 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)

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

              There is a fix for this bug and it is being incorporated in an upcoming Oracle patch.
              • 4. Re: ODAC incorrect EDM mapping
                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 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,

                  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 incorrect EDM mapping
                    Just wondering if anyone solved this problem? I am also hitting this problem and it is causing me great pain.
                    • 7. Re: ODAC incorrect EDM mapping
                      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 incorrect EDM mapping
                        Alex Keh - Product Manager-Oracle
                        The bug was patched back in June as part of the Oracle DB 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).


                        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:
                        • 9. Re: ODAC incorrect EDM mapping
                          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 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 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 incorrect EDM mapping
                              Thank you for your reply.

                              I submitted a service request. I will post the outcome here when (if) it comes available.
                              • 12. Re: ODAC incorrect EDM mapping
                                Using ODAC 11.2 Release 5 (, 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);

                                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">
                                <PropertyRef Name="C1" />
                                <Property Name="C1" Type="Int16" Nullable="false" />
                                <Property Name="ID" Type="Int32" Nullable="false" />

                                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)

                                5. Output


                                Edited by: shsu on Sep 20, 2012 3:48 PM
                                • 13. Re: ODAC incorrect EDM mapping
                                  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 incorrect EDM mapping
                                    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.

                                    <PropertyRef Name="ID" />