Connect to MySql in .NET and Entity Framework

I have found that connecting a .NET application to a MySql database (with Entity Framework) can be tricky. Here is what works for me.

Also, MySql.Data.EntityFrameworkCore has been depricated since October 2020. If you need setup instructions with it, read this.

NuGet packages

You’ll need to install Pomelo.EntityFrameworkCore.MySql. It provides the stuff necessary for connecting and using a MySql database.

Connection string

There are a lot of ways to setup the connection string but I’ve found that this one works the best:

"server=localhost; port=YOUR-PORT; user id=YOUR-USER-ID; password=YOUR-PASSWORD; database=NAME-OF-DATABASE; SslMode=none"

If you are using MySQL Workbench and you don’t know your port and id, you can look at the server information from the MySQL Connections page.

In this example, the user id is root and the port is 3306.

Connecting

To connect, you’ll need to call the UseMySql method, either in ASP.NET Startup or in a DBContext child class.

The method requires at least two arguments: connection string and MySql server version. The latter you can find, by going to the Help/About Workbench window inside MySQL Workbench.

For the server version argument, create a new object of type MySqlServerVersion and give it’s constructor a new object of type Version, to which you give the version numbers. The result should look something like this:

.UseMySql(CONNECTION_STRING, new MySqlServerVersion(new System.Version(8, 0, 21)))

Where CONNECTION_STRING is a (constant) variable that contains your connection string, and 8, 0, 21 are the version numbers.

ASP.NET Startup

For ASP.NET applications, the connection must be put inside the ConfigureServices method of the Startup.cs file. If you already have a .UseSQL method call in there, just replace it with your .UseMySql method and arguments.

The result should look something along the lines of:

// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
    ...
    services.AddDbContext<AppDbContext>(opt =>
    {
        opt.UseMySql(...);
    });
}

DBContext child class

Another way to connect is via a DBContext child class (so, inside your own DB context). Just, override the OnConfiguring method and call the .UseMySql method from the optionsBuilder parameter.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseMySql(...);
}