Entity Framework 6, database-first with Oracle

Facebooktwitterlinkedinmail

I spent a lot of time trying to find out how to implement Entity Framework 6 database-first scenario with Oracle database. It’s not as straightforward as you might think. I searched various websites and found only confusing information. Finally I got it working, therefore I can confirm that EF6 database-first works with Oracle databases.

First, let me clarify my environment. I have Visual Studio 2013 and .NET Framework 4.5.2 installed.

Secondly, a word about my goal. I wanted to create a MVC5 website that connects to existing Oracle database.

So, here is how I did it.

Setup Oracle Developer Tools

First I had to install Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio. Yep, long name. You can get it from Oracle web site (you’ll have to register). Make sure to take ODAC 12c Release 3 as previous versions don’t support Entity Framework 6. You will get it in a form of zip file. Next extract it and run setup.exe (it’s java so will take some time). Standard installation worked for me – just click Next couple of times and Install. If you want to alter the installation, make sure you:

  • Note Software location – the default is C:\app\client\USERNAME\product\12.1.0\client_1
  • Have Oracle Developer Tools for Visual Studio component selected – that’s what we are after :)
  • Select Visual Studio version that will be configured with the developer tools – you can select few, I needed only VS 2013
  • Check Configure ODP.NET at a machine-wide level – it will install it in GAC

Install Entity Framework 6

I wanted to use EF6 in my MVC5 web project. A new MVC5 project has already reference to EF6, so there was nothing to do. But if you have different project you might have to install Entity Framework 6 manually. The easiest way is to use NuGet packages:

  • NuGet manager is included in VS 2013, but if you use VS 2010 you will have to install it:
    • Open VS and click Tools menu, then Extensions and Updates
    • Click Online on the left hand panel, then type NuGet in search text box on the right side
    • It should find NuGet Package Manager – click on it and press Download button
    • Visual Studio restart will be required
  • Next, open you project, right click it and choose Manage NuGet Packages from context menu

manage-nuget-packages

  • Click Online on the left side, then type Entity in search box
  • Click EntityFramework and press Install

install-entity-framework-6

Reference Oracle libraries

To leverage Entity Framework capabilities you have to add reference to Oracle Data Access library.

  • Right click on References, then Add reference… in context menu

add-reference

  • Click Browse button and find following library:
    C:\app\client\USERNAME\product\12.1.0\client_1\odp.net\managed\common\Oracle.ManagedDataAccess.dll
    (location might be different if you changed it during ODAC installation)
  • Browse and find one more library:
    C:\app\client\USERNAME\product\12.1.0\client_1\odp.net\managed\common\EF6\Oracle.ManagedDataAccess.EntityFramework.dll

reference-oracle-data-access-components

Add Oracle provider for Entity Framework 6

This is quite important step. If you don’t do it  you will see following error when attempting to generate model from database. I lost a lot of time trying to resolve it :|

no-oracle-provider-defined

To add Oracle provider you need to open web.config and add following:

<configuration>
 <entityFramework>
  <providers>
   <provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />

It was enough to fix it for me, but seen a lot of comments that following is required as well. You can check if it’s needed in your case.

<configuration>
 <configSections>
  <section name="Oracle.ManagedDataAccess.Client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />

Generate Entity Data Model from database schema

Finally, it’s time to do the actual work.

  • Add new item to the project
  • Choose Visual C# then Data and select ADO.NET Entity Data  Model

add-ado-net-entity-data-model

  • Select Generate from database and press Next
  • Select connection or add new connection – I had some issues when tried to add new connection (see the solution at the bottom)
  • Choose how to store sensitive data

choose-data-connection

  • Choose database object that should be included in the model

choose-database-objects

That’s it. Well done!

Summary

Configuring Entity Framework 6 to work with Oracle database in database-first scenario isn’t as easy as one would expect. Fortunately it’s doable.

I also tried more elegant solution – to use ODAC NuGet package. But lost a lot of time and finally couldn’t make it work. Then found following comment on Oracle web site – meaning ODAC Release 3 which has EF6 support is not uploaded as NuGet package yet.

Note: NuGet installation is not currently available, but will be available shortly.

Issue with adding new Oracle connection

I had a strange issue when I tried to setup database connection. I clicked New connection and couldn’t find my database in Data source list, so I clicked Advanced button.

isue-with-tnsname

I filled following fields and clicked OK.

  • Security / User Id
  • Security / Password
  • Source / Data Source

Then clicked Test Connection and was again surprised – it couldn’t find my instance. I checked some SQL client and my database was working well. I did some searching and found following way to overcome this impairment.

  • Run tnsping INSTANCE_NAME command to get details about database instance
  • Go again to Advanced window and in Data Source type something like: server:port/INSTANCE.WORLD
  • This time test connection will succeed.

test-oracle-connection

Interesting is that it’s not able to connect only in wizard mode. Later I updated data source in web.config (from server:port/INSTANCE.WORLD back to only INSTANCE) and application worked fine.

Facebooktwitterlinkedinmail

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

88 thoughts on “Entity Framework 6, database-first with Oracle”

  • Reply ↓
    Marc Overmars

    This article/tutorial exemplifies the good about the internet. I am working on an enterprise project and was able to leverage Oracle Database using Visual Studio 2013 Ultimate and Entity Framework 6; using your instructions. There are hundreds of articles and posting online with misleading and “throw it on the wall see if it sticks,” solution. This posting helped me get it done without fluff!

    It is 1:22am and I stumbled on your site. You are a very intelligent and brilliant person, and I am very lucky!

    Thank you!

    MO

      • Reply ↓
        Raj

        Hi Mariusz,
        Thanks for a nice article. I was able to create a prototype in the dev environment, How do we deploy it in other environment? Which version of ODAC should we install and will it affect existing oracle clients on the server?

        Thanks,
        Raj

  • Reply ↓
    ps_prakash02

    I followed the above steps but I’m not able to connect to the database..

    I selected “ADO.NET Entity Data Model”, “EF Designer from Database”. After this step, it prompts for connection.

    I selected “New Connection” and in the connection properties, I selected “Oracle Database (ODP.Net Managed Driver) and entered the database details.

    After entering all the details, when I click Test Connections. I’m getting an exception like “ORA-12537: Network Session: End of file” .. and it is not allowing me to proceed further in this. Please let me know what is causing the issue and how to solve this ?

  • Reply ↓
    Neo

    Hi, I have followed all steps and able to add ADO.net Entity Data Model successfully but when I try to save data I get below error, kindly help. What I have missed?

    The Entity Framework provider type ‘Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342’ registered in the application config file for the ADO.NET provider with invariant name ‘Oracle.ManagedDataAccess.Client’ could not be loaded. Make sure that the assembly-qualified name is used and that the assembly is available to the running application. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

      • Reply ↓
        Randy

        Hi, I am developing a MVC5 web application with oracle database and follow all above tutorial, however when I rich the step (•Choose database object that should be included in the model) in the Entity Data Model Wizards tables are not clickable, is there anything that I have missed? Does anyone can help me about this issue?

        • Reply ↓
          Kevin

          Hi,
          I am having the same issue with non clickable tables and views. No models are getting generated. Did you solve this issue?
          Thanks

  • Reply ↓
    Nasir

    i am working on vs 2010 . i am trying entity framework using oracle database . can you give me a solution like that?

  • Reply ↓
    InHouse Training Computer

    Hello there! I could have sworn I’ve been to this blog before but after looking
    at some of the posts I realized it’s new to me. Anyways, I’m definitely
    pleased I discovered it and I’ll be bookmarking it and checking back frequently!

  • Reply ↓
    Richard

    You appear to have solved my problem of getting Oracle into Visual Studio 2013 for Entity Framework. I couldn’t help but notice a discrepancy in version numbers from what you posted. Admittedly I’m using a slightly newer version of: Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio (ODAC 12c Release 4 (12.1.0.2.4)) but the version numbers you posted into the web.config :
    <provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0,

    and

    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.121.2.0,

    seem to differ from the version numbers posted by Oracle by a moved decimal point:

    Oracle Data Provider for .NET 4 (12.1.0.2.0)

    Could this explain some of the erratic installation behavior you and others have seen?

    • Reply ↓
      Mariusz Bojkowski Post author

      Thanks Richard. This looks like typo. I’ve just corrected the post.

      Unfortunately, I can’t verify if it’s the cause of flawed installation behavior anymore…

  • Reply ↓
    Deon Commins

    Hello there, You have done an excellent job. I will certainly digg it and personally suggest to my friends. I’m sure they will be benefited from this site.

  • Reply ↓
    Twila Mabus

    Hey very cool blog!! Guy .. Excellent .. Superb .. I will bookmark your web site and take the feeds also? I’m satisfied to search out numerous helpful information right here within the post, we need work out extra strategies on this regard, thanks for sharing. . . . . .|

  • Reply ↓
    Gertdj

    Hi,
    This is a great post!
    I have VS 2015 and the latest ODAC drivers.
    I have followed your instructions, made the connection successfully, but are stuck on the last step when creating the Entity Data Model as indicated in your post. It is giving me that exact error as per your posting when not adding the Oracle Provider for Entity Framework 6, although I did add it as per your instruction. Can you help with this please?

    • Reply ↓
      federico balbi

      Hi,
      I had the same problem as well until I realized the project I was trying to add EF6 was MVC4. On MVC4 even if you have all the latest Oracle components it won’t work. You have to do an MVC5 project.

  • Reply ↓
    poornima

    i am getting the same issue as (your project references the latest version of EF………..)in Entity data model wizard
    please help me……………

  • Reply ↓
    Neeraj Aggarwal

    I am trying to add package or procedure from Oracle connection, but unsuccessful. I am getting error “Error 6046: Unable to generate function import return type of the store function ‘XXXXX’. The store function will be ignored and the function import will not be generated”

    Please help, there is no issue in using tables they are running fine.

    Thanks in advance.

  • Reply ↓
    Gudrun

    Thanks for another great post. The place else may just anyone get that type of information in such a perfect approach of writing?
    I’ve a presentation subsequent week, and I’m on the search for such info.

  • Reply ↓
    Leonardo

    Great website you have here but I was wanting to know if you knew of any discussion boards that cover the same topics talked
    about here? I’d really like to be a part of online community where I can get suggestions from other experienced people that
    share the same interest. If you have any suggestions, please
    let me know. Thank you!

  • Reply ↓
    Raj

    This is very well written article. I was able to create a prototype that works with oracle database with entity framework. Incidentally I have the same environment VS 2013,EF6. My app works without adding

    get this error:
    (Configuration Error
    Unrecognized element ‘providers’.
    )
    or

    Get this error
    (HTTP Error 500.19 – Internal Server Error
    The requested page cannot be accessed because the related configuration data for the page is invalid.)
    in the web config.
    As I said the application works fine on my machine.
    But how do we deploy it on the server? Will it cause any issues?

    Thanks,
    Raj

  • Reply ↓
    Azad

    I have installed Oracle Client(32 bit)12.1.0.2 which has ODP.NET in it.
    I have referenced Oracle.ManagedDataAccess and Oracle.ManagedDataAccess.EntityFramework EF6 dlls in my .net project in VS2013 I have followed the same step as you have mentioned But I am not able to see ODP.NET provider while creating new connection.
    .
    Please help

  • Reply ↓
    Meera

    Really good article. I have been struggling from a long time.But i follow all the steps and it works.
    Thank you so much.

  • Reply ↓
    Andy

    Nope. Still get the error that you displayed after the “Add Oracle provider for Entity Framework 6” section. Very frustrating.

  • Reply ↓
    Nirdesh

    Thank you very much. This article has been very helpful for me to start with entity framework.
    Very well explained.

  • Reply ↓
    Marcel Gosselin

    Thanks for your article, it was insightful.

    In case someone other than me has the same problem, I would like to add that the message in the “Entity Data Model Wizard – Choose Your Version” dialog above can also be caused if either of these situation occurs:

    1- If in the you the following is missing:

    2- If you forgot to compile the project between the changes in the app.config/web.config and launching the wizard.

    Thanks,
    Marcel

  • Reply ↓
    Ashraful Alam

    I have followed your instructions to setup a MVC5 project with EF6 and Oracle database. I have installed the ODAC with oracle developer tools with visual studio from oracle site and added Oracle.ManagedDataAccess.dll & Oracle.ManagedDataAccess.EntityFramework.dll as reference. Then I have added the section and on web.config file. I rebuild the project and tried to ADO.Net Entity Data Model but still got the compatibility error on Choose your version section of the Wizard.
    My Environment: Visual Studio 2003, Oracle 11g.

    Is there anything I am missing to that? Thanks in advance.

  • Reply ↓
    Gilles Leblanc

    Hello, great post.

    I noticed that the ODP.NET Managed drivers are also available on NuGet.

    Are they the same drivers? Is there a reason to prefer getting them from the Oracle website vs. NuGet?

  • Reply ↓
    Ahmed Fathy

    The post is great, however, this in web.config

    the version is wrong, made me spin over myself like crazy

    Version=4.121.2.0 should be Version=6.121.2.0

  • Reply ↓
    Petar

    Hi,
    do u know why I have this error when I run project:

    Server Error in ‘/’ Application.

    Could not load file or assembly ‘Oracle.ManagedDataAccess.EntityFramework, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131XXX)
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.IO.FileLoadException: Could not load file or assembly ‘Oracle.ManagedDataAccess.EntityFramework, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference. (Exception from HRESULT: 0x8013XXX)

    Source Error:

    Line 22: {
    Line 23: public ApplicationDbContext()
    Line 24: : base(“DefaultConnection”, throwIfV1Schema: false)
    Line 25: {
    Line 26: }

    Source File: c:\XX\XX\documents\visual studio 2015\Projects\WebApplication7\WebApplication7\Models\IdentityModels.cs Line: 24

    Assembly Load Trace: The following information can be helpful to determine why the assembly ‘Oracle.ManagedDataAccess.EntityFramework, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342’ could not be loaded.

    WRN: Assembly binding logging is turned OFF.
    To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
    Note: There is some performance penalty associated with assembly bind failure logging.
    To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

    • Reply ↓
      Isaac

      I suggest you open your Project’s Reference, right click on Oracle.ManagedDataAccess.EntityFramework, click on properties and check the version you have there.
      If not version 4, delete it and upgrade to version 4.

      Thanks.

  • Reply ↓
    Isaac

    Make sure to take ODAC 12c Release 3 as previous versions don’t support Entity Framework 6.

    That statement saved me from serious problem running Code First EF on Oracle database 11g.
    I did all I thought I should until I stumbled on your post.

    You are a very brilliant person, I must tell you.
    I will follow you on twitter. I believe you will be of great help to me.

    Thank you very much.

  • Reply ↓
    Faruck

    I repeat the same steps in my project and the model was created succesfully, but I deleted the model and try to create it again and the problem continues.

  • Reply ↓
    Ravi

    Hi Mariusz,

    Excellent Article!!

    I am developing a web application using VS2015, Entity Framework 6.1.3 & Oracle.ManagedDataAccess v12.1.2400 Oracle.ManagedDataAccess.EntityFramework v12.1.2400. I have finished couple of modules and it works fine in my local machine now i am deploying this application in Windows Server 2012 R2 but i can only access Login page nothing else, i am sure it has something to do with database access, Kindly advise do we need to install anything manually & it will not read from bin folder. at the moment in server i have only .Net Framework 4.5

    Thanks & Regards,
    Ravi

  • Reply ↓
    Suresh

    You Rock!!. Thank you very much for the detailed instructions . I really spent lot of time trying to understand how to achieve Entity Framework 6 database first with Oracle database and was almost going nuts!!. Agree with you where you say it is not straight forward to implement this approach. .

  • Reply ↓
    John Carroll

    Have you ever tried this using Windows authentication? A whole different set of problems, the worst of which is that after you create you edmx you can’t update it from the database.

    • Reply ↓
      Russ

      I would like to hear about your efforts with win auth….. we are struggling with it now. Works fine in rev but not on web server so far.

  • Reply ↓
    Indira Choudhari

    Hi, i am using entityframework with oracle database. I want to change connection string dynamically. I searched on lot of websites and implemented code which is working correct for sql server but not for oracle. Plz anyone help me.

    • Reply ↓
      Rudi

      Same issue here. Created a new schema (same structure) on a test server, set up web.config transforms and even adjusted an app.config connection string, but the version released on the test server still reads from the dev database, and fails when it attempts to write with an insufficient privileges message.

      What is the correct way to implement web transforms with EF on Oracle?

  • Reply ↓
    Jason

    This may be a long forgotten blog posting, but I will try anyway. We have gone thru steps and everything works fine on our local workstations. However, deploying to the server is a different story. Our Windows 2012R2 server has the 12 client installed, not just ODAC, the full Oracle client. When we put our .NET console app on the server and run it, it crashes every time. Seems to die on the line that tries to access the entity framework. I know the server has Oracle connectivity because I can open the database thru Oracle Developer and run queries. Do we need the full Oracle client on the server for this to work or should we uninstall the client and just install the ODAC release 3 client like we did locally?

  • Reply ↓
    uma

    I’m using VS 2013, EF 6.0.2, .net 4.0 and Oracle Db. While building sln: warning MSB3274: The primary reference “Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=MSIL” could not be resolved because it was built against the “.NETFramework,Version=v4.5” framework. This is a higher version than the currently targeted framework “.NETFramework,Version=v4.0”.
    I cant go to 4.5 for compatibility issues. Pls suggest.

  • Reply ↓
    Girish Chawrai

    Nice article. Although, I had to play with the registry settings to get the ODP in datasource list for Visual Studio 2015, but, this article gives a fair bit of “genuine” information. Thanks for sharing.
    Can you also share some info about mapping the types…specially Number(2,0)..etc.

  • Reply ↓
    Jim

    Your article confirmed that I was on the right path and I implemented an earlier release but today I am unable to see other named schema in EF Model Wizzard. I have defined the schema in the connection filter and correct schema is visible Server Explorer Data Connections but the wizzard is only shows dbo schema. Ideas?

    Thanks

    • Reply ↓
      Jim

      Luckily I found the solution shortly after posting my question, unfortunately I have spent considerable amount of time looking for it.

      …when you set up connection filter criteria you always have to include the default schema along with whatever schema you want…

  • Reply ↓
    Daniel

    I have tried everything you said here but when I go to add a new connection I do not have the OPD.net Data Source. What could I be missing?

  • Reply ↓
    laras

    I LOVE YOU DUDE.. SO MUCH !!!

    I search a lot about the strange issue like you then i found the best answer here.
    Thank you so much :*

  • Reply ↓
    Shraddha

    Hi,

    I am getting the error, “Which Entity Framework you want to use?”
    I followed your steps to resolve it but didn’t work. Any other suggestions?

    • Reply ↓
      adenicolas

      Yes, Shraddha, I have other suggestion for you who works for me.
      Be sure that the version of provider in Web.config or App.config (Version=4.121.2.0) is the same as the dll in Reference. I had different version and don’t worked until match both versions.
      I hope this suggestion works for you.

  • Reply ↓
    Grayfield Miracle

    Greate article, thank you. I also have tried to work with Oracle db and EF6 db first by just using the NuGet version of OracleManagedDataAccess and OracleManagedDataAccess.EntityFramework but keep failing when generating the EDMX. So I went on to install the ODAC and it worked well. But when I have to deploy it I prefer not to install anything in the production server, is there a way to deploy the app without installing anything to the server?

  • Reply ↓
    Nimya

    I have done all the same things, thanks, your article is the clearest one I could follow.

    But my .edmx is empty, no errors, seemed to go through successfully, but none of the tables from my ORACLE database appear in the edmx.

  • Reply ↓
    ceci bela

    Thanks for this awesome tutorial. I got it work using .NET Framework 4.6 with VS2013.
    However, I got a minor problem you can read it more here. https://stackoverflow.com/questions/51974741/oracle-provider-with-entity-frame-net-framework-4-6-fails-when-individual-use. Basically I need an that allows users to login so when I create the app but selecting “Individual User Account” for authentication it no longer works.

    Can you help me pls? Thanks again for that great tutorial.

  • Reply ↓
    John H

    Thanks for this post! It’s several years since it was posted…yet in 2018, using VS2017 version, this still corrected several bad NUGET setups that I had to remove. This WORKED…where multiple other approaches failed.

  • Reply ↓
    Radosław

    Cześć, postępuję zgodnie z wskazówkami, zainstalowałem Entity Framework 6.0, następnie dodałem referencje i skonfigurowałem odpowiednio kod web.config, po przeładowaniu projektu, występuje nadal ten sam błąd co na początku. Masz jakieś pomysły co może być nie tak?

  • Reply ↓
    Chris

    Very helpful post! Thank you for that. Any idea what is the backwards compatibility of the current Oracle drivers and EF4? Do they support Oracle 9?

  • Reply ↓
    random_dev

    The ‘data source’ list issue is quite simple.
    Just format your tnsnames.ora as follows

    BEFORE

    foodb = (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = foosvc)
    )
    )

    AFTER (Single line per tns entry)

    foodb=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=foosvc)))

  • Reply ↓
    Zameer

    i have also same issue INSTANCE NOT SET how should i resolve it
    what mean by WORLD in server:port/INSTANCE.WORLD