.NET Core Beta驱动已出,动手写EF Core Oracle

2018 年 5 月 22 日 DotNet

（点击上方蓝字，可快速关注我们）

来源：焰尾迭

cnblogs.com/yanweidie/p/9064609.html

使用.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();

}