使用.net core也有一段時間了，一直都沒有Oracle官方的正式版驅動程式，更別說EF版本了。之前基於Oracle官方的.net core預覽版本寫了個Dapper的資料庫操作實現，但是總感覺不太完美，有消息稱Oracle官方的EF版本可能要到第三季度出了，還需要靜靜等待幾個月的時間。既然有 ...

　　使用.net core也有一段時間了，一直都沒有Oracle官方的正式版驅動程式，更別說EF版本了。之前基於Oracle官方的.net core預覽版本寫了個Dapper的資料庫操作實現，但是總感覺不太完美，有消息稱Oracle官方的EF版本可能要到第三季度出了，還需要靜靜等待幾個月的時間。

　　既然有了Beta版本的驅動，在git上看到有人實現了MySQL非官方的EF版本。於是決定自己動手實現一套EF Oracle版本，方便項目使用。經測試後已能正常使用，已上傳到Nuget，github上和大家共用。

閱讀目錄

Nuget引用地址
項目使用演示
Github源碼地址
總結

回到頂部

Nuget引用地址

　　項目基於Oracle.ManagedDataAccess.Core官方Beta版本驅動，Nuget上搜索Citms.EntityFrameworkCore.Oracle即可找到包。

　　Oracle EF Core NuGet 地址：

　　https://www.nuget.org/packages/Citms.EntityFrameworkCore.Oracle

　　命令安裝：Install-Package Citms.EntityFrameworkCore.Oracle

回到頂部

項目使用演示

　　1.新建一個.NET Core控制台應用程式 NETCoreOracle

　　2.添加包引用

Install-Package Citms.EntityFrameworkCore.Oracle
Install-Package Microsoft.Extensions.Logging.Console

　　3.Oracle增刪查改

測試所用SQL腳本

---------------------------部門表--------------------------------
declare  tableExist number;
begin
    select count(1) into tableExist from user_tables where upper(table_name)=upper('SYS_DEPARTMENT') ;
    if tableExist = 0  then
        execute immediate '
            CREATE TABLE SYS_DEPARTMENT(
                DEPARTMENTID VARCHAR2(32) DEFAULT sys_guid()   NOT NULL  ,
                BUNAME NVARCHAR2(50)   ,
                BUFULLNAME NVARCHAR2(100)   ,
                BUCODE NVARCHAR2(50)   ,
                HIERARCHYCODE NVARCHAR2(500)   ,
                PARENTGUID VARCHAR2(32)   ,
                WEBSITE NVARCHAR2(50)   ,
                FAX NVARCHAR2(20)   ,
                COMPANYADDR NVARCHAR2(100)   ,
                CHARTER NVARCHAR2(50)   ,
                CORPORATIONDEPUTY NVARCHAR2(20)   ,
                CREATEDON DATE   ,
                MODIFIEDON DATE   ,
                CREATEDBY VARCHAR2(32)   ,
                COMMENTS NVARCHAR2(500)   ,
                MODIFIEDBY VARCHAR2(32)   ,
                ISENDCOMPANY NUMBER(1,0) DEFAULT 0   ,
                ISCOMPANY NUMBER(1,0) DEFAULT 0   ,
                BULEVEL INTEGER DEFAULT 0   ,
                BUTYPE NUMBER(3,0) DEFAULT 0   ,
                ORDERCODE NVARCHAR2(20)   ,
                ORDERHIERARCHYCODE NVARCHAR2(500)   ,
                AREACODE VARCHAR2(10)   ,
                SIMPLECODE NVARCHAR2(50)   
                    )
    ';

        execute immediate 'comment ON TABLE  SYS_DEPARTMENT IS ''組織機構表''';

        execute immediate 'comment on column SYS_DEPARTMENT.DEPARTMENTID is ''單位GUID''';
        execute immediate 'comment on column SYS_DEPARTMENT.BUNAME is ''單位簡稱''';
        execute immediate 'comment on column SYS_DEPARTMENT.BUFULLNAME is ''單位全稱''';
        execute immediate 'comment on column SYS_DEPARTMENT.BUCODE is ''單位代碼''';
        execute immediate 'comment on column SYS_DEPARTMENT.HIERARCHYCODE is ''層級代碼''';
        execute immediate 'comment on column SYS_DEPARTMENT.PARENTGUID is ''父級GUID''';
        execute immediate 'comment on column SYS_DEPARTMENT.WEBSITE is ''網址''';
        execute immediate 'comment on column SYS_DEPARTMENT.FAX is ''傳真''';
        execute immediate 'comment on column SYS_DEPARTMENT.COMPANYADDR is ''公司地址''';
        execute immediate 'comment on column SYS_DEPARTMENT.CHARTER is ''營業執照''';
        execute immediate 'comment on column SYS_DEPARTMENT.CORPORATIONDEPUTY is ''法人代表''';
        execute immediate 'comment on column SYS_DEPARTMENT.CREATEDON is ''創建時間''';
        execute immediate 'comment on column SYS_DEPARTMENT.MODIFIEDON is ''修改時間''';
        execute immediate 'comment on column SYS_DEPARTMENT.CREATEDBY is ''創建人''';
        execute immediate 'comment on column SYS_DEPARTMENT.COMMENTS is ''說明''';
        execute immediate 'comment on column SYS_DEPARTMENT.MODIFIEDBY is ''修改人''';
        execute immediate 'comment on column SYS_DEPARTMENT.ISENDCOMPANY is ''是否末級公司''';
        execute immediate 'comment on column SYS_DEPARTMENT.ISCOMPANY is ''是否公司''';
        execute immediate 'comment on column SYS_DEPARTMENT.BULEVEL is ''層級數''';
        execute immediate 'comment on column SYS_DEPARTMENT.BUTYPE is ''組織類型''';
        execute immediate 'comment on column SYS_DEPARTMENT.ORDERCODE is ''排序代碼''';
        execute immediate 'comment on column SYS_DEPARTMENT.ORDERHIERARCHYCODE is ''排序層級代碼''';
        execute immediate 'comment on column SYS_DEPARTMENT.AREACODE is ''單位所屬區域編碼''';
        execute immediate 'comment on column SYS_DEPARTMENT.SIMPLECODE is ''單位簡碼''';
        
    end if;
end;

View Code

---------------------------點位表--------------------------------
declare  tableExist number;
begin
    select count(1) into tableExist from user_tables where upper(table_name)=upper('COMMON_SPOTTING') ;
    if tableExist = 0  then
        execute immediate '
            CREATE TABLE COMMON_SPOTTING(
                SPOTTINGID VARCHAR2(50)  NOT NULL  ,
                SPOTTINGNO VARCHAR2(50)  NOT NULL  ,
                SPOTTINGNAME NVARCHAR2(100)  NOT NULL  ,
                UNIQUECODE VARCHAR2(50)   ,
                ROADID VARCHAR2(50)   ,
                LONGITUDE NUMBER(12,8)   ,
                LATITUDE NUMBER(12,8)   ,
                DEPARTMENTID VARCHAR2(50)  NOT NULL  ,
                SOURCEKIND VARCHAR2(50) DEFAULT ''local''   NOT NULL  ,
                CREATOR VARCHAR2(50)  NOT NULL  ,
                CREATEDTIME DATE DEFAULT sysdate   NOT NULL  ,
                MODIFIER VARCHAR2(50)   ,
                MODIFIEDTIME DATE   ,
                FLAGS VARCHAR2(10)   ,
                REMARK NVARCHAR2(500)   ,
                APPLICATIONNAME VARCHAR2(50) DEFAULT ''Citms.PIS''   NOT NULL  ,
                AREACODE VARCHAR2(50)   ,
                BOPOMOFO VARCHAR2(200)   ,
                SPOTTINGTYPE VARCHAR2(50)   ,
                VIRTUALDELETEFLAG INTEGER DEFAULT 0   ,
                DISABLED NUMBER(1,0) DEFAULT 0   ,
                PUNISHDEPARTMENT VARCHAR2(50)   ,
                DIVISIONCODE VARCHAR2(50)   ,
                APPROVESTATUS INTEGER DEFAULT 0   ,
                APPROVEUSERID VARCHAR2(50)   ,
                APPROVETIME DATE   ,
                APPROVEINFO NVARCHAR2(200)   ,
                MAXWEIGHT NUMBER(12,4)   ,
                MAXHEIGHT NUMBER(12,4)   ,
        PRIMARY KEY(SPOTTINGID) 
            )
    ';

        execute immediate 'comment ON TABLE  COMMON_SPOTTING IS ''道路點位表''';

        execute immediate 'comment on column COMMON_SPOTTING.SPOTTINGID is ''點位ID''';
        execute immediate 'comment on column COMMON_SPOTTING.SPOTTINGNO is ''點位編號(可以為廠家分配的點位編號)''';
        execute immediate 'comment on column COMMON_SPOTTING.SPOTTINGNAME is ''點位名稱''';
        execute immediate 'comment on column COMMON_SPOTTING.UNIQUECODE is ''上傳六合一標準代碼''';
        execute immediate 'comment on column COMMON_SPOTTING.ROADID is ''所在道路ID''';
        execute immediate 'comment on column COMMON_SPOTTING.LONGITUDE is ''經度坐標值''';
        execute immediate 'comment on column COMMON_SPOTTING.LATITUDE is ''緯度坐標值''';
        execute immediate 'comment on column COMMON_SPOTTING.DEPARTMENTID is ''所在管理部門''';
        execute immediate 'comment on column COMMON_SPOTTING.SOURCEKIND is ''來源類型''';
        execute immediate 'comment on column COMMON_SPOTTING.CREATOR is ''創建用戶ID''';
        execute immediate 'comment on column COMMON_SPOTTING.CREATEDTIME is ''創建時間''';
        execute immediate 'comment on column COMMON_SPOTTING.MODIFIER is ''修改人''';
        execute immediate 'comment on column COMMON_SPOTTING.MODIFIEDTIME is ''修改時間''';
        execute immediate 'comment on column COMMON_SPOTTING.FLAGS is ''保留標記''';
        execute immediate 'comment on column COMMON_SPOTTING.REMARK is ''備註''';
        execute immediate 'comment on column COMMON_SPOTTING.APPLICATIONNAME is ''應用名稱''';
        execute immediate 'comment on column COMMON_SPOTTING.AREACODE is ''所屬轄區代碼''';
        execute immediate 'comment on column COMMON_SPOTTING.BOPOMOFO is ''拼音簡稱''';
        execute immediate 'comment on column COMMON_SPOTTING.SPOTTINGTYPE is ''點位類型(字典表字典 ，Kind 為 1003 ， 十字路口/丁字路口/圓形轉盤/其它)''';
        execute immediate 'comment on column COMMON_SPOTTING.VIRTUALDELETEFLAG is ''邏輯刪除標記(0 正常數據， 1 邏輯刪除)''';
        execute immediate 'comment on column COMMON_SPOTTING.DISABLED is ''是否停用(0 未停用， 1 停用)，預設為0''';
        execute immediate 'comment on column COMMON_SPOTTING.PUNISHDEPARTMENT is ''處理單位''';
        execute immediate 'comment on column COMMON_SPOTTING.DIVISIONCODE is ''行政區劃代碼''';
        execute immediate 'comment on column COMMON_SPOTTING.APPROVESTATUS is ''審核狀態(0:未審核, 1:審核通過, 2:審核未通過), 預設為未審核狀態''';
        execute immediate 'comment on column COMMON_SPOTTING.APPROVEUSERID is ''審核用戶代碼''';
        execute immediate 'comment on column COMMON_SPOTTING.APPROVETIME is ''審核時間''';
        execute immediate 'comment on column COMMON_SPOTTING.APPROVEINFO is ''審核說明''';
        execute immediate 'comment on column COMMON_SPOTTING.MAXWEIGHT is ''最大限重(KG)''';
        execute immediate 'comment on column COMMON_SPOTTING.MAXHEIGHT is ''最大限高(m)''';
        
    end if;
end;

View Code

　　4.新建相關實體

///<summary>
    ///組織機構表
    ///</summary>
    [Table("SYS_DEPARTMENT")]
    public class  Department
    {
    ///<summary>
        ///單位GUID 
        ///</summary> 
        [Key,Column("DEPARTMENTID", TypeName = "VARCHAR2")]
        public string DepartmentId { get; set; }
          
        ///<summary>
        ///單位簡稱 
        ///</summary> 
        [Column("BUNAME")]
        public string BuName { get; set; }
          
        ///<summary>
        ///單位全稱 
        ///</summary> 
        [Column("BUFULLNAME")]
        public string BuFullName { get; set; }
          
        ///<summary>
        ///單位代碼 
        ///</summary> 
        [Column("BUCODE")]
        public string BuCode { get; set; }
          
        ///<summary>
        ///層級代碼 
        ///</summary> 
        [Column("HIERARCHYCODE",TypeName = "NVARCHAR2")]
        public string HierarchyCode { get; set; }
          
        ///<summary>
        ///父級GUID 
        ///</summary> 
        [Column("PARENTGUID", TypeName = "VARCHAR2")]
        public string ParentGuid { get; set; }
          
        ///<summary>
        ///網址 
        ///</summary> 
        [Column("WEBSITE")]
        public string WebSite { get; set; }
          
        ///<summary>
        ///傳真 
        ///</summary> 
        [Column("FAX")]
        public string Fax { get; set; }
          
        ///<summary>
        ///公司地址 
        ///</summary> 
        [Column("COMPANYADDR")]
        public string CompanyAddr { get; set; }
          
        ///<summary>
        ///營業執照 
        ///</summary> 
        [Column("CHARTER")]
        public string Charter { get; set; }
          
        ///<summary>
        ///法人代表 
        ///</summary> 
        [Column("CORPORATIONDEPUTY")]
        public string CorporationDeputy { get; set; }
          
        ///<summary>
        ///創建時間 
        ///</summary> 
        [Column("CREATEDON", TypeName = "DATE")]
        public DateTime?  CreatedOn { get; set; }
          
        ///<summary>
        ///修改時間 
        ///</summary> 
        [Column("MODIFIEDON", TypeName = "DATE")]
        public DateTime?  ModifiedOn { get; set; }
          
        ///<summary>
        ///創建人 
        ///</summary> 
        [Column("CREATEDBY", TypeName = "VARCHAR2")]
        public string CreatedBy { get; set; }
          
        ///<summary>
        ///說明 
        ///</summary> 
        [Column("COMMENTS")]
        public string Comments { get; set; }
          
        ///<summary>
        ///修改人 
        ///</summary> 
        [Column("MODIFIEDBY", TypeName = "VARCHAR2")]
        public string ModifiedBy { get; set; }
          
        ///<summary>
        ///是否末級公司 
        ///</summary> 
        [Column("ISENDCOMPANY")]
        public bool?  IsEndCompany { get; set; }
          
        ///<summary>
        ///是否公司 
        ///</summary> 
        [Column("ISCOMPANY")]
        public bool?  IsCompany { get; set; }
          
        ///<summary>
        ///層級數 
        ///</summary> 
        [Column("BULEVEL")]
        public  double?  BuLevel { get; set; }
          
        ///<summary>
        ///組織類型 
        ///</summary> 
        [Column("BUTYPE")]
        public  double?  BuType { get; set; }
          
        ///<summary>
        ///排序代碼 
        ///</summary> 
        [Column("ORDERCODE")]
        public string OrderCode { get; set; }
          
        ///<summary>
        ///排序層級代碼 
        ///</summary> 
        [Column("ORDERHIERARCHYCODE")]
        public string OrderHierarchyCode { get; set; }
          
        ///<summary>
        ///單位所屬區域編碼 
        ///</summary> 
        [Column("AREACODE", TypeName = "VARCHAR2")]
        public string AreaCode { get; set; }
          
    }

View Code

///<summary>
    ///道路點位表 Spottings 有"s"後輟，與現有的Spotting區別開來
    ///</summary>
    [Table("COMMON_SPOTTING")]
    public class Spotting
    {
        ///<summary>
        ///點位ID 
        ///</summary> 
        [Key, Column("SPOTTINGID", TypeName = "VARCHAR2")]
        public string SpottingId { get; set; }

        ///<summary>
        ///點位編號(可以為廠家分配的點位編號) 
        ///</summary> 
        [Column("SPOTTINGNO", TypeName = "VARCHAR2"),Required]
        public string SpottingNo { get; set; }

        ///<summary>
        ///點位名稱 
        ///</summary> 
        [Column("SPOTTINGNAME")]
        [Required]
        public string SpottingName { get; set; }

        ///<summary>
        ///上傳六合一標準代碼 
        ///</summary> 
        [Column("UNIQUECODE", TypeName = "VARCHAR2")]
        public string UniqueCode { get; set; }

        ///<summary>
        ///所在道路ID 
        ///</summary> 
        [Column("ROADID", TypeName = "VARCHAR2")]
        public string RoadId { get; set; }

        ///<summary>
        ///經度坐標值 
        ///</summary> 
        [Column("LONGITUDE")]
        public double? Longitude { get; set; }

        ///<summary>
        ///緯度坐標值 
        ///</summary> 
        [Column("LATITUDE")]
        public double? Latitude { get; set; }

        ///<summary>
        ///所在管理部門 
        ///</summary> 
        [Column("DEPARTMENTID", TypeName = "VARCHAR2")]
        [Required]
        public string DepartmentId { get; set; }

        ///<summary>
        ///來源類型 
        ///</summary> 
        [Column("SOURCEKIND", TypeName = "VARCHAR2"), Required]
        public string SourceKind { get; set; }

        ///<summary>
        ///創建用戶ID 
        ///</summary> 
        [Column("CREATOR", TypeName = "VARCHAR2")]
        public string Creator { get; set; }

        ///<summary>
        ///創建時間 
        ///</summary> 
        [Column("CREATEDTIME", TypeName = "DATE")]
        public DateTime? Createdtime { get; set; }

        ///<summary>
        ///修改人 
        ///</summary> 
        [Column("MODIFIER", TypeName = "VARCHAR2")]
        public string Modifier { get; set; }

        ///<summary>
        ///修改時間 
        ///</summary> 
        [Column("MODIFIEDTIME", TypeName = "DATE")]
        public DateTime? ModifiedTime { get; set; }

        ///<summary>
        ///保留標記 
        ///</summary> 
        [Column("FLAGS", TypeName = "VARCHAR2")]
        public string Flags { get; set; }

        ///<summary>
        ///備註 
        ///</summary> 
        [Column("REMARK")]
        public string Remark { get; set; }

        ///<summary>
        ///應用名稱 
        ///</summary> 
        [Column("APPLICATIONNAME", TypeName = "VARCHAR2")]
        public string ApplicationName { get; set; }

        ///<summary>
        ///所在地區編號（行政區劃代碼） 
        ///</summary> 
        [Column("AREACODE", TypeName = "VARCHAR2")]
        public string AreaCode { get; set; }

        ///<summary>
        ///拼音簡稱 
        ///</summary> 
        [Column("BOPOMOFO", TypeName = "VARCHAR2")]
        public string Bopomofo { get; set; }

        ///<summary>
        ///點位類型(字典表字典 ，Kind 為 1003 ， 十字路口/丁字路口/圓形轉盤/其它)

Oracle .NET Core Beta驅動已出，自己動手寫EF Core Oracle

Nuget引用地址

項目使用演示