Entity Framework, Connection Pooling and connection-scoped variables

[Former Member]

I started to use the EF data provider von SQL Anywhere about 1,5 years ago (version 12.0.1.3152). The application I was developing back then connects to a legacy database.

This database has a functionality to set a custom user name (independent from database user) in a temporary variable, that is initialized on every opened connection (via stored procedure that is set as login_procedure in database options). To realize this there is another stored procedure (let's call it set_current_user(user_name) here) for setting that user name, that has to be called right after opening the connection.

Then for the rest of the lifetime of the connection it is assumed that one can access this variable to get the custom user name, e.g. for use in triggers that set this user name in specific columns on inserting/updating a table.

In the application I developed I had to make use of this functionality too. My solution was to always call the set_current_user(user_name) procedure when I created an EF DbContext to access the database. This worked quite well with the default behavior of the data provider, without me worrying about things like connection pooling.

Recently I updated my development system to use a newer version of the data provider (12.0.1.3851), and noticed that the custom user name wasn't used anymore in the triggers on inserting/updating table rows. Instead the default value that is set in the login_procedure was inserted in the respective columns.

After some debugging using log messages (in login_procedure and set_current_user) I found a difference in connection handling:

Assuming the following (pseudo) code example:

using(var ctx = new EFContext) {
  var result = ctx.SomeDbSet.Where(...).ToList();
  var anotherresult = ctx.SomeOtherDbSet.Single(...);
}

Connection behavior with older data provider:

1. login_procedure is called because in constuctor of EFContext a connection is opened
2. set_current_user(...) is called in constructor of EFContext to set the custom user name
3. the two queries are sent to database (which the same connection created in 1.)

I assume the EF opens a connection on demand, so the reason why login_procedure (1.) is called seems to be the call of set_current_user (2.)

Another interesting point here is, that the connection opened in the very first EFContext seems to be also used in other EFContext instances that are created later in the application workflow. That means actually the call of set_current_user everytime an EFContext is created isn't even necessary, since the same connection is used all the time.

Connection behavior with new data provider (12.0.1.3851 and higher):

1. call to login_procedure (new connection opened for 2.)
2. call to set_current_user (in ctor of EFContext)
3. call to login_procedure (new connection opened for first query)
4. call to login_procedure (new connection opened for second query)

So while I assumed that the same connection is used at least during the lifetime of an EFContext, there is actually a new connection for every statement sent to the database (including the call of set_current_user).

In both test cases I looked at the "ConnPoolHits" database property:

1. (version < 3851) -> always 0 hits
2. (version >= 3851) -> hits > 0 (depending on statements issued by the application)

So I guess in the first case the connection pooling is done on the clientside (as stated in the SQL Anywhere documentation), while in the second case it is done on serverside. Another question on this forum hints, that there were changes of connection pooling behavior in Build 3821.

It seems that it is a lucky coincidence that my solution worked for the older versions of the data provider, but assuming that the pre Build 3821 connection pooling behavior is considered wrong, what would be the best way to achieve the described requirement?

[MCMartin]

this change might especially be a problem for authenticated databases, as it is already difficult to authenticate the connection for the Context, but doing so for the additional created ones will be impossible...

[Former Member]

If you are talking about setting the temporary option "connection_authentication", that is what we also do. I set this option as part of the connection string using the "InitString" parameter. It doesn't make any problems with the new connection pooling behavior.