Oracle .NET Core Beta驱动已出，自己动手写EF Core Oracle
程序员文章站
2022-07-04 21:34:39
使用.net core也有一段时间了，一直都没有Oracle官方的正式版驱动程序，更别说EF版本了。之前基于Oracle官方的.net core预览版本写了个Dapper的数据库操作实现，但是总感觉不太完美，有消息称Oracle官方的EF版本可能要到第三季度出了，还需要静静等待几个月的时间。 既然有 ......
使用.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。