0

I am currently attempting to generate code from tables from an existing Oracle 12 database from within Entity Framework 6. The problem I am having is that while it is able to see the schema that I own when I log into the environment, Entity Framework can't seem to see any other schemas that I have access to.

How can I change it so that I can see another schema?

I have used Entity Framework model-first and code-first. I can go in an manually change the schema on a generated table that I own in Oracle and it will work, but that would mean generating my own versions of tables from the other schema in my own schema just to be able to use them via Entity Framework.

Generating all the code from all the existing tables would be a cumbersome job otherwise.

How do I solve this problem?

tone
  • 1,374
  • 20
  • 47
  • I don't think EF supports schemas at this stage. One workaround is to create synonyms in your owned schema back to the other tables. – Nick.Mc Jan 17 '18 at 06:57
  • `[Table("TableName", Schema = "MyOtherSchema")] class MyEntityTypeName { ... }`? – grek40 Jan 17 '18 at 07:51
  • Inside `OnModelCreating` call `modelBuilder.HasDefaultSchema("MyOtherSchema")` for some default. But maybe I'm not yet understanding your actual workflow... – grek40 Jan 17 '18 at 07:54
  • Found in the list of Related questions, perhaps will help https://stackoverflow.com/questions/7422275/oracle-entity-framework-generate-entity-from-tables-in-different-schema – Hemid Abbasov Jan 17 '18 at 12:37

1 Answers1

4

Ok, I have solved the problem. There is a particular way to get schemas working with Oracle in Entity Framework, and you can get full use of Entity Framework, including the Designer/Canvas if you want.

First, in your application, using Nuget, install the Oracle.ManagedDataAccess.EntityFramework package. If you look at the Description of this package in Nuget, it says it depends on EntityFramework and Oracle.ManagedDataAccess. So install the one package and it will install all that, if its missing.

Next, if you read the comment at the bottom of that description, it says "Note: The 32-bit Oracle Developer Tools for Visual Studio download from http://otn.oracle.com/dotnet is required for Entity Framework design-time features and for other Visual Studio designers such as the TableAdapter Wizard. This NuGet download does not enable design-time tools, only run-time support."

So you need to install the Oracle Tools for Visual Studio that matches your environment. Installing ODTwithODAC packages won't give you the right outcome.

Finally, add a connection to the Oracle database. Then, open the Server explorer and find that connection. Right-click on the connection, and select Filters. Then select Displayed Schemas. Edit the value, adding in all the schemas you want to display, by clicking on the "..." button and selecting the schemas. Then click Update. Then click Ok.

Next time you want to select an object from a schema that you just added, it will appear in the Entity Framework list.

tone
  • 1,374
  • 20
  • 47
  • In Server Explorer, when you choose to add a new Data Connection, when you change the Data Source to Oracle, make sure you pick the ODP.NET, Managed driver from the Data provider drop down. If you don't, you'll end up with the wrong Oracle Server Data Connection and it will be a significantly inferior experience, without the ability to easily select Schemas.. – tone Mar 22 '18 at 05:55
  • Thank you @tone. I would like to add that the collection dropdown should be on the default "Connection" and "Displayed Schemas" is a property that can be found in the filter criteria table below. – drsbee Apr 22 '20 at 16:47
  • Still I cannot get this to work, aaaaargh. I can adjust the schema from the Server Explorer. But when I want to use this connection to create a new ADO.NET model it still uses the wrong schema. I also tried deleting/recreating the connection with the correct filter. (I'm on Visual Studio 2019, the rest is the same) – drsbee Apr 22 '20 at 16:57
  • It works. It is important to ADD the other schema to the connection instead of REPLACING it. (https://stackoverflow.com/questions/7422275/oracle-entity-framework-generate-entity-from-tables-in-different-schema) – drsbee Apr 23 '20 at 07:09
  • Thank you very much for this enlightning answer! I have struggled this for a long time and thought there had to be a much more complicated way to do it such as editing the files manually or overriding something in the instantiation of the entity or likewise. But it was as simple as selecting Filters on the connection itself - almost brilliant :D – StefanDK Jul 06 '20 at 12:11