Entity Framework Core Error: An error occurred using the connection to database '' on server 'localhost'

Question

I have made an ASP.NET Core 3.1 Web-based application and want to use MySQL as the database.

I have been following along with some YouTube tutorials on creating MySQL database with ASP.NET Core 3.1 [code first approach] including a tutorial from this site:

https://docs.microsoft.com/en-us/aspnet/core/data/ef-rp/intro?view=aspnetcore-3.1&tabs=visual-studio

I have created a DataModel Class, added a service to UseMySQL to the Startup.cs Class and created an AppDBContext Class that implements DbContext Class.

When I run this command in the Package Manager Console: Add-Migration InitialDatabase the application is creating a migration successfully.

When I run update-database it is throwing this exception:

fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]
      An error occurred using the connection to database '' on server 'localhost'.
An error occurred using the connection to database '' on server 'localhost'.
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.

When I call the EnableRetryOnFailure(); function as required, I am facing this exception:

fail: Microsoft.EntityFrameworkCore.Database.Connection[20004] An error occurred using the connection to database '' on server 'localhost'. An error occurred using the connection to database '' on server 'localhost'.

What could be the issue? Where am I getting it wrong?

If you have links to useful articles about using MySQL Database with ASP.NET Core I would appreciate or your help on this particular issue.

I am using Visual Studio IDE 2019 and ASP.NET Core 3.1.1

Additional Code:

This is the Startup.cs Class:

private IConfiguration _configuration;
public Startup(IConfiguration configuration)
{
    _configuration = configuration;
}

// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();

    // database connection string configuration  
    services.AddDbContextPool<AppDBContext>(options => options.UseMySql(_configuration.GetConnectionString("DatabaseConnectionString"),
        mySqlOptionsAction: options => { options.EnableRetryOnFailure(); }
        ));

    services.AddMvc();
}

This is the connection string in appsettings.json:

  "ConnectionStrings": {
    "DatabaseConnectionString": "Server=localhost;Database=MyAppDB;user=root;Password=123;"
  }

Solution 1

You can try this way

1. Install package Pomelo.EntityFrameworkCore.MySQL

2. Add services at Startup.cs

   services.AddCors(); services.AddDbContext(options => options.UseMySql(Configuration.GetConnectionString("DatabaseConnectionString")));

3. change connection string at appsettings.json

   "ConnectionStrings": { "DatabaseConnectionString": "server=localhost;port=3306;database=MyAppDB;user=root;password=" }

   *change the port number according to your MySQL server

   4.Run these commads at Package Manager Console for data migration Add-Migration InitialCreate

   Update-Database

You can look at the project at github, for better understanding

Solution 2

I think I found the root of the problem. This error also occurs when the connection to the database couldn't have been established. Make sure the information in your connection string is correct. This error message is very misleading, I spent couple of hours figuring this out because of it.

Comments

[lurker]

The message indicates you have an empty connection string. What does your config file look like? (should be called ...EntityFramework.dll.config or web.config or App.config).

[Bradley Grainger]

Are you using MySql.Data.EntityFrameworkCore or Pomelo.EntityFrameworkCore.MySql?

[Russell Chidhakwa]

Those are very good questions luker and Bradley. Let me post more code. I am using Pomelo.EntityFrameworkCore.MySql from Nuget

[Russell Chidhakwa]

@luker: I have edited the question and added more code. I am using appsettings.json to store Database Connection string

[Russell Chidhakwa]

@Bradley: I am using Pomelo.EntityFrameworkCore.MySql

[Alvan Rahimli]

Hi. Could you solve issue? I am also facing this issue.

[Russell Chidhakwa]

@Alvan Rahimli: I am yet to try the solutions offered here. Please try the solutions offered here and get back with feedback whether it worked for you. Also see the code by: YankTHEcode, Abdullah and the findings of Dominik.

[Alvan Rahimli]

I already solved problem. Some how i was not paying attention to server address. i was writing [email protected] instead of just 0.0.0.0 it was dumb typo. By the way, problem occurs when ef can't establish connection.

[Russell Chidhakwa]

@Rahimli: I appreciate your feedback.

[Russell Chidhakwa]

Let me try your recommendation

[Russell Chidhakwa]

The recommendation has not solved the issue unfortunately. Maybe there is somewhere I am missing it. I am still getting the exception.

[YankTHEcode]

Have you tried using integrated security instead of username and password in your connection string? And then try the above workaround command? I would first ensure that your connection string is correct!

[YankTHEcode]

I have updated my answer with a link. Try to confirm your connection string value following that post.

[Russell Chidhakwa]

Okay. I will try this approach

[Russell Chidhakwa]

I am still getting the exception. When I use integrated security I get an error stating that integrated security is not supported. I wonder if EF Core 3.1 has issues with .NET Core 3 because I tried the demo tutorial from Microsoft and EF Website and still get the exceptions. I appreciate your help this far.

[YankTHEcode]

Can you share your db context file and the migration file that was produced?

[Russell Chidhakwa]

I have decided to use SQLite with EF Core and ASP.NET Core 3.1. I was frustrated with the exceptions. I will post the DbContext file and Migration though. I appreciate

[Russell Chidhakwa]

I will try your approach and I really appreciate your time and response to this question.

[Russell Chidhakwa]

I have tried your approach but still got an exception: fail: Microsoft.EntityFrameworkCore.Database.Connection[20004] An error occurred using the connection to database '' on server 'localhost'. An error occurred using the connection to database '' on server 'localhost'. 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.

[Abdullah Al Mamun Shiham]

is your project working with Microsoft SQL Server ? did you try it ?

[Russell Chidhakwa]

Yes is it working with Microsoft SQL Server, I have tried it. However with MySQL I am facing these exceptions and I do not know where the problem is. I have tried to follow YouTube tutorials and examples from Microsoft Docs Website and even tried the implementation from Github as per your advice but for some reasons Visual Studio IDE is throwing these exceptions. I do not prefer Microsoft SQL Server due to its cost. Initially I preferred SQLite to MSSQL but found out that I may face concurrency issues with SQLite. Now I am left with MySQL as the only option, which is not working for me.

[Russell Chidhakwa]

I really appreciate your findings. I will surely try your recommendation and if I get some errors or exceptions I will post. Your explanations make some sense. I appreciate!

[Gert Arnold]

Yeah, kinda duh.