Sunday 10 January 2021

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

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,
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
Enter project name “EFCoreMySQL” (whatever you like), select the location you want, click “Create” button,
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
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.
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
Wait a while, the project “EFCoreMySQL” is created successfully:
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
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.
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
The default weather data is shown on the page:
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
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.
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
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.
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
“Licence Acceptance” will pop up, click “I Accept” to continue:
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
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.
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
“Licence Acceptance” will pop up, click “I Accept” to continue:
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
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:
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
Now we have all dependency packages installed on the project successfully:
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
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.
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
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”.
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
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;”
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
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”
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
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
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5.   
  6. namespace EFCoreMySQL.Models  
  7. {  
  8.     public class UserGroup  
  9.     {  
  10.         public int Id { getset;}  
  11.         public string Name { getset;}  
  12.         public DateTime CreationDateTime { getset;}  
  13.         public DateTime? LastUpdateDateTime { getset;}  
  14.     }  
  15. }  
User.cs
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5.   
  6. namespace EFCoreMySQL.Models  
  7. {  
  8.     public class User  
  9.     {  
  10.         public int Id { getset;}  
  11.         public string FirstName { getset;}  
  12.         public string LastName { getset;}  
  13.         public int UserGroupId { getset;}  
  14.         public DateTime CreationDateTime { getset;}  
  15.         public DateTime? LastUpdateDateTime { getset;}  
  16.     }  
  17. }  
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:
  1. using EFCoreMySQL.Models;  
  2. using Microsoft.EntityFrameworkCore;  
  3. using System;  
  4. using System.Collections.Generic;  
  5. using System.Linq;  
  6. using System.Threading.Tasks;  
  7.   
  8. namespace EFCoreMySQL.DBContexts  
  9. {  
  10.     public class MyDBContext : DbContext  
  11.     {  
  12.         public DbSet<UserGroup> UserGroups { getset; }  
  13.         public DbSet<User> Users { getset; }  
  14.   
  15.         public MyDBContext(DbContextOptions<MyDBContext> options) : base(options)  
  16.         {   
  17.         }  
  18.   
  19.         protected override void OnModelCreating(ModelBuilder modelBuilder)  
  20.         {  
  21.             // Use Fluent API to configure  
  22.   
  23.             // Map entities to tables  
  24.             modelBuilder.Entity<UserGroup>().ToTable("UserGroups");  
  25.             modelBuilder.Entity<User>().ToTable("Users");  
  26.   
  27.             // Configure Primary Keys  
  28.             modelBuilder.Entity<UserGroup>().HasKey(ug => ug.Id).HasName("PK_UserGroups");  
  29.             modelBuilder.Entity<User>().HasKey(u => u.Id).HasName("PK_Users");  
  30.   
  31.             // Configure indexes  
  32.             modelBuilder.Entity<UserGroup>().HasIndex(p => p.Name).IsUnique().HasDatabaseName("Idx_Name");  
  33.             modelBuilder.Entity<User>().HasIndex(u => u.FirstName).HasDatabaseName("Idx_FirstName");  
  34.             modelBuilder.Entity<User>().HasIndex(u => u.LastName).HasDatabaseName("Idx_LastName");  
  35.   
  36.             // Configure columns  
  37.             modelBuilder.Entity<UserGroup>().Property(ug => ug.Id).HasColumnType("int").UseMySqlIdentityColumn().IsRequired();  
  38.             modelBuilder.Entity<UserGroup>().Property(ug => ug.Name).HasColumnType("nvarchar(100)").IsRequired();  
  39.             modelBuilder.Entity<UserGroup>().Property(ug => ug.CreationDateTime).HasColumnType("datetime").IsRequired();  
  40.             modelBuilder.Entity<UserGroup>().Property(ug => ug.LastUpdateDateTime).HasColumnType("datetime").IsRequired(false);  
  41.   
  42.             modelBuilder.Entity<User>().Property(u => u.Id).HasColumnType("int").UseMySqlIdentityColumn().IsRequired();  
  43.             modelBuilder.Entity<User>().Property(u => u.FirstName).HasColumnType("nvarchar(50)").IsRequired();  
  44.             modelBuilder.Entity<User>().Property(u => u.LastName).HasColumnType("nvarchar(50)").IsRequired();  
  45.             modelBuilder.Entity<User>().Property(u => u.UserGroupId).HasColumnType("int").IsRequired();  
  46.             modelBuilder.Entity<User>().Property(u => u.CreationDateTime).HasColumnType("datetime").IsRequired();  
  47.             modelBuilder.Entity<User>().Property(u => u.LastUpdateDateTime).HasColumnType("datetime").IsRequired(false);  
  48.   
  49.             // Configure relationships  
  50.             modelBuilder.Entity<User>().HasOne<UserGroup>().WithMany().HasPrincipalKey(ug => ug.Id).HasForeignKey(u => u.UserGroupId).OnDelete(DeleteBehavior.NoAction).HasConstraintName("FK_Users_UserGroups");  
  51.         }  
  52.     }  
  53. }  
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)
  1. {  
  2.   "Logging": {  
  3.     "LogLevel": {  
  4.       "Default""Information",  
  5.       "Microsoft""Warning",  
  6.       "Microsoft.Hosting.Lifetime""Information"  
  7.     }  
  8.   },  
  9.   "AllowedHosts""*",  
  10.   "ConnectionStrings": {  
  11.     "DefaultConnection""server=localhost; port=3306; database=test; user=root; password=Wxp@Mysql; Persist Security Info=False; Connect Timeout=300"  
  12.   }  
  13. }  
Inject database connection
 
In Startup.cs, add following codes,
  1. using EFCoreMySQL.DBContexts;  
  2. using Microsoft.EntityFrameworkCore;  
  3.   
  4. .  .  .  
  5.   
  6.         public void ConfigureServices(IServiceCollection services)  
  7.         {  
  8.             string mySqlConnectionStr = Configuration.GetConnectionString("DefaultConnection");  
  9.             services.AddDbContextPool<MyDBContext>(options => options.UseMySql(mySqlConnectionStr, ServerVersion.AutoDetect(mySqlConnectionStr)));  
  10.   
  11.             services.AddControllers();  
  12.         }  
  13.   
  14. .   .   .  
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
  1. using EFCoreMySQL.DBContexts;  
  2. using EFCoreMySQL.Models;  
  3. using Microsoft.AspNetCore.Http;  
  4. using Microsoft.AspNetCore.Mvc;  
  5. using System;  
  6. using System.Collections.Generic;  
  7. using System.Linq;  
  8. using System.Threading.Tasks;  
  9.   
  10. namespace EFCoreMySQL.Controllers  
  11. {  
  12.     [Route("api/[controller]")]  
  13.     [ApiController]  
  14.     public class UserGroupController : ControllerBase  
  15.     {  
  16.         private MyDBContext myDbContext;  
  17.   
  18.         public UserGroupController(MyDBContext context)  
  19.         {  
  20.             myDbContext = context;  
  21.         }  
  22.   
  23.         [HttpGet]  
  24.         public IList<UserGroup> Get()  
  25.         {  
  26.             return (this.myDbContext.UserGroups.ToList());  
  27.         }  
  28.     }  
  29. }   
On VS 2019 Solution Explorer, right click folder “Controllers” - Add - Controller … Common - API Controller – Empty - Add - enter name: UserController.cs - Add.
 
UserController.cs
  1. using EFCoreMySQL.DBContexts;  
  2. using EFCoreMySQL.Models;  
  3. using Microsoft.AspNetCore.Http;  
  4. using Microsoft.AspNetCore.Mvc;  
  5. using System;  
  6. using System.Collections.Generic;  
  7. using System.Linq;  
  8. using System.Threading.Tasks;  
  9.   
  10. namespace EFCoreMySQL.Controllers  
  11. {  
  12.     [Route("api/[controller]")]  
  13.     [ApiController]  
  14.     public class UserController : ControllerBase  
  15.     {  
  16.         private MyDBContext myDbContext;  
  17.   
  18.         public UserController(MyDBContext context)  
  19.         {  
  20.             myDbContext = context;  
  21.         }  
  22.   
  23.         [HttpGet]  
  24.         public IList<User> Get()  
  25.         {  
  26.             return (this.myDbContext.Users.ToList());  
  27.         }  
  28.     }  
  29. }  
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”.
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
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.
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs 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.
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
Enter https://localhost:44397/api/usergroup
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application
 
Enter https://localhost:44397/api/user
 
Use Entity Framework Core 5.0 In .NET Core 3.1 With MySQL Database By Code-First Migration On Visual Studio 2019 For A RESTful APIs Application

1 comment: