EF架构~通过EF6的SQL命名拦截器来实现数据库读写分离 - 推酷
EF架构~通过EF6的SQL命名拦截器来实现数据库读写分离
前几天看了一个基于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.provider=System.Data.SqlCprovider connection string=&data source=.;initial catalog=persist security info=Tuser id=password=zzl123;multipleactiveresultsets=Tapplication name=EntityFramework&& providerName=&System.Data.EntityClient& /&
&!-- 只读--&
&add name=&backgroundEntitiesRead& connectionString=&metadata=res://*/background.csdl|res://*/background.ssdl|res://*/background.provider=System.Data.SqlCprovider connection string=&data source=.;initial catalog=background_Read1;persist security info=Tuser id=password=zzl123;multipleactiveresultsets=Tapplication 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 SuppressNoL
public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext&int& interceptionContext)
string conn = command.Connection.ConnectionS
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=Tuser id=password=zzl123;multipleactiveresultsets=Tapplication name=EntityFramework&;
command.Connection.Open();
if (!SuppressNoLock)
mandText =
_tableAliasRegex.mandText, &${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=Tuser id=password=zzl123;multipleactiveresultsets=Tapplication name=EntityFramework&;
command.Connection.Open();
if (!SuppressNoLock)
mandText =
_tableAliasRegex.mandText, &${tableAlias} WITH (NOLOCK)&);
4 修改大叔的DbContextRepository基数，添加拦截行为
public DbContextRepository(IUnitOfWork db, Action&string& logger)
UnitWork =
Db = (DbContext)
((IObjectContextAdapter)Db).mandTimeout = 0;
//SQL语句拦截器
System.Data.Entity.Infrastructure.Interception.DbInterception.Add(new EntityFrameworks.mon.NoLockInterceptor());
EntityFrameworks.mon.NoLockInterceptor.SuppressNoLock = true;
5 大功造成，感谢阅读！
本文章代码没有全部展示，只是展示一种思想，希望可以给大家带来帮助。
已发表评论数()
请填写推刊名
描述不能大于100个字符!
权限设置： 公开
仅自己可见
正文不准确
标题不准确
排版有问题
主题不准确
没有分页内容
图片无法显示
视频无法显示
与原文不一致EF架构~经过EF6的DbCommand拦截器来实现数据库读写分离~终结~配置的优化和事务里读写的统一 - 数据库当前位置:& &&&EF架构~经过EF6的DbCommand拦截器来实现数据库读写EF架构~经过EF6的DbCommand拦截器来实现数据库读写分离~终结~配置的优化和事务里读写的统一&&网友分享于：&&浏览：0次EF架构~通过EF6的DbCommand拦截器来实现数据库读写分离~终结~配置的优化和事务里读写的统一
本讲是通过DbCommand拦截器来实现读写分离的最后一讲，对之前几篇文章做了一个优化，无论是程序可读性还是实用性上都有一个提升，在配置信息这块，去除了字符串方式的拼接，取而代之的是section数组，这样在修改配置时更加清晰了；而实用性上，彻底改变了读和写不能共用一个仓储对象的缺点，并且在一个事务里可以读写并存，并为了数据的一致性，使事务里的curd操作指向主库，这一点很重要！
前几篇文章的目录
功能架构图如下
下面我们来分块看一下这次的修改
一　配置文件的修改
&configSections&
&section name="DistributedReadWriteSection" type="Project.DistributedReadWriteForEF.DistributedReadWriteSectionHandler, Project.DistributedReadWriteForEF"/&
&/configSections&
&DistributedReadWriteSection&
&add key="readDb1" Ip="192.168.2.71" Port="1433" DbName="background_read1" UserId="sa" Password="zzl123" /&
&add key="readDb2" Ip="192.168.2.71" Port="1433" DbName="TestWrite_Read_Zzl" UserId="sa" Password="zzl123" /&
&add key="readDb3" Ip="192.168.2.29" Port="1433" DbName="TestWrite_Read_Zzl" UserId="sa" Password="1" /&
&/DistributedReadWriteSection&
&appSettings&
&!-- 只读服务器的sql连接串配置模版--&
&add key ="readDbConnection" value="data source={0};initial catalog={1};persist security info=Tuser id={2};password={3};multipleactiveresultsets=Tapplication name=EntityFramework"/&
&add key ="writeDbConnection" value="data source=.;initial catalog=persist security info=Tuser id=password=zzl123;multipleactiveresultsets=Tapplication name=EntityFramework"/&
&/appSettings&
/// &summary&
/// redis配置信息加载
/// &/summary&
internal class DistributedReadWriteManager
/// &summary&
/// 配置信息实体
/// &/summary&
public static IList&DistributedReadWriteSection& Instance
return GetSection();
private static IList&DistributedReadWriteSection& GetSection()
var dic = ConfigurationManager.GetSection("DistributedReadWriteSection") as Dictionary&string, DistributedReadWriteSection&;
return dic.Values.ToList();
/// &summary&
/// DistributedReadWriteForEFSection块，在web.config中提供DistributedReadWriteForEFSection块定义
/// &/summary&
internal class DistributedReadWriteSection : ConfigurationSection
/// &summary&
/// 主机地址
/// &/summary&
[ConfigurationProperty("Ip", DefaultValue = "127.0.0.1")]
public string Ip
get { return (string)this["Ip"]; }
set { this["Ip"] = }
/// &summary&
/// 端口号
/// &/summary&
[ConfigurationProperty("Port", DefaultValue = "1433")]
public int Port
get { return (int)this["Port"]; }
set { this["Port"] = }
/// &summary&
/// 数据库名称
/// &/summary&
[ConfigurationProperty("DbName", DefaultValue = "Test")]
public string DbName
get { return (string)this["DbName"]; }
set { this["DbName"] = }
/// &summary&
/// 数据库账号
/// &/summary&
[ConfigurationProperty("UserId", DefaultValue = "sa")]
public string UserId
get { return (string)this["UserId"]; }
set { this["UserId"] = }
/// &summary&
/// 数据库账号
/// &/summary&
[ConfigurationProperty("Password", DefaultValue = "sa")]
public string Password
get { return (string)this["Password"]; }
set { this["Password"] = }
internal class DistributedReadWriteSectionHandler : IConfigurationSectionHandler
#region IConfigurationSectionHandler 成员
public object Create(object parent, object configContext, System.Xml.XmlNode section)
Dictionary&string, DistributedReadWriteSection& names = new Dictionary&string, DistributedReadWriteSection&();
string _key = string.E
string _ip = string.E
string _dbName = string.E
string _userId = string.E
string _password = string.E
int _port = 1433;
foreach (XmlNode childNode in section.ChildNodes)
if (childNode.Attributes["key"] != null)
_key = childNode.Attributes["key"].V
if (childNode.Attributes["Ip"] != null)
_ip = childNode.Attributes["Ip"].V
if (childNode.Attributes["Port"] != null)
_port = Convert.ToInt32(childNode.Attributes["Port"].Value);
if (childNode.Attributes["DbName"] != null)
_dbName = childNode.Attributes["DbName"].V
if (childNode.Attributes["UserId"] != null)
_userId = childNode.Attributes["UserId"].V
if (childNode.Attributes["Password"] != null)
_password = childNode.Attributes["Password"].V
names.Add(_key, new DistributedReadWriteSection { Ip = _ip, Port = _port, DbName = _dbName, UserId = _userId, Password = _password });
#endregion
二　仓储大叔事务块修改
public static void UsingNoMsdtc(IUnitOfWork db, bool isOutest, Action action)
var objectContext = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)db).ObjectC
objectContext.Connection.Close();
//强制将所有curd操作维持到主库
mandInterceptor.IsTransactionScope = true;
//重新设置链接串
if (System.Configuration.ConfigurationManager.AppSettings["writeDbConnection"] != null)
objectContext.TransactionHandler.DbContext.Database.Connection.ConnectionString = System.Configuration.ConfigurationManager.AppSettings["writeDbConnection"];
objectContext.Connection.Open();
using (TransactionScope trans = new TransactionScope())
mandInterceptor.IsTransactionScope = false;//事务结束将走读写分离
if (isOutest)//如果是最外层事务，再将连接关闭！内部事务与外部事务需要共用一个Connection的连接
objectContext.Connection.Close(); //只能关闭,不能dispose,因为dispose之后,上下文就无法得到链接串了
三　DbCommand拦截器的修改
/// &summary&
/// SQL命令拦截器
/// 主要实现EF的读写分离
/// &/summary&
public class CommandInterceptor : DbCommandInterceptor
static CommandInterceptor()
readConnList = DistributedReadWriteManager.I
sysTimer.Enabled = true;
sysTimer.Elapsed += sysTimer_E
sysTimer.Start();
/// &summary&
/// 是否在一个事务中，如果是select,insert,update,delete都走主库
/// ThreadStatic标识它只在当前线程有效
/// &/summary&
[ThreadStatic]
public static bool IsTransactionScope = false;
/// &summary&
/// 锁住它
/// &/summary&
private static object lockObj = new object();
/// &summary&
/// 定期找没有在线的数据库服务器
/// &/summary&
private static Timer sysTimer = new Timer(5000);
/// &summary&
///　读库，从库集群，写库不用设置走默认的EF框架
/// &/summary&
private static IList&DistributedReadWriteSection& readConnL
#region Private Methods
private static void sysTimer_Elapsed(object sender, ElapsedEventArgs e)
if (readConnList != null && readConnList.Any())
foreach (var item in readConnList)
//心跳测试，将死掉的服务器IP从列表中移除
var client = new TcpClient();
client.Connect(new IPEndPoint(IPAddress.Parse(item.Ip), item.Port));
catch (SocketException)
//异常，没有连接上
readConnList.Remove(item);
if (!client.Connected)
readConnList.Remove(item);
/// &summary&
/// 处理读库字符串
/// &/summary&
/// &returns&&/returns&
private string GetReadConn()
if (readConnList != null && readConnList.Any())
var resultConn = readConnList[Convert.ToInt32(Math.Floor((double)new Random().Next(0, readConnList.Count)))];
return string.Format(System.Configuration.ConfigurationManager.AppSettings["readDbConnection"]
, resultConn.Ip
, resultConn.DbName
, resultConn.UserId
, resultConn.Password);
return string.E
/// &summary&
/// 只读库的选择,加工command对象
/// 说明：事务中,所有语句都走主库，事务外select走读库,insert,update,delete走主库
/// 希望：一个ＷＥＢ请求中，读与写的仓储使用一个，不需要在程序中去重新定义
/// &/summary&
/// &param name="command"&&/param&
private void ReadDbSelect(DbCommand command)
if (!string.IsNullOrWhiteSpace(GetReadConn()))//如果配置了读写分离，就去实现
command.Connection.Close();
if (!mandText.StartsWith("insert", StringComparison.InvariantCultureIgnoreCase) && !IsTransactionScope)
command.Connection.ConnectionString = GetReadConn();
command.Connection.Open();
#endregion
#region Override Methods
/// &summary&
/// Linq to Entity生成的update,delete
/// &/summary&
/// &param name="command"&&/param&
/// &param name="interceptionContext"&&/param&
public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext&int& interceptionContext)
base.NonQueryExecuting(command, interceptionContext);//update,delete等写操作直接走主库
/// &summary&
/// 执行sql语句，并返回第一行第一列，没有找到返回null,如果数据库中值为null，则返回 DBNull.Value
/// &/summary&
/// &param name="command"&&/param&
/// &param name="interceptionContext"&&/param&
public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext&object& interceptionContext)
ReadDbSelect(command);
base.ScalarExecuting(command, interceptionContext);
/// &summary&
/// Linq to Entity生成的select,insert
/// 发送到sqlserver之前触发
/// warning:在select语句中DbCommand.Transaction为null，而ef会为每个insert添加一个DbCommand.Transaction进行包裹
/// &/summary&
/// &param name="command"&&/param&
/// &param name="interceptionContext"&&/param&
public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext&DbDataReader& interceptionContext)
ReadDbSelect(command);
base.ReaderExecuted(command, interceptionContext);
/// &summary&
/// 发送到sqlserver之后触发
/// &/summary&
/// &param name="command"&&/param&
/// &param name="interceptionContext"&&/param&
public override void ReaderExecuted(DbCommand command, DbCommandInterceptionContext&DbDataReader& interceptionContext)
base.ReaderExecuted(command, interceptionContext);
#endregion
好了，到这里，通过拦截器来实现数据库读写分离的方案就彻底完成了，这个版本应该算是个终级了吧，呵呵！感谢您的阅读！
12345678910
12345678910
12345678910 上一篇：下一篇：文章评论相关解决方案 12345678910 Copyright & &&版权所有EntityFramework 实现读写分离
时间： 10:33:16
&&&& 阅读：93
&&&& 评论：
&&&& 收藏：0
标签：&&&&&&&&&&&&&&&&&&&&&&&&&&&1、数据库切换器
public class RandomGet&T&
private class RandomModel
public T Value { get; set; }
public RandomModel Next { get; set; }
public RandomGet(params T[] value)
if (value != null)
foreach (var item in value)
AllObject.Add(new RandomModel() { Value = item });
if (AllObject.Count & <span style="color: #)
//创建链表
AllObject[AllObject.Count - <span style="color: #].Next = AllObject[<span style="color: #];
for (int i = <span style="color: #; i & AllObject.Count - <span style="color: #; i++)
AllObject[i].Next = AllObject[i + <span style="color: #];
Current = AllObject[<span style="color: #];
throw new ArgumentException("");
private List&RandomModel& AllObject { get; set; } = new List&RandomModel&();
private RandomModel C
public T GetValue()
Current = Current.N //超高的并发，可能短暂的无法切换，但是也会实现数据库的切换
return Current.V
2、EntityFramework拦截器
public class BreakAwayContext : DbContext
/// &summary&
/// 命令拦截器
/// &/summary&
public class NoLockInterceptor : DbCommandInterceptor
public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext&object& interceptionContext)
command.Connection.Close(); //这里的Close不会大量关闭Connection，因为C#自己的连接池机制，这里不会关闭连接
command.Connection.ConnectionString = SelectModel.GetValue();
command.Connection.Open();
public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext&DbDataReader& interceptionContext)
command.Connection.Close();
command.Connection.ConnectionString = SelectModel.GetValue();
command.Connection.Open();
static BreakAwayContext()
System.Data.Entity.Infrastructure.Interception.DbInterception.Add(new NoLockInterceptor()); //关键代码，设置拦截器
static RandomGet&String& InsertModel = new RandomGet&String&("Data Source=192.168.0.150;Initial Catalog=MyBuser id=password=123123;");//修改删除的字符串
static RandomGet&String& SelectModel = new RandomGet&String&(
"Data Source=192.168.0.150;Initial Catalog=MyBuser id=password=123123;"
, "Data Source=192.168.0.150;Initial Catalog=MyBuser id=password=123123;"
, "Data Source=192.168.0.150;Initial Catalog=MyBuser id=password=123123;");//查询的字符串
public BreakAwayContext()
: base(InsertModel.GetValue()) //这里每个EF对象创建的时候，调用这个，获得一个连接字符串
public DbSet&Destination& Destinations { get; set; }
&标签：&&&&&&&&&&&&&&&&&&&&&&&&&&&原文：/jiecaoge/p/6361944.html
教程昨日排行
&&国之画&&&& &&&&&&
&& &&&&&&&&&&&&&&
鲁ICP备号-4
打开技术之扣，分享程序人生！