CODEWITHSUNDEEP
c#asp.netsql-serverentity-frameworkado.net
Roland
Roland

Reputation: 487

Entity Framework connection pooling: how to inject UserId into SQL Servers session_context?

I'm working on a multi-user ASP.Net web application with SQL Server (v2016 on developer machine, production runs on Azure). Within my application I utilize EF-6 with a single technical user connecting to database. Connection pooling is active.

For auditing purposes within this application I want to add the current UserId to a column of each table of the database (I also want to utilize the UserId for further stuff, but this doesn't add any value to my discussion).

As I do not want to pass the UserId within every LINQ statement of my app, I figured that it would be great to utilize SQL Servers session_context functionality (As described here: https://lennilobel.wordpress.com/2016/02/29/sharing-state-in-sql-server-2016-with-session_context/) and make the database state-aware by setting the userId into a session_context variable at runtime of the app.

After doing so, I am able to utilize this session_context variable within DEFAULT values of the user column of my tables via my small database function GetUserId (which is a quite robust solution from my perspective). Function looks as follows:

CREATE FUNCTION [dbo].[GetUserId] ()
RETURNS INT
AS
BEGIN
    -- -1 is means "unknown user" (e.g. for transactions executed via SQL Mgmt Studio)
    RETURN COALESCE(CAST(SESSION_CONTEXT(N'UserId') AS INT), -1)
END

Now here comes the thing I am not sure about:

Where is the best point to inject my session_context variable within Entity Framework (or ADO.Net in general)?

Currently I overwrite the SaveChanges() method of the DbContext and execute a stored procedure before the base class SaveChanges is called:

    public override int SaveChanges()
    {
        SetUserIdContext();
        return base.SaveChanges();
    }

    private void SetUserId()
    {
        this.Database.OpenConnection();

        //Set the user context
        using (var cmd = this.Database.GetDbConnection().CreateCommand())
        {
            var parm = cmd.CreateParameter();
            parm.ParameterName = "@userId";
            parm.Value = this.UserId;

            cmd.CommandText = "SetUserId";
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(parm);

            cmd.ExecuteNonQuery();
        };
    }

Currently, this approach works, but it's definitely not the best way of doing that. I figured that I might overwrite the constructor in some way, but I am not sure how to do this properly. (Also, as I do not want to deactivate connection pooling, overwriting the connections open command seems to be wrong as well)

Does anyone have a better idea on how or where I should inject my stored procedure call, so that I can ensure the state is always correct for the user using my application?

P.s: For the stake of completeness, here is the stored procedure I call to set the UserId:

CREATE PROCEDURE [dbo].[SetUserId]
    @userId INT
AS
BEGIN
    SET NOCOUNT ON;

    EXEC sp_set_session_context 'UserId', @userId; 
END

Thank you very much for your ideas on this!

Cheers Roland

Upvotes: 4

Views: 1414

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89444

Open the connection in the DbContext constructor and set the UserId there. Connection pooling will not be significantly affected unless you keep the DbContext alive for a long time (which you should never do in an ASP.NET application).

how can I guarantee that the connection I've opened in the constructor (and passed the UserId to) is still the same used during the SaveChanges call later on in my business logic?

If the connection is explicitly opened in the Constructor (or opened and passed to the constructor), then the DbContext will not close and re-open it.

Upvotes: 2

Related Questions