7

I want to add ADO.NET Entity Data Model to my project. I am using Oracle database and Oracle ODP.NET to connect to the database.

I need to query from two schemas. So I configured my connection to use two different Schemas (using the information I found in another SO question ).

The tables are visible in my Server explorer. But they are not visible in ADO.NET Entity Data Model Adding wizard. It shows only tables from the first schema and they have schema name in front: 

TableName(SchemaName).

What must I do to see tables from both schemas?

Community
  • 1
  • 1
Adil Mammadov
  • 8,476
  • 4
  • 31
  • 59

4 Answers4

16

UPDATE: I had a very similar problem and this workaround found in the Oracle forums helped me. The aformentioned workaround did not seem to make into Oracle's new forums area. Please see my SO answer here for more details.

Summary: In VS2012 with the latest ODAC, when you set up connection filter criteria you always have to include the default schema along with whatever schema you want.

For some reason, I didn't have to do that in VS2010 with previous version of ODAC.

Community
  • 1
  • 1
bflow1
  • 969
  • 1
  • 10
  • 25
  • thank you for answer. Can you please explain what is default schema? – Adil Mammadov Dec 20 '12 at 06:34
  • We were having this same problem with the newest Beta (11.2.0.3.60) for the ManagedDataAccess provider and this tip fixed it for us. Just add any additional schema you need, but leave the default schema there too. The default schema is the schema that matches your login. – CactusPCJack Apr 19 '13 at 16:17
  • I am having this same issue, but I have the default schema as well as the target schema in the filter... still no luck. – Scott Baker Sep 25 '13 at 22:13
  • 2
    This solution worked for me in VS2013 with the latest ODP.NET 12r2 managed driver, at least with EF5. Thanks! – Tom Tregenna Apr 11 '14 at 14:03
  • @Tom Tregenna you bet! – bflow1 Apr 11 '14 at 14:06
  • It works for me with VS 2012 + ODP.NET 11 2 3 60 ! Can't believe oracle never fixed this !!! – RolandoCC Apr 28 '14 at 21:01
  • Thank you!! Including the default schema did it for me in vs2013, EF6 and ODP.NET 12r3 – Jesse Apr 08 '15 at 14:43
2

This is a known bug. I too suffer from it and at one point it went away, but now has returned for me. Oracle is aware of it and hopefully will fix it with the next ODAC release. See here for a thread concerning it:

https://forums.oracle.com/forums/thread.jspa?messageID=10396897

Reply from Oracle Employee:

Posted: May 2, 2012 3:53 PM

We've been able to reproduce this bug at Oracle and are currently investigating a solution. We hope to include this bug fix in the next release.

Justin
  • 6,373
  • 9
  • 46
  • 72
1

In case it's helpful... I was unable to add tables at all from the non-default schema, using VS 2010, EF 5 and ODAC 2.102.2.20. Because I'd set the filter to use the schema that had my table definitions (which was different than the default schema for the user id) I could see the tables in the server explorer but could not add them to the model. I had to add both the default schema for the user id and the schema for the tables to the filter list.

Chris Anderson
  • 666
  • 3
  • 4
1

The key for me was when I created the Data Connection in Server Explorer, use the Oracle Unmanaged Driver. Specify TNS Alias, point to your .ora file (crucial). Then, and this is what kept causing problems, is to click Filters, and add the schema needed to see the tables. Leave the default schema, and ADD the schema you need. You have to hit the Update button in order for this to take effect!!! If all goes well you should see the tables in the Server Explorer Data Connection.

Now go to your solution and add the ADO.NET Entity Data Model, EF Designer from database. You should get a dialog that holds the Data Connection we created above. Choose that. I chose to include the sensitive data in the connection string.

Now when you go to the window that shows the tables they automagically appear!

Hope this helps someone someday. I was stuck on this off and on for a couple weeks.

pegler
  • 11
  • 1