Entity Framework Core connections management

0

I'm running a C# (v2.2) application that accessing my database (MySQL) using Entity Framework.

This is my code:

public static async Task<int> IncerementByAsync(string id, int incrBy, string counterType)
{
    using (var context = new CacheDbContext())
    {
        int result = 0;

        using (var command = context.Database.GetDbConnection().CreateCommand())
        {
            if (command.Connection.State != ConnectionState.Open)
            {
                Console.WriteLine($"Connection staus is {command.Connection.State.ToString()}. Opening");
                context.Database.OpenConnection();
            }

            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = $"Counter_increment2";
            command.Parameters.Add(new MySqlParameter("_Id", id));
            command.Parameters.Add(new MySqlParameter("_CounterType", counterType));
            command.Parameters.Add(new MySqlParameter("_IncrBy", incrBy));
            command.Parameters[command.Parameters.Add(new MySqlParameter("_NewValue", result))].Direction = ParameterDirection.Output;

            await command.ExecuteNonQueryAsync().ConfigureAwait(false);

            return (int)command.Parameters[3].Value; // Read output parameter
        }
    }
}

I'm running the stored procedure Counter_increment2 and read the OUT parameter from the response.

When I tested it on the development environment - it's working fine. When tested it on the production environment, when a lot of calls are being made, I started to get errors from time to time:

MySql.Data.MySqlClient.MySqlException (0x80004005): Connect Timeout expired.

System.OperationCanceledException: The operation was canceled.

at System.Threading.SemaphoreSlim.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ConnectionPool.cs:line 46
at MySql.Data.MySqlClient.MySqlConnection.CreateSessionAsync(Nullable1 ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlConnection.cs:line 507
at MySql.Data.MySqlClient.MySqlConnection.CreateSessionAsync(Nullable
1 ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlConnection.cs:line 523
at MySql.Data.MySqlClient.MySqlConnection.OpenAsync(Nullable1 ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlConnection.cs:line 232
at MySql.Data.MySqlClient.MySqlConnection.Open() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlConnection.cs:line 218
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected) at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected) at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func
3 operation, Func`3 verifySucceeded) at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.OpenConnection(DatabaseFacade databaseFacade) at ***.IncrementByAsync(String id, Int32 incrBy, String counterType) in /****/CounterOperations.cs:line 46

My DB connection string limits the number of concurrent connections for a certain number. I guess that at some point, all the optional connections were used and I got connect time exception.

Since I'm using the EF connection pool, I expected to reuse connections from the pool. Surprisingly, each call to the function was opened a new connection. This is the print that I got every call:

Connection status is Closed. Opening

This mean that a connection is opened every time I enter this code.

It must be some sort of wrong code here, otherwise, I hadn't experienced those connection problems. But what I'm doing wrong? How to manage my database connection?

Update 1

According to @Ivan comment, I modified the code to this:

public static async Task<int> IncerementByAsync(string id, int incrBy, string counterType)
{
    using (var context = new CacheDbContext())
    {
        int result = 0;

        using (var command = context.Database.GetDbConnection().CreateCommand())
        {
            bool wasOpen = command.Connection.State == ConnectionState.Open;

            if (!wasOpen)
            {
                Console.WriteLine($"Connection status is {command.Connection.State.ToString()}. Opening");
                context.Database.OpenConnection();
            }

            try
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = $"Counter_increment2";

                command.Parameters.Add(new MySqlParameter("_Id", id));
                command.Parameters.Add(new MySqlParameter("_CounterType", counterType));
                command.Parameters.Add(new MySqlParameter("_IncrBy", incrBy));
                command.Parameters[command.Parameters.Add(new MySqlParameter("_NewValue", result))].Direction = ParameterDirection.Output;

                await command.ExecuteNonQueryAsync().ConfigureAwait(false);

                return (int)command.Parameters[3].Value; // Read output parameter
            }
            finally
            {
                if (!wasOpen) 
                   command.Connection.Close();
            }
        }
    }
}

After this change, I still can see the same behaviour (Connect timeout exception). What else should I check?

c#
entity-framework
entity-framework-core
asked on Stack Overflow Apr 26, 2020 by No1Lives4Ever • edited Apr 26, 2020 by marc_s

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0