随笔 - 43  文章 - 0  评论 - 1152  0

  使用.net core也有一段时间了,一直都没有Oracle官方的正式版驱动程序,更别说EF版本了。之前基于Oracle官方的.net core预览版本写了个Dapper的数据库操作实现,但是总感觉不太完美,有消息称Oracle官方的EF版本可能要到第三季度出了,还需要静静等待几个月的时间。

  既然有了Beta版本的驱动,在git上看到有人实现了MySQL非官方的EF版本。于是决定自己动手实现一套EF Oracle版本,方便项目使用。经测试后已能正常使用,已上传到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 , 十字路口/丁字路口/圆形转盘/其它) 
        ///</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; }

    }
View Code

  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; }
    }
  6.增删改代码演示
 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

posted on 2018-05-21 08:44 焰尾迭 阅读(...) 评论(...) 编辑 收藏