使用.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,BULEVEL INTEGER DEFAULT 0,BUTYPE NUMBER(3,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;
    

---------------------------点位表--------------------------------
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,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,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,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;
    

　　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; }
          
    }  

///<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",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 ， 十字路口/丁字路口/圆形转盘/其它) 
        ///</summary> 
        [Column("SPOTTINGTYPE",TypeName = "VARCHAR2")]
        public string SpottingType { get; set; }

        ///<summary>
        ///逻辑删除标记(0 正常数据， 1 逻辑删除) 
        ///</summary> 
        [Column("VIRTUALDELETEFLAG")]
        public double? VirtualDeleteFlag { get; set; }

        ///<summary>
        ///是否停用(0 未停用， 1 停用)，默认为0 
        ///</summary> 
        [Column("DISABLED")]
        public bool? Disabled { get; set; }

    }

?　5.新建DBContext

    public class CommonDBContext : DbContext
    {
        //public CommonDBContext(DbContextOptions options) : base(options)
        //{
        //}

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var logger = new LoggerFactory();
            logger.AddConsole();
            optionsBuilder.UseLoggerFactory(logger);
            optionsBuilder.USEOracle("DATA SOURCE=127.0.0.1:1521/tjims;PASSWORD=test;PERSIST SECURITY INFO=True;USER ID=test");
            base.OnConfiguring(optionsBuilder);
        }

        public DbSet<Spotting> Spotting { get; set; }

        public DbSet<Department> Department { get; set; }
    }

 static void Main(string[] args)
        {
            using (CommonDBContext db = new CommonDBContext())
            {
                int total = db.Spotting.Count();
                Console.WriteLine("路口总行数：{0}",total);
                var fDisItem = db.Spotting.FirstOrDefault(e => e.Disabled == true);
                Console.WriteLine("第一条禁用路口：{0}",fDisItem.SpottingName);
                //分页查询演示
                var pageList = db.Spotting.Where(e => e.Disabled == true)
                    .OrderBy(e => e.SpottingName).Skip(10).Take(20).ToList();
                Console.WriteLine("分页查询禁用路口：{0}",pageList.Count);
                var list = db.Spotting.ToList();
                string[] arrSpottingNo = new string[] { "123","34" };
                db.Spotting.Where(e => arrSpottingNo.Contains(e.SpottingNo)).ToList();



                string minSpottingNo = db.Spotting.Min(e => e.SpottingNo);
                string maxSpottingNo = db.Spotting.Max(e => e.SpottingNo);
                string[] arrAreaCode = db.Spotting.Select(e => e.AreaCode).Distinct().ToArray();
                db.Spotting.Average(e => e.Longitude);
                var dt = DateTime.Now.AddDays(-100);
                //日期过滤
                db.Spotting.Where(e =>
                    e.Createdtime >= dt && e.Createdtime <= DateTime.Now && e.Disabled == true).ToList();


                var itemNew = new Spotting
                {
                    SpottingId = Guid.NewGuid().ToString("N"),SpottingName = "test",SpottingNo = "test",Creator = "admin",Createdtime = DateTime.Now,DepartmentId = Guid.NewGuid().ToString("N")
                };
                db.Entry(itemNew).State = EntityState.Added;
                Console.WriteLine("新增一条路口Id：{0} 数据",itemNew.SpottingId);

                var dItem = db.Spotting.Find(itemNew.SpottingId);
                db.Remove(dItem);
                Console.WriteLine("删除路口Id：{0} 数据",itemNew.SpottingId);

                //关联查询
                var x = (from p in db.Spotting
                         join q in db.Department
                         on p.DepartmentId equals q.DepartmentId
                         select new { p.SpottingName,p.SpottingId,p.DepartmentId,q.BuName }).OrderBy(e => e.SpottingName)
                         .Skip(10).Take(20).ToList();

                db.SaveChanges();
            }
            Console.Read();
        }

?

Github源码地址

https://github.com/CrazyJson/Citms.EntityFrameworkCore.Oracle，有兴趣的可以把源码下下来看看。

总结

? ? ? 如果项目中也想使用EF Core Oracle，可以安装包开始Coding。如果使用中遇到BUG，请在Git上回复，我将进行修复。后续等Oracle官方出了正式版后，可以直接进行替换。

项目代码参考EF Core?https://github.com/aspnet/EntityFrameworkCore/tree/dev/samples/OracleProvider。

如果，您认为阅读这篇博客让您有些收获，不妨点击一下右下角的【推荐】按钮。
如果，您希望更容易地发现我的新博客，不妨点击一下绿色通道的【关注我】。

因为，我的写作热情也离不开您的肯定支持。

感谢您的阅读，如果您对我的博客所讲述的内容有兴趣，请继续关注我的后续博客，我是焰尾迭 。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!