使用.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;
---------------------------点位表-------------------------------- 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;
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", 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; } }
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。
