How do I add a scoped DbContext to my ASP NET Core API Controllers that uses EntityFrameworkCore and SSH tunneling into MySQL?

0

I have the following ConfigureServices() content in my Startup.cs

public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllers();
            services.AddDbContext<GMContext>(options =>
            {

                var myConnectionSettings = Configuration.GetSection("ConnectionSettings").Get<ConnectionSettings>();
                var sshSettings = myConnectionSettings.SSH;
                var databaseSettings = myConnectionSettings.Database;
                string keyFilePath = Path.Combine(Directory.GetCurrentDirectory(), "test4");
                var authMethods = new List<AuthenticationMethod>
                {
                    new PrivateKeyAuthenticationMethod(sshSettings.UserName, new PrivateKeyFile(keyFilePath))
                };
                using (var sshClient = new SshClient(new ConnectionInfo(sshSettings.Server,
                    sshSettings.Port, sshSettings.UserName, authMethods.ToArray())))
                {
                    sshClient.Connect();
                    var forwardedPort = new ForwardedPortLocal(databaseSettings.BoundHost, databaseSettings.Host,
                        databaseSettings.Port);
                    sshClient.AddForwardedPort(forwardedPort);
                    forwardedPort.Start();


                    MySqlConnectionStringBuilder csb;
                    csb = new MySqlConnectionStringBuilder
                    {
                        Server = databaseSettings.BoundHost,
                        Port = forwardedPort.BoundPort,
                        UserID = databaseSettings.UserName,
                        Password = databaseSettings.Password,
                        Database = databaseSettings.DatabaseName
                    };
                    options.UseMySQL(csb.ConnectionString);
                }

            });

I then pick up the context in a controller and attempt to query the context I get a connection denied error:

namespace CoreGMWebAPI.Controllers
{
    [Produces("application/json")]
    [Route("api/[controller]")]
    public class CropController : Controller
    {
        private readonly ILogger<CropController> _logger;
        private readonly GMContext _context;
        public CropController(ILogger<CropController> logger, GMContext context)
        {
            _logger = logger;
            _context = context;

        }
        /// <summary>
        /// Gets a Sample Items
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<List<TCountries>> GetSampleItems()
        {
                var result = _context.TCountries.Take(10).ToList();
                return result;
        }
        
    }
}

The Error:

System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure()' to the 'UseMySql' call. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts. ---> System.AggregateException: One or more errors occurred. (No connection could be made because the target machine actively refused it. 127.0.31.1:54960) ---> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (10061): No connection could be made because the target machine actively refused it. 127.0.31.1:54960 at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw(Exception source) at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult) at System.Net.Sockets.TcpClient.EndConnect(IAsyncResult asyncResult)
at System.Net.Sockets.TcpClient.<>c.b__28_1(IAsyncResult asyncResult) at System.Threading.Tasks.TaskFactory1.FromAsyncCoreLogic(IAsyncResult iar, Func2 endFunction, Action1 endAction, Task1 promise, Boolean requiresSynchronization) --- End of inner exception stack trace --- at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions) at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken) at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout) at MySql.Data.Common.StreamCreator.GetTcpStream(MySqlConnectionStringBuilder settings) at MySql.Data.Common.StreamCreator.GetStream(MySqlConnectionStringBuilder settings) at MySql.Data.MySqlClient.NativeDriver.Open() at MySql.Data.MySqlClient.NativeDriver.Open() at MySql.Data.MySqlClient.Driver.Open() at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings) at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection() at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection() at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver() at MySql.Data.MySqlClient.MySqlPool.GetConnection() at MySql.Data.MySqlClient.MySqlConnection.Open() at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected) at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.InitializeReader(DbContext _, Boolean result) at MySql.Data.EntityFrameworkCore.Storage.Internal.MySQLExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) --- End of inner exception stack trace --- at MySql.Data.EntityFrameworkCore.Storage.Internal.MySQLExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at CoreGMWebAPI.Controllers.CropController.GetSampleItem() in C:\Users\lobos\source\repos\CoreGMWebAPI\CoreGMWebAPI\Controllers\CropController.cs:line 31 at lambda_method(Closure , Object ) at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult() at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Logged|12_1(ControllerActionInvoker invoker) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync() --- End of stack trace from previous location where exception was thrown --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Logged|17_1(ResourceInvoker invoker) at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

HEADERS ======= Accept: application/json Accept-Encoding: gzip, deflate, br Accept-Language: en-US,en;q=0.9,ca;q=0.8,fr;q=0.7,nb;q=0.6 Connection: close Host: localhost:44313 Referer: https://localhost:44313/swagger/index.html User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36 dnt: 1 sec-fetch-site: same-origin sec-fetch-mode: cors sec-fetch-dest: empty sec-gpc: 1

When I run the following quick test inside the ConfigureServices() method in Startup.cs it connects and queries without any issues:

                          ...
                        options.UseMySQL(csb.ConnectionString);

                        var otherOps = new DbContextOptionsBuilder<GMContext>();
                        otherOps.UseMySQL(csb.ConnectionString);
                        using (var context = new GMContext(otherOps.Options))
                        {
                            var testRun = context.TCountries.Take(3).ToList();
                        }

This leads me to suspect that the SSHClient is closing before the Controller can use the dbContext. Should I somehow be passing the SSHClient as well as the dbContext? Any help would be appreciated.

c#
mysql
asp.net-core
ssh
entity-framework-core
asked on Stack Overflow Nov 24, 2020 by user1533613

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0