Connect to MySql in .NET and Entity Framework

Article Source || GitLab Discussion
Created: 21 May 2020 || Edited: 19 June 2021


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.

Contents

  1. NuGet packages
  2. Connection string
  3. Connecting
    1. ASP.NET Startup
    2. DBContext child class

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.

MyServer root 127.0.0.1:3306 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(...);
}

^