This discussion is archived
13 Replies Latest reply: Jul 10, 2013 2:10 PM by Alex_Keh - Oracle_Product_Manager RSS

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

977106 Newbie
Currently Being Moderated
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 Employee ACE
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 - Oracle_Product_Manager Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated

    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


Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points