DevForce 2012: Oracle schema name

Oracle schema name

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2012
Forum Discription: For .NET 4.5
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=3674
Printed Date: 28-Apr-2025 at 11:06am

Topic: Oracle schema name

Posted By: mikke63
Subject: Oracle schema name
Date Posted: 27-Sep-2012 at 6:48am

I have a SL5/DF2012 project based on an Oracle database. The model is generated from an Oracle development server and a schema (Oracle user) named dev. Running the app against the default connection string is ok.

In production another database server will be used, as well as different schema names (we have one separate schema for each customer). All schemas of course have identical table definitions.

I have succesfully created an implementation of IDataSourceKeyResolver that returns a ClientEdmKey with the approriate connection string to switch between development and production servers, as well as log in with different credentials. What I am NOT able to do is to select the correct schema. Every query I make tries to address the dev schema which the model initially was created from. I can see that this schema name is stored in the edmx file for each table.

How can I dynamically change the schema at run time like I can change server and credentials?

Mikael

Replies:

Posted By: mgood
Date Posted: 27-Sep-2012 at 7:04am

You have to create seperate storage models for each schema, the SSDL part of an edmx. As you discovered, the schema name is part of the mapping and cannot be changed. The only way to change it at runtime is to reference the correct storage model in the connection string, provided you previously created and deployed a storage model for the schema name in question.

Different schema names is a pretty bad idea with an ORM unless you don't mind maintaining multiple mappings.

Posted By: mikke63
Date Posted: 27-Sep-2012 at 11:03pm

We will have to update and rebuild the application each time we add a new customer and database schema then...

For SQL Server there is no problem with having the schema name constant, since you have the concept of catalogs to manage different databases within one single server instance. With Oracle there is only one database for a single server instance, and hence we need to vary the schema name to select which data to work with.

If I understand correctly this is not directly related to DF, but actually a consequence of the inner working of EF. I'll follow it up with Microsoft/Oracle regarding EF instead, and post any findings here later.

Thanks anyway,

Mikael

Posted By: mgood
Date Posted: 27-Sep-2012 at 11:10pm

No, you don't have to rebuild the application each time. The csdl/msl/ssdl can be outside the assembly anywhere in the file system.

http://msdn.microsoft.com/en-us/library/cc716756.aspx -

Posted By: mikke63
Date Posted: 28-Sep-2012 at 1:39am

Thanks for your input, it helped point me in the right direction.

As it turnes out it wasn't at all that dificcult. In the IDataSourceKeyResolver implementation, where I create the ClientEdmKey with correct user credentials, I also change the SSDL reference in the connection string. I grab the original single SSDL file built with the project, creates a copy, replaces the schema name in the copy, and finally inserts a reference to the copy in the connection string.

With this solution we can dynamically add new customers and schemas to the database, update a single configuration file, and the application will automatically adapt to the new schema at logon time. We don't have to manually maintain and update multiple SSDL files.

Mikael

Posted By: mgood
Date Posted: 28-Sep-2012 at 1:44am

Sounds like a good solution. Glad you got it sorted out.