How to Connect MySQL Database with Entity Framework Core in .NET

In this tutorial, I will teach you how to connect a .NET 6/7 Web API to a MySQL database using Entity Framework Core. We will be implementing MySQL into a project that I created earlier (for demo purposes), you can find this project on GitHub and clone it, or follow along in your own project, the choice is yours.

A quick introduction to MySQL and EF Core 🤓

What is MySQL? 💡

MySQL is a popular open-source relational database management system (RDBMS) that is widely used in web applications and software development. It is one of the most widely used databases in the world and is known for its reliability, speed, and ease of use.

MySQL is based on Structured Query Language (SQL) and allows developers to create, read, update, and delete data from databases (CRUD - Operations). It is commonly used in web applications to store data such as user information, product details, and transaction records.

MySQL is compatible with a wide range of programming languages, including C#, PHP, Python, Java, and C++. It can be used on various operating systems, including Windows, Linux, and Mac OS X, making it a flexible choice for developers. Another great thing is the Docker support, where you get the option to move your database around without problems.

MySQL

What is Entity Framework Core? 💡

Entity Framework Core (EF Core) is an open-source, cross-platform Object-Relational Mapping (ORM) framework for .NET applications. It is a modern version of the Entity Framework that provides developers with a way to access and manipulate data stored in databases as objects.

With Entity Framework Core, developers can work with data in a more object-oriented way, using classes and objects to represent tables and rows in a database. This approach can make it easier to develop and maintain applications, as developers can work with code that more closely matches the way they think about data. This is what we also refer to as "code-first".

Entity Framework Core supports a variety of database platforms, including SQL Server, MySQL (fortunately 🤠), Oracle, PostgreSQL, and SQLite, among others. It also offers features such as LINQ support, change tracking, and automatic schema migration.

Language-Integrated Query (LINQ) (C#)

Learn about Language-Integrated Queries (LINQs) and review an example of the complete query operation.

Microsoft LearnBillWagner

Entity Framework Core is a cross-platform framework, meaning that it can be used on Windows, Linux, and macOS. It is designed to work seamlessly with .NET Core, a free, open-source, and cross-platform framework for building modern applications.

Requirements 🧰

To follow along you will need the following tools installed on your development computer.

• .NET SDK (Either 6 or 7)
• Visual Studio IDE or similar. If you prefer to go with Visual Studio Code, make sure that you got the C# Extension installed. This will add support in VS Code for developing a .NET application.

Clone, and Run the Demo Web API 🌍

I have prepared a .NET Core 7 Web API we will be using for testing and integration of MySQL in .NET using EF Core. It's available on GitHub, and you are more than welcome to grab a copy for your own testing.

GitHub - Tech-With-Christian/Dotnet-CRUD-Api: This is a simple Demo CRUD API for use in my tutorials. It contains a simple implementation of a CRUD service for products.

This is a simple Demo CRUD API for use in my tutorials. It contains a simple implementation of a CRUD service for products. - GitHub - Tech-With-Christian/Dotnet-CRUD-Api: This is a simple Demo CRU…

GitHubTech-With-Christian

I'm working on an in-depth article about how I made this project and some more documentation for each of the services, etc... in this application. Stay tuned for that. 😅

NuGet Packages / Libraries 📦

To work with MySQL using Entity Framework Core, we must install a new package named Pomelo.EntityFrameworkCore.MySql.

Pomelo.EntityFrameworkCore.MySql 7.0.0

Pomelo’s MySQL database provider for Entity Framework Core.

Pomelo.EntityFrameworkCore.MySql 7.0.0

Pomelo.EntityFrameworkCore.MySql is the most popular Entity Framework Core provider for MySQL-compatible databases. It has full support for EF Core, including the latest version, and employs MySqlConnector to maintain high-performance communication with the database server. Awesome! 🙌

This package has to be installed inside the Data project, as this contains the application database context for communicating with the database. For your own project, you should just install the package inside the project where the DbContext class lives.

Install-Package Pomelo.EntityFrameworkCore.MySql

The next required package is Microsoft.EntityFrameworkCore.Design and it contains all the design-time logic for Entity Framework Core. It's the code that all of the various tools (PMC cmdlets like Add-Migration, dotnet ef & ef.exe) call into. In other words - it provides cross-platform command line tooling support and is used when we would like to design our database.

This package has to be installed in our startup project 🌐. This demo, it's our API project. You can avoid doing this if you specify the parameter -StartUpProjectwhen running the Add-Migration command. For now, let's just install the package in our startup project.

Install-Package Microsoft.EntityFrameworkCore.Design

If you would like to know more about the Add-Migrations parameters, you can read more on the link below. 🔗

EF Core tools reference (Package Manager Console) - EF Core

Reference guide for the Entity Framework Core Visual Studio Package Manager Console

Microsoft Learnbricelam

Add MySQL Support in .NET

With the packages in place, let's move on and wire up things to connect our .NET Web API to MySQL using Entity Framework Core. This section will be divided into two sections below and finally, we will add a new migration and update the database to match our domain in the application. Let's get started! 😁

Add a database connection string to appsettings.json

A standard connection string for MySQL will look like the following:

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

If you need any encryption, details on how to specify a TCP port, etc... you can refer to this site.

MySQL connection strings - ConnectionStrings.com

Connection strings for MySQL. Connect using MySqlConnection, MySQLDriverCS, SevenObjects MySqlClient, Devarts MySqlConnection, MySQLProv.

ConnectionStrings.com

Head to the API project, open up the Configurations folder, and open the appsettings.json file. Here we have to add a new property/entry to the configurations file for our connection string to the MySQL database, it will look like the following:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
  	"MySqlDatabase": "Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"
  }
}

In the JSON code above we have specified a new connection string named MySqlDatabase. You should now update:

• The Server Address
• Database Name
• Username
• Password