This content has been marked as final. Show 5 replies
Usually if the tables & views aren't showing, it's because they aren't in the schema of the user you're connecting as. It's SUPER* annoying. Sometimes you can fix it by going back into the connection settings and adding more schemas under the "filters" tab, but I find that to not work reliably in the case of EF (particularly when updating a model, it tends to work when creating a new one).
The most reliable fix is to log in as the schema owner... at least until we can convince Oracle to fix it so that the default case always works (and let you remove stuff to improve performance).
Hi Alex. Here's my version of it:
- Windows 7 Enterprise x64.
- Visual Studio 2012, update 2. I've found it happens with all versions of 2012 reliably. I've found it also happens with 2010... sometimes.
- Oracle "fat" client 18.104.22.168 x86. This is the stock client the DBA downloaded from My Oracle Support, every system in the building uses this as it's Oracle client for ODBC, OO4O, and .net applications (except the ones using the managed provider).
- ODP.net managed beta 2 (with it's instant client) in a second home.
1. In VS 2012, create a project. Doesn't seem to matter what it is, but Class Library is one I've had 100% success with.
2. Create a connection in server explorer, and add a schema to it. The user you're connecting as should have access to the other schema, but not own it. (You want something that won't show up by default if you don't add the schema.)
3. Create a new EDMX model. Tell it to save the connection information (user/password) in the config file. Add some tables from that other schema.
4. Write some simple code to do a select on one of the tables and run it, to make sure it all works. If you used Class Libary above, you'll need to add another project and reference the first one.
5. Restart Visual Studio and reload the project.
6. Open the model and try "update from database".
What should happen now is you won't see any tables to add except from the connecting user's schema. In 2012 it seems to ignore what you set in Server Explorer and uses the connection information in the app.config/web.config for the model update, which by default won't show the other schema.
Along with other annoyances, the problem would go away if the deafult was just to show what the user has access to, instead of filtering out schemas. It'd be nice if the filter let you remove schemas for performance instead.
Does this problem occur if you use the Filters in Server Explorer to allow viewing the user's non-owned schemas?
What you describe is the default behavior expected. By default, Oracle Dev Tools will filter out non-owned schema, even if there is permission to view them because of feedback from customers who have access to large numbers of non-owned schema objects. They didn't like the clutter and performance that resulted in having to retrieve a large number of schema objects.
In VS 2012, it does happen when I'm updating a model. It doesn't happen when I'm creating it, in that case it uses the filters set in Server Explorer correctly.
In 2012 it seems to ignore Server Explorer when updating and just uses whatever is set in app.config for a connection. The behavior in 2010 is a bit different, in my experience.
I get the feedback about the performance and clutter, though in 2012 the model wizard is smart enough to split things up into schemas and reduces clutter noticably. The problem is that the default this way just flat out doesn't work in some cases, which is really no better. In my situation the users that access the schemas never own them, as they're just application server proxy accounts. So I need to change the connection to the schema owner in order to update a model, then change it back anytime I want to debug the application. (I can only imagine how ugly it'd get if you needed to use things from two schemas in one model!)
I guess another workaround would be a connection string parameter that tells it to include a schema (or all schemas). Something needs to be done IMO, because the updating functionality doesn't work in this case and the schema filters in Server Explorer don't solve it.
Edited by: Tridus on Apr 26, 2013 7:00 PM
Edited by: Tridus on Apr 26, 2013 7:04 PM