C# Oracle Entity 測試範例

開發環境

1. .NET Framework 4.8.1
2. Oracle.ManagedDataAccess.EntityFramework

App.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8.1" />
  </startup>
  <entityFramework>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.122.23.1, Culture=neutral, PublicKeyToken=89b483f429c47342" />
    </providers>
  </entityFramework>
  <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.23.1, Culture=neutral, PublicKeyToken=89b483f429c47342" />
    </DbProviderFactories>
  </system.data>

  <connectionStrings>
    <add name="AppDbContext" providerName="Oracle.ManagedDataAccess.Client" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=Test)));User Id=Test;Password=Test;" />
  </connectionStrings>
</configuration>

Entities Models

AppDbContext.cs

using Oracle.ManagedDataAccess.EntityFramework;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;


namespace ConsoleApp3.Models
{
    public class AppDbContext : DbContext
    {
        public AppDbContext() : base("name=AppDbContext")
        {
        }

        public virtual DbSet<Department> Departments { get; set; }
        public virtual DbSet<Employee> Employees { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

            modelBuilder.Entity<Department>()
                .HasKey(d => d.DeptId)
                .ToTable("DEPARTMENTS", "SYSTEM");

            modelBuilder.Entity<Employee>()
                .HasKey(e => e.EmpId)
                .ToTable("EMPLOYEES", "SYSTEM");

            modelBuilder.Entity<Employee>()
                .HasOptional(e => e.Department)
                .WithMany(d => d.Employees)
                .HasForeignKey(e => e.DeptId);
        }
    }
}

Department.cs

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace ConsoleApp3.Models
{
    [Table("DEPARTMENTS", Schema = "SYSTEM")]
    public class Department
    {
        [Key]
        [Column("DEPT_ID")]
        public decimal DeptId { get; set; }

        [Column("DEPT_NAME")]
        [MaxLength(50)]
        public string DeptName { get; set; }

        public virtual ICollection<Employee> Employees { get; set; } = new HashSet<Employee>();
    }
}

Employee.cs

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace ConsoleApp3.Models
{
    [Table("EMPLOYEES", Schema = "SYSTEM")]
    public class Employee
    {
        [Key]
        [Column("EMP_ID")]
        public decimal EmpId { get; set; }

        [Column("EMP_NAME")]
        [MaxLength(50)]
        public string EmpName { get; set; }

        [Column("DEPT_ID")]
        public decimal? DeptId { get; set; }

        [Column("HIRE_DATE")]
        public DateTime? HireDate { get; set; }

        [Column("SALARY")]
        public decimal? Salary { get; set; }

        [ForeignKey("DeptId")]
        public virtual Department Department { get; set; }
    }
}

Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ConsoleApp3.Models;

namespace ConsoleApp3
{
    internal class Program
    {
        static void Main(string[] args)
        {
            using (var context = new AppDbContext())
            {
                var employees = context.Employees.Include("Department").ToList();

                foreach (var emp in employees)
                {
                    Console.WriteLine($"員工：{emp.EmpName}, 部門：{emp.Department?.DeptName}, 入職日：{emp.HireDate:yyyy-MM-dd}, 薪水：{emp.Salary}");
                }
            }
        }
    }
}