.Net with MySql

You can find the source code for this project here https://github.com/joaocosta88/foodie

While developing a new pet project, I figured it would be a good time to start documenting the process. Never done this before, so why not give it a shot.
If you find this article useful, or if there's anything that can be improved, please let me know in the comments!

This project will be an application to save restaurants that we enjoy so that we can return to them later.
There's probably a million of these applications out there, but the key feature is that they weren't done by me 😎
Let's call it Foodie.

The tech stack of this application will be react+dotnet+mysql, and in the next posts I'll focus on the backend side of things.

In the current post I'll show we can setup a MySql database and connect to it through the dotnet application.

Setting up MySql

We'll setup MySql in a docker container.
Additionally, we will use MySql Workbench (https://dev.mysql.com/downloads/workbench/) to navigate through the database instance and make sure everything is correctly setup.

Regarding the MySql instance, we can run it in a docker instance with the following command:

docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest

A few considerations that have to be made:

• "my-secret-pw" is not the best password
• We are not setting a volume for this instance. This is something that we might want to do.
• We are running the latest version of MySql. In a more production-ready scenario we might consider to define a specific version.

After running this command, we can use the MySql Workbench to make sure we can connect to this instance.

If we can connect to the database, the next step is to setup our dotnet application to connect to it.

Connect to MySql from .net

For the sake of simplicity, in this solution we will adopt a simple three layer aproach instead of using something like an hexagonal architectural.

The three layers will be:

• Api The entry point for the application and where our endpoints will be defined. It will have a dependency for the two other layers.
• Services Business logic layer. It will have a dependency for the Entities layer.
• Entities Where our entities, dbcontext and repositories logic will be defined. Won't depend on any other project.

The process for wiring everything up will be:

1. Define our entities
2. Define our DbContext
3. Setup our DI container to use the defined DbContext
4. Generate and apply migrations

Entities

We will start by defining a simple entity. This entity will live inside our Entities layer.
Let's call it Location and it will represent a place that we have visited and that we can rate. It will also have a string representing it's coordinates.

namespace Foodie.Entities.Entities {

    public class Location {
        public string Id { get; set; }
        public string Coordinates { get; set; }
        public string Rating { get; set; }

    }
}

Define a DbContext

Still inside the Entities layer, we need to create a new class to represent our DbContext.
Let's call it FoodieDbContext.
It will inherit from DbContext, and contain DbSet for the Locations stored in the database.
We are also required to create a constructor that receives a DbContextOptions and pass it to the parent base class.
The code will be something similar to this:

using Foodie.Entities.Entities;
using Microsoft.EntityFrameworkCore;

namespace Foodie.Entities {
    public class FoodieDbContext : DbContext {
        public DbSet<Location> Locations { get; set; }

        public FoodieDbContext(DbContextOptions<FoodieDbContext> options) : base(options) { }
    }
}

The next step will be to wire everything in our DI container.

Setup DI container

Add connection string

Before we setup our container, we will add our database connection string to the appsettings.config file.
Add the following entry to the appsettings.development.config:

  "ConnectionStrings": {
    "FoodieDatabase": "server=127.0.0.1;port=3306;database=foodie;uid=root;pwd=my-secret-pw"
  }

The file content will look something like this:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "FoodieDatabase": "server=127.0.0.1;port=3306;database=foodie;uid=root;pwd=my-secret-pw"
  }
}

Once we are ready to deploy this to multiple environments we will need to add environment-specific connection strings, but this will work for now.

Wire everything

In this version 7 of dotnet, everything is wired in the Program.cs class of the startup project (in our case, it will be Foodie.Api).
To make our code cleaner, I created an extension method that will register all the services related to our application. This extension method will be called inside the Program.cs class.

We can start by creating a static class called FoodieServices that will contain all the wiring logic.
Inside this class we can write a new method, RegisterFoodieServices, where we will register our dependencies:

namespace Foodie.Api {
    public static class FoodieServices {
        public static void RegisterFoodieServices(this IServiceCollection services, IConfiguration configuration)
        {
          //services will be registered here    
        }
    }
}

Here we are adding this extension method to the IServiceCollection interface, and we are receiving an IConfiguration instance so we can access our appsettings file contents. This will be useful to access information such as our connection string.

Inside this method we will need to register our FoodieDbContext as being our DbContext. We will also need to set MySql as our database, and pass the connection string as an argument.
The final code will be something like this:

using Foodie.Entities;
using Microsoft.EntityFrameworkCore;

namespace Foodie.Api {
    public static class FoodieServices {
        public static void RegisterFoodieServices(this IServiceCollection services, IConfiguration configuration)
        {
            services.AddDbContext<FoodieDbContext>(opt => opt.UseMySQL(configuration.GetConnectionString("FoodieDatabase")));  
        }
    }
}

We will need to install the following nugets:
https://www.nuget.org/packages/MySql.EntityFrameworkCore/
https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.Design

Inside Program.cs, we can invoke this method by doing the following:

builder.Services.RegisterFoodieServices(builder.Configuration);

The next step will be to create and run our initial migration (check the links at the bottom for more information).

Generate and apply migrations

This part of the process will require that you have the ef cli tools installed. I believe they are installed by default, but you may be required to install them separetly depending on how you installed dotnet.

Check the following link on how to install this tool:
https://learn.microsoft.com/en-us/ef/core/cli/dotnet

The migrations part will consist of two steps:

1. Generate the migration
2. Apply the migration

It's also important to note that everytime your database entities change, you will need to generate and apply new migrations.

When running these commands, you will need to pass information about two different projects:

• Startup-project The project that contains information about which class is the our application DbContext (that was wired on one of the previous steps). In our case is Foodie.Api.
• Project Project that contains the definition of our Entities and DbContext. It will have a folder with all the migrations. In our case, this will be the Foodie.Entities project.

In the root of the solution, run the following command to generate a new migration:
dotnet ef migrations add InitialCreate --project Foodie.Entities --startup-project Foodie.Api

And then run the following command to apply the generated migration
dotnet ef database update --project Foodie.Entities --startup-project Foodie.Api

If all went as expected, no erros were logged in the console window.
We can check with MySql Workbench if the migrations were correcly applied.

In the next part we will create some endpoints to create, update, delete and retrieve data.

Please let me know if there's something that should be added in this article, or if this was useful to you. Your feedback is relevant!

Useful resources:

https://hub.docker.com/_/mysql
https://learn.microsoft.com/en-us/ef/core/get-started/overview/first-app?tabs=netcore-cli
naging-schemas/migrations/?tabs=dotnet-core-cli