EFCore高级Saas系统下单DbContext如何支持不同数据库的迁移

随着系统的不断开发和迭代默认的efcore功能十分强大，但是随着Saas系统的引进efcore基于表字段的多租户模式已经非常完美了，但是基于数据库的多租户也是可以用的，但是也存在缺点，缺点就是没有办法支持不同数据库，migration support multi database provider with single dbcontext，本人不才，查询了一下，官方文档只说明了dbcontext的迁移如何实现多数据源，但是缺不是单个dbcontext,这个就让人很头疼。所以秉着尝试一下的原则进行了这篇博客的编写,因为本人只有mmsql和mysql所以这次就用这两个数据库来做测试

本人开发了一款efcore的分表分库读写分离组件

https://github.com/dotnetcore/sharding-core

希望有喜欢的小伙伴给我点点star谢谢

那么废话不多说我们马上开始migration support multi database provider with single dbcontext

1.按装依赖

2.新建一个User类

[Table(nameof(User))]
public class User
{
    public string UserId { get; set; }
    public string UserName { get; set; }
}

3.创建DbContext

public class MyDbContext:DbContext
{
    public DbSet<User> Users { get; set; }
    public MyDbContext(DbContextOptions<MyDbContext> options):base(options)
    {
        
    }

4.StartUp配置

var provider = builder.Configuration.GetValue("Provider", "UnKnown");

//Add-Migration InitialCreate -Context MyDbContext -OutputDir Migrations\SqlServer -Args "--provider SqlServer"
//Add-Migration InitialCreate -Context MyDbContext -OutputDir Migrations\MySql -Args "--provider MySql"
builder.Services.AddDbContext<MyDbContext>(options =>
{
    _ = provider switch
    {
        "MySql" => options.UseMySql("server=127.0.0.1;port=3306;database=DBMultiDataBase;userid=root;password=L6yBtV6qNENrwBy7;", new MySqlServerVersion(new Version())),
        "SqlServer" => options.UseSqlServer("Data Source=localhost;Initial Catalog=DBMultiDataBase;Integrated Security=True;"),
        _ => throw new Exception($"Unsupported provider: {provider}")
    };
});

迁移区分数据库

新建一个迁移命名空间提供者

    public interface IMigrationNamespace
    {
        string GetNamespace();
    }

mysql和sqlserver的实现分别是项目名称迁移文件夹

    public class MySqlMigrationNamespace:IMigrationNamespace
    {
        public string GetNamespace()
        {
            return "EFCoreMigrateMultiDatabase.Migrations.MySql";
        }
    }

    public class SqlServerMigrationNamespace:IMigrationNamespace
    {
        public string GetNamespace()
        {
            return "EFCoreMigrateMultiDatabase.Migrations.SqlServer";
        }
    }

efcore扩展

添加efcore扩展

    public class MigrationNamespaceExtension : IDbContextOptionsExtension
    {
        public IMigrationNamespace MigrationNamespace { get; }

        public MigrationNamespaceExtension(IMigrationNamespace migrationNamespace)
        {
            MigrationNamespace = migrationNamespace;
        }
        public void ApplyServices(IServiceCollection services)
        {
            services.AddSingleton<IMigrationNamespace>(sp => MigrationNamespace);
        }

        public void Validate(IDbContextOptions options)
        {
        }


        public DbContextOptionsExtensionInfo Info => new MigrationNamespaceExtensionInfo(this);

        private class MigrationNamespaceExtensionInfo : DbContextOptionsExtensionInfo
        {
            private readonly MigrationNamespaceExtension _migrationNamespaceExtension;
            public MigrationNamespaceExtensionInfo(IDbContextOptionsExtension extension) : base(extension)
            {
                _migrationNamespaceExtension = (MigrationNamespaceExtension)extension;
            }

            public override int GetServiceProviderHashCode() => _migrationNamespaceExtension.MigrationNamespace.GetNamespace().GetHashCode();

            public override bool ShouldUseSameServiceProvider(DbContextOptionsExtensionInfo other) => true;

            public override void PopulateDebugInfo(IDictionary<string, string> debugInfo)
            {
            }

            public override bool IsDatabaseProvider => false;
            public override string LogFragment => "MigrationNamespaceExtension";
        }
    }

重写MigrationsAssembly支持多数据库

    public class EFCoreMultiDatabaseMigrationsAssembly: IMigrationsAssembly
    {
        public  string MigrationNamespace { get; }
        private readonly IMigrationsIdGenerator _idGenerator;
        private readonly IDiagnosticsLogger<DbLoggerCategory.Migrations> _logger;
        private IReadOnlyDictionary<string, TypeInfo>? _migrations;
        private ModelSnapshot? _modelSnapshot;
        private readonly Type _contextType;

        /// <summary>
        ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
        ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
        ///     any release. You should only use it directly in your code with extreme caution and knowing that
        ///     doing so can result in application failures when updating to a new Entity Framework Core release.
        /// </summary>
        public EFCoreMultiDatabaseMigrationsAssembly(
            IMigrationNamespace migrationNamespace,
            ICurrentDbContext currentContext,
            IDbContextOptions options,
            IMigrationsIdGenerator idGenerator,
            IDiagnosticsLogger<DbLoggerCategory.Migrations> logger)
        {

            _contextType = currentContext.Context.GetType();

            var assemblyName = RelationalOptionsExtension.Extract(options)?.MigrationsAssembly;
            Assembly = assemblyName == null
                ? _contextType.Assembly
                : Assembly.Load(new AssemblyName(assemblyName));

            MigrationNamespace = migrationNamespace.GetNamespace();
            _idGenerator = idGenerator;
            _logger = logger;
        }

        /// <summary>
        ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
        ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
        ///     any release. You should only use it directly in your code with extreme caution and knowing that
        ///     doing so can result in application failures when updating to a new Entity Framework Core release.
        /// </summary>
        public virtual IReadOnlyDictionary<string, TypeInfo> Migrations
        {
            get
            {
                IReadOnlyDictionary<string, TypeInfo> Create()
                {
                    var result = new SortedList<string, TypeInfo>();
                    var items
                        = from t in Assembly.GetConstructibleTypes()
                          where t.IsSubclassOf(typeof(Migration))&& print(t)
                                && t.Namespace.Equals(MigrationNamespace)
                              && t.GetCustomAttribute<DbContextAttribute>()?.ContextType == _contextType
                          let id = t.GetCustomAttribute<MigrationAttribute>()?.Id
                          orderby id
                          select (id, t);
                    Console.WriteLine("Migrations:" + items.Count());
                    foreach (var (id, t) in items)
                    {
                        if (id == null)
                        {
                            _logger.MigrationAttributeMissingWarning(t);

                            continue;
                        }

                        result.Add(id, t);
                    }

                    return result;
                }

                return _migrations ??= Create();
            }
        }

        private bool print(TypeInfo t)
        {
            Console.WriteLine(MigrationNamespace);
            Console.WriteLine(t.Namespace);
            return true;
        }

        /// <summary>
        ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
        ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
        ///     any release. You should only use it directly in your code with extreme caution and knowing that
        ///     doing so can result in application failures when updating to a new Entity Framework Core release.
        /// </summary>
        public virtual ModelSnapshot? ModelSnapshot
            => GetMod();

        private ModelSnapshot GetMod()
        {
            Console.WriteLine("_modelSnapshot:"+ _modelSnapshot);
            if (_modelSnapshot == null)
            {
                Console.WriteLine("_modelSnapshot:null");
                _modelSnapshot = (from t in Assembly.GetConstructibleTypes()
                        where t.IsSubclassOf(typeof(ModelSnapshot)) && print(t)
                                                                    && MigrationNamespace.Equals(t?.Namespace)
                                                                    && t.GetCustomAttribute<DbContextAttribute>()?.ContextType == _contextType
                        select (ModelSnapshot)Activator.CreateInstance(t.AsType())!)
                    .FirstOrDefault();

                Console.WriteLine("_modelSnapshot:" + _modelSnapshot);
            }
            return _modelSnapshot;
        }

        /// <summary>
        ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
        ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
        ///     any release. You should only use it directly in your code with extreme caution and knowing that
        ///     doing so can result in application failures when updating to a new Entity Framework Core release.
        /// </summary>
        public virtual Assembly Assembly { get; }

        /// <summary>
        ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
        ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
        ///     any release. You should only use it directly in your code with extreme caution and knowing that
        ///     doing so can result in application failures when updating to a new Entity Framework Core release.
        /// </summary>
        public virtual string? FindMigrationId(string nameOrId)
            => Migrations.Keys
                .Where(
                    _idGenerator.IsValidId(nameOrId)
                        // ReSharper disable once ImplicitlyCapturedClosure
                        ? id => string.Equals(id, nameOrId, StringComparison.OrdinalIgnoreCase)
                        : id => string.Equals(_idGenerator.GetName(id), nameOrId, StringComparison.OrdinalIgnoreCase))
                .FirstOrDefault();

        /// <summary>
        ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
        ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
        ///     any release. You should only use it directly in your code with extreme caution and knowing that
        ///     doing so can result in application failures when updating to a new Entity Framework Core release.
        /// </summary>
        public virtual Migration CreateMigration(TypeInfo migrationClass, string activeProvider)
        {
            Console.WriteLine(migrationClass.FullName);

            var migration = (Migration)Activator.CreateInstance(migrationClass.AsType())!;
            migration.ActiveProvider = activeProvider;

            return migration;
        }
    }

编写startup

参考 https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/providers?tabs=vs

//Add-Migration InitialCreate -Context MyDbContext -OutputDir Migrations\SqlServer -Args "--provider SqlServer"
//Add-Migration InitialCreate -Context MyDbContext -OutputDir Migrations\MySql -Args "--provider MySql"
//update-database -Args "--provider MySql"
//update-database -Args "--provider SqlServer"
builder.Services.AddDbContext<MyDbContext>(options =>
{
    options.ReplaceService<IMigrationsAssembly, EFCoreMultiDatabaseMigrationsAssembly>();
    _ = provider switch
    {
        "MySql" => options.UseMySql("server=127.0.0.1;port=3306;database=DBMultiDataBase;userid=root;password=L6yBtV6qNENrwBy7;", new MySqlServerVersion(new Version()))
            .UseMigrationNamespace(new MySqlMigrationNamespace()),
        "SqlServer" => options.UseSqlServer("Data Source=localhost;Initial Catalog=DBMultiDataBase;Integrated Security=True;")
        .UseMigrationNamespace(new SqlServerMigrationNamespace()),
        _ => throw new Exception($"Unsupported provider: {provider}")
    };
});

到此为止我这边想我们应该已经实现了把,但是如果我们分别执行两个迁移命令会导致前一个迁移命令被覆盖掉,经过一整个下午的debug调试最后发现是因为在迁移脚本生成写入文件的时候会判断当前DbContext'的ModelSnapshot,同一个dbcontext生成的文件是一样的，所以我们这边有两个选择

• 1.让生成的文件名不一样
• 2.让ModelSnapshot不进行深度查询只在当前目录下处理
  这边选了第二种

    public class MyMigrationsScaffolder: MigrationsScaffolder
    {
        private readonly Type _contextType;
        public MyMigrationsScaffolder(MigrationsScaffolderDependencies dependencies) : base(dependencies)
        {
            _contextType = dependencies.CurrentContext.Context.GetType();
        }
        protected override string GetDirectory(string projectDir, string? siblingFileName, string subnamespace)
        {
            var defaultDirectory = Path.Combine(projectDir, Path.Combine(subnamespace.Split('.')));

            if (siblingFileName != null)
            {
                if (!siblingFileName.StartsWith(_contextType.Name + "ModelSnapshot."))
                {
                    var siblingPath = TryGetProjectFile(projectDir, siblingFileName);
                    if (siblingPath != null)
                    {
                        var lastDirectory = Path.GetDirectoryName(siblingPath)!;
                        if (!defaultDirectory.Equals(lastDirectory, StringComparison.OrdinalIgnoreCase))
                        {
                            Dependencies.OperationReporter.WriteVerbose(DesignStrings.ReusingNamespace(siblingFileName));

                            return lastDirectory;
                        }
                    }
                }
            }

            return defaultDirectory;
        }
    }

添加designservices

    public class MyMigrationDesignTimeServices: IDesignTimeServices
    {
        public void ConfigureDesignTimeServices(IServiceCollection serviceCollection)
        {
            serviceCollection.AddSingleton<IMigrationsScaffolder, MyMigrationsScaffolder>();
        }
    }

分别运行两个迁移命令

运行更新数据库命令

记得我们需要在参数里面添加选项

下期我们将实现efcore在Saas系统下的多租户+code-first(迁移)+分表+分库+读写分离+动态分表+动态分库+动态读写分离+动态添加多租户 全程零sql脚本的解决方案

是不是buffer叠满

最后的最后

附上demo:EFCoreMigrateMultiDatabase https://github.com/xuejmnet/EFCoreMigrateMultiDatabase

您都看到这边了确定不点个star或者赞吗,一款.Net不得不学的分库分表解决方案,简单理解为sharding-jdbc在.net中的实现并且支持更多特性和更优秀的数据聚合,拥有原生性能的97%,并且无业务侵入性,支持未分片的所有efcore原生查询