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 TestModelwith 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 MyDbContextinherited 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 DbContextinstance. Function SaveChangesAsyncis 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!