MVC: EF6 Connection Pooling and SQL Server CONTEXT_INFO

Question

In an ASP.NET MVC application, I'm trying to use SQL Server's CONTEXT_INFO to pass the currently logged in user so my audit triggers record not only the web server login, but also the login of the site.

I'm having trouble being certain that the current user will always be fed into the database server context though.

On the backend I have everything set up, a sproc to set the context, a function to pull it and DML triggers to record, no problem.

The app end is a bit more involved. I subscribe to the Database.Connection.StateChange event so I can catch each newly opened connection and set this context accordingly.

Additionally, to be able to retrieve the current login ID of the MVC site in the data layer (which has no access to the web project), I supply a delegate to the EF constructor that will return the user ID. This also means that any other peripheral projects I have set up require this dependency as well, and it keeps most of the implementation detail out of my hair during the web dev:

public class CoreContext : DbContext
{

    Func<int> _uidObtainer;

    public CoreContext(Func<int> uidObtainer) : base(nameof(CoreContext)) { construct(uidObtainer); }
    public CoreContext(Func<int> uidObtainer, string connection) : base(connection) { construct(uidObtainer); }

    void construct(Func<int> uidObtainer) {

        // disallow updates of the db from our models
        Database.SetInitializer<CoreContext>(null);

        // catch the connection change so we can update for our userID
        _uidObtainer = uidObtainer;
        Database.Connection.StateChange += connectionStateChanged;

    }

    private void connectionStateChanged(object sender, System.Data.StateChangeEventArgs e) {

        // set our context info for logging

        if (e.OriginalState == System.Data.ConnectionState.Open || 
            e.CurrentState != System.Data.ConnectionState.Open) {
            return;
        }

        int uid = _uidObtainer();

        var conn = ((System.Data.Entity.Core.EntityClient.EntityConnection)sender).StoreConnection;
        var cmd = conn.CreateCommand();
        cmd.CommandText = "audit.SetContext";
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@DomainUserID", uid));
        cmd.ExecuteNonQuery();

    }

    // etc etc...

In my MVC project, I'll have code that looks like this:

context = new Data.CoreContext(() => AppService.UserID());

(making use of a readily accessible method to pass as delegate, which in turn reads from HttpContext.Current.User)

This is all shaping up nicely, except one unknown:

I know that it's possible for a EF Context instance to span multiple logged in users as this lives as part of the IIS app pool and not per HttpContext

What I don't know is enough about connection pooling and how connections are opened/re-opened to be safe in knowing that for each time my StateChange handler runs, I'll actually be retrieving the new UserID from the delegate.

Said differently: is it possible for a single connection to be open and used over the span of two separate HttpContext instances? I believe yes, seeing as how there's nothing to enforce otherwise (at least not that I'm aware of).

What can I do to ensure that each connection is getting the current HttpContext?

(possibly pertinent notes: There's no UoW/Repository pattern outside of EF itself, and data contexts are generally instantiated once per controller)

score 0 · Answer 1 · edited May 23 '17 at 11:51

I see: the one context per controller is generally incorrect. Instead I should be using one context per request, which (besides other advantages), ensures my scenario operates correctly as well.

I found this answer, which explains the reasoning behind it: One DbContext per web request... why?

And I found this answer, which explains quite succinctly how to implement via BeginRequest and EndRequest: One DbContext per request in ASP.NET MVC (without IOC container)

(code from second answer pasted below to prevent linkrot)

protected virtual void Application_BeginRequest()
{
    HttpContext.Current.Items["_EntityContext"] = new EntityContext();
}

protected virtual void Application_EndRequest()
{
    var entityContext = HttpContext.Current.Items["_EntityContext"] as EntityContext;
    if (entityContext != null)
        entityContext.Dispose();
}

And in your EntityContext class...

public class EntityContext
{
    public static EntityContext Current
    {
        get { return HttpContext.Current.Items["_EntityContext"] as EntityContext; }
    }
}

MVC: EF6 Connection Pooling and SQL Server CONTEXT_INFO

1 Answers1