TNBLOG
首页
博客
视频
资源
问答
猿趣
手机
关于
搜索
收藏
便签
笔记
消息
创作
登录
剑轩
故如虹,知恩;故如月,知明
博主信息
排名
6
文章
6
粉丝
16
评论
8
文章类别
CSS
15篇
微服务
41篇
Git
14篇
.NET
102篇
移动开发
33篇
软件架构
23篇
.NET Core
119篇
.NET MVC
11篇
英语
3篇
随笔
86篇
Bootstrap
3篇
Redis
21篇
编辑器
10篇
Js相关
15篇
虚拟化
8篇
更多
Oracle
7篇
Python
14篇
数据库
26篇
EF
17篇
微信
3篇
前端
151篇
消息队列
6篇
docker
41篇
多线程
1篇
Java
4篇
软件基础
2篇
C++
2篇
WCF
7篇
Linux
7篇
nginx
5篇
K8S
9篇
ABP
2篇
最新文章
最新评价
{{item.articleTitle}}
{{item.blogName}}
:
{{item.content}}
关于我们
ICP备案 :
渝ICP备18016597号-1
网站信息:
2018-2024
TNBLOG.NET
技术交流:
群号656732739
联系我们:
contact@tnblog.net
欢迎加群
欢迎加群交流技术
原
扩展ef支持with nolock。修改EF表的别名
1348
人阅读
2022/8/8 18:56
总访问:
3962283
评论:
0
收藏:
0
手机
分类:
EF
### 可以使用继承EF的DbCommandInterceptor,然后重写里边的方法,修改生成的sql语句 代码如下 ``` public class QueryWithNoLockDbCommandInterceptor : DbCommandInterceptor { // 可以把 from [XX] as [y] 替换为 from [XX] as [y] WITH (NOLOCK)。也就是需要在后面加上WITH (NOLOCK) 其中[XX]与[y]可以是任意英文与数字的组合名称 // string pattern = @"FROM\s+\[[a-zA-Z0-9_]+\]\s+AS\s+\[[a-zA-Z0-9]+\]"; // 除了支持把 from [XX] as [y] 替换为 from [XX] as [y] WITH (NOLOCK)还能支持把JOIN [XX] as [y] 替换为 from [XX] as [y] WITH (NOLOCK) string pattern = @"(?:FROM|JOIN)\s+\[[a-zA-Z0-9_]+\]\s+AS\s+\[[a-zA-Z0-9]+\]"; public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result) { //command.CommandText = TableAliasRegex.Replace( // command.CommandText, // "$& WITH (NOLOCK)" // ); //string pattern = @"FROM\s+\[[a-zA-Z0-9]+\]\s+AS\s+\[[a-zA-Z0-9]+\]"; string replacement = "$& WITH (NOLOCK)"; string output = Regex.Replace(command.CommandText, pattern, replacement); command.CommandText = output; //Console.WriteLine("ScalarExecuting替换后的sql:" + output); return base.ScalarExecuting(command, eventData, result); } public override Task<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result, CancellationToken cancellationToken = new CancellationToken()) { //command.CommandText = TableAliasRegex.Replace( // command.CommandText, // "$& WITH (NOLOCK)" // ); //string pattern = @"FROM\s+\[[a-zA-Z0-9]+\]\s+AS\s+\[[a-zA-Z0-9]+\]"; string replacement = "$& WITH (NOLOCK)"; string output = Regex.Replace(command.CommandText, pattern, replacement); command.CommandText = output; //Console.WriteLine("ScalarExecutingAsync替换后的sql:" + output); return base.ScalarExecutingAsync(command, eventData, result, cancellationToken); } public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result) { //command.CommandText = TableAliasRegex.Replace( // command.CommandText, // "$& WITH (NOLOCK)" // ); //string pattern = @"FROM\s+\[[a-zA-Z0-9]+\]\s+AS\s+\[[a-zA-Z0-9]+\]"; string replacement = "$& WITH (NOLOCK)"; string output = Regex.Replace(command.CommandText, pattern, replacement); command.CommandText = output; //Console.WriteLine("ReaderExecuting替换后的sql:" + output); return result; } public override Task<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = new CancellationToken()) { /* command.CommandText = TableAliasRegex.Replace( command.CommandText, "$& WITH (NOLOCK)" );*/ //string pattern = @"FROM\s+\[[a-zA-Z0-9]+\]\s+AS\s+\[[a-zA-Z0-9]+\]"; string replacement = "$& WITH (NOLOCK)"; string output = Regex.Replace(command.CommandText, pattern, replacement); command.CommandText = output; //Console.WriteLine("ReaderExecutingAsync替换后的sql:" + output); return base.ReaderExecutingAsync(command, eventData, result, cancellationToken); } } ``` 然后在初始化ef的时候添加即可。这样就可以自动添加with nolock了。 ``` var connection = Configuration.GetConnectionString("BloggingDatabase"); services.AddDbContext<CNBLOGContext>(options => options.UseSqlServer(connection) .AddInterceptors(new QueryWithNoLockDbCommandInterceptor()) ); ``` ### 修改EF表的别名 在EF 中,可以通过重写 sqlGenerator 类中的方法来修改表别名。下面是一个示例: ``` public class CustomSqlGenerator : SqlServerQuerySqlGenerator { // 重写 VisitTable 方法以添加表别名和 WITH (NOLOCK) 提示 protected override Expression VisitTable(TableExpression tableExpression) { var sql = base.VisitTable(tableExpression); if (!string.IsNullOrEmpty(tableExpression.Alias)) { sql = new StringFragment($"{sql} AS {tableExpression.Alias}"); } return new StringFragment($"{sql} WITH (NOLOCK)"); } } ``` 在这个示例中,我们创健了一个自定义的SqlGenerator 类,并重写了其中的 VisitTable方法,在这方法中,我们首先调用类的 Visitable 方法生成 SOL,然后判断当前表达式是否有别名,如果有,则添加别名; 最后添加“WITH(NOLOCK)”提示并返回结果。 要使用自走义的 sglGenerator ,需要在 DbContext 中进行配置 ``` protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.ReplaceService<ISglGenerationHelper, SqlServerSqlGenerationHelper>(); optionsBuilder,ReplaceService<IQuerySqlGeneratorFactory, CustomOuerySqlGeneratorFactory>(); } private class CustomQuerySglGeneratorFactory : QuerySglGeneratorFactoryBase { public CustomQuerySqlGeneratorFactory(QuerysqlGeneratorDependencies dependencies): base(dependencies) { } public override IQuerySglGenerator Create() } ```
欢迎加群讨论技术,1群:677373950(满了,可以加,但通过不了),2群:656732739
👈{{preArticle.title}}
👉{{nextArticle.title}}
评价
{{titleitem}}
{{titleitem}}
{{item.content}}
{{titleitem}}
{{titleitem}}
{{item.content}}