2 Replies Latest reply on Jul 25, 2012 7:05 PM by Christian.Shay -Oracle

    Cannot see Oracle Data Provider for .NET in Visual Studio 2012 RC

      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 ( 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 `ObjectContext`s with another class that handles the choice between either of them, but as question #2 says, I cannot do that.