Using MySQL with Entity Framework Core and ASP.NET Core

With the introduction of Entity Framework Core also the support of other databases then SQL got a boost. Off course there was support for MySQL in Entity Framework, but the NuGet package still had some problems and you could end up with a large variety of runtime errors when executing even the simplest of queries. Entity Framework Core added better support for database providers and you can see this in the quality and quantity of providers. Database Providers - EF Core

Using these providers is also a breeze. If we look at the default template of an ASP.NET Core application with EF Core and SQL. You will see something like this in the ConfigureServices method of the StartUp class.

services.AddDbContext<ApplicationDbContext>(options =>
options.UseSqlServer(config.GetConnectionString("DefaultConnection")));

To start using MySQL the only thing needed is to configure another Database Provider. You can use the MySql.Data.EntityFrameworkCore package (or the Pomelo.EntityFrameworkCore.MySql) for this. When installed you can just change this configuration to:

services.AddDbContext<ApplicationDbContext>(options =>
options.UseMySQL(config.GetConnectionString("DefaultConnection")));

After updating the connection string to your MySQL instance try running your migrations and see if it works. I had some problems with the key of the default IdentityUser that didn’t had a MaxLength. Those were all small problems that were fixed easily. After this you should be all setup to continue developing against a MySQL database.

Comments

[Katie Nelson]

I’ve been using the Pomelo provider for a year now and find that it works very well. I use scaffolding to generate the DbContext classes in .NET Core 3.1 using the database first approach.

[Ruben Jonker]

Good to know. Are there any advantages of using the Pomelo provider over the one that Oracle created?

[Katie Nelson]

I just remember having some problems at first with the Oracle one. Pomelo also uses the open source MySqlConnector library which I find has more functionality than the MySql.Data.MySqlClient one. And, the opensource packages respond to bug reports and are updated much more frequently than the Oracle ones.

[Dave]

I like this. However, where does the "config." come from. Visual Studio Code says it is not in my namespace. I've had this same problem with other EF addDBContexts. I see it in examples, but I don't know where it comes from.

I solved my problem. Though, I'd still like to know where the config. comes from as my solution is less than desirable.