前几天看了一个基于sqlserver的负载均衡与读写分离的软件Moebius,实现的方式还是不错的，这使得用sqlserver数据库的同学时有机会对数据库进行更有效的优化了
看着人有做的东西，自己也想用ＥＦ来实现一个读写分离，所以就有了本篇文章，仓储大叔读写分离的思路是：
1 用sqlserver自带的发布、订阅实现主，从数据库的结构，同步这事由sql帮我们完成
2 配置文件建立几个供只读的数据库连接串
3 建立ＳＱＬ命令拦截器
4 修改大叔的DbContextRepository基数，添加拦截行为
5 测试，搞定
有了上面的想法，咱就可以干事了，第一步不用说了，可以自己百度，从第２步说起
2 配置文件建立几个供只读的数据库连接串
<!-- 只写-->
<add name="backgroundEntities" connectionString="metadata=res://*/background.csdl|res://*/background.ssdl|res://*/background.msl;provider=System.Data.SqlClient;provider connection string="data source=.;initial catalog=background;persist security info=True;user id=sa;password=zzl123;multipleactiveresultsets=True;application name=EntityFramework"" providerName="System.Data.EntityClient" />
<!-- 只读-->
<add name="backgroundEntitiesRead" connectionString="metadata=res://*/background.csdl|res://*/background.ssdl|res://*/background.msl;provider=System.Data.SqlClient;provider connection string="data source=.;initial catalog=background_Read1;persist security info=True;user id=sa;password=zzl123;multipleactiveresultsets=True;application name=EntityFramework"" providerName="System.Data.EntityClient" />
3 建立ＳＱＬ命令拦截器
/// <summary> /// SQL命令拦截器 /// </summary> public class NoLockInterceptor : DbCommandInterceptor { private static readonly Regex _tableAliasRegex = new Regex(@"(?<tableAlias>AS \[Extent\d+\](?! WITH \(NOLOCK\)))", RegexOptions.Multiline | RegexOptions.IgnoreCase); [ThreadStatic] public static bool SuppressNoLock; public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { string conn = command.Connection.ConnectionString; base.NonQueryExecuting(command, interceptionContext); } public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { command.Connection.Close(); command.Connection.ConnectionString = "data source=.;initial catalog=background_Read1;persist security info=True;user id=sa;password=zzl123;multipleactiveresultsets=True;application name=EntityFramework"; command.Connection.Open(); if (!SuppressNoLock) { command.CommandText = _tableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)"); } } public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { command.Connection.Close(); command.Connection.ConnectionString = "data source=.;initial catalog=background_Read1;persist security info=True;user id=sa;password=zzl123;multipleactiveresultsets=True;application name=EntityFramework"; command.Connection.Open(); if (!SuppressNoLock) { command.CommandText = _tableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)"); } } }
4 修改大叔的DbContextRepository基数，添加拦截行为
public DbContextRepository(IUnitOfWork db, Action<string> logger) { UnitWork = db; Db = (DbContext)db; Logger = logger; ((IObjectContextAdapter)Db).ObjectContext.CommandTimeout = 0; //SQL语句拦截器 System.Data.Entity.Infrastructure.Interception.DbInterception.Add(new EntityFrameworks.Data.Core.Common.NoLockInterceptor()); EntityFrameworks.Data.Core.Common.NoLockInterceptor.SuppressNoLock = true; }
5 大功造成，感谢阅读！
本文章代码没有全部展示，只是展示一种思想，希望可以给大家带来帮助。
本文转自博客园张占岭（仓储大叔）的博客，原文链接：EF架构~通过EF6的DbCommand拦截器来实现数据库读写分离，如需转载请自行联系原博主。
