EntityFramework Core - Connect to MySQL
insights Stats
Everything about .NET framework, .NET Core, .NET Standard, .NET 5 and .NET 6.
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
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!