13 Replies Latest reply: Jul 10, 2013 4:10 PM by Alex.Keh .Product.Manager-Oracle RSS

    How to connect to Oracle db with Entity Framework & ODP.NET Managed Driver

    977106
      Oracle Data Provider for .NET, Managed Driver Installation Guide, Quick Start, and Release Notes for Release 11.2.0.3.50 Beta suggests that it supports Entity Framework.

      I've followed the steps in the Quick Start and have a command line project which successfully connects to an Oracle db via the means outlined.

      Now I would like to connect to an existing Oracle DB to use EF database first to create and Entity Data Model (edm), however, the ODP.NET Managed Driver is not available as a means of connecting. I tried going to the Server Explorer to create a connection, and it is not available there either.

      Can you please tell me the steps necessary to connect to a db using EF and ODP.NET Managed Driver?

      Respectfully,

      David C
        • 1. Re: How to connect to Oracle db with Entity Framework & ODP.NET Managed Driver
          Mark Williams-Oracle
          I've never done anything with Entity Framework so I may be off base here, but I suspect that what you would like to do involves using the Oracle Developer Tools for .NET and support for that is not in the current beta of the managed provider. From the readme.htm file:

          "Note: ODP.NET, Managed Driver is not supported to work with Oracle Developer Tools for .NET (ODT.NET)."

          Of course, if anyone knows otherwise please comment/correct!
          • 2. Re: How to connect to Oracle db with Entity Framework & ODP.NET Managed Driver
            Tridus
            I've been trying to figure out if there's a way to make this work after the model is already created, and it seems like it should be possible. But I'm having some issues. What I've done:

            1. Create a model as normal using the normal Oracle client.
            2. Open up the EF model in the XML viewer and change Oracle.DataAccess to Oracle.ManagedDataAccess (It's right up near the top)
            3. Make the same change in app.config's EF connection string.
            4. Change the reference.
            5. Configure app.config for the managed client (sample config at the bottom of this post)

            What I've found is a bunch of problems:
            1. So far I can't get it to work at all if the managed driver isn't registered in machine.config, which is annoying. I'll come back to this one if I can solve the other problem.
            2. Getting past that, EDM mappings don't use the same configuration syntax, and I'm running into major issues there. Primarily that in my model I only remapped boolean (everything else is default). The managed version throws errors if I specify a mapping for boolean and don't also specify one for BYTE... but I have nothing mapped to BYTE so I can't do that and get a workable model. ("{"The type initializer for 'OracleInternal.Common.ProviderConfig' threw an exception."}"{"BYTE is invalid"}") is the error. If I do map byte, I get invalid model errors when it tries to validate instead. The only way around this I've found is to map BYTE to number(2,0) and change the model appropriately.
            3. You can't edit the model under this configuration. In order to edit it, you need to change back to the normal Oracle client.

            With those limitations, I have managed to get the managed driver to connect and talk to the database using EF. Now if I can fix #1 up there, this will be workable for my environment (though obviously sub-optimal).


            Here's my sample app.config:
            {code}
            <oracle.manageddataaccess.client>
            <version number="*">
            <settings>
            <setting name="TNS_ADMIN" value="C:\oracle\client\11gR2\network\admin"/>
            </settings>
            <edmMappings>
            <edmMapping dataType="number">
            <add name="bool" precision="1"/>
            <add name="byte" precision="2" />
            <add name="int16" precision="5" />
            </edmMapping>
            </edmMappings>
            </version>
            </oracle.manageddataaccess.client>

            <!-- This section is needed when editing the model -->
            <oracle.dataaccess.client>
            <settings>
            <add name="bool" value="edmmapping number(1,0)" />
            <add name="byte" value="edmmapping number(2,0)" />
            </settings>
            </oracle.dataaccess.client>
            {code}
            • 3. Re: How to connect to Oracle db with Entity Framework & ODP.NET Managed Driver
              Tridus
              To update this - I figured out how to get it to work without registering it in machine.config. I needed to add this to app.config:
                <system.data>
                  <DbProviderFactories>
                    <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" 
                         description="Oracle Data Provider for .NET, Managed Driver" 
                         type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.112.3.50, Culture=neutral, PublicKeyToken=89b483f429c47342" />
                  </DbProviderFactories>
                </system.data>
              Unfortunately, I also needed to add a reference to Oracle.ManagedDataAccessDTC, which is the assembly that's dependent on the bit-ness of the OS (there's an x86 version and an x64 version). Without that reference, EF wouldn't load Oracle.ManagedDataAccess and threw exceptions complaining that the DTC one wasn't found. I'm not sure why that is, EF must rely on something in it.

              Anyway, if you follow all this you can get it to work, with all the caveats included. :) I probably wouldn't do it in production quite yet, given the issues in this beta.
              • 4. Re: How to connect to Oracle db with Entity Framework & ODP.NET Managed Driver
                977106
                OK. This is Beta, so grace is required.

                That stated, if the Beta doesn't support Entity Framework, there shouldn't be a section called "ENTITY FRAMEWORK RELATED TIPS, LIMITATIONS AND KNOWN ISSUES" on the "Installation Guide, Quick Start, and Release Notes - Release 11.2.0.3.50 Beta". See http://www.oracle.com/technetwork/database/windows/downloads/odpmbetainstall-1696475.html

                The video on ODP.NET Managed states that EF will be fully supported in the production, therefore, not in beta.
                • 5. Re: How to connect to Oracle db with Entity Framework & ODP.NET Managed Driver
                  Tridus
                  Yeah. The notes also say it doesn't support DbContext, and it does (so does the normal EF support). In this case right now they need to be clearer that EF is supported at runtime (with the issues I mentioned), but not supported at design time at all.
                  • 6. Re: How to connect to Oracle db with Entity Framework & ODP.NET Managed Driver
                    977106
                    Thank you Tridus. I appreciate that you share your hard earned knowledge.

                    My main objective right now is to find out if any version of ODP.NET will be able to run our EF queries. I've been trying out Devart's dotConnect for Oracle. I have a type of query that fails with their data provider and they say that it is an Oracle limitation.

                    Are you aware of any Oracle limitation that cause the following query to throw and error from Oracle?

                    var entityCatalogFields = _Context.Fields
                    .Where(f => f.ObjectFields
                    .Any(otf => otf.Object.Things
                    .Any(p => p.ThingID == thingID))
                    && f.Definitions.Any())
                    .Select(f=>f.FieldID)
                    .ToList();

                    TRANSLATES TO:

                    CODE: SELECT ALL
                    SELECT "Extent1"."FieldID" FROM "Field" "Extent1"
                    WHERE (
                    EXISTS (
                    SELECT 1 AS C1 FROM (
                    SELECT "Extent2"."ObjectID"
                    FROM "ObjectField" "Extent2"
                    WHERE "Extent1"."FieldID" = "Extent2"."FieldID" )
                    "Project1"
                    WHERE EXISTS (
                    SELECT 1 AS C1 FROM "Thing" "Extent3"
                    WHERE ("Project1"."ObjectID" = "Extent3"."ObjectID")
                    AND ("Extent3"."ThingID" = :p__linq__0) ) ))
                    AND (
                    EXISTS (
                    SELECT 1 AS C1 FROM "Definition" "Extent4"
                    WHERE "Extent1"."FieldID" = "Extent4"."FieldID" ))

                    Edited by: 974103 on Nov 30, 2012 11:58 AM
                    • 7. Re: How to connect to Oracle db with Entity Framework & ODP.NET Managed Driver
                      15208
                      What is your Oracle database version? Is it Oracle Express 11.2?
                      The reason I asked is because of this link:
                      Re: Entity framework TPT does not work -> showstopper
                      • 8. Re: How to connect to Oracle db with Entity Framework & ODP.NET Managed Driver
                        kiquenet
                        Any final solution about it (Entity Framework & ODP.NET Managed Driver) with full source code sample?

                        Edited by: 886608 on 04-abr-2013 15:37
                        • 9. Re: How to connect to Oracle db with Entity Framework & ODP.NET Managed Driver
                          Tridus
                          Not until beta 2 comes out... and no, I don't know when that is. :)
                          • 10. Re: How to connect to Oracle db with Entity Framework & ODP.NET Managed Driver
                            kiquenet
                            See section B. in answer.
                            I tried this with the beta and it sort of worked - the queries returned different results.

                            http://stackoverflow.com/questions/13721025/deploying-and-configuring-odp-net-to-work-without-installation-with-entity-frame
                            • 11. Re: How to connect to Oracle db with Entity Framework & ODP.NET Managed Driver
                              Alex.Keh .Product.Manager-Oracle
                              Beta 2 is available and supports using the Visual Studio EF tooling and DB First with ODP.NET, Managed Driver.
                              • 12. Re: How to connect to Oracle db with Entity Framework & ODP.NET Managed Driver
                                phowe001

                                Hi, Could you please post (or re-post) the link to that?  I have downloaded a couple different versions and still cannot get EF to play nice with our Oracle 11g database.

                                 

                                Thanks,

                                Peter