DW5S/DW5S.Repostory/EFContext/OracleContext.cs

using DW5S.Entity;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Conventions;
using Microsoft.Extensions.Logging;
using System.Configuration;

namespace DW5S.Repostory
{
    public class OracleContext : DbContext
    {
        public OracleContext()
        {
            //发布为单文件后找不到Microsoft.EntityFrameworkCore.Relational.dll,需要将这个dll拷贝到AddIns中
        }
        public async Task ExecuteSqlAsync(string sql)
        {
            await this.Database.ExecuteSqlRawAsync(sql);
        }

        /// <summary>
        /// 执行SQL查询(查询最大ID需要给int?等可空类型,否则会出错)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<List<T>> SqlQuery<T>(string sql)
        {
            var res = await this.Database.SqlQueryRaw<T>(sql).ToListAsync();
            return res;
        }
        /// <summary>
        /// 更新所有表的序列值为对应表的最大ID(更新后需要重新编译触发器)
        /// </summary>
        public void UpdateSeq()
        {
            List<string> tables = new List<string>();
            var props = typeof(OracleContext).GetProperties().Where(p => p.PropertyType.Name.StartsWith("DbSet")).ToList();
            foreach (var item in props)
            {
                var tb = item.PropertyType.GetGenericArguments().First().Name.ToUpper();
                tables.Add(tb);
            }
            foreach (var tb in tables)
            {
                try
                {
                    int? idMax = this.SqlQuery<int?>($"select max(id) from {tb}").Result.FirstOrDefault();
                    if (idMax == null)
                        idMax = 1;
                    string sql = $"DROP SEQUENCE SQ_{tb}";
                    this.ExecuteSqlAsync(sql).Wait();
                    sql = $"CREATE SEQUENCE SQ_{tb} INCREMENT BY 1 START WITH {idMax}";
                    this.ExecuteSqlAsync(sql).Wait();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }

        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var conStr = ConfigurationManager.ConnectionStrings["OracleCon"].ToString();
            Console.WriteLine($"conStr={conStr}");
            optionsBuilder.UseOracle(conStr,
                options =>
                {
                    //options.MigrationsAssembly("Ips.Sps.Repostory");
                    //options.UseOracleSQLCompatibility("11");//11=Oracle11g,12=Oracle12c,EFCore8不支持老版数据库了

                    //使用全局拆分查询,将带集合导航的查询拆分为多条SQL执行，避免结果发生笛卡尔爆炸
                    options.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);

                    //true:生成数据库null语意,如 select * from Products where Name is null;
                    //false:生成c# null语意,如 select * from Products where Name = null;
                    //options.UseRelationalNulls(true);
                })
                //.UseSnakeCaseNamingConvention()//FullName->full_name
                //.UseLowerCaseNamingConvention()//FullName->fullname
                //.UseUpperSnakeCaseNamingConvention()// FullName->FULL_NAME
                .UseUpperCaseNamingConvention();//FullName->FULLNAME



            //sql语句输出到日志文件
            var loggerFactory = new LoggerFactory();
            loggerFactory.AddProvider(new SqlLoggerProvider());
            optionsBuilder.UseLoggerFactory(loggerFactory);

            base.OnConfiguring(optionsBuilder);
        }
        protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
        {
            //EFCore默认表名会带上复数形式,通过这句话删除复数约定
            configurationBuilder.Conventions.Remove(typeof(TableNameFromDbSetConvention));
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            //在Oracle11g中列名、索引、字段、表名、序列、触发器等名称长度均不能超过30个字符
            //自动生成的超出30个字符的需要在下面自定义
            modelBuilder.Entity<CgRes>().HasOne(p => p.StationRes).WithMany()
                .HasConstraintName("FK_CGRES_STATIONRESID");
            modelBuilder.Entity<SampleInfo>().HasOne(p => p.SigInfo).WithOne()
                .HasConstraintName("FK_SAMPLEINFO_SIGINFOID");
            modelBuilder.Entity<PosRes>().HasOne(p => p.StationRes).WithOne()
                .HasConstraintName("FK_POSRES_STATIONRESID");
            modelBuilder.Entity<PosRes>().HasOne(p => p.TargetInfo).WithOne()
                .HasConstraintName("FK_POSRES_TARGETINFOID");
            modelBuilder.Entity<RefTaskFreq>().HasOne(p => p.SampleInfo).WithOne()
                .HasConstraintName("FK_REFTASKFREQ_SAMPLEINFOID");
            modelBuilder.Entity<RefTaskFreq>().HasOne(p => p.SatInfo).WithOne()
                .HasConstraintName("FK_REFTASKFREQ_SATINFOID");

            modelBuilder.Entity<LogRes>().HasIndex(p => new { p.Module, p.LogTime, p.LogType })
                .HasDatabaseName("IX_LOGRES_MODULE_TIME_TYPE");
            modelBuilder.Entity<RefCgRes>().HasIndex(p => new { p.FileTime, p.FrequpHz, p.YbSnr })
                .HasDatabaseName("IX_REFCGRES_TIME_FREQ_SNR");
            modelBuilder.Entity<CgRes>().HasIndex(p => new { p.TaskID, p.SigTime, p.FrequpHz })
                .HasDatabaseName("IX_CGRES_TASKID_TIME_FREQ");
            modelBuilder.Entity<PosRes>().HasIndex(p => new { p.TaskID, p.SigTime, p.FrequpHz })
                .HasDatabaseName("IX_POSRES_TASKID_TIME_FREQ");



        }
        public DbSet<XlInfo> XlInfos { set; get; }

        public DbSet<TaskTemplate> TaskTemplates { set; get; }

        public DbSet<TaskInfo> TaskInfos { set; get; }

        public DbSet<TaskSig> TaskSigs { set; get; }

        public DbSet<TxInfo> TxInfos { get; set; }

        public DbSet<AdCard> AdCards { get; set; }

        public DbSet<AdChannel> AdChannels { get; set; }

        public DbSet<SatInfo> SatInfos { get; set; }

        public DbSet<FixedStation> FixedStation { get; set; }

        public DbSet<SigInfo> SigInfos { get; set; }

        public DbSet<SigDelay> SigDelays { get; set; }

        public DbSet<TargetInfo> TargetInfos { get; set; }

        public DbSet<SysSetings> SysSetings { get; set; }

        public DbSet<TaskRunnningInfo> TaskRunnningInfos { get; set; }

        public DbSet<SampleInfo> SampleInfos { get; set; }

        public DbSet<RefTaskFreq> RefTaskFreqs { get; set; }

        /*以下是Range分区表的DbSet*/

        public DbSet<AdRes> AdRes { get; set; }

        public DbSet<LogRes> LogRes { set; get; }
        public DbSet<StationRes> StationRes { get; set; }
        public DbSet<CxRes> CxRes { get; set; }
        public DbSet<CgRes> CgRes { get; set; }

        public DbSet<RefCgRes> RefCgRes { get; set; }
        public DbSet<CgXgfRes> CgXgfRes { get; set; }
        public DbSet<PosRes> PosRes { get; set; }
        public DbSet<CheckRes> CheckRes { get; set; }
    }
}