EF Architecture ~ Database read-write separation via EF6 's SQL named Interceptor

Back to Catalog

A few days ago looked at a SQL Server-based load balancing and read-write separation software Moebius, the implementation of the way is good, which makes use of SQL Server database students have the opportunity to more effectively optimize the database

Look at people have to do things, they also want to use EF to achieve a read and write separation, so there is this article, storage uncle Read and write separation of ideas is:

1 with SQL Server's own publish, subscribe to implement the main, from the structure of the database, synchronization of the cause of the need to help us do

2 configuration file establish several read-only database connection strings

3 Creating a SQL command Interceptor

4 Modify Uncle's dbcontextrepository base, add interception behavior

5 Tests, done.

With the above idea, we can be the director, the first step needless to say, can own Baidu, from the 2nd step

2 configuration file establish several read-only database connection strings

<!--write-only-<add name="backgroundentities"connectionstring="metadata=res://*/background.csdl|res://*/background.ssdl|res://*/background.msl;provider= System.data.sqlclient;provider connection String=&quot;data source=.; Initial catalog=background;persist security Info=true;user id=sa;password=zzl123;multipleactiveresultsets=true; Application name=entityframework&quot;"Providername="System.Data.EntityClient"/> <!--read-only-<add name="Backgroundentitiesread"connectionstring="metadata=res://*/background.csdl|res://*/background.ssdl|res://*/background.msl;provider= System.data.sqlclient;provider connection String=&quot;data source=.; Initial catalog=background_read1;persist Security Info=true;user id=sa;password=zzl123;multipleactiveresultsets= True;application name=entityframework&quot;"Providername="System.Data.EntityClient"/>

3 Creating a SQL command Interceptor

 /// <summary>    ///SQL command Blocker/// </summary>     Public classNolockinterceptor:dbcommandinterceptor {Private Static ReadOnlyRegex _tablealiasregex =NewRegex (@"(? <tablealias>as \[extent\d+\] (?! With \ (nolock\)))", Regexoptions.multiline|regexoptions.ignorecase); [ThreadStatic] Public Static BOOLSuppressnolock;  Public Override voidnonqueryexecuting (DbCommand command, dbcommandinterceptioncontext<int>Interceptioncontext) {            stringconn =command.            connection.connectionstring; Base.        Nonqueryexecuting (command, Interceptioncontext); }         Public Override voidscalarexecuting (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 voidreaderexecuting (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 Modify Uncle's dbcontextrepository base, add interception behavior

   PublicDbcontextrepository (Iunitofwork db, action<string>logger) {Unitwork=DB; Db=(DbContext) DB; Logger=logger; ((Iobjectcontextadapter) Db). Objectcontext.commandtimeout=0; //SQL statement BlockerSYSTEM.DATA.ENTITY.INFRASTRUCTURE.INTERCEPTION.DBINTERCEPTION.ADD (NewEntityFrameworks.Data.Core.Common.NoLockInterceptor ()); EntityFrameworks.Data.Core.Common.NoLockInterceptor.SuppressNoLock=true; }

5 merit caused, thanks for reading!

The code of this article does not show all, just show a kind of thought, hope can bring help to everybody.

Back to Catalog

EF Architecture ~ Database read-write separation via EF6 's SQL named Interceptor