Loading...

Use Entity Framework to connect to MySQL database running in a Docker container


I have started to learn ASP.NET Core MVC and I am trying to build an app which makes use of a database. I wanted to integrate an ORM, so I picked up Entity Framework Core.

I have prepared a project, following this tutorial. I have successfully performed the migration using

dotnet ef migrations add Initial --context ReceptionistContext

but I am unable to update the database running inside my Docker container. I wanted to perform this action by using

dotnet ef database update --context ReceptionistContext

I am getting this error:

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 2.2.3-servicing-35854 initialized 'ReceptionistContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None

System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 35 - An internal exception was caught)

System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (00000005, 6): No such device or address

at System.Net.Dns.InternalGetHostByName(String hostName)
at System.Net.Dns.GetHostAddresses(String hostNameOrAddress) at System.Data.SqlClient.SNI.SNITCPHandle.Connect(String serverName, Int32 port, TimeSpan timeout) at System.Data.SqlClient.SNI.SNITCPHandle..ctor(String serverName, Int32 port, Int64 timerExpire, Object callbackObject, Boolean parallel) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected) at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass18_0.<Exists>b__0(DateTime giveUp) at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_02.b__0(DbContext c, TState s) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, Func2 operation, Func2 verifySucceeded, TState state) at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists(Boolean retryOnNotExists) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists() at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists() at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration) at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:00000000-0000-0000-0000-000000000000
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 35 - An internal exception was caught)

The docker-compose.yml:

version: "3"
services:
    db:
      # get the image
      image: mysql:5.7
      # define the container name
      container_name: "mysql_database"
      # set up the envinronment
      environment:
        MYSQL_DATABASE: 'hospital_management'
        # Set up a user
        MYSQL_USER: 'app'
        # Set up the password
        MYSQL_PASSWORD: 'password'
        # Password for root access
        MYSQL_ROOT_PASSWORD: 'root'
      # persists the data outside the container
      volumes:
        - ./data:/var/lib/mysql
      # map the ports host:container
      ports:
        - "3308:3306"

The appsettings.json:

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "ReceptionistContext": "Data Source=127.0.0.1:3608"
  }
}

Finally, app.csproj:

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>netcoreapp2.2</TargetFramework>
    <AspNetCoreHostingModel>InProcess</AspNetCoreHostingModel>
  </PropertyGroup>


  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.App" />
    <PackageReference Include="Microsoft.AspNetCore.Razor.Design" Version="2.2.0" PrivateAssets="All" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SQLite" Version="2.2.3" />
    <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.2.3" />
    <PackageReference Include="MySql.Data.EntityFrameworkCore" Version="8.0.15" />
  </ItemGroup>

P.S. I have ensured that the database can be reached outside the database and I also used MySql workbench to connect to it. I have also made sure that the container was running when I tried to do the update.

Thanks!

- - Source

Answers

answered 1 week ago swapmeet_Lou #1

I think Chris Pratt nailed it above. I just spent hours learning .NET MVC using EF6. I used a MS SQL DB. I don't know much about Docker or why you're using it. If all you're doing is learning the framework, there are simpler ways to do so.

The way I learned was you can either add a new ADO.NET Entity Data model and implement (ITS) model connections or, open server explorer, create a new data connection and connect to your table(s). Create a connectionstring in your web.config file like this.

<connectionStrings>
<add name="Db" connectionString="server=SERVERNAME;database=DBNAME;Integrated Security=true;" providerName="System.Data.SqlClient" />
<add name="GTU_AppsConnectionString" connectionString="Data Source=SERVERNAME;Initial Catalog=DBNAME;Integrated Security=True" providerName="System.Data.SqlClient" />

MySQL is really not much different:

<add name="MySqlConnectionString" connectionString="server=127.0.0.1;User Id=root;password=bpdash;database=sample"
 providerName="MySql.Data.MySqlClient" />

From there you create your models and move on.

answered 1 week ago Andre Kraemer #2

There are several issues with your code / configuration.

The first thing is that you get an exception that says that your context is using a Microsoft.EntityFrameworkCore.SqlServer provider. This means that you try to connect to a Microsoft SQL Server instead to MySql.

The reason is that in your context class you probably have a line like this:

options.UseSqlServer(Configuration.GetConnectionString("ReceptionistContext")));

what you do need however is a config call that says that you want to use MySql:

options.UseMySql(Configuration.GetConnectionString("ReceptionistContext")));

The next thing is that currently you don't specify a database, a user name or a password in your connection string:

"ReceptionistContext": "Data Source=127.0.0.1:3608"

and you're trying to connect to port 3608 but in your docker compose file you're mapping the port 3308

  ports:
    - "3308:3306"

so try the following for your connection string

"ReceptionistContext": "Server=127.0.0.1;Port=3308;Database=hospital_management;Uid=app;Pwd=password"

this should match your docker compose config:

Environment:
    MYSQL_DATABASE: 'hospital_management'
    # Set up a user
    MYSQL_USER: 'app'
    # Set up the password
    MYSQL_PASSWORD: 'password'

answered 6 day ago CyberFox #3

As everyone mentioned above, I had no connection strings defined. Thus, I added those line to appsettings.json:

"AllowedHosts": "*",
  "ConnectionStrings": {
    "HMContext": "Server=127.0.0.1;Port=3308;Database=hospital_management;Uid=app;Pwd=password"
  }

The other problem was that I was using UseSql insted of UseMySql in Startup.cs.

I still had issues. It was due to the connection strings not matching the signature of UseMySql(the above lines are correct).

After all of those, the app connected to the docker database, but I still had an error related to the migrations table missing. It seems to be a bug, but it can be solved by running the following script:

CREATE TABLE `__EFMigrationsHistory` ( `MigrationId` nvarchar(150) NOT NULL, `ProductVersion` nvarchar(32) NOT NULL, PRIMARY KEY (`MigrationId`) );

comments powered by Disqus