Oracle Database

This guide provides step-by-step instructions for setting up Entity Framework Core with Oracle Database and importing your database schema into your .NET application using the SAIF platform templates.

On this page

• 📚 Useful Resources
• 📋 Prerequisites
• ⚡ Oracle Database Setup
• 🔐 Setting Up Kerberos Authentication
• ⚙️ Configuration
• 📥 Importing Schema from Oracle Database
• 🔄 Updating Your Data Model
• ✨ Best Practices
• ☁️ Azure Deployment Configuration
• 🔍 Troubleshooting
• 🎓 Next Steps

📚 Useful Resources

• Entity Framework Core Documentation
• Oracle EF Core Provider
• EF Core Database First
• EF Core CLI Tools
• SAIF Platform Entity Framework Package

📋 Prerequisites

Before you begin, ensure you have:

• ✅ A System API project created with SAIF platform templates (with Oracle database selected)
• ✅ SAIF platform templates installed
• ✅ .NET 10.0 SDK installed
• ✅ Access to an Oracle Database instance
• ✅ Oracle database connection credentials

⚡ Oracle Database Setup

When you create a System API using the SAIF platform templates, you can select Oracle as your database during the initial project creation. This will automatically set up the necessary projects and configuration files.

📦 What's Included

Your System API project with Oracle will include:

📁 Projects

• Data Project: Contains Entity Framework context and data models
• Data.Seed Project: Contains data seeding functionality (if applicable)

🔧 Additional Files

• OracleDatabaseResourceBuilder.g.cs: Extension method for Aspire integration with Oracle Database

🔄 Adding Oracle to an Existing Project

If you created your System API without Oracle and need to add it later, you can use the feature template:

dotnet new saif-feature-database-oracle --name <your-app-name> --force --project_id <your-project-id>

Parameters:

• --name: Replace <your-app-name> with your application name (e.g., "myapp")
• --project_id: Replace <your-project-id> with your project identifier (e.g., "it-api-sys-myapp")
• --force: Overwrites existing files if they exist

🔐 Setting Up Kerberos Authentication

For local development with Oracle databases, you need to configure Kerberos authentication to securely connect to the database using your Active Directory credentials.

📋 Requirements

• ✅ Oracle Client installed on your machine (version 19.3 or later)
• ✅ Active Directory account with database access
• ✅ Windows environment

🔍 Step 1: Locate Your Oracle Installation

First, find the Oracle client installation path:

1. Open PowerShell or Command Prompt
2. Run the following command:

echo %PATH%

1. Look for the first Oracle reference in the PATH, for example:

C:\Oracle\product\19.3.0\client_64\bin

This is your ORACLE_HOME path. The base directory (e.g., C:\Oracle\product\19.3.0\client_64) is what you'll need for configuration.

📁 Step 2: Create Kerberos Configuration Directory

Navigate to your Oracle installation's network configuration folder and create a Kerberos directory:

# Example path - adjust based on your ORACLE_HOME
cd C:\Oracle\product\19.3.0\client_64\network\admin
mkdir kerberos

The full path should be: C:\Oracle\product\19.3.0\client_64\network\admin\kerberos

📝 Step 3: Create krb5.conf File

Create a file named krb5.conf in the kerberos directory with the following content:

File location: C:\Oracle\product\19.3.0\client_64\network\admin\kerberos\krb5.conf

[libdefaults]
default_realm = CORP.SAIF.COM

[realms]
CORP.SAIF.COM = {
kdc = CORP.SAIF.COM:88
}

[domain_realm]
.CORP.SAIF.COM = CORP.SAIF.COM
CORP.SAIF.COM = CORP.SAIF.COM
.corp.saif.com = CORP.SAIF.COM
corp.saif.com = CORP.SAIF.COM

Configuration explanation:

• default_realm: The default Kerberos realm (SAIF corporate domain)
• kdc: The Kerberos Key Distribution Center server and port
• domain_realm: Maps domain names to Kerberos realms

⚙️ Step 4: Update sqlnet.ora File

Locate and update the sqlnet.ora file in your Oracle network\admin directory:

File location: C:\Oracle\product\19.3.0\client_64\network\admin\sqlnet.ora

Add or update the following configuration:

SQLNET.KERBEROS5_CC_NAME=MSLSA:
SQLNET.KERBEROS5_CONF=C:\Oracle\product\19.3.0\client_64\network\admin\kerberos\krb5.conf
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5)
SQLNET.FALLBACK_AUTHENTICATION=TRUE
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Important:

• Replace C:\Oracle\product\19.3.0\client_64 with your actual ORACLE_HOME path
• If the file already exists, update the SQLNET.KERBEROS5_CONF line and add the last two lines if they don't exist

Configuration explanation:

• SQLNET.KERBEROS5_CC_NAME: Points to the Microsoft Kerberos credential cache
• SQLNET.KERBEROS5_CONF: Path to your krb5.conf file
• SQLNET.KERBEROS5_CONF_MIT: Enables MIT Kerberos compatibility
• SQLNET.AUTHENTICATION_SERVICES: Specifies authentication methods (Kerberos5 and NTS)
• SQLNET.FALLBACK_AUTHENTICATION: Allows fallback to alternative authentication if Kerberos fails
• NAMES.DIRECTORY_PATH: Specifies name resolution methods

🎫 Step 5: Obtain Kerberos Ticket

Before connecting to the database, you need to obtain a Kerberos ticket using your Active Directory credentials:

okinit <ad_username>

Example:

okinit johdoe

You'll be prompted to enter your Active Directory password. After successful authentication, you'll receive a Kerberos ticket.

🔌 Step 6: Connect to Oracle Database

Once you have a valid Kerberos ticket, you can connect to the Oracle database without specifying credentials:

sqlplus /@<TNS_ALIAS>

Example:

sqlplus /@entdev1

The @ symbol followed by the TNS alias connects using Kerberos authentication without requiring username/password.

✅ Verify Configuration

To verify your Kerberos configuration is working:

1. Check your Kerberos ticket:

oklist

This shows your current Kerberos tickets and their expiration times.

1. Test database connection:

sqlplus /@entdev1

If successful, you should connect without entering credentials.

🔄 Ticket Management

Kerberos tickets expire after a certain period (typically 10 hours). When your ticket expires:

• Renew your ticket:

okinit <ad_username>

• Check ticket status:

oklist

• Destroy tickets (logout):

okdstry

🔧 Troubleshooting Kerberos

Error: "kerberos5_conf_mit=false only supported with -old option."

This error typically indicates a mismatch between your TNS_ADMIN environment variable and your Oracle installation path.

Solution:

1. Check your TNS_ADMIN environment variable:

echo %TNS_ADMIN%

1. Verify it matches your Oracle network\admin path:

The output should match your Oracle installation path, for example:

C:\Oracle\product\19.3.0\client_64\network\admin

1. If it doesn't match, update the TNS_ADMIN environment variable:

Option A: Set temporarily for current session:

set TNS_ADMIN=C:\Oracle\product\19.3.0\client_64\network\admin

Option B: Set permanently (System Environment Variables):

• Right-click This PC → Properties → Advanced system settings
• Click Environment Variables
• Under System variables or User variables, find TNS_ADMIN
• Click Edit (or New if it doesn't exist)
• Set the value to your Oracle network\admin path

• Click OK and restart your terminal/IDE

• Verify the change:

echo %TNS_ADMIN%

1. Try connecting again:

okinit <ad_username>
sqlplus /@entdev1

📚 Additional Resources

For more detailed information about Oracle Kerberos authentication, refer to the Oracle Database Security Documentation.

💡 Tips

• ✅ Run okinit at the start of your development session
• ✅ Set up a reminder to renew tickets before they expire
• ✅ Keep your sqlnet.ora and krb5.conf files backed up
• ✅ Ensure your Active Directory account has appropriate database access permissions
• ✅ Verify TNS_ADMIN environment variable matches your Oracle installation path

⚙️ Configuration

🏠 Local Development

For local development, the Oracle database setup uses a local Oracle database and Aspire service discovery for connection string management. Connection strings are automatically configured through Aspire, so you don't need to manually add them to configuration files.

📥 Importing Schema from Oracle Database

Entity Framework Core provides powerful tools to scaffold your existing Oracle database schema into C# classes. This is known as the "Database First" approach.

📦 Step 1: Add Required NuGet Packages

Before scaffolding, add the required packages to your Data project:

1. Right-click on your Data project in Visual Studio
2. Select Manage NuGet Packages
3. Search for and install:
4. Oracle.ManagedDataAccess.Kerberos
5. Microsoft.EntityFrameworkCore.Design

Or use the Package Manager Console:

Install-Package Oracle.ManagedDataAccess.Kerberos
Install-Package Microsoft.EntityFrameworkCore.Design

Note: Both packages are only needed for scaffolding and can be removed afterward.

🖥️ Step 2: Open Solution in Visual Studio

1. Navigate to the root directory of your repository
2. Open the solution file (.sln) in Visual Studio
3. Locate your Data project (e.g., [project-id].Data)

💻 Step 3: Open Developer PowerShell

Open the Developer PowerShell terminal in the correct directory:

1. In Solution Explorer, right-click on your Data project
2. Select Open in Terminal

This will open a Developer PowerShell window already positioned in your Data project folder.

⚙️ Step 4: Set PowerShell Variables

Set up the variables for the scaffolding command:

$schema = "[schema_for_the_tables_you_want_to_import]"
$applicationName = "[applicationName]"
$dbcontext = $applicationName + "Context.Custom"
$namespace = $applicationName + ".Data"

Example:

$schema = "PREMIUMAUDIT"
$applicationName = "PremiumAudit"
$dbcontext = $applicationName + "Context.Custom"  # Results in "PremiumAuditContext.Custom"
$namespace = $applicationName + ".Data"           # Results in "PremiumAudit.Data"

🚀 Step 5: Run Scaffold Command

Option A: Scaffold by Schema (Recommended)

Use this approach to import all tables from a specific schema:

dotnet ef dbcontext scaffold "User Id=/;Data Source=ORADEV08.CORP.SAIF.COM:1521/enttst1" Oracle.EntityFrameworkCore --context $dbcontext --namespace $namespace --no-onconfiguring --schema $schema

What this does:

• User Id=/: Uses Kerberos authentication (no username/password needed)
• Data Source=ORADEV08.CORP.SAIF.COM:1521/enttst1: Connection to the Oracle database
• You can use any Oracle database you have read access to (development, test, or production)
• Replace with your target database server and service name
• Example alternatives: ORAPRD01.CORP.SAIF.COM:1521/entprd1, entdev1, etc.
• --context $dbcontext: Names your DbContext class (e.g., PremiumAuditContext)
• --namespace $namespace: Sets the namespace (e.g., PremiumAudit.Data)
• --no-onconfiguring: Excludes the OnConfiguring method (connection strings managed by Aspire)
• --schema $schema: Imports all tables from the specified schema

Option B: Scaffold Specific Tables 🎯

If your schema contains more tables than you need, specify individual tables:

dotnet ef dbcontext scaffold "User Id=/;Data Source=ORADEV08.CORP.SAIF.COM:1521/enttst1" Oracle.EntityFrameworkCore --context $dbcontext --namespace $namespace --no-onconfiguring --table [schema].[table1] --table [schema].[table2]

💡 Example importing location_code table:

dotnet ef dbcontext scaffold "User Id=/;Data Source=ORADEV08.CORP.SAIF.COM:1521/enttst1" Oracle.EntityFrameworkCore --context $dbcontext --namespace $namespace --no-onconfiguring --table WCIS_DBA.LOCATION_CODE

📋 What this does:

• 📥 Imports only the LOCATION_CODE table from the WCIS_DBA schema
• 📄 Creates entity class: LocationCode.cs
• 🔗 Generates a DbContext with DbSet<LocationCode> property
• 🚫 Excludes all other tables in the schema

💡 Pro Tip: To import multiple tables, simply add more --table parameters. Use --force to overwrite existing files when re-scaffolding:

dotnet ef dbcontext scaffold "User Id=/;Data Source=ORADEV08.CORP.SAIF.COM:1521/enttst1" Oracle.EntityFrameworkCore --context $dbcontext --namespace $namespace --no-onconfiguring --table WCIS_DBA.LOCATION_CODE --force

✨ When to use this approach:

• ✅ Your schema has many tables but you only need a subset
• ✅ You want to minimize the number of generated entity classes
• ✅ You want to avoid importing system or audit tables
• ✅ You know exactly which tables your application needs

🔧 Command Parameters Explained

Parameter	Description
User Id=/	Uses Kerberos authentication instead of username/password
Data Source	Oracle server connection string (server:port/service_name)
Provider	Oracle.EntityFrameworkCore - The Oracle EF Core provider
--context	Name of the DbContext class (e.g., PremiumAuditContext)
--namespace	Namespace for generated classes (e.g., PremiumAudit.Data)
--no-onconfiguring	Excludes OnConfiguring method (connection managed externally by Aspire)
--schema	Schema to import (imports all tables from this schema)
--table	Specific table to import (format: [schema].[table], can be repeated for multiple tables)

📊 Choosing Between Schema and Table Import

Use --schema when:

• ✅ You need all or most tables from a schema
• ✅ The schema is dedicated to your application
• ✅ You want to ensure all related tables are imported

Use --table when:

• ✅ You only need specific tables from a larger schema
• ✅ The schema contains many tables you don't need
• ✅ You want fine-grained control over what gets imported
• ✅ You want to minimize the size of your data project

🧹 Step 6: Remove Scaffolding Packages (Optional)

After successfully scaffolding your database schema, you can remove the packages that were only needed for scaffolding:

1. Right-click on your Data project in Visual Studio
2. Select Manage NuGet Packages
3. Find and uninstall:
4. Oracle.ManagedDataAccess.Kerberos
5. Microsoft.EntityFrameworkCore.Design

Or use the Package Manager Console:

Uninstall-Package Oracle.ManagedDataAccess.Kerberos
Uninstall-Package Microsoft.EntityFrameworkCore.Design

Note: These packages are only required during the scaffolding process. Your application uses the standard Oracle Entity Framework provider for runtime database connections.

⚠️ Step 7: Update Decimal Types to Long

Important: Oracle's EF Core provider generates decimal types for Oracle NUMBER columns, but you'll typically want to use long (Int64) for better performance and compatibility.

After scaffolding, you'll need to:

1. Review generated entity classes and identify decimal properties that should be long
2. Update the data types from decimal to long for:
3. ✅ Primary keys (IDs)
4. ✅ Foreign keys
5. ✅ Integer values stored in Oracle NUMBER columns
6. Keep decimal for actual decimal/currency values that need precision

Why make this change?

• 🚀 Performance: long is more efficient than decimal for integer operations
• 🎯 Type safety: Prevents accidental decimal arithmetic on integer IDs
• 🔄 API compatibility: Most APIs expect integer IDs, not decimals
• 💾 Memory efficiency: long uses less memory than decimal

Note: You'll need to make these changes after each scaffold/re-scaffold operation.

📁 Project Structure After Scaffolding

After running the scaffold command, your Data project will contain:

• [ApplicationName]Context.Custom.cs: The generated DbContext file with all entity DbSets and configurations
• Entity model files: One C# class file for each table you imported (e.g., LocationCode.cs, Policy.cs)

These generated files work together with the base context file included in the SAIF template to provide your complete data access layer.

🔄 Updating Your Data Model

When your Oracle database schema changes, you'll need to re-scaffold to update your entity models.

🔄 Re-scaffolding Process

1. Make sure you have the latest database schema in your Oracle database
2. Run the scaffold command again with the --force flag to overwrite existing files
3. Review the changes to ensure they match your expectations
4. Test your application to ensure everything works correctly

⚠️ Important Notes on Re-scaffolding

• Custom code will be overwritten: Any manual changes you made to generated entity classes will be lost
• Use partial classes: To add custom logic to entities, use partial classes in separate files
• Source control: Always commit your changes before re-scaffolding so you can review differences

🎯 Extending Generated Models with Partial Classes

Create partial class files to add custom logic without modifying generated code:

// Employee.Custom.cs (create this file separately)
namespace YourApp.Data.Models
{
    public partial class Employee
    {
        // Add custom properties
        [NotMapped]
        public string FullName => $"{FirstName} {LastName}";

        // Add custom methods
        public bool IsActive()
        {
            // Your custom logic
            return HireDate.HasValue;
        }
    }
}

✨ Best Practices

🎯 Database First Workflow

1. Design your schema in Oracle using SQL scripts or database tools
2. Apply schema changes to your Oracle database
3. Scaffold the updated schema using EF Core tools
4. Review and test the generated models
5. Commit changes to source control

🔒 Security Best Practices

• ✅ Never commit connection strings with credentials to source control
• ✅ Use Azure Key Vault for storing production connection strings
• ✅ Use separate credentials for different environments
• ✅ Implement least privilege access - grant only necessary database permissions

📊 Performance Best Practices

• ✅ Use AsNoTracking() for read-only queries to improve performance
• ✅ Use projection (Select) to retrieve only needed columns
• ✅ Implement proper indexing in your Oracle database
• ✅ Use connection pooling (enabled by default with Oracle provider)
• ✅ Consider pagination for large result sets

🧪 Code Organization

• ✅ Keep generated models in a separate folder (e.g., Models/)
• ✅ Use partial classes for custom logic
• ✅ Separate concerns - keep business logic out of entity models

☁️ Azure Deployment Configuration

For deployment to Azure, you need to configure Oracle database settings in two places:

1. Settings Configuration (infra/api/settings.yml)

Update the settings.yml file with your Oracle database configuration:

- name: oracle-username
  value: username
- name: oracle-instance-type
  value: ENT

Configuration values:

• oracle-username: The username for connecting to your Oracle database (e.g., username, SYSTEM)
• oracle-instance-type: The Oracle instance type you selected during project creation (e.g., ENT, POL, CLM)

2. Password Configuration (Azure DevOps Library)

Oracle database passwords are stored securely in Azure DevOps Library as variables. Follow these steps:

1. Navigate to Azure DevOps:

2. Go to Pipelines → Library in the Team Project where your application repository is stored

3. Create or update a variable group:

4. Create a variable group with the naming pattern: <api>-<project_id>-<environment> (e.g., au-api-sys-premiumaudit)

5. Add a description like "Database credentials to Oracle ENTDB"

6. Add password variables for each environment:

7. Variable name pattern: [ENV]oracle-password (e.g., [PROD]oracle-password, [TEST]oracle-password)
8. Click the Lock icon to mask the password value
9. Paste your database password in the Value field

Example variable group setup:

Name	Value
[PROD]oracle-password	**** (locked)
[QA]oracle-password	**** (locked)
[TEST]oracle-password	**** (locked)
[UAT]oracle-password	**** (locked)

📝 Complete Configuration Example

Here's a complete example of Oracle configuration in settings.yml:

# infra/api/settings.yml

# Oracle Database Configuration
- name: oracle-username
  value: username
- name: oracle-instance-type
  value: ENT

# Other application settings...
- name: ApplicationName
  value: MyApp

Important Notes:

• ✅ The oracle-instance-type is typically set during project creation and rarely changes
• ✅ Only update oracle-username if you need to use a different database user
• ✅ Never commit passwords to settings.yml - always use Azure DevOps Library
• ✅ Password variables must be locked/masked in the Azure DevOps Library

For more detailed information on settings and secrets management, refer to the Settings and Secrets Management Guide.

🔍 Troubleshooting

❌ Common Issues and Solutions

Issue: "Could not find provider Oracle.EntityFrameworkCore"

Solution: Make sure you have the Oracle EF Core provider package installed:

dotnet add package Oracle.EntityFrameworkCore

Issue: "Unable to connect to database"

Solutions:

• ✅ Verify your connection string is correct
• ✅ Check that the Oracle database is running and accessible
• ✅ Ensure firewall rules allow connection to Oracle port (usually 1521)
• ✅ Verify your username and password are correct
• ✅ Check that the service name or SID is correct

Issue: "Tables not being scaffolded"

Solutions:

• ✅ Ensure your database user has SELECT permissions on the tables
• ✅ Check if tables exist in the correct schema
• ✅ Use --schema parameter if tables are in a non-default schema
• ✅ Verify table names are spelled correctly if using --table parameter

Issue: "Generated models have incorrect data types"

Solutions:

• ✅ Update to the latest version of Oracle.EntityFrameworkCore
• ✅ Review Oracle to .NET type mappings
• ✅ Manually adjust types after scaffolding (in partial classes)

Issue: "Re-scaffolding overwrites my custom code"

Solution: Always use partial classes for custom logic:

// Keep this file - it gets regenerated
// Employee.cs (generated)

// Create this file for custom code - never gets overwritten
// Employee.Custom.cs (your custom partial class)
public partial class Employee
{
    // Your custom code here
}

Issue: "Aspire unable to pull Oracle container image"

Problem: When using Aspire, the Oracle container may fail to start because the Docker image cannot be pulled automatically.

Solution: Manually pull the Oracle Docker image:

1. Check the Oracle container source in your Aspire project to identify the image name
2. Open Command Prompt or PowerShell
3. Manually pull the image:

docker pull <imagename>

Example:

# Oracle Free Database container image
docker pull container-registry.oracle.com/database/free:23.8.0.0

Additional troubleshooting steps:

• ✅ Ensure Docker Desktop is running
• ✅ Check your internet connection
• ✅ Verify you have sufficient disk space for the Oracle image
• ✅ If using Oracle Enterprise images, ensure you're authenticated with Oracle Container Registry
• ✅ Check Docker logs: docker logs <container_name>

📞 Getting Help

If you encounter issues not covered here:

1. Check the Entity Framework Core Documentation
2. Review Oracle EF Core Provider Documentation
3. Consult the SAIF Platform team
4. Check existing issues in your project's repository

🎓 Next Steps

After setting up Entity Framework with Oracle:

• 📖 Read about Settings and Secrets Management
• 🚀 Learn about Blue-Green Deployments
• 🧪 Implement unit tests for your data access layer
• 📊 Set up monitoring and logging with Application Insights
• 🔄 Configure CI/CD pipelines for database schema management

---

Happy Coding! 🎉