时间戳

首页 > 解决方案 > Ef-Core - 我可以使用什么正则表达式在 Db 拦截器中用 nolock 替换表名

c# - Ef-Core - 我可以使用什么正则表达式在 Db 拦截器中用 nolock 替换表名

问题描述

我一直在尝试将我们的 EF6 项目移植到 EF-Core-2.0。

在 EF6 中，我们使用DbNolock拦截器来添加我们想要的查询的With (NOLOCK)提示。您可以在下面找到我之前运行的 Db 拦截器代码。

   public class DbNoLockInterceptor : DbCommandInterceptor
    {
    private static readonly Regex TableAliasRegex = new Regex(@"((?<!\){1,5})AS \[Extent\d+\](?! WITH \(NOLOCK\)))", RegexOptions.Multiline | RegexOptions.IgnoreCase);

    public override void ScalarExecuting(DbCommand command,
        DbCommandInterceptionContext<object> interceptionContext)
    {
        command.CommandText =
            TableAliasRegex.Replace(command.CommandText, mt => mt.Groups[0].Value + " WITH (NOLOCK) ");
    }

    public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        command.CommandText = TableAliasRegex.Replace(command.CommandText,  mt => mt.Groups[0].Value + " WITH (NOLOCK) ");
    }
}

在 Ef-Core 中，我们可以用几乎相同的方式进行拦截。但是由于更改了表的命名约定，我无法为新表编写正则表达式。您可以在下面找到新的 Ef-Core 版本：

public class DbNoLockListener
{
    private static readonly Regex TableAliasRegex = new Regex(@"((?<!\){1,5})AS \[Extent\d+\](?! WITH \(NOLOCK\)))", RegexOptions.Multiline | RegexOptions.IgnoreCase);
    [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandExecuting")]
    public void OnCommandExecuting(DbCommand command, DbCommandMethod executeMethod, Guid commandId, Guid connectionId, bool async, DateTimeOffset startTime)
    {
        command.CommandText =
                        TableAliasRegex.Replace(command.CommandText, mt => mt.Groups[0].Value + " WITH (NOLOCK) ");
    }
}

Ef6 生成的 SQL：

SELECT
    [Extent1].[Id] AS [Extent1Id], 
    [Extent2].[Id] AS [Extent2Id]
    FROM [Advert].[Advert]  AS [Extent1]
    INNER JOIN [Membership].[Members] AS [Extent2] ON [Extent1].[MemberId] = [Extent2].[MemberId]

Ef-Core 生成的 SQL：

SELECT 
     [t].[Id]
    ,[t.Member].[Id]
FROM [Advert].[Advert] AS [t]
INNER JOIN [Membership].[Members] AS [t.Member] ON [t].[MemberId] = [t.Member].[MemberId]

您还可以查看此 github 问题以获取更多详细信息。

我想 用AS [t] WITH (NOLOCK)替换AS [t]并 用AS [ t.Member ] WITH (NOLOCK)替换 AS [t.Member]

我可以使用哪种模式在 Ef-Core 中做同样的事情？

标签： c#regexentity-framework.net-coreentity-framework-core

解决方案

这种拦截方式我觉得不太好。IMO 的一种更好的方法是连接到 EF Core 基础架构，以使用覆盖以下方法的自定义实现替换 SqlServer的IQuerySqlGenerator服务实现：VisitTable

public override Expression VisitTable(TableExpression tableExpression)
{
    // base will append schema, table and alias
    var result = base.VisitTable(tableExpression);
    Sql.Append(" WITH (NOLOCK)");
    return result;
}

挂钩有点复杂，因为我们需要创建和替换“工厂”服务才能替换 sql 生成器。所有这些的完整代码以及辅助扩展方法如下：

EF 核心 3.x：

using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.SqlServer.Query.Sql.Internal;
using Microsoft.EntityFrameworkCore.SqlServer.Query.Internal;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;

namespace Microsoft.EntityFrameworkCore
{
    public static class CustomDbContextOptionsBuilderExtensions
    {
        public static DbContextOptionsBuilder UseCustomSqlServerQuerySqlGenerator(this DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.ReplaceService<IQuerySqlGeneratorFactory, CustomSqlServerQuerySqlGeneratorFactory>();
            return optionsBuilder;
        }
    }
}

namespace Microsoft.EntityFrameworkCore.SqlServer.Query.Sql.Internal
{
    class CustomSqlServerQuerySqlGeneratorFactory : IQuerySqlGeneratorFactory
    {
        public CustomSqlServerQuerySqlGeneratorFactory(QuerySqlGeneratorDependencies dependencies)
            => Dependencies = dependencies;
        public QuerySqlGeneratorDependencies Dependencies { get; }
        public QuerySqlGenerator Create() => new CustomSqlServerQuerySqlGenerator(Dependencies);
    }

    public class CustomSqlServerQuerySqlGenerator : SqlServerQuerySqlGenerator
    {
        public CustomSqlServerQuerySqlGenerator(QuerySqlGeneratorDependencies dependencies)
            : base(dependencies) { }
        protected override Expression VisitTable(TableExpression tableExpression)
        {
            // base will append schema, table and alias
            var result = base.VisitTable(tableExpression);
            Sql.Append(" WITH (NOLOCK)");
            return result;
        }
    }
}

EF 核心 2.x：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore.Query.Expressions;
using Microsoft.EntityFrameworkCore.Query.Sql;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.EntityFrameworkCore.SqlServer.Infrastructure.Internal;
using Microsoft.EntityFrameworkCore.SqlServer.Query.Sql.Internal;

namespace Microsoft.EntityFrameworkCore
{
    public static class CustomDbContextOptionsBuilderExtensions
    {
        public static DbContextOptionsBuilder UseCustomSqlServerQuerySqlGenerator(this DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.ReplaceService<IQuerySqlGeneratorFactory, CustomSqlServerQuerySqlGeneratorFactory>();
            return optionsBuilder;
        }
    }
}

namespace Microsoft.EntityFrameworkCore.SqlServer.Query.Sql.Internal
{
    class CustomSqlServerQuerySqlGeneratorFactory : SqlServerQuerySqlGeneratorFactory
    {
        private readonly ISqlServerOptions sqlServerOptions;
        public CustomSqlServerQuerySqlGeneratorFactory(QuerySqlGeneratorDependencies dependencies, ISqlServerOptions sqlServerOptions)
            : base(dependencies, sqlServerOptions) => this.sqlServerOptions = sqlServerOptions;
        public override IQuerySqlGenerator CreateDefault(SelectExpression selectExpression) =>
            new CustomSqlServerQuerySqlGenerator(Dependencies, selectExpression, sqlServerOptions.RowNumberPagingEnabled);
    }

    public class CustomSqlServerQuerySqlGenerator : SqlServerQuerySqlGenerator
    {
        public CustomSqlServerQuerySqlGenerator(QuerySqlGeneratorDependencies dependencies, SelectExpression selectExpression, bool rowNumberPagingEnabled)
            : base(dependencies, selectExpression, rowNumberPagingEnabled) { }
        public override Expression VisitTable(TableExpression tableExpression)
        {
            // base will append schema, table and alias
            var result = base.VisitTable(tableExpression);
            Sql.Append(" WITH (NOLOCK)");
            return result;
        }
    }
}

仅添加一个有意义的行的代码相当多，但它的好处是它以 EF Core 的方式可能会这样做，以防有这样的查询选项。

无论如何，使用上面的代码，您只需要从您的上下文 OnConfiguring覆盖中激活它：

optionsBuilder.UseCustomSqlServerQuerySqlGenerator();

推荐阅读