How can I collect the current SQL Server Session ID from an Entity Framework DbContext?

Question

Is there a way to determine the current SQL Server session ID (@@SPID) for an opened DbContext, short of making a SQL query directly to the database?

If there is, is there any guarantee that the SQL Server session ID will remain the same until the DbContext is released and its connection is released back to the Entity Framework connection pool? Something similar to this:

using (MyEntities db = new MyEntities()) {

    // the following 3 pieces of code are not existing properties and will result in compilation errors
    // I'm just looking for something similar to the following 3 lines
    db.CurrentSessionId; //error
    db.Database.CurrentSessionId; //error
    ((IObjectContextAdapter)db).ObjectContext.Connection.CurrentSessionId; //error

    // the following code will work, but will this session id be the same until the original DbContext is disposed?
    // is there any chance that a db.Database.SqlQuery call will spin off it's own connection from the pool?
    short spid = db.Database.SqlQuery<short>("SELECT @@SPID").FirstOrDefault();
}

score 5 · Accepted Answer · edited May 23 '17 at 12:22

First of all, The Dbcontext alone will NOT open any sql process on your database. The query does.

so in this case when you run SELECT @@SPID you will definitely open a new process with a new ID.

The good news is Entityframework will use the same process to run your subsequent query. So ideally in the same using block you will always get the same @@SPID value.

You can run this query

select *
from    master.dbo.sysprocesses
where program_name = 'EntityFramework'

to observe the current processes on your database associated with Entity Framework.

You can then use the query below to get the SQL statement that associated with specific process. For more information please take a look the accepted answer here: List the queries running on SQL Server

declare
    @spid int
,   @stmt_start int
,   @stmt_end int
,   @sql_handle binary(20)

set @spid = XXX -- Fill this in

select  top 1
    @sql_handle = sql_handle
,   @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end
,   @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end
from    master.dbo.sysprocesses
where   spid = @spid
order by ecid

SELECT
    SUBSTRING(  text,
            COALESCE(NULLIF(@stmt_start, 0), 1),
            CASE @stmt_end
                WHEN -1
                    THEN DATALENGTH(text)
                ELSE
                    (@stmt_end - @stmt_start)
                END
        )
FROM ::fn_get_sql(@sql_handle)

Interesting. I'm still trying to wrap my head around how Entity Framework handles the connection pool. My question stemmed from the fact that I am storing user/device information from a webapi request in a table with @@spid as a primary key followed by a secondary change on another table through EntityFramework. The goal is to get triggers on that table to select the user/device by current @@spid and store audit information about the change. We've found that about 1/50,000 times, the @@spid will have the wrong user/device information stored by the time those triggers execute. — dvlsg, Feb 19 '14 at 19:35

How can I collect the current SQL Server Session ID from an Entity Framework DbContext?

1 Answers1