【四】EFCore代码运行时迁移@Asp.netcore Code First DDD学习笔记

最新推荐文章于 2022-12-31 17:24:26 发布

dotnet_Monkey

最新推荐文章于 2022-12-31 17:24:26 发布

阅读量1.8k

点赞数

分类专栏： Asp.netcore EfCore DDD

本文链接：https://blog.csdn.net/qq_38762313/article/details/102514047

版权

Asp.netcore 同时被 3 个专栏收录

23 篇文章 2 订阅

订阅专栏

EfCore

4 篇文章 1 订阅

订阅专栏

DDD

4 篇文章 0 订阅

订阅专栏

EfCore实现全自动化迁移数据库结构，非执行命令。达到运行时自动迁移，且数据库有数据结构的记录。

主题：Asp.netcore Code First +DDD学习笔记

在Efcore官网上说的运行时迁移是你执行完成Add-Migration后在代码运行时执行myDbContext.Database.Migrate();数据库的数据结构会被更改，但是这个并不是我们所需要。我不想执行Add-Migration我想实现全自动化的方式。我们要实现自动化数据结构迁移，并在迁移时，非数据错误的字段修改和调整不会删除数据。就和执行命令达到的效果是一样的。

寻找Efcore内的方法发现：

在Efcore官方里面有如下三个方法：

var modelDiffer = dbContext.GetInfrastructure().GetService<IMigrationsModelDiffer>();

modelDiffer.HasDifferences(lastModel, dbContext.Model)；//这个方法返回值是true或者false，这个可以比较老版本的model和当前版本的model是否出现更改。

var upOperations = modelDiffer.GetDifferences(lastModel, dbContext.Model);//这个方法返回的迁移的操作对象。

dbContext.GetInfrastructure() .GetRequiredService<IMigrationsSqlGenerator>().Generate(upOperations, dbContext.Model).ToList()；//这个方法是根据迁移对象和当前的model生成迁移sql脚本。

根据以上三个方法我们可以把迁移做成自动化，首先我们要知道是否出现版本修改，我们要先知道老版本的model是什么样子的，这时候我们需要在每次迁移完成后，把model存到数据库即可。

每次我们从数据库拿出来最后一次的model使用HasDifferences方法和现在的model做比较，出现需要修改的时候我们使用GetDifferences方法获取迁移对象，然后根据迁移对象使用Generate方法来生成sql脚本，最后用efcore执行sql脚本即可。

如何把model存到数据库，并在从数据库取出后能还原成model用来最比较呢。我第一个想到的是序列化，于是我进行了尝试，发现该dbContext.Model不允许进行序列化。后来看到一大佬说：每次迁移生成的快照实际上是动态生成的类，所以要用动态编辑机制CSharpCompilation和CSharpCompilationOptions。大佬的文章链接：https://blog.csdn.net/weixin_38687913/article/details/81122169，于是得出如下解决方案

一. 给domain安装nuget包

Microsoft.CodeAnalysis.Common 版本选择2.8

二.创建保存数据库版本的实体类

在Domain的Domains创建MigrationLogs文件夹，并在该文件夹里面创建MigrationLog类，具体代码如下：

using EfCoreRepository.EfModelAttributes;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Text;

namespace Domain.MigrationLogs
{
    [EfModel]
    public class MigrationLog
    {
        [Key]
        public int Id { get; set; }
        public string SnapshotDefine { get; set; }

        public DateTime MigrationTime { get; set; }
    }
}

三.修改efcontent如下

using Domain.MigrationLogs;
using EfCoreRepository;
using EfCoreRepository.EfModelAttributes;
using Microsoft.CodeAnalysis;
using Microsoft.CodeAnalysis.CSharp;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.EntityFrameworkCore.Design.Internal;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Design;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Microsoft.Extensions.DependencyInjection;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Threading.Tasks;


namespace Domain.EfCoreContent
{
    public class EfContent : DbContext
    {
        public DbContextOptions<EfContent> dbContextOptions { get; set; }
        public EfContent(DbContextOptions<EfContent> options) : base(options)
        {
            dbContextOptions = options;
        }

        /// <summary>
        /// 创建实体
        /// </summary>
        /// <param name="modelBuilder"></param>
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.AddEntityConfigurationsFromAssembly<EfModelAttribute>(Assembly.GetExecutingAssembly());
            base.OnModelCreating(modelBuilder);
        }

        /// <summary>
        /// 扩展查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public IQueryable<T> Where<T>(Expression<Func<T, bool>> predicate) where T : class
        {
            return base.Set<T>().Where(predicate);
        }
    }

    /// <summary>
    /// efcore扩展sql查询
    /// </summary>
    public static class EntityFrameworkCoreExtensions
    {
        /// <summary>
        /// 执行sql返回datatable
        /// </summary>
        /// <param name="efcontent"></param>
        /// <param name="sql"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataTable SqlQuery(this EfContent efcontent, string sql, params object[] commandParameters)
        {
            var dt = new DataTable();
            using (var connection = efcontent.Database.GetDbConnection())
            {
                using (var cmd = connection.CreateCommand())
                {
                    efcontent.Database.OpenConnection();
                    cmd.CommandText = sql;

                    if (commandParameters != null && commandParameters.Length > 0)
                        cmd.Parameters.AddRange(commandParameters);
                    using (var reader = cmd.ExecuteReader())
                    {
                        dt.Load(reader);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 执行多条sql
        /// </summary>
        /// <param name="efcontent"></param>
        /// <param name="sqlList"></param>
        public static int ExecuteListSqlCommand(this EfContent efcontent,List<string> sqlList)
        {
            int retunInt = 0;
            try
            {
                using (var trans = efcontent.Database.BeginTransaction())
                {
                    
                    sqlList.ForEach(cmd => retunInt+= efcontent.Database.ExecuteSqlCommand(cmd));
                    efcontent.Database.CommitTransaction();
                }
            }
            catch (DbException ex)
            {
                try
                {
                    efcontent.Database.RollbackTransaction();
                }
                catch (DbException)
                {
                    
                }
            }
            return retunInt;
        }

        /// <summary>
        /// 执行sql返回list
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="efcontent"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static List<T> SqlQuery<T>(this EfContent efcontent, string sql, params object[] parameters) where T : class, new()
        {
            var dt = SqlQuery(efcontent, sql, parameters);
            return dt.ToList<T>();
        }

        /// <summary>
        /// datatable转list
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> ToList<T>(this DataTable dt) where T : class, new()
        {
            var propertyInfos = typeof(T).GetProperties();
            var list = new List<T>();
            foreach (DataRow row in dt.Rows)
            {
                var t = new T();
                foreach (PropertyInfo p in propertyInfos)
                {
                    if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
                        p.SetValue(t, row[p.Name], null);
                }
                list.Add(t);
            }
            return list;
        }
    }

    /// <summary>
    /// 给efcontent扩展自动处理数据库版本的方法
    /// </summary>
    public static class EfCoreMigration
    {
        /// <summary>
        /// 给EfContent添加的更新数据库结构的方法
        /// </summary>
        public static void RunUpdateDataBaseEntity(this EfContent _dbContext)
        {
            // 创建一个DbContext，具体方法怎样都行
            IModel lastModel = null;
            try
            {
                // 读取迁移记录，把快照还原会model
                var lastMigration = _dbContext.Set<MigrationLog>()
                    .OrderByDescending(e => e.Id) 
                    .FirstOrDefault();
                lastModel = lastMigration == null ? null : (CreateModelSnapshot(lastMigration.SnapshotDefine)?.Model);
            }
            catch (DbException) { }
            var modelDiffer = _dbContext.Database.GetService<IMigrationsModelDiffer>();
            //判断是否有更改
            if (modelDiffer.HasDifferences(lastModel, _dbContext.Model))
            {
                // 用 IMigrationsModelDiffer 的 GetDifferences 方法获取迁移的操作对象
                var upOperations = modelDiffer.GetDifferences(lastModel, _dbContext.Model);

                //执行迁移
                Migrationing(upOperations, _dbContext);

                // 生成新的快照，存起来
                var snapshotCode = new DesignTimeServicesBuilder(typeof(EfContent).Assembly, Assembly.GetEntryAssembly(), new OperationReporter(new OperationReportHandler()), new string[0])
                    .Build((DbContext)_dbContext)
                    .GetService<IMigrationsCodeGenerator>()
                    .GenerateSnapshot("ApiHost.Migrations", typeof(EfContent), "EfContentModelSnapshot", _dbContext.Model);//modelSnapshotNamespace：给动态生成类添加nameplace（必须和当前代码所在的命名控件下或者一样）modelSnapshotName：动态生成类的名称
                _dbContext.Set<MigrationLog>().Add(new MigrationLog()
                {
                    SnapshotDefine = snapshotCode,
                    MigrationTime = DateTime.Now
                });
                _dbContext.SaveChanges();
            }
        }


        /// <summary>
        /// 迁移数据库结构
        /// </summary>
        /// <param name="upOperations"></param>
        /// <param name="_dbContext"></param>
        private static void Migrationing(IReadOnlyList<MigrationOperation> upOperations,EfContent _dbContext)
        {
            List<string> sqlChangeColumNameList = new List<string>();
            List<MigrationOperation> list = new List<MigrationOperation>();
            //执行迁移列名修改
            foreach (var upOperation in upOperations)
            {
                if (upOperation is RenameColumnOperation)
                {
                    
                    sqlChangeColumNameList.Add(RenameColumnOperationToSql(upOperation as RenameColumnOperation, _dbContext));
                }
                else
                {
                    list.Add(upOperation);
                }
            }
            int columChangeCount = _dbContext.ExecuteListSqlCommand(sqlChangeColumNameList); 
            //处理剩余迁移
            if (list.Count > 0)
            {
                //通过 IMigrationsSqlGenerator 将操作迁移操作对象生成迁移的sql脚本，并执行
                var sqlList = _dbContext.Database.GetService<IMigrationsSqlGenerator>()
                    .Generate(list, _dbContext.Model)
                    .Select(p=>p.CommandText).ToList();
               int changeCount= _dbContext.ExecuteListSqlCommand(sqlList);
            }
        }

        /// <summary>
        /// 把RenameColumnOperation该字段转换成sql
        /// </summary>
        /// <param name="renameColumnOperation"></param>
        /// <returns></returns>
        private static string RenameColumnOperationToSql(RenameColumnOperation renameColumnOperation, EfContent _dbContext)
        {
            string column_type = string.Empty;
            string sql = "select column_type from information_schema.columns where table_name='" + (renameColumnOperation as RenameColumnOperation).Table + "'  and column_name='" + (renameColumnOperation as RenameColumnOperation).Name + "'";
            var dataTable = _dbContext.SqlQuery(sql);
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                column_type = dataTable.Rows[0].ItemArray[0].ToString();
            }
            return "alter table " + (renameColumnOperation as RenameColumnOperation).Table + " change  column " + (renameColumnOperation as RenameColumnOperation).Name + " " + (renameColumnOperation as RenameColumnOperation).NewName + " " + column_type + " ;";
        }


        /// <summary>
        /// 把实体的string转成该对象
        /// </summary>
        /// <param name="codedefine"></param>
        /// <returns></returns>
        private static ModelSnapshot CreateModelSnapshot(string codedefine)
        {
            // 生成快照，需要存到数据库中供更新版本用
            var references = typeof(EfContent).Assembly
                .GetReferencedAssemblies()
                .Select(e => MetadataReference.CreateFromFile(Assembly.Load(e).Location))
                .Union(new MetadataReference[]
                {
                    MetadataReference.CreateFromFile(Assembly.Load("netstandard").Location),
                    MetadataReference.CreateFromFile(Assembly.Load("System.Runtime").Location),
                    MetadataReference.CreateFromFile(typeof(Object).Assembly.Location),
                    MetadataReference.CreateFromFile(typeof(EfContent).Assembly.Location)
                });
            var compilation = CSharpCompilation.Create("ApiHost.Migrations")//assemblyName：给动态生成类添加nameplace（必须和当前代码所在的命名控件下或者一样）和生成快照时要保持一直
                .WithOptions(new CSharpCompilationOptions(OutputKind.DynamicallyLinkedLibrary))
                .AddReferences(references)
                .AddSyntaxTrees(SyntaxFactory.ParseSyntaxTree(codedefine));
            using (var stream = new MemoryStream())
            {
                var compileResult = compilation.Emit(stream);
                return compileResult.Success
                    ? Assembly.Load(stream.GetBuffer()).CreateInstance("ApiHost.Migrations.EfContentModelSnapshot") as ModelSnapshot //typeName即生成的快照时设置的modelSnapshotNamespace+modelSnapshotName（nameplace+动态生成类的名称）
                    : null;
            }
        }
    }
}

四.在Api项目内调用该静态扩展方法，修改Startup的Configure如下：

  public void Configure(IApplicationBuilder app, IHostingEnvironment env, IServiceProvider serviceProvider)
        {
            //使用跨越配置
            app.UseCors();
            //使用SwaggerApi自动生成器
            app.UseSwagger();
            //使用SwaggerApi自动生成器的Ui界面
            app.UseSwaggerUI(option =>
            {
                option.SwaggerEndpoint("/swagger/v1/swagger.json", "My API V1");
            });


            using (var scope = serviceProvider.CreateScope())
            {
                var efContent = scope.ServiceProvider
                    .GetRequiredService<EfContent>();
                efContent.RunUpdateDataBaseEntity();
            }


            //if (env.IsDevelopment())
            //{
            //    app.UseDeveloperExceptionPage();
            //}
            //else
            //{
            //    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
            //    app.UseHsts();
            //}

            //app.UseHttpsRedirection();
            app.UseMvc();

        }

到这里，每次启动后数据库的结构会自动同步，根据测试和手动用命令执行的过程基本一致。执行后会发现数据库里面版本表里面会保存每次修改的model代码如下：

具体内容就是真实的代码，和手动用命令创建的这个类的代码一样

如下：

到这里。efcore全自动迁移全部完成。

github地址：https://github.com/houliren/Asp.netcore-Code-First-DDD

dotnet_Monkey

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
4
评论
【四】EFCore代码运行时迁移@Asp.netcore Code First DDD学习笔记

EfCore实现全自动化迁移数据库结构，非执行命令。达到运行时自动迁移，且数据库有数据结构的记录。主题：Asp.netcore Code First +DDD学习笔记目录：【一】 CodeFirst+DDD项目结构的构建【二】Asp.Netcore使用Panda.DynamicWebApi来进行Controller解耦【三】Asp.NetCore使用Efcor...
复制链接

扫一扫