Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For RESTful API Application

Introduction 

 

Code-First is mainly useful in Domain-Driven Design. In the Code-First approach, you focus on the domain of your application and start creating classes for your domain entity rather than design your database first and then create the classes which match your database design. 

 

Today, we will show you step by step how to use EF Core 5.0 in .NET Core 3.1 to implement Code-First approach to create/update MySQL database on Visual Studio 2019 for a RESTful API application.

 

Let’s get started.

 

Step 1 - Create .NET Core 3.1 project on Visual Studio 2019

 

We will create a RESTful API project with .NET Core 3.1 on Visual Studio 2019 (FYI: we use Microsoft Visual Studio Professional 2019 Version 16.8.0).

 

VS 2019 - File - New - Project… choose “ASP.NET Core Web Application” - Next,

 

 

Enter project name “EFCoreMySQL” (whatever you like), select the location you want, click “Create” button,

 

 

Make sure “ASP.NET Core 3.1” is selected, and also “API” is chosen as the project template. “Configure for HTTPS” and “Enable Docker Support” can be checked or unchecked based on your needs. Click the “Create” button.

 

 

Wait a while, the project “EFCoreMySQL” is created successfully:

 

 

On VS 2019 ribbon, click the drop-down as shown below, select “Google Chrome”, then select “IIS Express”. The dropdown will close and show “IIS Express”. Click “IIS Express” to build & run for a test.

 

 

The default weather data is shown on the page:

 

 

Step 2 - Install dependency packages

 

In order to use Entity Framework Core to implement the Code-First approach to MySQL database, we need to install following packages of dependencies to the project:

• Microsoft.EntityFrameworkCore (v5.0.0 – the latest stable version)
• Microsoft.EntityFrameworkCore.Tools (v5.0.0 – the latest stable version)
• Pomelo.EntityFrameworkCore.MySql (version 5.0.0-alpha.2)

Pomelo.EntityFrameworkCore.MySql is the most popular Entity Framework Core provider for MySQL compatible databases. It supports EF Core 3.1 (and lower) and uses MySqlConnector for high-performance database server communication.

 

The following versions of MySqlConnector, EF Core, .NET Standard and .NET Core are compatible with Pomelo.EntityFrameworkCore.MySql. We can see that “5.0.0-alpha.2” is a pre-release version of Pomelo.EntityFrameworkCore.MySql, and so far it’s the only version that can work with EF Core 5.0.0 and .NET Core 3.1, that’s why we need to install “Pomelo.EntityFrameworkCore.MySql” of version “5.0.0-alpha.2” for the project.

 

 

MySql.Data.EntityFrameworkCore latest version is 8.0.22 that is not working with EF Core 5.

 

Install “Microsoft.EntityFrameworkCore” (v5.0.0 – the latest stable version)

 

VS 2019 - right-click the project node “EFCoreMySQL” in Solution Explorer - Manage NuGet Packages… Browse - enter “Microsoft.EntityFrameworkCore” to search - select it and click the “Install” button to install.

 

 

“Licence Acceptance” will pop up, click “I Accept” to continue:

 

 

Install “Microsoft.EntityFrameworkCore.Tools” (v5.0.0 – the latest stable version)

 

VS 2019 - right-click the project node “EFCoreMySQL” in Solution Explorer - Manage NuGet Packages… Browse - enter “Microsoft.EntityFrameworkCore.Tools” to search - select it and click “Install” button to install.

 

 

“Licence Acceptance” will pop up, click “I Accept” to continue:

 

 

Install “Pomelo.EntityFrameworkCore.MySql” (version 5.0.0-alpha.2)

 

For this pre-release version, we need to install it from the Package Manager Console.

 

VS 2019 - Tools - NuGet Package Manager - Package Manager Console - enter “Install-Package Pomelo.EntityFrameworkCore.MySql -Version 5.0.0-alpha.2”, hit ENTER key to install:

 

 

Now we have all dependency packages installed on the project successfully:

 

 

Step 3 - Install MySQL Workbench and MySQL Server

 

In order to manage MySQL database, we need MySQL server that hosts MySQL database and also the management tool – MySQL Workbench.

 

We will briefly show how to install them on your machine. If you already have MySQL server and MySQL Workbench to use, then skip this step.

 

MySQL Community Server (latest version 8.0.22) is free to download and use. Go to Oracle MySQL Community Downloads page https://dev.mysql.com/downloads/mysql/ to download the MSI package that suits your operating system, and then install both MySQL Workbench and MySQL Server from it on your machine.

 

You need to sign in to download the MSI package. In other words, you need to have an account there first for you to sign in. If you do not have an account yet, just create one. It’s free.

 

 

Two things to remind, the first one is that on the MySQL Installer, you need to click “Add …” to select products to install, you can select “MySQL Server 8.0” and “MySQL Workbench 8.0”.

 

 

The second thing is that along with the installation you will be asked to enter password for the root, you need to remember password for future use.

 

Once installation is done, you can double-check to make sure that MySQL Server is running on your machine. Simply run “MySQL 8.0 Command Line Client” (installed by the MySQL Server installation), and type “Show Databases;”

 

 

In MySQL Workbench, connect the MySQL Server. Run MySQL Workbench à click the “MySQL Connections” plus button to open “Setup New Connection” window - enter the connection name you like - click “Test Connection” button - enter the root password - check “Save password in vault” - click “OK” - click “OK” - click “OK”

 

 

 

Step 4 - Create model classes

 

In VS 2019 Solution Explorer, create a folder at project root called “Models” (whatever you like), and add model classes in the folder. To simplify, we just add two classes “UserGroup” and “User”,

 

UserGroup.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace EFCoreMySQL.Models
{
    public class UserGroup
    {
        public int Id { get; set;}
        public string Name { get; set;}
        public DateTime CreationDateTime { get; set;}
        public DateTime? LastUpdateDateTime { get; set;}
    }
}

User.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace EFCoreMySQL.Models
{
    public class User
    {
        public int Id { get; set;}
        public string FirstName { get; set;}
        public string LastName { get; set;}
        public int UserGroupId { get; set;}
        public DateTime CreationDateTime { get; set;}
        public DateTime? LastUpdateDateTime { get; set;}
    }
}

Step 5 - Create a database context

 

In VS 2019 Solution Explorer, create a folder at project root called “DBContexts” (whatever you like), and add a class called “MyDBContext” with the following content:

using EFCoreMySQL.Models;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace EFCoreMySQL.DBContexts
{
    public class MyDBContext : DbContext
    {
        public DbSet<UserGroup> UserGroups { get; set; }
        public DbSet<User> Users { get; set; }

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

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Use Fluent API to configure

            // Map entities to tables
            modelBuilder.Entity<UserGroup>().ToTable("UserGroups");
            modelBuilder.Entity<User>().ToTable("Users");

            // Configure Primary Keys
            modelBuilder.Entity<UserGroup>().HasKey(ug => ug.Id).HasName("PK_UserGroups");
            modelBuilder.Entity<User>().HasKey(u => u.Id).HasName("PK_Users");

            // Configure indexes
            modelBuilder.Entity<UserGroup>().HasIndex(p => p.Name).IsUnique().HasDatabaseName("Idx_Name");
            modelBuilder.Entity<User>().HasIndex(u => u.FirstName).HasDatabaseName("Idx_FirstName");
            modelBuilder.Entity<User>().HasIndex(u => u.LastName).HasDatabaseName("Idx_LastName");

            // Configure columns
            modelBuilder.Entity<UserGroup>().Property(ug => ug.Id).HasColumnType("int").UseMySqlIdentityColumn().IsRequired();
            modelBuilder.Entity<UserGroup>().Property(ug => ug.Name).HasColumnType("nvarchar(100)").IsRequired();
            modelBuilder.Entity<UserGroup>().Property(ug => ug.CreationDateTime).HasColumnType("datetime").IsRequired();
            modelBuilder.Entity<UserGroup>().Property(ug => ug.LastUpdateDateTime).HasColumnType("datetime").IsRequired(false);

            modelBuilder.Entity<User>().Property(u => u.Id).HasColumnType("int").UseMySqlIdentityColumn().IsRequired();
            modelBuilder.Entity<User>().Property(u => u.FirstName).HasColumnType("nvarchar(50)").IsRequired();
            modelBuilder.Entity<User>().Property(u => u.LastName).HasColumnType("nvarchar(50)").IsRequired();
            modelBuilder.Entity<User>().Property(u => u.UserGroupId).HasColumnType("int").IsRequired();
            modelBuilder.Entity<User>().Property(u => u.CreationDateTime).HasColumnType("datetime").IsRequired();
            modelBuilder.Entity<User>().Property(u => u.LastUpdateDateTime).HasColumnType("datetime").IsRequired(false);

            // Configure relationships
            modelBuilder.Entity<User>().HasOne<UserGroup>().WithMany().HasPrincipalKey(ug => ug.Id).HasForeignKey(u => u.UserGroupId).OnDelete(DeleteBehavior.NoAction).HasConstraintName("FK_Users_UserGroups");
        }
    }
}

Step 6: Configure and inject the database connection

 

Configure the database connection string

 

Add following database connection string codes in appsettings.json (you need to update the connection string with your server name, database name, port, user, or password)

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "server=localhost; port=3306; database=test; user=root; password=Wxp@Mysql; Persist Security Info=False; Connect Timeout=300"
  }
}

Inject database connection

 

In Startup.cs, add following codes,

using EFCoreMySQL.DBContexts;
using Microsoft.EntityFrameworkCore;

.  .  .

        public void ConfigureServices(IServiceCollection services)
        {
            string mySqlConnectionStr = Configuration.GetConnectionString("DefaultConnection");
            services.AddDbContextPool<MyDBContext>(options => options.UseMySql(mySqlConnectionStr, ServerVersion.AutoDetect(mySqlConnectionStr)));

            services.AddControllers();
        }

.   .   .

Step 7 - Create API Controllers

 

To show data on webpage, we need to have controllers. Let’s add simple controller files.

 

On VS 2019 Solution Explorer, right click folder “Controllers” - Add - Controller … Common - API Controller – Empty - Add - enter name: UserGroupController.cs - Add.

 

UserGroupController.cs

using EFCoreMySQL.DBContexts;
using EFCoreMySQL.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace EFCoreMySQL.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class UserGroupController : ControllerBase
    {
        private MyDBContext myDbContext;

        public UserGroupController(MyDBContext context)
        {
            myDbContext = context;
        }

        [HttpGet]
        public IList<UserGroup> Get()
        {
            return (this.myDbContext.UserGroups.ToList());
        }
    }
}

On VS 2019 Solution Explorer, right click folder “Controllers” - Add - Controller … Common - API Controller – Empty - Add - enter name: UserController.cs - Add.

 

UserController.cs

using EFCoreMySQL.DBContexts;
using EFCoreMySQL.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace EFCoreMySQL.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class UserController : ControllerBase
    {
        private MyDBContext myDbContext;

        public UserController(MyDBContext context)
        {
            myDbContext = context;
        }

        [HttpGet]
        public IList<User> Get()
        {
            return (this.myDbContext.Users.ToList());
        }
    }
}

Step 8 - Add migration and update database

 

VS 2019 - Tools - NuGet Package Manager - Package Manager Console - run command “Add-Migration DBInit” - once done, run another command “Update-Database”.

 

 

Run MySQL Workbench, connect to the local MySQL server, we can see the database “test” along with two tables “UserGroups” and “Users” are already created automatically.

 

For testing purposes, we manually enter some records in these two tables on MySQL Workbench, so that we will see some data when we run this application.

 

 

Step 9 - Run application to test

 

On VS 2019 ribbon, click the drop-down as shown below, select “Google Chrome”, then select “IIS Express”. The dropdown will close and show “IIS Express”. Click “IIS Express” to build & run for a test.

 

 

Enter https://localhost:44397/api/usergroup

 

 

Enter https://localhost:44397/api/user