Connection Pool Management with ElasticContext EF

Question

After following a few examples from Microsoft I've implemented an Elastic version of the DbContext that gets its connection string from the Elastic ShardMapManager dependent on the current users Tenant (or Customer) Id.

This technically works and I've deployed it to my Azure account. My concern is over the Connection Pool management now that I've overridden the default context connection creation mechanism. Also I am not sure how the connection is managed each time shardMap.OpenConnectionForKey is called which is on every request (see my ninject settings below).

Yesterday after some light testing my web app failed with the following message:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.`

This could have been a one-off as I can't recreate it today but I would like to make sure that the connection pool is being used efficiently as the last thing I want is that happening when real users start hammering the system.

The code that it failed on is commented below in the full code for my ElasticScaleContext:

public class ElasticScaleContext<T> : DbContext
{
    /// <summary>
    /// 
    /// </summary>
    /// <param name="shardMapManager">This is injected - only one of these exists per Application (Singleton)</param>
    /// <param name="customerId">The Shard Key is the Customer Id</param>
    /// <param name="shardConnectionString">The connection string for the Shard - this should only have the credentials and NOT any datasource. The correct datasource and initial catalog are returned by the Shard Map</param>
    /// <param name="metadataWorkSpaceConnectionString">Metadata required by EF model first cannot be passed in the shard connection string, it is passed here and used to return the MetadataWorkspace - no actual connection is created</param>
    public ElasticScaleContext(ShardMapManager shardMapManager, IPrincipal user, string shardConnectionString, string metadataWorkSpaceConnectionString)
      : base(CreateEntityConnection(shardMapManager, user, shardConnectionString, metadataWorkSpaceConnectionString), true)
    {
    }

    private static DbConnection CreateEntityConnection(ShardMapManager shardMapManager, IPrincipal user, string shardConnectionString, string metadataWorkSpaceConnectionString)
    {

        int shardKey = 0; // Default just to get a valid connection string on login page (it's never actually used)

        if (user != null && user.Identity.IsAuthenticated)
        {
            shardKey = user.Identity.GetCustomerId();
        }

        // Loads the Shard Map from the Shard Manager
        // This has the details of which shards are located on which databases
        ListShardMap<T> shardMap = shardMapManager.GetListShardMap<T>(AppConfig.ShardMapName);

        // No initialization
        Database.SetInitializer<ElasticScaleContext<T>>(null);

        // Create Elastic Scale SqlConnection
        // ******* FAILED HERE *********
        var shardConnection = shardMap.OpenConnectionForKey(shardKey, shardConnectionString, ConnectionOptions.None);

        // Allocate metadata workspace via an EF connection 
        var efConnection = new EntityConnection(metadataWorkSpaceConnectionString);

        // Create Entity connection that holds the sharded SqlConnection and metadata workspace
        var workspace = efConnection.GetMetadataWorkspace();
        EntityConnection entcon = new EntityConnection(workspace, shardConnection);

        return entcon;

    }

}

I am using Ninject with ShardMapManager being injected as a Singleton:

// Only ever create one Shard Map Manager
kernel.Bind<ShardMapManager>().ToMethod(context =>
{
   return ShardMapManagerFactory.GetSqlShardMapManager(AppConfig.ConnectionString, ShardMapManagerLoadPolicy.Lazy);
}).InSingletonScope();

and the Context is created per request:

kernel.Bind<DbContext>().ToMethod(ctx =>
{
   return new ElasticScaleContext<int>(kernel.Get<ShardMapManager>(), kernel.Get<IPrincipal>(), AppConfig.BaseConnectionString, AppConfig.SCSMetaDataConnectionString);
}}).InRequestScope();

So a few questions:

1. Should the Connection I am creating within the Context be disposed off as normal?

2. Does anyone know how the ShardMapManger connections are managed?

3. Could the code below (necessary because of my Model First EF approach) be opening and then not closing connections?

UPDATE - After advice from @Evk (see comments) I have modified the constructor call to EntityConnection to pass true for entityConnectionOwnsStoreConnection which should allow it to properly close the store connection after use, see below. I really need a way of monitoring the connection pool to see if this has any effect:

 var efConnection = new EntityConnection(metadataWorkSpaceConnectionString);

  // Create Entity connection that holds the sharded SqlConnection and metadata workspace
  var workspace = efConnection.GetMetadataWorkspace();
  EntityConnection entcon = new EntityConnection(workspace, shardConnection. true);

Finally, is there any way for me to monitor and view the current status of the connections on an Azure Elastic SQL Pool?

I realise that's a lot of questions, but I'm after any information on this area - there really isn't that much existing information on the web.

Additional Info: The solution uses EF6, SQL Server 2014, MVC 5.0

Answer 1

As figured out in comments, in CreateEntityConnection method you create EntityConnection from your underlying connection, but do not set important parameter "entityConnectionOwnsStoreConnection" to true. That means entity connection is not responsible to manage your store connection and will not close it, so your connections will leak. To fix, use another EntityConnection constructor:

var entcon = new EntityConnection(workspace, shardConnection, true);

Then, when context is disposed, it will dispose your EntityConnection, which in turn will dispose underlying database connection.