Entity Framework - Setting session_context using IDbConnectionInterceptor
<p>I'm following <a href="https://docs.microsoft.com/en-us/azure/app-service-web/web-sites-dotnet-entity-framework-row-level-security" rel="noreferrer">this tutorial</a> 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 <code>session_context</code>. To retrieve the user id, it uses static accessor method <code>HttpContext.Current.User.Identity.GetUserId()</code> which is coupled with Asp.Net identity and System.Web namespace.</p>

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

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

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

<pre><code>public void Opened(DbConnection connection, DbConnectionInterceptionContext interceptionContext)
{
    var firstDbContext = interceptionContext.DbContexts.FirstOrDefault(d =&gt; 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();
    }
}
</code></pre>

<p>My code checks whether the DbContexts collection contains the <code>TenantDataContext</code> as the first element and executes the <code>sp_set_session_context</code>. 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 <code>session_context</code> which I don't need. I'm wondering why Microsoft has provided this as a collection property rather than a single <code>DbContext</code> property. This property makes you wonder whether the same connection can be used by multiple DbContexts.</p>

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