Adding multiple oracle schemas in visual studio 2013 and EF

Question

I have a serious problem that needs to be resolved.

In my server explorer in VS2013, I set up a connection to the database with valid user and password credentials.

So I have a schema called: Connect1, and it needs to have access to another schema called Connect2, both under the same data source name: Dev1. The user I'm connecting with is called Connect1, so by default I'm connected to the schema Connect1. I was able to verify that I have access permissions for this user to connect to Connect2.

Using ODP.NET Managed Driver, I was able to set this up. Putting all the connection details.

Configurations are shown in this page:

Using Oracle Developer Tools for Visual Studio

In order to allow the schemas to be viewed, I had to change the Filters tab, and add all the related schemas I'm using (adding Connect2). Then I click update, and I'm able to use the tables into my edmx file.

However, the problem occurs when I close Visual Studios. My filter gets lost, and my connection is back to using only Connect1.

This then destroys my table mapping in my edmx file with a series of

Error 11007: Entity type 'Table1' is not mapped

This is really frustrating as I'd have to keep on repeating the same procedure over and over again manually remapping all my tables.

What's confusing is when I update the data connection from my Server Explorer to include the second schema.. EF works and detects the tables; but my app config and the connection string has not been changed. So this leads me to believe that if I was to promote my project to production or test, that it will break since there's nothing in my project to show that Connect2 is needed.

Answer 1

Okay I realize the big pitfall that was never explained properly. I highly urge people to follow this if you ever want to use EF with Oracle and you're using another schema.

The pitfall is that since we introduced oracle's schema convention to the EDMX, there was no way of knowing how volatile the mappings would become. The reason why this was never a problem for microsoft is because there is no such thing as a schema in SQL server. To implement two databases in the same edmx file, there is some visible prevention that inhibits you from adding any tables whatsoever. After we introduce schemas, it becomes very fragile.

So what you have to do is follow the steps of adding the schema (as I explained in the question). Going to Filter tab, adding the schemas that way, and clicking the Update button.

Once that is applied to the server explorer, the edmx will have access to adding the tables from that schema. When you do, the EDMX has recollection of the schema embedded in its file. So if you ever do close visual studios, and reopen and run the project, it will still work.

Here are the few tips you want to AVOID: Do not rename any of the entities and the columns (properties), even though it makes your code more readable. That's the first uh-oh. Since the mappings are very volatile, if you happen to mess up, remapping everything back again will be a chore.

The second uh-oh, is when you close Visual Studios, reopen it again, forget to reinclude your schema in Server Explorer, go to the edmx and do "Update model from database" to include anything else (but most importantly.. EVEN IF YOU DON'T DO ANYTHING) and you click OK, your model will still update regardless of non-changes, lose all the mappings that you previously made. That is what I mean by the maps being volatile.

In case that happens, and you didn't do the first uh-oh of renaming entities or the properties.. is to delete all your entities already there, and re-add them again.

The reason why you want to delete all the entities is because adding new ones with the same name will add a new entity with the same name + a number. So something like entity of Customer will still be there, adding the entity again will create a new entity called Customer1.

Sigh.