Scaffold-DbContext To Tables from another schema

[parejajd]

I try Scaffold tables that is stored in another schema, using the next command:

Scaffold-DbContext "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=X.X.X.X)(PORT=1521)))(CONNECT_DATA=(SID=MYSID)));User Id=MYUSER;Password=MYPASSWORD;" Oracle.EntityFrameworkCore -Tables "AnotherScheme.TheTable"

and i am getting:

Unable to find a table in the database matching the selected table AnotherScheme.TheTable.

I can make queries to the table.

[alexkeh]

This is likely bug 29443379/28963848. This has been fixed post-beta 4, which means it will be in the production release.

[pnmcosta]

Hi @alexkeh,

I've just come across this problem, we're on netcoreapp2.2 and have the following packages installed:

    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.2.6"/>
    <PackageReference Include="Oracle.EntityFrameworkCore" Version="2.19.60" />
    <PackageReference Include="Oracle.ManagedDataAccess.Core" Version="2.19.60" />

I'm new to Oracle and this package, could you confirm if it should be fixed on these versions? If not, could you confirm what the state of beta 4 is and if we can use it on netcoreapp2.2?

Thanks so much for your help in advance.

Cheers,
P.

[alexkeh]

These bugs were fixed and are in the 2.19.60 release. You are likely running into another bug. Do you have a test case to share that we can use to reproduce the problem, find the root cause, and fix it?

[pnmcosta]

Exactly what's above, trying to auth with a user/schema that is diferent from the tables schema to be scaffolded.

It always says "Unable to find a table in the database matching the selected table". However, if I set "-Schemas: AnotherScheme" with the same connection string, it works fine, except it's scaffolding all the tables.

[alexkeh]

Did you try using the "-Tables " format?

If you don't specify the specific tables, then all the schema's tables will be scaffolded.

[pnmcosta]

yup, tried:

-Tables "AnotherScheme.TheTable"
and
-Tables "[AnotherScheme].[TheTable]"

to no avail.

[alexkeh]

I just tried it out with the -Schemas and -Tables parameters and it works if you use both of these. For example:
Scaffold-DbContext "<connection string>" Oracle.EntityFrameworkCore -Schemas <schema> -Tables <tables>

[pnmcosta]

If i use both, -Tables seems to get ignored and ALL the tables under the -Schemas I determined get scaffolded, including the only one I want.

Please note what I'm having is expected behavior, see comment at the bottom of https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/powershell

I'm using Package manager console to run these, I ensure I have the default project selected to the one with EF core and the latest version of the oracle provider. However do you know of a way to ensure the versions being used?

Get-Package lists the expected versions for the project I selected:

Oracle.EntityFrameworkCore          {2.19.60}                                                                                                                                                                                                                                                                                                
System.ServiceModel.Primitives      {4.7.0}                                                                                                                                                                                                                                                                                                  
Microsoft.AspNetCore.App            {2.2.0}                                                                                                                                                                                                                                                                                                  
Microsoft.AspNetCore.Razor.Design   {2.2.0}                                                                                                                                                                                                                                                                                                  
Swashbuckle.AspNetCore              {4.0.1}                                                                                                                                                                                                                                                                                                  
AutoMapper.Extensions.Microsoft.... {7.0.0}                                                                                                                                                                                                                                                                                                  
Microsoft.EntityFrameworkCore.Tools {2.2.6}                                                                                                                                                                                                                                                                                                  
Microsoft.NETCore.App               {2.2.0}                                                                                                                                                                                                                                                                                                  
Microsoft.AspNetCore.Hosting.Win... {2.1.1}                                                                                                                                                                                                                                                                                                  
Serilog.Sinks.File                  {4.1.0}                                                                                                                                                                                                                                                                                                  
Oracle.ManagedDataAccess.Core       {2.19.60}                                                                                                                                                                                                                                                                                                
Serilog                             {2.9.0}                                                                                                                                                                                                                                                                                                  
System.Diagnostics.EventLog         {4.7.0}                                        

Get-Help about_EntityFrameworkCore doesn't list it's version or the providers within. Any advanced commands from the Oracle provider I could use to ensure I'm running the latest version?

Otherwise, this is an existing DB, could it be something related to how it's configured, I can try and get the DBA to have a look at it, but big firm and can take a while.

Thanks so much for all your help,

Cheers,
P.

[alexkeh]

2.19.60 is the latest available versions of the Oracle providers.

Which Oracle DB version are you using? I tested with a 19c DB.

Can you provide the ODP.NET trace for when you scaffold? You can turn on tracing by setting the following properties in OnConfiguring() and using the Oracle.ManagedDataAccess.Client namespace.

OracleConfiguration.TraceLevel = 7;
OracleConfiguration.TraceFileLocation = @"D:\traces";

Can you also provide the verbose trace (-Verbose parameter) when you run Scaffold-DbContext command?

Between those two traces, we can see what ODP.NET EF Core and the scaffold command are doing to identify the root cause.

[pnmcosta]

Hi Alex,

Thanks, could I send the logs privately to you?

The oracle version is 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production.

Cheers,
P.

[alexkeh]

@pnmcosta
Yes. You can send me the logs: alex.keh <at> oracle.com.

[lgoodw]

@pnmcosta @alexkeh Hi, I was just wondering if you were able to find the root cause of this issue in the end? Thanks

[alexkeh]

@lgoodw I haven't received a test case yet. Perhaps an email filter stopped it if the project contained binaries.

[pnmcosta]

ah yes, I was going to send the log outputs but was told I cannot (firm policy!)

in the meantime EF migration was put on hold, so I didn't follow up. sorry!