前言 公司之前使用Ado.net和Dapper進行數據訪問層的操作, 進行讀寫分離也比較簡單, 只要使用對應的資料庫連接字元串即可. 而最近要遷移到新系統中,新系統使用.net core和EF Core進行數據訪問. 所以趁著國慶假期拿出一兩天時間研究了一下如何EF Core進行讀寫分離. 思路 根 ...
前言
公司之前使用Ado.net和Dapper進行數據訪問層的操作, 進行讀寫分離也比較簡單, 只要使用對應的資料庫連接字元串即可. 而最近要遷移到新系統中,新系統使用.net core和EF Core進行數據訪問. 所以趁著國慶假期拿出一兩天時間研究了一下如何EF Core進行讀寫分離.
思路
根據園子里的Jeffcky大神的博客, 參考
EntityFramework Core進行讀寫分離最佳實踐方式，瞭解一下（一）？
EntityFramework Core進行讀寫分離最佳實踐方式，瞭解一下（二）？
最簡單的思路就是使用手動切換EF Core上下文的連接, 即context.Database.GetDbConnection().ConnectionString = "xxx", 但必須要先創建上下文, 再關閉之前的連接, 才能進行切換
另一種方式是通過監聽Diagnostic來將進行查詢的sql切換到從庫執行, 這種方式雖然可以實現無感知的切換操作, 但不能滿足公司的業務需求. 在後臺管理或其他對數據實時性要求比較高的項目里,查詢操作也都應該走主庫,而這種方式卻會切換到從庫去. 另一方面就是假若公司的庫比較多,每種業務都對應了一個庫, 每個庫都對應了一種DbContext, 這種情況下, 要實現自動切換就變得很複雜了.
上面的兩種方式都是從切換資料庫連接入手,但是頻繁的切換資料庫連接勢必會對性能造成影響. 我認為最理想的方式是要避免資料庫連接的切換, 且能夠適應多DbContext的情況, 在創建上下文實例時,就指定好是訪問主庫還是從庫, 而不是在後期再進行資料庫切換. 因此, 在上下文實例化時,就傳入相應的資料庫連接字元串, 這樣一來DbContext的創建就需要交由我們自己來進行, 就不是由DI容器進行創建了. 同時倉儲應該區分為只讀和可讀可寫兩種,以防止其他人對從庫進行寫操作.
實現
public interface IReadOnlyRepository<TEntity, TKey>
where TEntity : class, IEntity<TKey>
where TKey : IEquatable<TKey>
{}
public interface IRepository<TEntity, TKey> : IReadOnlyRepository<TEntity, TKey>
where TEntity : class, IEntity<TKey>
where TKey : IEquatable<TKey>
{}IReadOnlyRepository介面是只讀倉儲介面,提供查詢相關方法,IRepository介面是可讀可寫倉儲介面,提供增刪查改等方法, 介面的實現就那些東西這裡就省略了.
public interface IRepositoryFactory
{
IRepository<TEntity, TKey> GetRepository<TEntity, TKey>(IUnitOfWork unitOfWork)
where TEntity : class, IEntity<TKey>
where TKey : IEquatable<TKey>;
IReadOnlyRepository<TEntity, TKey> GetReadOnlyRepository<TEntity, TKey>(IUnitOfWork unitOfWork)
where TEntity : class, IEntity<TKey>
where TKey : IEquatable<TKey>;
}
public class RepositoryFactory : IRepositoryFactory
{
public RepositoryFactory()
{
}
public IRepository<TEntity, TKey> GetRepository<TEntity, TKey>(IUnitOfWork unitOfWork)
where TEntity : class, IEntity<TKey>
where TKey : IEquatable<TKey>
{
return new Repository<TEntity, TKey>(unitOfWork);
}
public IReadOnlyRepository<TEntity, TKey> GetReadOnlyRepository<TEntity, TKey>(IUnitOfWork unitOfWork)
where TEntity : class, IEntity<TKey>
where TKey : IEquatable<TKey>
{
return new ReadOnlyRepository<TEntity, TKey>(unitOfWork);
}
}RepositoryFactory提供倉儲對象的實例化
public interface IUnitOfWork : IDisposable
{
public DbContext DbContext { get; }
/// <summary>
/// 獲取只讀倉儲對象
/// </summary>
IReadOnlyRepository<TEntity, TKey> GetReadOnlyRepository<TEntity, TKey>()
where TEntity : class, IEntity<TKey>
where TKey : IEquatable<TKey>;
/// <summary>
/// 獲取倉儲對象
/// </summary>
IRepository<TEntity, TKey> GetRepository<TEntity, TKey>()
where TEntity : class, IEntity<TKey>
where TKey : IEquatable<TKey>;
int SaveChanges();
Task<int> SaveChangesAsync(CancellationToken cancelToken = default);
}
public class UnitOfWork : IUnitOfWork
{
private readonly IServiceProvider _serviceProvider;
private readonly DbContext _dbContext;
private readonly IRepositoryFactory _repositoryFactory;
private bool _disposed;
public UnitOfWork(IServiceProvider serviceProvider, DbContext context)
{
Check.NotNull(serviceProvider, nameof(serviceProvider));
_serviceProvider = serviceProvider;
_dbContext = context;
_repositoryFactory = serviceProvider.GetRequiredService<IRepositoryFactory>();
}
public DbContext DbContext { get => _dbContext; }
public IReadOnlyRepository<TEntity, TKey> GetReadOnlyRepository<TEntity, TKey>()
where TEntity : class, IEntity<TKey>
where TKey : IEquatable<TKey>
{
return _repositoryFactory.GetReadOnlyRepository<TEntity, TKey>(this);
}
public IRepository<TEntity, TKey> GetRepository<TEntity, TKey>()
where TEntity : class, IEntity<TKey>
where TKey : IEquatable<TKey>
{
return _repositoryFactory.GetRepository<TEntity, TKey>(this);
}
public void Dispose()
{
if (_disposed)
{
return;
}
_dbContext?.Dispose();
_disposed = true;
}
// 其他略
} /// <summary>
/// 資料庫提供者介面
/// </summary>
public interface IDbProvider : IDisposable
{
/// <summary>
/// 根據上下文類型及資料庫名稱獲取UnitOfWork對象, dbName為null時預設為第一個資料庫名稱
/// </summary>
IUnitOfWork GetUnitOfWork(Type dbContextType, string dbName = null);
}IDbProvider 介面, 根據上下文類型和配置文件中的資料庫連接字元串名稱創建IUnitOfWork, 在DI中的生命周期是Scoped,在銷毀的同時會銷毀資料庫上下文對象, 下麵是它的實現, 為了提高性能使用了Expression來代替反射.
public class DbProvider : IDbProvider
{
private readonly IServiceProvider _serviceProvider;
private readonly ConcurrentDictionary<string, IUnitOfWork> _works = new ConcurrentDictionary<string, IUnitOfWork>();
private static ConcurrentDictionary<Type, Func<IServiceProvider, DbContextOptions, DbContext>> _expressionFactoryDict =
new ConcurrentDictionary<Type, Func<IServiceProvider, DbContextOptions, DbContext>>();
public DbProvider(IServiceProvider serviceProvider)
{
_serviceProvider = serviceProvider;
}
public IUnitOfWork GetUnitOfWork(Type dbContextType, string dbName = null)
{
var key = string.Format("{0}${1}$", dbName, dbContextType.FullName);
IUnitOfWork unitOfWork;
if (_works.TryGetValue(key, out unitOfWork))
{
return unitOfWork;
}
else
{
DbContext dbContext;
var dbConnectionOptionsMap = _serviceProvider.GetRequiredService<IOptions<FxOptions>>().Value.DbConnections;
if (dbConnectionOptionsMap == null || dbConnectionOptionsMap.Count <= 0)
{
throw new Exception("無法獲取資料庫配置");
}
DbConnectionOptions dbConnectionOptions = dbName == null ? dbConnectionOptionsMap.First().Value : dbConnectionOptionsMap[dbName];
var builderOptions = _serviceProvider.GetServices<DbContextOptionsBuilderOptions>()
?.Where(d => (d.DbName == null || d.DbName == dbName) && (d.DbContextType == null || d.DbContextType == dbContextType))
?.OrderByDescending(d => d.DbName)
?.OrderByDescending(d => d.DbContextType);
if (builderOptions == null || !builderOptions.Any())
{
throw new Exception("無法獲取匹配的DbContextOptionsBuilder");
}
var dbUser = _serviceProvider.GetServices<IDbContextOptionsBuilderUser>()?.FirstOrDefault(u => u.Type == dbConnectionOptions.DatabaseType);
if (dbUser == null)
{
throw new Exception($"無法解析類型為“{dbConnectionOptions.DatabaseType}”的 {typeof(IDbContextOptionsBuilderUser).FullName} 實例");
}
var dbContextOptions = dbUser.Use(builderOptions.First().Builder, dbConnectionOptions.ConnectionString).Options;
if (_expressionFactoryDict.TryGetValue(dbContextType, out Func<IServiceProvider, DbContextOptions, DbContext> factory))
{
dbContext = factory(_serviceProvider, dbContextOptions);
}
else
{
// 使用Expression創建DbContext
var constructorMethod = dbContextType.GetConstructors()
.Where(c => c.IsPublic && !c.IsAbstract && !c.IsStatic)
.OrderByDescending(c => c.GetParameters().Length)
.FirstOrDefault();
if (constructorMethod == null)
{
throw new Exception("無法獲取有效的上下文構造器");
}
var dbContextOptionsBuilderType = typeof(DbContextOptionsBuilder<>);
var dbContextOptionsType = typeof(DbContextOptions);
var dbContextOptionsGenericType = typeof(DbContextOptions<>);
var serviceProviderType = typeof(IServiceProvider);
var getServiceMethod = serviceProviderType.GetMethod("GetService");
var lambdaParameterExpressions = new ParameterExpression[2];
lambdaParameterExpressions[0] = (Expression.Parameter(serviceProviderType, "serviceProvider"));
lambdaParameterExpressions[1] = (Expression.Parameter(dbContextOptionsType, "dbContextOptions"));
var paramTypes = constructorMethod.GetParameters();
var argumentExpressions = new Expression[paramTypes.Length];
for (int i = 0; i < paramTypes.Length; i++)
{
var pType = paramTypes[i];
if (pType.ParameterType == dbContextOptionsType ||
(pType.ParameterType.IsGenericType && pType.ParameterType.GetGenericTypeDefinition() == dbContextOptionsGenericType))
{
argumentExpressions[i] = Expression.Convert(lambdaParameterExpressions[1], pType.ParameterType);
}
else if (pType.ParameterType == serviceProviderType)
{
argumentExpressions[i] = lambdaParameterExpressions[0];
}
else
{
argumentExpressions[i] = Expression.Call(lambdaParameterExpressions[0], getServiceMethod);
}
}
factory = Expression
.Lambda<Func<IServiceProvider, DbContextOptions, DbContext>>(
Expression.Convert(Expression.New(constructorMethod, argumentExpressions), typeof(DbContext)), lambdaParameterExpressions.AsEnumerable())
.Compile();
_expressionFactoryDict.TryAdd(dbContextType, factory);
dbContext = factory(_serviceProvider, dbContextOptions);
}
var unitOfWorkFactory = _serviceProvider.GetRequiredService<IUnitOfWorkFactory>();
unitOfWork = unitOfWorkFactory.GetUnitOfWork(_serviceProvider, dbContext);
_works.TryAdd(key, unitOfWork);
return unitOfWork;
}
}
public void Dispose()
{
if (_works != null && _works.Count > 0)
{
foreach (var unitOfWork in _works.Values)
unitOfWork.Dispose();
_works.Clear();
}
}
}
public static class DbProviderExtensions
{
public static IUnitOfWork GetUnitOfWork<TDbContext>(this IDbProvider provider, string dbName = null)
{
if (provider == null)
return null;
return provider.GetUnitOfWork(typeof(TDbContext), dbName);
}
} /// <summary>
/// 業務系統配置選項
/// </summary>
public class FxOptions
{
public FxOptions()
{
}
/// <summary>
/// 預設資料庫類型
/// </summary>
public DatabaseType DefaultDatabaseType { get; set; } = DatabaseType.SqlServer;
/// <summary>
/// 資料庫連接配置
/// </summary>
public IDictionary<string, DbConnectionOptions> DbConnections { get; set; }
}
public class FxOptionsSetup: IConfigureOptions<FxOptions>
{
private readonly IConfiguration _configuration;
public FxOptionsSetup(IConfiguration configuration)
{
_configuration = configuration;
}
/// <summary>
/// 配置options各屬性信息
/// </summary>
/// <param name="options"></param>
public void Configure(FxOptions options)
{
SetDbConnectionsOptions(options);
// ...
}
private void SetDbConnectionsOptions(FxOptions options)
{
var dbConnectionMap = new Dictionary<string, DbConnectionOptions>();
options.DbConnections = dbConnectionMap;
IConfiguration section = _configuration.GetSection("FxCore:DbConnections");
Dictionary<string, DbConnectionOptions> dict = section.Get<Dictionary<string, DbConnectionOptions>>();
if (dict == null || dict.Count == 0)
{
string connectionString = _configuration["ConnectionStrings:DefaultDbContext"];
if (connectionString == null)
{
return;
}
dbConnectionMap.Add("DefaultDb", new DbConnectionOptions
{
ConnectionString = connectionString,
DatabaseType = options.DefaultDatabaseType
});
return;
}
var ambiguous = dict.Keys.GroupBy(d => d).FirstOrDefault(d => d.Count() > 1);
if (ambiguous != null)
{
throw new Exception($"數據上下文配置中存在多個配置節點擁有同一個資料庫連接名稱，存在二義性：{ambiguous.First()}");
}
foreach (var db in dict)
{
dbConnectionMap.Add(db.Key, db.Value);
}
}
}
/// <summary>
/// DbContextOptionsBuilder配置選項
/// </summary>
public class DbContextOptionsBuilderOptions
{
/// <summary>
/// 配置DbContextOptionsBuilder, dbName指定資料庫名稱, 為null時表示所有資料庫,預設為null
/// </summary>
/// <param name="build"></param>
/// <param name="dbName"></param>
/// <param name="dbContextType"></param>
public DbContextOptionsBuilderOptions(DbContextOptionsBuilder build, string dbName = null, Type dbContextType = null)
{
Builder = build;
DbName = dbName;
DbContextType = dbContextType;
}
public DbContextOptionsBuilder Builder { get; }
public string DbName { get; }
public Type DbContextType { get; }
}FxOptions是業務系統的配置選項(隨便取得), 在通過service.GetService<IOptions
public interface IDbContextOptionsBuilderUser
{
/// <summary>
/// 獲取 資料庫類型名稱，如 SQLSERVER，MYSQL，SQLITE等
/// </summary>
DatabaseType Type { get; }
/// <summary>
/// 使用資料庫
/// </summary>
/// <param name="builder">創建器</param>
/// <param name="connectionString">連接字元串</param>
/// <returns></returns>
DbContextOptionsBuilder Use(DbContextOptionsBuilder builder, string connectionString);
}
public class SqlServerDbContextOptionsBuilderUser : IDbContextOptionsBuilderUser
{
public DatabaseType Type => DatabaseType.SqlServer;
public DbContextOptionsBuilder Use(DbContextOptionsBuilder builder, string connectionString)
{
return builder.UseSqlServer(connectionString);
}
}IDbContextOptionsBuilderUser介面用來適配不同的資料庫來源
使用
{
"FxCore": {
"DbConnections": {
"TestDb": {
"ConnectionString": "xxx",
"DatabaseType": "SqlServer"
},
"TestDb_Read": {
"ConnectionString": "xxx",
"DatabaseType": "SqlServer"
}
}
}
} class Program
{
static void Main(string[] args)
{
var config = new ConfigurationBuilder()
.AddJsonFile("appsettings.json")
.Build();
var services = new ServiceCollection()
.AddSingleton<IConfiguration>(config)
.AddOptions()
.AddSingleton<IConfigureOptions<FxOptions>, FxOptionsSetup>()
.AddScoped<IDbProvider, DbProvider>()
.AddSingleton<IUnitOfWorkFactory, UnitOfWorkFactory>()
.AddSingleton<IRepositoryFactory, RepositoryFactory>()
.AddSingleton<IDbContextOptionsBuilderUser, SqlServerDbContextOptionsBuilderUser>()
.AddSingleton<DbContextOptionsBuilderOptions>(new DbContextOptionsBuilderOptions(new DbContextOptionsBuilder<TestDbContext>(), null, typeof(TestDbContext)));
var serviceProvider = services.BuildServiceProvider();
var dbProvider = serviceProvider.GetRequiredService<IDbProvider>();
var uow = dbProvider.GetUnitOfWork<TestDbContext>("TestDb"); // 訪問主庫
var repoDbTest = uow.GetRepository<DbTest, int>();
var obj = new DbTest { Name = "123", Date = DateTime.Now.Date };
repoDbTest.Insert(obj);
uow.SaveChanges();
Console.ReadKey();
var uow2 = dbProvider.GetUnitOfWork<TestDbContext>("TestDb_Read");
var uow2 = dbProvider.GetUnitOfWork<TestDbContext>("TestDb_Read"); // 訪問從庫
var repoDbTest2 = uow2.GetReadOnlyRepository<DbTest, int>();
var data2 = repoDbTest2.GetFirstOrDefault();
Console.WriteLine($"id: {data2.Id} name: {data2.Name}");
Console.ReadKey();
}
}這裡直接用控制台來做一個例子,中間多了一個Console.ReadKey()是因為我本地沒有配置主從模式,所以實際上我是先插入數據,然後複製到另一個資料庫里,再進行讀取的.
總結
本文給出的解決方案適用於系統中存在多個不同的上下文,能夠適應複雜的業務場景.但對已有代碼的侵入性比較大,不知道有沒有更好的方案,歡迎一起探討.
