Create an ASP. NET Core MVC application (3)-Create a MySQL database table based on the entity Framework Core (Code first)

Create ASP.NET Core MVC application (3)-Create MySQL database table based on Entity Framework Core (Code First) Create data model class (POCO class)
Add a User class under the Models folder:

namespace MyFirstApp.Models
{
    public class User
    {
        public int ID {get; set;}
        public string Name {get; set;}
        public string Email {get; set;}
        public string Bio {get; set;}
    }
}

In addition to the attributes you expect to build the Movie model, the ID field that will be the primary key of the database is required.

Install Entity Framework Core MySQL related dependencies
Note: Among them, "MySQL.Data.EntityFrameworkCore": "7.0.6-ir31" requires version 7.0.6 or higher
Missing implementation for running EntityFramework Core code first migration.

Create Entity Framework Context database context
Add a UserContext class under the Models folder:

/// <summary>
/// The entity framework context with a User DbSet
///> dotnet ef migrations add MyMigration
/// </ summary>
public class UserContext: DbContext
{
    public DbSet <User> Users {get; set;}

    protected override void OnConfiguring (DbContextOptionsBuilder optionsBuilder)
    {
        var builder = new ConfigurationBuilder ()
                    .AddJsonFile ("appsettings.json", optional: false, reloadOnChange: true);

        var configuration = builder.Build ();

        string connectionString = configuration.GetConnectionString ("MyConnection");

        optionsBuilder.UseMySQL (connectionString);
    }

    protected override void OnModelCreating (ModelBuilder builder)
    {
        builder.Entity <User> (). HasKey (m => m.ID);
        base.OnModelCreating (builder);
    }
}

The logic specified in the OnConfiguring method using the MySQL provider and the specific connection string can also be put into the ConfigureServices method in the Startup class:

public void ConfigureServices (IServiceCollection services)
{
    string connectionString = Configuration.GetConnectionString ("MyConnection");

    services.AddDbContext <UserContext> (options =>
        options.UseMySQL (connectionString)
    );

    // Add framework services.
    services.AddMvc ();
}

Note: UseMySQL is an extension method in MySQL.Data.EntityFrameworkCore.Extensions, so manually add using MySQL.Data.EntityFrameworkCore.Extensions; namespace. This small problem also cost me a lot of time and energy.

Create a database
Use the Migrations tool to create the database.

Run dotnet ef migrations add MyMigration Entity Framework .NET Core CLI Migrations command to create an initial migration command.

Run dotnet ef database update to apply a new migration you created to the database. Because your database does not yet exist, it will create the required database for you before the migration is applied.

Then the Migrations folder will be generated in the project, including the 20161121064725_MyMigration.cs file, 20161121064725_MyMigration.Designer.cs file, and UserContextModelSnapshot.cs file:

20161121064725_MyMigration.Designer.cs class:

[DbContext (typeof (UserContext))]
[Migration ("20161121064725_MyMigration")]
partial class MyMigration
{
    protected override void BuildTargetModel (ModelBuilder modelBuilder)
    {
        modelBuilder
            .HasAnnotation ("ProductVersion", "1.0.0-rtm-21431");

        modelBuilder.Entity ("MyFirstApp.Models.User", b =>
            {
                b.Property <int> ("ID")
                    .ValueGeneratedOnAdd ();

                b.Property <string> ("Bio");

                b.Property <string> ("Email");

                b.Property <string> ("Name");

                b.HasKey ("ID");

                b.ToTable ("Users");
            });
    }
}

20161121064725_MyMigration.cs Partial class:

public partial class MyMigration: Migration
{
    protected override void Up (MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable (
            name: "Users",
            columns: table => new
            {
                ID = table.Column <int> (nullable: false)
                    .Annotation ("MySQL: AutoIncrement", true),
                Bio = table.Column <string> (nullable: true),
                Email = table.Column <string> (nullable: true),
                Name = table.Column <string> (nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey ("PK_Users", x => x.ID);
            });
    }

    protected override void Down (MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable (
            name: "Users");
    }
}

UserContextModelSnapshot class:

[DbContext (typeof (UserContext))]
partial class UserContextModelSnapshot: ModelSnapshot
{
    protected override void BuildModel (ModelBuilder modelBuilder)
    {
        modelBuilder
            .HasAnnotation ("ProductVersion", "1.0.0-rtm-21431");

        modelBuilder.Entity ("MyFirstApp.Models.User", b =>
            {
                b.Property <int> ("ID")
                    .ValueGeneratedOnAdd ();

                b.Property <string> ("Bio");

                b.Property <string> ("Email");

                b.Property <string> ("Name");

                b.HasKey ("ID");

                b.ToTable ("Users");
            });
}
}

The newly created database structure is as follows:

Compare the code in the above Migrations folder with the MySQL database table __EFMigrationsHistory. You will find that this table is used to track the migration information that has actually been applied to the database.

Create a User instance and save the instance to the database
public class Program
{
    public static void Main (string [] args)
    {
        using (var db = new UserContext ())
        {
            db.Users.Add (new User {Name = "Charlie Chu", Email = "[email protected]", Bio = "I am Chalrie Chu."});
            var count = db.SaveChanges ();

            Console.WriteLine ("{0} records saved to database", count);

            Console.WriteLine ();

            Console.WriteLine ("All users in database:");
            foreach (var user in db.Users)
            {
                Console.WriteLine ("-{0}", user.Name);
            }
        }
    }
}

Reference documents
HowTo: Starting with MySQL EF Core provider and Connector / Net 7.0.4
.NET Core-New Database
ASP.NET CORE 1.0 WITH MYSQL AND ENTITY FRAMEWORK CORE
ASP.NET CORE 1.0 WITH SQLITE AND ENTITY FRAMEWORK 7
personal blog
My personal blog

Create ASP.NET Core MVC application (3)-Create MySQL database table based on Entity Framework Core (Code First)