1、项目运行环境及EF版本信息
<Project Sdk="Microsoft.NET.Sdk"> <PropertyGroup> <TargetFramework>netstandard2.1</TargetFramework> <AssemblyName>XFC.EF.Oracle</AssemblyName> <GeneratePackageOnBuild>true</GeneratePackageOnBuild> <AssemblyVersion>3.0.1.0</AssemblyVersion> <FileVersion>3.0.1.0</FileVersion> <Version>3.0.1.0</Version> <Authors>Test</Authors> <Company>Test</Company> <PackageId>XFC.EF.Oracle</PackageId> <Product>XFC.EF.Oracle</Product> </PropertyGroup> <ItemGroup> <PackageReference Include="Microsoft.EntityFrameworkCore" Version="5.0.13" /> <PackageReference Include="Oracle.EntityFrameworkCore" Version="5.21.4" /> </ItemGroup> </Project>
2、OracleDbContext基类实现,在此代码中着重解决Oracle序列绑定问题。
using Microsoft.EntityFrameworkCore; using System; using System.ComponentModel.DataAnnotations; using System.Linq; namespace XFC.EF.Oracle { public abstract class OracleDbContext : DbContext { public OracleDbContext(DbContextOptions options) : base(options) { } public abstract string Schema { get; } public override int SaveChanges() { ChangeTracker.DetectChanges(); // Important! var addedEntities = this.ChangeTracker .Entries() .Where(x => x.State == EntityState.Added) .Select(x => x.Entity) .ToList();
//循环将要保存的Entity对象集合,识别Entity对象属性字段上了SequenceAttribute信息,若设置了SequenceAttribute信息则根据设置去取Sequence的值,并将取到的值赋给当前属性字段 foreach (var entity in addedEntities) { var property = (from p in entity.GetType().GetProperties() where p.GetCustomAttributes(typeof(SequenceAttribute), false).Any() select p).FirstOrDefault(); if (property == null) continue; if (property.PropertyType == typeof(int)) { var pValue = Convert.ToInt32(property.GetValue(entity)); if (pValue == 0) { var sequenceAttr = property.GetCustomAttributes(typeof(SequenceAttribute), false).First() as SequenceAttribute; var seqValue = this.GetSequenceValue(Schema.ToUpper(), sequenceAttr.Name); //根据设置获取一个新的Sequence的值 property.SetValue(entity, seqValue); } } } return base.SaveChanges(); } private int GetSequenceValue(string schema, string sequence) { var con = this.Database.GetDbConnection(); var cmd = con.CreateCommand(); if (con.State != System.Data.ConnectionState.Open) con.Open(); cmd.CommandText = $"select {schema}.{sequence}.NEXTVAL from dual"; return Convert.ToInt32(cmd.ExecuteScalar()); } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { //optionsBuilder.LogTo(Console.WriteLine); base.OnConfiguring(optionsBuilder); } protected override void OnModelCreating(ModelBuilder modelBuilder) { //判断当前数据库是Oracle 需要手动添加Schema(DBA提供的数据库账号名称) if (this.Database.IsOracle()) { modelBuilder.HasDefaultSchema(Schema.ToUpper()); } foreach (var entity in modelBuilder.Model.GetEntityTypes()) { foreach (var item in entity.GetProperties()) { if (item.ClrType.Name == "DateTime") item.AddAnnotation("Relational:ColumnType", "datetime"); if (item.ClrType.Name == "Decimal") item.AddAnnotation("Relational:ColumnType", "decimal(18,2)"); } } } } }
3、OracleDbContext子类实现
using Microsoft.EntityFrameworkCore; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using XFC.EF.Oracle; using XFC.Test.OracleEfTest.Domain; namespace XFC.Test.OracleEfTest { public class DemoDbContext : OracleDbContext { public DemoDbContext(DbContextOptions<DemoDbContext> options) : base(options) { } public override string Schema => TableMapConfig.Schema; public DbSet<RebateRule> RebateRules { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity<RebateRule>() .HasMany(r => r.Items) .WithOne(r => r.RebateRule) .HasForeignKey(r => r.RabateRuleID) .IsRequired(true); } } }
4、Entity定义
using Newtonsoft.Json; using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Linq; using System.Text; using System.Threading.Tasks; using XFC.EF.Oracle; using XFC.Test.OracleEfTest.Domain.Enums; namespace XFC.Test.OracleEfTest.Domain { /// <summary> /// 返点规则 /// </summary> [Table(TableMapConfig.RebateRule)] public class RebateRule { public RebateRule() { CreatedTime = DateTime.Now; LastModifiedTime = DateTime.Now; Status = RebateRuleStatus.待审核; Items = new List<RebateRuleItem>(); } /// <summary> /// ID /// </summary> [JsonProperty("id")] [Column("RULE_ID")] [Key] [Sequence(TableMapConfig.RebateRuleSeq)] public int ID { get; set; } /// <summary> /// 供应商Id /// </summary> [JsonProperty("supplierId")] [Column("SUPPLIER_ID")] public int SupplierID { get; set; } /// <summary> /// 名称 /// </summary> [JsonProperty("name")] [Column("RULE_NAME")] public string Name { get; set; } /// <summary> /// 价格模式 /// </summary> [JsonProperty("priceMode")] [Column("PRICE_MODE")] public PriceMode PriceMode { get; set; } /// <summary> /// 备注 /// </summary> [JsonProperty("remark")] [Column("REMARK")] public string Remark { get; set; } /// <summary> /// 返点周期设置 /// </summary> [JsonProperty("rebatePeriod")] public RebatePeriod RebatePeriod { get; set; } /// <summary> /// 返点规则明细集合 /// </summary> [JsonProperty("rebateRuleItems")] public IList<RebateRuleItem> Items { get; set; } /// <summary> /// 规则明细集合应用策略 /// </summary> [JsonProperty("ruleItemsApplyPolicy")] [Column("RULE_STRATEGY")] public RuleItemsApplyPolicy RuleItemsApplyPolicy { get; set; } /// <summary> /// 生效开始时间 /// </summary> [JsonProperty("workStartTime")] [Column("EFFECTIVE_START_DATE")] public DateTime WorkStartTime { get; set; } /// <summary> /// 生效结束时间 /// </summary> [JsonProperty("workEndTime")] [Column("EFFECTIVE_END_DATE")] public DateTime WorkEndTime { get; set; } /// <summary> /// 状态 /// </summary> [JsonProperty("status")] [Column("STATUS")] public RebateRuleStatus Status { get; set; } /// <summary> /// 审核说明 /// </summary> [JsonProperty("auditRemark")] [Column("REVIEW_DESC")] public string AuditRemark { get; set; } /// <summary> /// 创建人 /// </summary> [JsonProperty("creatorID")] [Column("CREATE_BY")] public int? CreatorID { get; set; } /// <summary> /// 创建时间 /// </summary> [JsonProperty("createdTime")] [Column("CREATE_DATE")] public DateTime CreatedTime { get; set; } /// <summary> /// 修改时间 /// </summary> [JsonProperty("lastModifiedTime")] [Column("LAST_UPDATE_DATE")] public DateTime LastModifiedTime { get; set; } } }
5、扩展
using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.DependencyInjection; using Oracle.EntityFrameworkCore.Infrastructure; using System; using System.Collections.Generic; using System.Text; namespace XFC.EF.Oracle { public static class ServiceExtensions { public static void AddOracleDbContext<TDbContext>(this IServiceCollection services, string connectionString, Action<OracleDbContextOptionsBuilder> oracleOptionsAction = null) where TDbContext : DbContext { if (oracleOptionsAction == null) { services.AddDbContext<TDbContext>(options => { options.UseOracle(connectionString, b => { b.UseOracleSQLCompatibility("11"); }); }); return; } services.AddDbContext<TDbContext>(options => { options.UseOracle(connectionString, b => oracleOptionsAction(b)); }); } } }