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.
I'll explain the situation in detail right below, but the main questions are:
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 (22.214.171.124.0) from http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html
I have managed to initialize our model,
SIAEntities (which inherites from
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);
SIA.Models.SIAModelOracle.ssdl is a custom written file (embedded resource) which overrides the SSDL part of the .edmx and has the following modifications:
Providerattribute of the root
Schemanode set to
oracle.dataaccess.clientnode added in the
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.
Edit: Oracle has now released a version (126.96.36.199.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:
Edit #2: Visual Studio 2013 is supported beginning with version 188.8.131.52.2
FYI - I created a video to highlight some of the other features of these tools: