Entity Framework Core in .NET Core: MySQL Code-First Migration

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

Step 4. Create model classes

In VS 2019 Solution Explorer, create a folder at the 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 the 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 the 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 the 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 the command “Add-Migration DBInit” - once done, run another command “Update-Database”.

Run MySQL Workbench, and 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 the application to test

On the 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.