基于Database First创建Oracle EDMX模型的技术咨询

Hey there! Let's walk through getting your Oracle EDMX set up properly using Database First in Visual Studio 2017 (.NET Framework 4.5). You've already checked off some key prep steps—installing the ODP.NET packages and tweaking the connection string—so let's build on that to get things working smoothly.

First, make sure your project's config file (web.config or app.config) has the correct ODP.NET provider registration. This is easy to miss and often causes connection issues:

<system.data>
  <DbProviderFactories>
    <remove invariant="Oracle.ManagedDataAccess.Client" />
    <add 
      name="ODP.NET, Managed Driver" 
      invariant="Oracle.ManagedDataAccess.Client" 
      description="Oracle Data Provider for .NET, Managed Driver" 
      type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" 
    />
  </DbProviderFactories>
</system.data>

Note: Match the version number to the exact ODP.NET package you installed via NuGet—you can find this in the NuGet Package Manager under installed packages.

Also, confirm your Oracle connection string follows the correct Entity Framework format:

<connectionStrings>
  <add 
    name="FacetsDataModel" 
    connectionString="metadata=res://*/EntityDataModel.csdl|res://*/EntityDataModel.ssdl|res://*/EntityDataModel.msl;provider=Oracle.ManagedDataAccess.Client;provider connection string=&quot;DATA SOURCE=YourOracleServer:1521/YourServiceName;USER ID=YourUsername;PASSWORD=YourPassword;&quot;" 
    providerName="System.Data.EntityClient" 
  />
</connectionStrings>

Replace YourOracleServer, YourServiceName, YourUsername, and YourPassword with your actual Oracle database details.

Now let's create the EDMX from your Oracle database:

• Right-click your project in Solution Explorer → Add → New Item
• Under the Data category, select ADO.NET Entity Data Model, give it a name (e.g., OracleFacetsDataModel.edmx), and click Add
• Choose the EF Designer from database option, then click Next
• In the "Choose Your Data Connection" screen, click New Connection
• From the "Data Source" dropdown, select Oracle Database (ODP.NET, Managed Driver). If you don't see this option, restart Visual Studio—sometimes the ODP.NET provider doesn't show up until VS refreshes.
• Fill in your Oracle database credentials: enter the data source (format: //Host:Port/ServiceName), username, and password. Click Test Connection to confirm you can reach the database.
• Once the connection is verified, check Save entity connection settings in App.Config as (use the name you set in your connection string earlier), then click Next
• Select the database objects (tables, views, stored procedures) you want to include in your model, set a model namespace if needed, and click Finish

If you want to reuse business logic from your existing SQL Server EDMX, keep these Oracle-specific differences in mind:

• Data Type Mapping: SQL Server's nvarchar maps to Oracle's VARCHAR2, int maps to NUMBER(10,0), and datetime maps to DATE or TIMESTAMP.
• Primary Keys & Sequences: Oracle relies on sequences for auto-generated primary keys. After generating the EDMX, open the Model Browser, select your entity's primary key property, set its StoreGeneratedPattern to Identity, and map it to the corresponding Oracle sequence (you may need to adjust this in the SSDL).
• Stored Procedures/Functions: If you had SQL Server-specific stored procedures, you'll need to recreate their equivalents in Oracle first, then re-import them into the new EDMX.

Here are quick fixes for issues you might run into:

• Connection Failures: Ensure your Oracle server's listener is running, the port/service name is correct, and your user has sufficient permissions to access the database.
• EDMX Generation Errors: If the model fails to generate, check if your database user has SELECT permissions on all the objects you're trying to import. Also, avoid database object names with special characters or excessive length—these can break EDMX generation.
• Provider Not Found: Double-check the DbProviderFactories section in your config file. The version number must match the ODP.NET package you installed, and the PublicKeyToken should be correct (it's usually 89b483f429c47342 for official Oracle packages).

内容的提问来源于stack exchange，提问作者Kavitha