EntityFramework Core - Connect to MySQL

In article Connect to MySQL in .NET 5, I showed how to use MySQL.Data ADO.NET approach to read data from MySQL database. This article shows a different approach which utilizes EntityFramework Core APIs. We will use code-first approach even there is already test table created. 

About EF Core database provider

There are several MySQL EF core database providers. In this article, we will use Oracle's MySql.Data.EntityFrameworkCore 8.0.22.

Create a sample project

Create a .NET 5.0 console application project via Visual Studio 2019 or dotnet CLI and then add reference to the Nuget package mentioned above.

dotnet add package MySQL.Data.EntityFrameworkCore

The project file looks like the following:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net5.0</TargetFramework>
    <RootNamespace>dotnetcore_mysql</RootNamespace>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="MySQL.Data.EntityFrameworkCore" Version="8.0.22" />
  </ItemGroup>

</Project>

Add model Test

Add a model class named TestModel with the following properties:

using System.ComponentModel.DataAnnotations.Schema;

namespace  dotnetcore_mysql
{
    public class TestModel
    {
        [Column("id")]
        public int ID {get;set;}
        [Column("value")]
        public string Value {get;set;}
    }
}

This model has two properties named ID and Value which matches with the table schema in the MySQL database:

mysql> show create table test_table;
+------------+------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                       |
+------------+------------------------------------------------------------------------------------------------------------------------------------+
| test_table | CREATE TABLE `test_table` (
  `id` int(11) DEFAULT NULL,
  `value` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+------------------------------------------------------------------------------------------------------------------------------------+

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Options;
using System;
using System.Reflection;

namespace dotnetcore_mysql
{
    public class MyDbContext : DbContext
    {
        public DbSet<TestModel> TestModels { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var connString ="server=127.0.0.1;user id=hive;password=hive;port=10101;database=test_db";
            optionsBuilder.UseMySQL(connString, options =>
            {
                options.MigrationsAssembly(Assembly.GetExecutingAssembly().FullName);
            });
            base.OnConfiguring(optionsBuilder);
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Map table names
            modelBuilder.Entity<TestModel>().ToTable("test_table");
            base.OnModelCreating(modelBuilder);
        }
    }
}

Use MyDbContext

Retrieve data

using System;
using MySql.Data.MySqlClient;

namespace dotnetcore_mysql
{
    class Program
    {
        static void Main(string[] args)
        {
            EntityFrameworkMySQLExample();
        }

        static void EntityFrameworkMySQLExample()
        {
             using (var dbContext = new MyDbContext())
             {
                foreach (var model in dbContext.TestModels)
                 {
                     Console.WriteLine($"id={model.ID}\tvalue={model.Value}");
                 }
             }
             Console.ReadLine();
        }
    }
}

> dotnet run
id=0    value=Record 0
id=1    value=Record 1
id=2    value=Record 2
id=3    value=Record 3
id=4    value=Record 4

Add record

This example add data into existing table.

using System;
using MySql.Data.MySqlClient;

namespace dotnetcore_mysql
{
    class Program
    {
        static void Main(string[] args)
        {
            EntityFrameworkMySQLExample();
        }

        static void EntityFrameworkMySQLExampleAddRecord()
        {
            using (var dbContext = new MyDbContext())
            {
                var testModel = new TestModel
                {
                    ID = 5,
                    Value = "EF Core"
                };
                dbContext.Add(testModel);
                dbContext.SaveChanges();
            }
            Console.ReadLine();
        }
    }
}

> dotnet run
id=0    value=Record 0
id=1    value=Record 1
id=2    value=Record 2
id=3    value=Record 3
id=4    value=Record 4
id=5    value=EF Core

Update record

We can also easily update data using DbContext. The following code snippet updates value of record 6 (id=5) from 'EF Core' to 'EF Core!'.

        static void EntityFrameworkMySQLExampleUpdateRecord()
        {
            using (var dbContext = new MyDbContext())
            {

                var record6 = dbContext.TestModels.Where(record => record.ID == 5).FirstOrDefault();
                record6.Value = "EF Core !";
                dbContext.Update(record6);
                dbContext.SaveChanges();
            }
        }

> dotnet run
id=0    value=Record 0
id=1    value=Record 1
id=2    value=Record 2
id=3    value=Record 3
id=4    value=Record 4
id=5    value=EF Core !

Delete record

Deleting record is also very easy using Entity Framework. The following code snippet deletes the added record (id=5).

        static void EntityFrameworkMySQLExampleDeleteRecord()
        {
            using var dbContext = new MyDbContext();

            var record6 = dbContext.TestModels.Where(record => record.ID == 5).FirstOrDefault();
            if (record6 != null)
            {
                dbContext.Remove(record6);
                dbContext.SaveChanges();
            }
        }

Other references

SQLite in .NET Core with Entity Framework Core

Have fun with EntityFramework Core and MySQL!