Entity Framework 6, database-first with Oracle

转载自http://csharp.today/entity-framework-6-database-first-with-oracle/

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

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

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

• 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

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 :|

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

• 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 database object that should be included in the model

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.

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.

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