Data store command interception

[Bartmax]

Is there any extensible point on where EF hits the db, something that can be overridable to log or do anything everytime a hit to the db.

im wondering of something like

override OnDatabaseHit(Operation op, Context context) {
     if (typeof(op) == Query | SaveChanges | ... {
         Console.WriteLine(context.Query)
     }
}

if not, take it as a suggestion for EF7 :)

[rowanmiller]

This is more generally covered by #626 but we'll leave this one active to track the particular scenario of intercepting database commands. Something like IDbCommandInterceptor from EF6.x.

[NikolajDL]

Is there a time frame for something like IDbCommandInterceptor to be added to EF7? Using EF6 with ASP.NET 5 is a pain compared to EF7, but I really need the IDbCommandInterceptor to implement seamless row level security in EF7.

[rowanmiller]

@excolo it will be post 1.0.0 release. You may be able to achieve something similar now if you start mucking around with replacing/wrapping low level services... but this item is about building a more usable experience.

[MariovanZeist]

@rowanmiller Can you give a hint on which low level service to wrap? I tried overriding the Open function in SqlServerConnection but it seems like this class is used for accessing metadata and not data related to a DbContext

    public class TestSqlServerConnection : SqlServerConnection
    {
        public TestSqlServerConnection(IDbContextOptions options, ILogger<SqlServerConnection> logger) 
            : base(options, logger)
        {
        }

        public override void Open()
        {
            base.Open();
            //Set session context
            var cmd = DbConnection.CreateCommand();
            cmd.CommandText = "EXEC sp_set_session_context @key=N'CompanyId', @value=1";
            cmd.ExecuteNonQuery();
        }
    }

[MariovanZeist]

It might have to do with how I register the above class (I might have to do some more detective work to get it to work)
I register the above class in my ConfigureServices function (before the call to AddEntityFramework) with

services.TryAddScoped<ISqlServerConnection,TestSqlServerConnection>();

But when I run the following code from dbContext it will still return an instance of SqlServerConnection instead of TestSqlServerConnection

    public class TestDbContext : DbContext
    {
        public ISqlServerConnection TestConnection()
        {
            return this.GetService<ISqlServerConnection>();
        }
    }

[rowanmiller]

@MariovanZeist use AddScoped instead of TryAddScoped

[MariovanZeist]

@rowanmiller
My issue was actually RTFM related. Instead of creating the DbContext using DI, I was creating it by hand (using Activator.CreateInstance()) which I obviously shouldn't do. That's why it didn't pick up my ISqlServerConnection implementation.

I think i fell into the trap referenced by #4668

[rowanmiller]

@MariovanZeist 👍 - the changes from #4668 are being worked on at the moment

[wpostma]

I put a question on stack-overflow to seek alternative techniques for EF Core.

http://stackoverflow.com/questions/37660746/can-i-configure-an-interceptor-yet-in-entityframework-core

Instead of a heavyweight framework approach perhaps whatever replaces interceptors could be a very lightweight and simple pattern.

[tourili]

Hi there
What about Intercepting connection level to support Row Level Security. I checked the latest release, but still not supported yet.
I'm working around this lack by connecting the legacy handler Database.GetDbConnection().StateChange to monitor the connection state in order to inject my session context:

this.Database.GetDbConnection().StateChange += (s, e) =>
{
    if (e.OriginalState != System.Data.ConnectionState.Open && e.CurrentState == System.Data.ConnectionState.Open)
    {
        DbCommand cmd = this.Database.GetDbConnection().CreateCommand();
        cmd.CommandText = "EXEC sp_set_session_context @key=N'ObjectId', @value=@UserId";
        ....
    }
};

on a derived DbContext constructor.

I would like to see a better approach using DI to pipe this injection?
Thanks