关键词：
前言
随着系统的不断开发和迭代默认的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原生查询
github地址 https://github.com/xuejmnet/sharding-core
gitee地址 https://gitee.com/dotnetchina/sharding-core
EFCore Scaffold-DbContext 可以生成自定义模型名称吗？
】EFCoreScaffold-DbContext可以生成自定义模型名称吗？【英文标题】：CanEFCoreScaffold-DbContextgeneratecustomModelnames?【发布时间】：2020-03-1717:46:37【问题描述】：我正在使用具有数据库优先方法的EFCore3.0，并且想知道如何生成Model名称。首... 查看详情
efcore启用数据库连接池
本文环境为.Net5+ASP.NetCoreWebAPI+EFCore5.0.12+MySQL5，介绍了如何使用EFCore开启数据库连接池。在实现了自己的DbContext之后，需要在Startup.cs的ConfigureServices方法中注册DbContext。在A 查看详情
abpvnext4.4:统一efcore的dbcontext/移除efcoremigrations项目(代码片段)
Abpvnext4.4出现了一个比较重大的变更：在Startuptemplate中移除了EFCoreMigrations项目，本文翻译自community.abp.io/articl由于本文发布的时候Abpvnext的版本还没有到4.4，所以本文演示了如何从4.4以前的版本移除EntityFrameworkCore.DbMigr... 查看详情
将 EF Core 搭建到现有 DbContext 的脚手架
】将EFCore搭建到现有DbContext的脚手架【英文标题】：ScaffoldEFCoretoexistingDbContext【发布时间】：2019-06-1820:31:01【问题描述】：我的MVCCore应用程序中有一个ApplicationDbContext上下文。有没有一种方法可以在不创建新的DbContext的情况下将... 查看详情
在 EF Core 中使用多个 dbContext
】在EFCore中使用多个dbContext【英文标题】：UsemultipledbContextonEFCore【发布时间】：2021-11-2312:59:30【问题描述】：我正在一个带有EFCore的dotnetCoreWebAPI应用程序中工作，该应用程序使用多个（至少两个）dbContext将表访问到两个不同的S... 查看详情
EF Core 为 dbContext 传递 TEntity 以执行更新或添加
】EFCore为dbContext传递TEntity以执行更新或添加【英文标题】：EFCorepassTEntityfordbContexttodoUpdateorAdd【发布时间】：2021-08-0610:17:11【问题描述】：使用EFCore，我有10种方法基本上做同样的事情。更新或添加，然后SaveChanges()即publicvoidUpdat... 查看详情
efcore6简化的数据库上下文注册(代码片段)
...数据库上下文注册IntroEFCore6将简化现在的服务注册，DbContext的服务注册将会更简单一些Sample直接来看示例代码吧：现在我们注册EFCore的DbContext通常是这样的：const string connectionString = "DataSource=test";var se 查看详情
[efcore]数据迁移
...在了Data层。看一下BlogContext内容如下：publicclassBlogContext:DbContext{publicBlogContext(DbContextOptionsoptions):base(options){ 查看详情
efcore动态切换schema
...分库分表这么一件事，抽象为某个单一的逻辑。于是乎，DbContext是个好东西！当我们在说Ef的时候，实际上我们在讨论的主要就是他里面的DbContext。而一个DbContext，则逻辑上代表了一个数据库映射（包含数据库连接/表等和数据库... 查看详情
efcore反像工程
管理控制台第一步：安装环境输入：install-packageMicrosoft.EntityFrameworkCore.SqlServer-Version2.2.0第二部：导入数据库：Scaffold-DbContext‘DataSource=.;InitialCatalog=PrintShareServer;PersistSecurityInfo=True;UserID=sa;Password 查看详情
配置 DbContext 构造函数
】配置DbContext构造函数【英文标题】：ConfiguringDbContextConstructor【发布时间】：2016-12-1300:18:08【问题描述】：我正在尝试使用EFCore工具来管理我在C#类库中设计的SqlServer数据库。它在类库中，因为我需要在MVC6网站和一些命令行工... 查看详情
efcore修改部分字段(代码片段)
...Func<T,object>>[]updatedProperties,boolIsCommit=true)intresult=0;_dbContext.Set<T>().Attach(entity);if(updatedProperties.Any())foreach(varpropertyinupdatedProperties)_dbContext.Entry<T>(entity).Property(property).IsModified=true;if(IsCommit)result=_U 查看详情
efcore入门(代码片段)
...据库读取连接和实体,打开Nuget包管理控制台，输入Scaffold-DbContext"Server=(localdb)mssqllocaldb;Database=Blogging;Trusted_Connection=True;"Microsoft.EntityFrameworkCore.SqlServer-OutputDirModels成功后会在项目中自动生成实体模型Model和数据库上下文DBContext修... 查看详情
我应该将啥 DbContext 与 EntityFrameworkCore 一起使用？
】我应该将啥DbContext与EntityFrameworkCore一起使用？【英文标题】：WhatDbContextshouldIusewithEnityFrameworkCore?我应该将什么DbContext与EntityFrameworkCore一起使用？【发布时间】：2019-04-1205:45:39【问题描述】：我有带有EFCore的.NetCore应用程序。... 查看详情
微软跨平台orm框架之efcore(代码片段)
...库如Mysql引入相应的包即可）。 3.创建继承成自DbContext上下文，并重载OnConfiguring方法来构建数据库连接字符串 publicclassCoreDbContext:DbContext///<summary>///重载OnConfiguring构建数据库连接字符串///</summary>///<paramname... 查看详情
无法使用 DbContext 实体框架核心 SaveChanges()
】无法使用DbContext实体框架核心SaveChanges()【英文标题】：CannotSaveChanges()withDbContextEntityFrameworkCore【发布时间】：2020-10-1508:37:24【问题描述】：我对EF核心比较陌生，所以对我来说很简单。我正在尝试使用EntityFrameworkCore使用数据... 查看详情
如何在 EF Core 中实例化 DbContext
】如何在EFCore中实例化DbContext【英文标题】：HowtoinstantiateaDbContextinEFCore【发布时间】：2018-11-2003:29:55【问题描述】：我也设置了.net核心项目和数据库上下文。但是由于这个错误，我还不能开始使用dbContext-"没有给出与所需形式... 查看详情
与实体相比，另一层中的 DbContext
】与实体相比，另一层中的DbContext【英文标题】：DbContextinanotherlayercomparedtotheEntities【发布时间】：2021-09-1904:46:02【问题描述】：目前正在学习如何在.NETcoreMVC中实现DDD项目，我在尝试使用efcore创建Db表时遇到了问题。基本上我所... 查看详情