Cannot see Oracle Data Provider for .NET in Visual

Question

I am porting a web application to Entity Framework 4.5. My main goal is to support both MSSQL and Oracle databases and be able to tell the ORM which data source to target programmatically (in the Global.asax). The SQL and Oracle schemas are obviously functionally identical; this is an approach we already use in production with another ORM we want to throw away.

NOTE: The machine we are testing it on has Windows 8 Release Preview Build 8400 64 bit, Visual Studio Professional 2012 RC 11.0.50706.0 and .NET framework 4.5.50501. It is not a standard development machine (the others have XP and 7 w/Visual Studio 2010 and .NET 4), but a new one we are using to test these new technologies. I have no idea if any of this new technologies may be the problem, haven't tested it on a "standard" dev machine yet.

Short version

I'll explain the situation in detail right below, but the main questions are:

1. In the "Add new ADO.NET Entity Data Model" wizard, I cannot see the ODP.NET data provider I installed (see below) and thus I cannot create a model from an Oracle database. Why? Furthermore, in the "About Visual Studio" pop-up I don't see listed Oracle Developer Tools for Visual Studio. My fear is that the Oracle Developer Tools aren't working somehow, but the Oracle Universal Installer said everything was correctly installed (and I rebooted several times).
2. What is the best practise to achieve this requirement?

Long version

First, we created the Entity Data Model from a SQL Server database. So far so good.

Then, we installed ODAC 11.2 Release 4 and Oracle Developer Tools for Visual Studio (11.2.0.3.0) from http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

I have managed to initialize our model, SIAEntities (which inherites from ObjectContext), with:

            System.Data.EntityClient.EntityConnectionStringBuilder builder = new System.Data.EntityClient.EntityConnectionStringBuilder();
        builder.Metadata = "res://*/SIA.Models.SIAModelOracle.ssdl|res://*/Models.SIAModel.csdl|res://*/Models.SIAModel.msl";
        builder.ProviderConnectionString = "data source=***;persist security info=True;user id=***;password=***";
        builder.Provider = "Oracle.DataAccess.Client"; 
        SIAEntities db2 = new SIAEntities(builder.ConnectionString);

where SIA.Models.SIAModelOracle.ssdl is a custom written file (embedded resource) which overrides the SSDL part of the .edmx and has the following modifications:

1. Provider attribute of the root Schema node set to Oracle.DataAccess.Client
2. All data type names converted to their Oracle equivalent (datetime > date, nvarchar > varchar2, image > blob, etc)
3. An oracle.dataaccess.client node added in the web.config which maps bool to number(1,0) and tinyint to number(3,0)

The error arrived when executing the first query (a simple SELECT): I tracked it as being due to the double quotes in the query that gets executed against the db, forcing it to be case-sensitive.

By manually editing the custom .ssdl file and uppercasing everything I can let EF "see" the table and recognize a column after the other, but I didn't push this solution too far (thus stopping before getting to read the whole table) because a) it didn't seem too clean b) I have difficulties understanding the links between the .ssdl and the .csdl/.msl parts so I wasn't sure where to uppercase and where not to!

Another strategy I was contemplating to avoid this manual work could be to try and generate a separate model from the Oracle schema and wrap the two ObjectContexts with another class that handles the choice between either of them, but as question #2 says, I cannot do that.

Answer 1

Edit: Oracle has now released a version (11.2.0.3.20) of Oracle Developer Tools for Visual Studio that works with Visual Studio 2012 and .NET 4.5. You'll need this version (or higher) of these tools for Visual Studio 2012 Entity Designer support and other Visual Studio 2012 integration. Get it here:

http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

Edit #2: Visual Studio 2013 is supported beginning with version 12.1.0.1.2

FYI - I created a video to highlight some of the other features of these tools:

http://www.youtube.com/watch?v=68tlel4iJdM

Christian Shay

Oracle

Answer 2

Oracle has released ODP.NET with VS2012 and .NET 4.5 support. I don't see much from them in the way of documentation for this release.