How to use MySQL with ASP.NET Core

Entity Framework Core aka EF Core is an Object Relational Mapper (ORM) library which maps the database objects from a specified database onto their equivalent entity models in the application and translates all the operations performed on the application entity models into compatible database SQL operations.

EF Core provides the much needed flexibility for developers so that they can focus more on the application logic rather than spending time on building the data persistence layer.

We generally see most of the developers using EF Core to connect their AspNetCore applications to SQL Server, to which the EF Core provides the highest grade compatibility and support. But it doesn’t mean that EF Core can’t be used in working with other database providers such as MySQL, Oracle or PostgreSQL.

EF Core provides nuget package libraries for developers to connect and work with other database providers apart from SQL Server. In this article, let’s look at how we can connect an AspNetCore application to a MySQL database using EFCore.

A Little about MySQL

MySQL is a popular opensource database with high patronage and developer community. It is much known for its performance and replication capabilities in addition to its opensource nature which makes it popular among developers and customers alike. It is widely used along with PHP in developing serverside applications in LAMP stack.

One can download and install MySQL for free from their website and comes with a complete package called as MySQL Workbench which comprises of the MySQL server along with Client and Command Line plugin.

To keep things simple for our example, we go by the Docker route which helps us run a MySQL instance in our machine without having to go through all the installations. We make use of the Docker image for MySQL available in Docker Hub and run it inside a Container using Docker Compose in our system.

#docker-compose.yaml#
version: '3'
services: 
    database:
        image: mysql
        command: --default-authentication-plugin=mysql_native_password
        restart: always
        environment:
            MYSQL_ROOT_PASSWORD: abcd@1234
        ports: 
            - 3306:3306

Let’s take the example of building our very familiar Readers API that stores and fetches Readers from a database for a given request. We have already looked at how we achieve this with EF Core with an SQL Server background.

Configuring EF Core for MySQL

To connect our application to the MySQL database via EFCore, we begin by installing the Nuget package for EFCore which configures it to work with MySQL. Microsoft documentation for EFCore cites two packages – one a free version and the other provided by Oracle which is a paid one. For our purpose, we go by the former which is a free one – Pomelo.EntityFrameworkCore.MySql

>  dotnet add package Pomelo.EntityFrameworkCore.MySql

The above dotnet core command installs the MySql plugin into our application. Remember that we should have already have the actual EFCore library installed in our application for this to work. For starters, we add EFCore capabilities to our application by adding the EntityFrameworkCore.Design library to our application through CLI as:

> dotnet add package Microsoft.EntityFrameworkCore.Design

Once these packages are added to the application, we would register MySql database provider service inside the ConfigureServices() method as below:

public void ConfigureServices(IServiceCollection services)
{
    services.AddScoped<IDataService, DataService>();
    services.AddDbContextPool<MySqlContext>(builder =>
    {
        builder.UseMySql(
            Configuration.GetConnectionString("DefaultConnection"), 
            options =>
            {
                options.EnableRetryOnFailure();
            });
    });

    services.AddControllersWithViews();
}

where the connectionString is configured in appsettings with the below value:

"Server=127.0.0.1;Port=3306;Uid=root;Password=abcd@1234;Database=readers"

Since we’re connecting from a localhost service via Docker, we provide the Server as 127.0.0.1 (instead of localhost) and 3306 is the default port under which the MySql server runs. Uid and Password are our username and password for the database and Database refers to the database we’re gonna work on.

the line builder.UseMySql() is similar to the line builer.UseSqlServer() we use for an SQL Server context. This configures the EFCore to work on a MySql context. This ensures that all the types we use in our C# application entity models are translated into their equivalent MySql datatypes and the constraints specified are translated accordingly.

First Migration

To finish things off, we can run a migration on the connected database just to make sure our database works fine. This creates the database readers specified in the connectionString if not already available. If the application already contains migrations created before, they can also be applied as well.

> dotnet ef migrations add InitialCreate

> dotnet ef database update

By this step, we can see that the database is now created in MySQL with the tables as configured in our DbContext class. To confirm, we can just refresh the tables under Workbench or for a CLI client which is our case, we can run these MySQL commands:

> show databases;

> use readers;
> show tables;

Verifying Reads and Writes

To verify the db reads and writes with MySql, we’ll setup and call a Seed() method during our application bootstrap which pushes a set of Users to the database. This happens just before the application Host is built and run as below:

public static void Main(string[] args)
{
    var host = CreateHostBuilder(args).Build();

    using (var scope = host.Services.CreateScope())
    {
        var data = scope.ServiceProvider.GetRequiredService<IDataService>();
        SeedUsers(data);
    }

    host.Run();
}

private static void SeedUsers(IDataService data)
{
    data.UsersRepository.Insert(new User
    {
        EmailAddress = "user1001@yopmail.com",
        IsActive = true
    });
    data.UsersRepository.Insert(new User
    {
        EmailAddress = "user1001@yopmail.com",
        IsActive = true
    });
    data.UsersRepository.Insert(new User
    {
        EmailAddress = "user1002@yopmail.com",
        IsActive = true
    });
    data.UsersRepository.Insert(new User
    {
        EmailAddress = "user1003@yopmail.com",
        IsActive = true
    });
    data.UsersRepository.Insert(new User
    {
        EmailAddress = "user1004@yopmail.com",
        IsActive = true
    });
    data.UsersRepository.Insert(new User
    {
        EmailAddress = "user1005@yopmail.com",
        IsActive = true
    });
    data.UsersRepository.Insert(new User
    {
        EmailAddress = "user1006@yopmail.com",
        IsActive = true
    });
    data.UsersRepository.Insert(new User
    {
        EmailAddress = "user1007@yopmail.com",
        IsActive = true
    });
}

When we call the GET Users API, EF Core should fetch the User rows we have pushed to the database as a part of the Seed() method during application bootstrap and return the collection.

[ApiController]
[Route("api/[controller]")]
public class UsersController 
    : ControllerBase, IController<User>
{
    private readonly IDataService data;
    public UsersController(IDataService data)
    {
        this.data = data;
    }

    [HttpGet]
    [Route("all")]
    public IEnumerable<User> Get()
    {
        return this.data
            .UsersRepository.Get(x => x.IsActive == true);
    }

    ...
}

HOST https://localhost:5001 
GET /api/users/all

Response: [{"id":1,"emailAddress":"user1001@yopmail.com","isActive":true},
{"id":2,"emailAddress":"user1001@yopmail.com","isActive":true},
{"id":3,"emailAddress":"user1002@yopmail.com","isActive":true},
{"id":4,"emailAddress":"user1003@yopmail.com","isActive":true}..]

Reverse Scaffolding

Similar to how we can scaffold an existing SQL Server database into our application similar to a Database-First approach in EF Core, it is still possible with MySQL using a similar approach with the below command:

> dotnet ef dbcontext scaffold <connString> "Pomelo.EntityFrameworkCore.MySql"

where connectionString can be something as specified before.

In this way, we can connect and work with MySQL in an AspNetCore application without any much difference using the capabilities of EFCore.

The source code for the example used in this article is available at: https://github.com/referbruv/mysql-efcore-sample

Found this article helpful? Please consider supporting!