Entity Framework - Setting session_context using IDbConnectionInterceptor

Question

I'm following this tutorial in order to use Row Level security in SQL Server via Entity Framework 6 CodeFirst. The tutorial code sample shows how to use IDbConnectionInterceptor and set the current user id in session_context. To retrieve the user id, it uses static accessor method HttpContext.Current.User.Identity.GetUserId() which is coupled with Asp.Net identity and System.Web namespace.

In my multi-tenant web app, I wanted to have the tenantId injected into the DbConnectionInterceptor using Unity (without creating hard-coupling with HttpContext) and set the tenantId in the session_context. I found out that the DbConnectionInterceptor needs to be registered globally (eg. at application startup) and therefore you cannot have Unity create DbConnectionInterceptor instance per request.

I also have 2 DbContexts in my solution representing 2 different databases (Tenant database and a system database) and I only want to apply session_context to the Tenant database only.

It seems that the only option remaining to me is have the tenantId injected into the DbContext isntance via Unity and access the DbContext instance inside the Opened() method of the DbConnectionInterceptor. For this purpose I thought of using the interceptionContext parameter in the Opened() method. interceptionContext has a DbContexts(plural) property. There's no documentation on this so I assumed something like this would work:

public void Opened(DbConnection connection, DbConnectionInterceptionContext interceptionContext)
{
    var firstDbContext = interceptionContext.DbContexts.FirstOrDefault(d => d is TenantDataContext);
    if (firstDbContext != null)
    {
        var dataContext = firstDbContext as TenantDataContext;
        var tenantId = dataContext.TenantId;

        DbCommand cmd = connection.CreateCommand();
        cmd.CommandText = $"EXEC sp_set_session_context @key=N'TenantId', @value={tenantId};";
        cmd.ExecuteNonQuery();
    }
}

My code checks whether the DbContexts collection contains the TenantDataContext as the first element and executes the sp_set_session_context. But what I'm worried about is whether there's any chance for both DbContexts to be there at the same time? If that was the case, the connection to my other database would also set the session_context which I don't need. I'm wondering why Microsoft has provided this as a collection property rather than a single DbContext property. This property makes you wonder whether the same connection can be used by multiple DbContexts.

Is there anyone who has achieved what I want? Any explanation on this interceptionContext would also be helpful for me.

Bennington

I'm facing the same issue. Did you ever figure this out? – Vansickle

Unfortunately no. My code is still the same. We haven't gone to production yet, but so far didn't encounter a visible issue with this. – Bennington

Answer 1

You can use the Connection_StateChaned event of your DbContext if you are using EF like so.

 static void Main(string[] args)
    {               
        using (var db = new AdventureWorks2016CTP3Entities())
        {
            db.Database.Connection.StateChange += Connection_StateChange;
            db.Database.Log = (log) => System.Diagnostics.Debug.WriteLine(log);

            var purchase = db.SalesOrderHeader.Select(i => i.SalesPersonID);

            foreach (var m in purchase)
            {
                Console.WriteLine(m);
            }
        }

    }

    private static void Connection_StateChange(object sender, System.Data.StateChangeEventArgs e)
    {
        if(e.CurrentState == System.Data.ConnectionState.Open)
        {
            var cmd = (sender as System.Data.SqlClient.SqlConnection).CreateCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = "exec sp_set_session_context 'UserId', N'290'";

            cmd.ExecuteNonQuery();
        }
    }

Nailbrush

We are injecting the dbContext through Autofac, so we'd have to find a way to add the event handler through Autofac registration. Thoughts? – Vansickle

I have no experience with Autofac but there should be a way to instantiate the dbContext manually and attach events then allow Autofac to inject that instance. – Nailbrush

Answer 2

I realize this is an older question, but figured I would post our solution for those looking for one. We are using interceptors to Inject a SQLServer session_context statement into the commands/connections running through EF.

In our case, we had to create Interceptors for DbCommand and DbConnection to handle both EF Linq queries and raw SQL queries that run through Commands. These Interceptor classes implement IDbCommandInterceptor and IDbConnectionInterceptor respectively.

For DbCommandInterceptor, we use the SqlCommand.CommandText to prepend our EXEC sp_set_session_context raw SQL to each command coming through the interceptor.

public class SessionContextDbCommandInterceptor : IDbCommandInterceptor

For DbConnectionInterceptor, we implement the Opened method and execute a SqlCommand against the connection that runs our sp_set_session_context SQL.

public class SessionContextDbConnectionInterceptor : IDbConnectionInterceptor
{
    public void Opened(DbConnection connection, DbConnectionInterceptionContext interceptionContext)
    {...}

We then created a DbConfiguration class that adds the interceptors within the constructor:

public class SessionContextConfiguration : DbConfiguration
{
    public SessionContextConfiguration()
    {
        AddInterceptor(new SessionContextDbConnectionInterceptor());
        AddInterceptor(new SessionContextDbCommandInterceptor());
    }
}

Then add this DbConfiguration class to our DbContext class via the DbConfigurationType Attribute as well as to our web.config:

[DbConfigurationType(typeof(SessionContextConfiguration))]
public class MyContext : DbContext

<entityFramework codeConfigurationType="MyAssembly.SessionContextConfiguration, MyAssembly">

We inject our DbContexts using Autofac as we normally would and the interceptors are automatically added to the DbContext instances because of the Configuration class.

Vansickle

I was searching for a way to do this but trying to not "waste" a round-trip to the database everytime a connection is opened. I was trying to do it at the command interceptor concatenating it before the statement, but it would be better to do only it only for the first command for every opened connection. I also tried to look about "opening" a connection already with a session_context set, something like it would be in a connection string, but I guess this doesn't exist. – Pall