Efcore: Need DbCommandInterceptor or nolock extension method for linq on ef core.

[turenc]

Hi,

I need an item like DbCommandInterceptor on ef core.
We have hundreds of linq query working with the following interceptor.

My old solution on ef6:
"http://www.gitshah.com/2014/08/how-to-add-nolock-hint-to.html"

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 ApplyNoLock;

    public override void ScalarExecuting(DbCommand command, 
        DbCommandInterceptionContext<object> interceptionContext)
    {
        if (ApplyNoLock)
        {
            command.CommandText = 
                _tableAliasRegex.Replace(command.CommandText, 
                "${tableAlias} WITH (NOLOCK)");
        }
    }

    public override void ReaderExecuting(DbCommand command, 
        DbCommandInterceptionContext<dbdatareader> interceptionContext)
    {
        if (ApplyNoLock)
        {
            command.CommandText = 
                _tableAliasRegex.Replace(command.CommandText, 
                "${tableAlias} WITH (NOLOCK)");
        }
    }
}

NoLockInterceptor.ApplyNoLock = true;



md5-1b51131ecac101e608cfb1f91a7b5e0b



DbInterception.Add(new NoLockInterceptor());

Or is it possible to add an extension method for linq queries that table-base sql "with nolock" mssql statement?

I don't want change db transaction isolation level this case. Because I don't begin a database transaction for select queries and I want generate a sql query with "with nolock" mssql statement.

Regards,
Türenç.

[ajcvickers]

@turenc Support for hints is tracked by #6717. The equivalent of the interceptor can be done by hooking into the DiagnosticSource infrastructure. For example, first create an interceptor:
```C#
public class NoLockInterceptor : IObserver>
{
public void OnCompleted()
{
}

public void OnError(Exception error)
{
}

public void OnNext(KeyValuePair<string, object> value)
{
    if (value.Key == RelationalEventId.CommandExecuting.Name)
    {
        var command = ((CommandEventData)value.Value).Command;

        // Do command.CommandText manipulation here
    }
}

}

Next, create a global listener for EF diagnostics. Something like:
```C#
public class EfGlobalListener : IObserver<DiagnosticListener>
{
    private readonly NoLockInterceptor _noLockInterceptor = new NoLockInterceptor();

    public void OnCompleted()
    {
    }

    public void OnError(Exception error)
    {
    }

    public void OnNext(DiagnosticListener listener)
    {
        if (listener.Name == DbLoggerCategory.Name)
        {
            listener.Subscribe(_noLockInterceptor);
        }
    }
}

And register this as part of application startup:
C# DiagnosticListener.AllListeners.Subscribe(new EfGlobalListener());

[wangpengxpy]

@ajcvickers use DiagnosticListener implement read/write seperation, for example
```C#
public class DbCommandInterceptor : IObserver>
{
private const string masterConnectionString = "data source=WANGPENG;User Id=sa;Pwd=sa123;initial catalog=Demo1;integrated security=True;";
private const string slaveConnectionString = "data source=WANGPENG;User Id=sa;Pwd=sa123;initial catalog=Demo2;integrated security=True;";
public void OnCompleted()
{
}

    public void OnError(Exception error)
    {
    }

    public void OnNext(KeyValuePair<string, object> value)
    {
        if (value.Key == RelationalEventId.CommandExecuting.Name)
        {
            var command = ((CommandEventData)value.Value).Command;
            var executeMethod = ((CommandEventData)value.Value).ExecuteMethod;

            if (executeMethod == DbCommandMethod.ExecuteNonQuery)
            {
                ResetConnection(command, masterConnectionString);
            }
            else if (executeMethod == DbCommandMethod.ExecuteScalar)
            {
                ResetConnection(command, slaveConnectionString);
            }
            else if (executeMethod == DbCommandMethod.ExecuteReader)
            {
                ResetConnection(command, slaveConnectionString);
            }
        }
    }

    void ResetConnection(DbCommand command, string connectionString)
    {
        if (command.Connection.State == ConnectionState.Open)
        {

            if (!command.CommandText.Contains("@@ROWCOUNT"))
            {
                command.Connection.Close();
                command.Connection.ConnectionString = connectionString;
            }
        }
        if (command.Connection.State == ConnectionState.Closed)
        {
            command.Connection.Open();
        }
    }
}

As above，if i use master  for add operations，but  master need to return primary key,that is to say that need to read slave， i use the following way to achieve 


```C# 
if (!command.CommandText.Contains("@@ROWCOUNT"))
                {
                    command.Connection.Close();
                    command.Connection.ConnectionString = connectionString;
                }

but obviously this is not true about above。how to do it at this time?

[ajcvickers]

@wangpengxpy I don't really understand your question, but changing the connection in this way is not something I would recommend.

[wangpengxpy]

@ajcvickers changing the connection by Database.GetDbConenction??

[ibrahimozgon]

Hi, @ajcvickers
I realized that 'AS Extent1' naming convention is changed in EF Core.
Which pattern can I use to do the same?

[ajcvickers]

@ibrahimozgon I think you will need to look at the kind of queries that you want to change and figure out which kind of pattern you need to match. I'm not sure that there is anything common that can be used as a pivot since EF Core attempts to create more human-readable queries.