使用. 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 , 十字路口 / 丁字路口 / 圆形转盘 / 其它)
- ///</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 .
如果, 您认为阅读这篇博客让您有些收获, 不妨点击一下右下角的 [推荐] 按钮.
如果, 您希望更容易地发现我的新博客, 不妨点击一下绿色通道的 [关注我] .
如果, 想给予我更多的鼓励, 求打
因为, 我的写作热情也离不开您的肯定支持.
感谢您的阅读, 如果您对我的博客所讲述的内容有兴趣, 请继续关注我的后续博客, 我是焰尾迭 .
来源: https://www.cnblogs.com/yanweidie/p/9064609.html