EntityFramework Core - Connect to MySQL

event 2021-01-23 thumb_up 0 visibility 751 comment 0 insights toc

more_vert

warning Please login first to view stats information.

About EF Core database provider
Create a sample project
Add model Test
Create a DbContext class named MyDbContext
Use MyDbContext
Retrieve data
Add record
Update record
Delete record
Other references

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 |
+------------+------------------------------------------------------------------------------------------------------------------------------------+

Create a DbContext class named MyDbContext

Create a class MyDbContext inherited from DbContext.

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);
        }
    }
}

In this DbContext, connection string to MySQL database is specified based on my system; please change it accordingly.

In the override function OnConfiguring, UseMySQL extended function is used to configure the connection string. In the options configuration, migration assembly is also configured.

Use MyDbContext

Now we can use MyDbContext directly to manage data in the test MySQL database.

Retrieve data

The following example use it to retrieve data and you can easily change it to add, delete or update 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();
        }
    }
}

Build and then run the program. The output looks like the following:

> 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();
        }
    }
}

The above program first creates a new model object and then add it to the DbContext instance. Function SaveChangesAsync is called to save the changes to the database.

Run the retrieve data function again and the output will now have one extra record:

> 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();
            }
        }

Run the retrieve data function again and the output looks like the following:

> 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!