How to set CONTEXT_INFO on Entity Framework connection

Question

I'm working on isolating store data in a shared database in an Entity Framework-based application. I want to use SQL Server 2016 row-level security but I'd prefer to have my database connections all use a single user. So I want to set the SQL Server CONTEXT_INFO to a store number for all of my Entity Framework queries. Then RLS can determine if the row belongs to the store number CONTEXT_INFO is set to.

The only idea I have for accomplishing this is to update my DbContext constructor to create a connection itself and execute the SET CONTEXT_INFO statement before passing the connection on to the base constructor. I think I could either add a constructor overload in a partial class for my DbContext to do that, or modify the t4 template to generate the original constructor that way.

Does EF offer a better way to do this? I'm using DB first.

Answer 1

It looks like an implementer of IDbConnectionInterceptor is able to execute some code each time a connection is opened. That allows me to execute a stored procedure to set the CONTEXT_INFO or SESSION_CONTEXT variable. My interceptor looks like this:

public class MAContextInterceptor : IDbConnectionInterceptor
{
    private static ITenantIDProvider _tenantIDProvider;
    public MAContextInterceptor(ITenantIDProvider tenantIDProvider)
    {
        _tenantIDProvider = tenantIDProvider;
    }

    public void Opened(DbConnection connection, DbConnectionInterceptionContext interceptionContext)
    {
        // Set SESSION_CONTEXT to current tenant ID whenever EF opens a connection
        try
        {
            Guid tenantID = _tenantIDProvider == null ? Guid.Empty : _tenantIDProvider.GetTenantID();

            if (tenantID != Guid.Empty)
            {
                DbCommand cmd = connection.CreateCommand();
                cmd.CommandText = "ma_setcontextinfo";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@tenantID";
                param.Value = tenantID;
                cmd.Parameters.Add(param);
                cmd.ExecuteNonQuery();
            }
        }
        catch (System.NullReferenceException)
        {
            //log error
        }
    }
}

Since this is in a data layer class library, I added an ITenantIDProvider which allows the layer above to provide an implementation that provides the tenant ID. This ultimately gets set in my Global.asax when the application starts. It also calls System.Data.Entity.Infrastructure.Interception.DbInterception.Add(new MAContextInterceptor(tenantIDProvider)); to register the interceptor.

It looks like it will apply to all DbContexts, which I could see being problematic, but for my purposes this works. This page pretty much spelled it all out. I don't know why it didn't come up in any of my searches prior to asking this question!