Prepare to access MySQL with Entity Framework Core (database-first)

Operation confirmation environment

Visual Studio

• Visual Studio 2022

.NET

• .NET 8

Entity Framework Core

• Entity Framework Core 8.0

MySQL

• MySQL 8.4

Pomelo.EntityFrameworkCore.MySql

• 8.0.2

*The above is a verification environment, but it may work in other versions.

At first

Entity Framework Core is a library of O/R mappers that allow you to access database records through code-defined models (classes) without directly writing SQL statements when accessing the database. This provides the following benefits:

• Doesn't write SQL statements directly, reducing security risks such as SQL injection
• Since SQL statements are strings, even if they make a mistake in the syntax, they are not subject to check for build errors, but since the model is a program syntax, it can be checked for errors at build time.

Entity Framework Core automatically generates code to connect to these models and databases from existing databases, Conversely, there is a way to manually write code and then automatically generate a database.

The former is called "database-first" and the latter is called "code-first". There is also "model-first" that generates code and databases from blueprints, such as ER diagrams, but it is not widely used in Entity Framework Core.

This time, we'll use a "database-first" pattern that generates code on the assumption that a database already exists.

MySQL Setup

In this tip, make sure you set up MySQL beforehand to access your MySQL database. It can be set up on a PC in your development environment or on another PC over the network. If you can connect to MySQL from your development environment, you're good to go. In this tip, we have installed MySQL in a different environment.

The MySQL setup steps will be omitted as they will be redundant. The following pages contain MySQL-related tips for setup steps.

• MySQL Tips

Creating a table

In this case, we will create the following schema (database) and table as a sample.

• Schema Name : test_schema
• Table Name : user
• Table Columns : [id], [name], [password], [age], [email], [birthday], [is_provisional_registration], [update_datetime]

It doesn't matter how you write it, but if it's tedious, run and generate the following SQL against MySQL:

The following is schema creation SQL:

CREATE DATABASE `test_schema`;

Table creation is SQL.

CREATE TABLE `user` (
  `id` int NOT NULL,
  `name` varchar(20) NOT NULL,
  `password` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `email` varchar(200) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `is_provisional_registration` tinyint(1) NOT NULL,
  `update_datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='テストコメント';

Add a record to SQL.

INSERT INTO `test_schema`.`user` (`id`,`name`,`password`,`age`,`email`,`birthday`,`is_provisional_registration`,`update_datetime`) VALUES (1, '氏名１', 'aaaa', 20, 'aaaa@example.com', '2020-04-01', 0, '2021-03-14T00:00:00.0000000');
INSERT INTO `test_schema`.`user` (`id`,`name`,`password`,`age`,`email`,`birthday`,`is_provisional_registration`,`update_datetime`) VALUES (2, '氏名２', 'bbbb', 30, 'bbbb@example.com', '2010-04-01', 1, '2021-03-14T00:00:00.0000000');

Set permissions for your account

Make sure you have a hostname set up so that the account you program can access the MySQL schema.

You also need to set permissions to test_schema access the account you want to connect to.

This time, we will perform SELECT the process of retrieving the records. I won't do it this time, but if you INSERT UPDATE want to insert or update, please check and .

Visual Studio Setup

Assuming you've already set it up as well. If you want to know the setup procedure, we have summarized it on the following page.

• Visual Studio Tips

Create a project

Entity Framework Core does not depend on a specific execution environment, so it can be used in many projects. In this case, we'll use Entity Framework Core in a simple console application environment.

In the new project, select Console App (whichever is not .NET Framework).

You have created the project. The project name can be anything.

Get the Entity Framework Core package for MySQL

Get a package to use Entity Framework Core with NuGet. The package uses: This is not official from Microsoft, but it is widely used.

• Pomelo.EntityFrameworkCore.MySql

Right-click on the dependency and select Manage NuGet Packages.

With "Browse" selected from the tab, type in the search field Pomelo . The list of related packages will be displayed.

Install the following packages from this:

• Pomelo.EntityFrameworkCore.MySql

Select what you want to install and click the Install button. Select the latest stable version for the version.

The dialog is basically OK and fine.

I think the package is as follows.

Then type EntityFrameworkCore in the search field and install the following as well.

• Microsoft.EntityFrameworkCore.Tools

For versions, Pomelo.EntityFrameworkCore.MySql match the version referenced Microsoft.EntityFrameworkCore by . This time we are referring to 8.0.2, so Microsoft.EntityFrameworkCore.Tools let's make it 8.0.2 as well. This package is used to generate code from the database.

In the end, it should look like this:

Create a model (code) from a database table configuration

To have your model and other code automatically generated, you must first build your project to make sure there are no errors. If there is an error, the model will fail. If you have already verified that there are no errors, you do not need to build.

Open the Package Manager Console from Visual Studio. If not, you can open it from the menu Tools, NuGet Package Manager, and Package Manager Console.

You will see a window like the one below, so make sure that the "Default project" in the upper right corner is the project you want to create the model for. (You need to be careful if you have multiple projects)

Enter the following text in the input field: The parameters will change depending on the environment, so please change them in a timely manner based on the following explanations.

Scaffold-DbContext "Server=<サーバー名>; Database=<スキーマ名>; user=<接続ユーザー名>;password=<接続パスワード>;" Pomelo.EntityFrameworkCore.MySql -f -OutputDir "<出力フォルダパス>" -Context "<コンテキストクラス名>" -UseDatabaseNames -DataAnnotations -NoPluralize

Input Example

Scaffold-DbContext "Server=TestServer; Database=test_schema; user=test_user;password=XXXXXXXX;" Pomelo.EntityFrameworkCore.MySql -f -OutputDir "Models\Database" -Context "TestDatabaseDbContext" -UseDatabaseNames -DataAnnotations -NoPluralize

Press Enter to run it and auto-generate the code: If it is an error, the reason will be displayed, so please respond according to the content of the error.

user The model code for the table looks like this:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace SetupMysqlDatabaseFirst.Models.Database;

/// <summary>
/// テストコメント
/// </summary>
public partial class user
{
  [Key]
  public int id { get; set; }

  [StringLength(20)]
  public string name { get; set; } = null!;

  [StringLength(20)]
  public string? password { get; set; }

  public int? age { get; set; }

  [StringLength(200)]
  public string? email { get; set; }

  public DateOnly? birthday { get; set; }

  public bool is_provisional_registration { get; set; }

  [Column(TypeName = "datetime")]
  public DateTime? update_datetime { get; set; }
}

By the way, the warning is displayed because the connection string is written as it is in the code of the generated context class. If possible, the connection string should be stored in a separate place and set at runtime, but this time it is for the purpose of checking the operation, so I will leave it as it is.

Get a record and view it

Now that you have the code to access the records in the database, let's try to get the records and display them in the console.

Program.cs and modify it as follows.

using SetupMysqlDatabaseFirst.Models.Database;
using System.Text.Json;

Console.WriteLine("Hello, World!");

// データベースコンテキストのインスタンスを生成する
using var dbContext = new TestDatabaseDbContext();

// データベースから User 一覧を取得する
var users = dbContext.user.ToList();

// 取得した User 情報をコンソールに書き出す
foreach (var user in users)
{
  Console.WriteLine(JsonSerializer.Serialize(user));
}

Generate the DbContext auto-generated class new with . It is declared in to allow using var the database connection to be automatically destroyed.

dbContext has generated properties to access each model, so this time user you can manipulate the records in the table by User accessing the properties. You don't need to be aware of the SQL that is issued because it is automatically generated internally.

ToList Here we are using user an extension method to get all the records in the table.

foreach After that, I use user the and JsonSerializer.Serialize methods to display the information in the console. As user mentioned above, each column in the table is declared as a property, so it is also possible to retrieve the value individually.