MySQLの場合、コードファースト的にコードからデータベースを更新していけばよいのですが、既存のデータベースから Entity を作りたいときがあります。まあ、先日てもとの Redmine をバージョンアップさせて、せっかくなので .NET6 からアクセスしようと思って、さて、と思案した経過を残しておきます。
redmine.projects のエンティティクラスを手作業で作る
一番手っ取り早いのは、手作業でエンティティクラスを作ることです。エンティティクラスは単純な値クラスなので、プロパティを並べれば ok.
MySQL Workbench の結果から、ちまちまと C# のクラスを作るか、スキーマを参照しながら手作業で作ります。
CREATE TABLE `projects` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
`description` text,
`homepage` varchar(255) DEFAULT '',
`is_public` tinyint(1) NOT NULL DEFAULT '1',
`parent_id` int DEFAULT NULL,
`created_on` timestamp NULL DEFAULT NULL,
`updated_on` timestamp NULL DEFAULT NULL,
`identifier` varchar(255) DEFAULT NULL,
`status` int NOT NULL DEFAULT '1',
`lft` int DEFAULT NULL,
`rgt` int DEFAULT NULL,
`inherit_members` tinyint(1) NOT NULL DEFAULT '0',
`default_version_id` int DEFAULT NULL,
`default_assigned_to_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_projects_on_lft` (`lft`),
KEY `index_projects_on_rgt` (`rgt`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ;
- NULL 許可の部分をチェック
- tinyint(1) を bool に直す
- timestamp を DateTime あるいは DateTimeOffset に直す
- 主キーに Key 属性をつける
部分を注意すれば比較的簡単に C# のエンティティクラスができます。
public class projects
{
[Key]
public int id { get; set; }
public string name { get; set; } = "";
public string? description { get; set; }
public string? homepage { get; set; }
public bool is_public { get; set; }
public int parent_id { get; set; }
public DateTime created_on { get; set; }
public DateTime updated_on { get; set; }
public string identifier { get; set; } = "";
public bool status { get; set; }
public int? lft { get; set; }
public int? rgt { get; set; }
public int inherit_members { get; set; }
public int? default_version_id { get; set; }
public int? default_assigned_to_id { get; set; }
}個人的なプロジェクトならば、これで十分です（自分で趣味的に Redmine を扱うにもこれで十分）。
しかし、命名規則が C# にあっていないために大量に警告が出ます。さらに、仕事で使う場合にはプロパティ名自体が開発プロジェクト全体で使うために、あちこちに MySQL 特有のケバブケース（アンダースコアを使う命名法）が散らかってしまいます。たとえば、is_public ではなくて IsPublic にしておきたいわけで、ここで MySQL のカラム名と C# のプロパティ名の変換が発生します。
名前の変換は System.ComponentModel.DataAnnotations.Schema にある属性を使って切り替えます。
using System.ComponentModel.DataAnnotations.Schema;
[Table("proejcts")]
public class Project
{
...
[Column("is_public")]
public bool IsPublic { get; set; }
...
}Table 属性と Column 属性をちまちまと指定していけば、
- MySQL 側のカラム名を Column で指定
- プロパティ名は C# の命名規約に合わせる
ことができます。ちなみに MySQL の設定によってはテーブル名やカラム名の大文字小文字が区別されるため、環境にそろえようとすると（特に Linux上）、この属性は必須になります。
これを DbContext を継承したクラスに設定すれば、自由に LINQ が使えるようになります。
public class RedmineDataContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var builder = new MySqlConnectionStringBuilder();
builder.Server = "localhost";
builder.Database = "redmine";
builder.UserID = "redmine";
builder.Password = "redmine";
optionsBuilder.UseMySQL(builder.ConnectionString);
}
public DbSet<Project> Project => Set<Project>();
}さて、問題はここから、この手作業で作ったエンティティクラスをどのように大量に作っているのか？を考えます。数個のテーブルならば手作業で作ってよいのですが、数十個になると結構大変。100以上になるとちょっと手作業では太刀打ちできない。しかも、いつの間にかデータベースのほうから更新する場合に再び手作業に変えていくのはもっと大変な作業です。
コードファースト的にコードからデータベースを更新（マイグレーション）していけばいいのですが、既存のDBを扱う場合にはなかなかコードファーストという訳にはいきません。
テーブル構成を取得する
結論から言いますが、テーブル構成をちまちま取得するよりも、「dotnet ef dbcontext scaffold」でエンティティクラスを一気に作ったほうが楽です。dontet の scaffold は MySQL に正式には対応していないようなのですが、一応は動きます。
desc で取得する
MySQL のコマンドラインから desc ＜テーブル名＞ で取得が可能です。
MariaDB [redmine]> desc projects ;
+------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | | |
| description | text | YES | | NULL | |
| homepage | varchar(255) | YES | | | |
| is_public | tinyint(1) | NO | | 1 | |
| parent_id | int(11) | YES | | NULL | |
| created_on | timestamp | YES | | NULL | |
| updated_on | timestamp | YES | | NULL | |
| identifier | varchar(255) | YES | | NULL | |
| status | int(11) | NO | | 1 | |
| lft | int(11) | YES | MUL | NULL | |
| rgt | int(11) | YES | MUL | NULL | |
| inherit_members | tinyint(1) | NO | | 0 | |
| default_version_id | int(11) | YES | | NULL | |
| default_assigned_to_id | int(11) | YES | | NULL | |
+------------------------+--------------+------+-----+---------+----------------+
15 rows in set (0.01 sec)
MariaDB [redmine]>これをテキストに落としてからちまちま適当なスクリプトでコンバートしてもよいのですが、実は FromSqlRaw メソッドを使って直接 SQL 文を実行しても取得できます。
public class DescResult
{
[Key]
public string Field { get; set; }
public string Type { get; set; }
public string Null { get; set; }
public string? Key { get; set; }
public string? Default { get; set; }
public string? Extra { get ; set; }
}
public class RedmineDataContext : DbContext
{
...
public DbSet<DescResult> DescResult => Set<DescResult>();
}
var cnn = context.Database.GetDbConnection() as MySqlConnection;
var result = context.DescResult.FromSqlRaw("DESC projects");
Console.WriteLine("\nDESC projects");
Console.WriteLine("Field Type Null Key Default Extra");
foreach ( var it in result )
{
Console.WriteLine($"{it.Field}\t{it.Type}\t{it.Null}\t{it.Key}\t{it.Default}\t{it.Extra}");
}
DbContext に DescResult のように結果を含めるクラスが必要になるのが不満ではありますが、こんな風に取れます。
この中身をチェックして自前でエンティティクラスを取得してもよいでしょう。
GetSchema メソッドを使う
実は DbConnection クラスには GetSchema というスキーマ（テーブル構成）を取得するための便利なメソッドがついています。かつて VB で使っていた ADO に対する ADOX のようなもので、対象となるデータベースの構成情報が取得できます。これは、SQL Server に限らず、MySQL でも取れるのでこれを活用できます。
neue cc – Micro-ORMとテーブルのクラス定義自動生成について http://neue.cc/2013/06/30_411.html
を参考にしながら作っていきましょう。いまとなっては dynamic だと辛いので、エンティティクラスを作ります。
ところで、GetSchema に渡すテーブル名？はデータベースそれぞれになるので、Oracle とか SQL Server の場合は独自に探さないといけません。他のデータベースはさておき、MySQL の場合は、GetSchema(“COLUMNS”) で構成情報が取得できます。
エンティティクラスを作成するために（仕方がなく手作業になりますが）、列名の詳細が必要になります。
MySQL :: MySQL 8.0 Reference Manual :: 26.3.8 The INFORMATION_SCHEMA COLUMNS Table https://dev.mysql.com/doc/refman/8.0/en/information-schema-columns-table.html
MySQL のマニュアルを参照しながら、ちまちまと作ってもよいのですが、desc コマンドでも取れます。
MariaDB [redmine]> desc INFORMATION_SCHEMA.COLUMNS ;
+--------------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| COLUMN_NAME | varchar(64) | NO | | | |
| ORDINAL_POSITION | bigint(21) unsigned | NO | | 0 | |
| COLUMN_DEFAULT | longtext | YES | | NULL | |
| IS_NULLABLE | varchar(3) | NO | | | |
| DATA_TYPE | varchar(64) | NO | | | |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES | | NULL | |
| CHARACTER_OCTET_LENGTH | bigint(21) unsigned | YES | | NULL | |
| NUMERIC_PRECISION | bigint(21) unsigned | YES | | NULL | |
| NUMERIC_SCALE | bigint(21) unsigned | YES | | NULL | |
| DATETIME_PRECISION | bigint(21) unsigned | YES | | NULL | |
| CHARACTER_SET_NAME | varchar(32) | YES | | NULL | |
| COLLATION_NAME | varchar(32) | YES | | NULL | |
| COLUMN_TYPE | longtext | NO | | NULL | |
| COLUMN_KEY | varchar(3) | NO | | | |
| EXTRA | varchar(27) | NO | | | |
| PRIVILEGES | varchar(80) | NO | | | |
| COLUMN_COMMENT | varchar(1024) | NO | | | |
+--------------------------+---------------------+------+-----+---------+-------+
20 rows in set (0.01 sec)
- bigint(21) を System.UInt64 に変換
の部分を注意して、エンティティクラスを作ります。
[Table("COLUMNS", Schema = "INFORMATION_SCHEMA")]
public class MySqlColumns
{
public string TABLE_CATALOG { get; set; } = "";
public string TABLE_SCHEMA { get; set; } = "";
public string TABLE_NAME { get; set; } = "";
public string COLUMN_NAME { get; set; } = "";
public System.UInt64 ORDINAL_POSITION { get; set; }
public string? COLUMN_DEFAULT { get; set; }
public string IS_NULLABLE { get; set; } = "";
public string DATA_TYPE { get; set; } = "";
public System.UInt64? CHARACTER_MAXIMUM_LENGTH { get; set; }
public System.UInt64? NUMERIC_PRECISION { get; set; } = null;
public System.UInt64? NUMERIC_SCALE { get; set; } = null;
public System.UInt64? DATETIME_PRECISION { get; set; } = null;
public string? CHARACTER_SET_NAME { get; set; } = null;
public string? COLLATION_NAME { get; set; } = "";
public string? COLUMN_TYPE { get; set; } = null;
public string COLUMN_KEY { get; set; } = "";
public string EXTRA { get; set; } = "";
public string PRIVILEGES { get; set; } = "";
public string COLUMN_COMMENT { get; set; } = "";
}
これで、直接 INFORMATION_SCHEMA.COLUMNS を呼び出すか、 GetSchema(“COLUMNS”) を呼び出して結果を LINQ で検索すればお手軽ですね。と思ったのですが、落とし穴があります。
- なぜか MySQL の場合は、Table 属性で Schema を切り替えてくれない。
- GetSchema の戻り値が、DataTable になっている。
まずは、DataTable の中身をチェックします。
Console.WriteLine("MySQL GetSchema");
var context = new RedmineDataContext();
var cn = context.Database.GetDbConnection();
cn!.Open();
var t = cn.GetSchema("columns");
Console.WriteLine("columns.列名");
foreach (DataColumn col in t.Columns)
{
Console.WriteLine("{0} {1}", col.ColumnName, col.DataType);
}
Console.WriteLine("\ncolumns.rows");
Console.WriteLine("TABLE_NAME COLUMN_NAME COLUMN_TYPE IS_NULLABLE COLUMN_KEY EXTRA");
foreach ( DataRow row in t.Rows )
{
Console.WriteLine("{0} {1} {2} {3} {4} {5} {6}",
row["TABLE_NAME"],
row["COLUMN_NAME"],
row["COLUMN_TYPE"],
row["IS_NULLABLE"],
row["COLUMN_KEY"],
row["COLUMN_DEFAULT"],
row["EXTRA"]);
}
columns のテーブル構造は、DataTable#Columns を使っても確認ができます。DataRow にいちいちカラム名を指定するのがアレですが、ひとまず DESC コマンド互換のものが取得できます。
DataTable からエンティティへ変換する拡張を作る
DataTable.Rows からエンティティに変換させる拡張メソッドを作ります。
public static class DataTableExtenstions
{
/// <summary>
/// DataTable.Rows を指定した List＜T＞に変換する
/// </summary>
public static List<T> ToList<T>(this DataTable src) where T : new()
{
var items = new List<T>();
var properties = typeof(T).GetProperties();
// TODO: Column 属性があれば、探索するカラム名を変更する
foreach ( DataRow row in src.Rows )
{
var item = new T();
foreach ( var pi in properties )
{
var value = row[pi.Name];
if ( value == System.DBNull.Value )
{
pi.SetValue(item, null);
}
else
{
pi.SetValue(item, row[pi.Name]);
}
}
items.Add(item);
}
return items;
}
}本当は Column 属性を調べないとダメなのですが、これは後日。ひとまず、詰め込みをできる ToList メソッドを作ったので、これを使って LINQ で使えるようにします。
var items = cn.GetSchema("columns").ToList<MySqlColumns>();
Console.WriteLine("\nMySqlColumns");
Console.WriteLine("TABLE_NAME COLUMN_NAME COLUMN_TYPE IS_NULLABLE COLUMN_KEY EXTRA");
items = items.OrderBy( t => t.TABLE_NAME).ThenBy( t => t.ORDINAL_POSITION ).ToList();
foreach (var it in items)
{
Console.WriteLine($"{it.TABLE_NAME} {it.COLUMN_NAME} {it.COLUMN_TYPE} {it.IS_NULLABLE} {it.COLUMN_KEY} {it.COLUMN_DEFAULT} {it.EXTRA}");
}
GetSchema の戻り値は、テーブル名等でソートされていないので、ソートしておきます。
結果は DESC コマンドと似た感じで取得できます。多分、DESC コマンドのほうは整形しているのでしょう。
これを使って T4 で変換してもよいし、別途コマンドツールを使って Models 群を作ります。が、ここで再び dotnet ef dbcontext scaffold に立ち戻って、もうちょっとスイッチを使ったら使いやすいように出ないか？（特に命名規約の部分）を調べ直します。
dotnet ef dbcontext scaffold を使う
使い方が下記のページに書いてあります。
リバース エンジニアリング – EF Core | Microsoft Docs https://docs.microsoft.com/ja-jp/ef/core/managing-schemas/scaffolding?tabs=dotnet-core-cli
書いてはあるのですが、実に使いにくいのです。dotnet ef コマンド自体がコードファーストのためにツールという感じがするので、あまり既存のデータベースから細かく取得することができません。まあ、それでもおおざっぱに取れればいいので、以下のコマンドを powershell で実行します。
コマンドを実行するときは、
- Microsoft.EntityFrameworkCore.Design を含んだプロジェクトであること
が必須です。もともと webapi や mvc プロジェクトにエンティティクラスを追加するコマンドなので、このような形になっています。
dotnet ef dbcontext scaffold `
"Server=localhost;User=redmine;Password=redmine;Database=redmine" `
"MySql.EntityFrameworkCore" `
--data-annotations `
-o Models `
--table projects `
--force
- `（バッククォート）は継続文字です
- 接続文字列をそのまま記述します（これが結構面倒）
- 接続するためのアセンブリを指定。MySQL の場合は、MySql.EntityFrameworkCore
- –data-annotations C# と MySQL のカラム名の変換を属性で指定する
- Models フォルダーに出力（これがないと、カレントフォルダーにばらまかれる）
- –table スイッチで出力するテーブルを指定。指定しないと全テーブルが対象になる。
- –force 上書き用のスイッチ
–data-annotations が肝で、デフォルトでは、DbContext の OnModelCreating にばらばらと記述されるのですが、これをエンティティクラスの属性に切り替えます。
これがうまくいくと Models/Project.cs が出力されます。この部分は、ASP.NET MVC 本とか Blazor本とかで必ず出てくるのですが、dotnet ef コマンドの扱いが難しくて、躓きやすいところですよね。なんとか乗り越えてください。
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;
#nullable disable
namespace getschema.Models
{
[Table("projects")]
[Index(nameof(Lft), Name = "index_projects_on_lft")]
[Index(nameof(Rgt), Name = "index_projects_on_rgt")]
public partial class Project
{
[Key]
[Column("id", TypeName = "int(11)")]
public int Id { get; set; }
[Required]
[Column("name")]
[StringLength(255)]
public string Name { get; set; }
[Column("description")]
public string Description { get; set; }
[Column("homepage")]
[StringLength(255)]
public string Homepage { get; set; }
[Required]
[Column("is_public")]
public bool? IsPublic { get; set; }
[Column("parent_id", TypeName = "int(11)")]
public int? ParentId { get; set; }
[Column("identifier")]
[StringLength(255)]
public string Identifier { get; set; }
[Column("status", TypeName = "int(11)")]
public int Status { get; set; }
[Column("lft", TypeName = "int(11)")]
public int? Lft { get; set; }
[Column("rgt", TypeName = "int(11)")]
public int? Rgt { get; set; }
[Column("inherit_members")]
public bool InheritMembers { get; set; }
[Column("default_version_id", TypeName = "int(11)")]
public int? DefaultVersionId { get; set; }
[Column("default_assigned_to_id", TypeName = "int(11)")]
public int? DefaultAssignedToId { get; set; }
}
}Visual Studio 2022 ではデフォルトで nullable enable となっているので、警告をおさえるために「#nullable disable」が追加になっています。
すべてのカラム（プロパティ）にColumn属性がついているので、MySQLとC#のマッピングが出来た状態です。
これでおおむねはいけますね。
timestamp のカラムが消されているので注意
スキャフォードしたときに警告が出ているのですが、先の Project クラスを見ると、created_on と updated_on に相当するものがありません。実は、この2つのカラムの型は「timestamp」となっているので、C# の型に自動でコンバートしないため、削除されてしまっているのです。
仕方がないので、
- timestamp を DatTime に変換
したプロパティを手作業で追加します。
MySQL の場合は、時間絡みの型がいくつかってそれをC#にどのようにコンバートするのか、日付の 0000/00/00 はどう扱うのか、が問題になるので注意しなければいけないところです。
でもって、やっとこさエンティティクラスの出力の目途が立ってので、これ gRPC で利用します。これは後日。
Pomelo.EntityFrameworkCore.MySql を使う
追記で、
スキャフォードを使うときに、Pomelo.EntityFrameworkCore.MySql を使うと timestamp の問題が解決できます。
PomeloFoundation/Pomelo.EntityFrameworkCore.MySql: Entity Framework Core provider for MySQL and MariaDB built on top of MySqlConnector https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql
dotnet ef dbcontext scaffold `
"Server=localhost;User=redmine;Password=redmine;Database=redmine" `
"Pomelo.EntityFrameworkCore.MySql" `
--data-annotations `
-o Models `
--table projects `
--forceこの状態で project.cs が以下のように出力されます。
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;
namespace getschema.Models
{
[Table("projects")]
[Index(nameof(Lft), Name = "index_projects_on_lft")]
[Index(nameof(Rgt), Name = "index_projects_on_rgt")]
[MySqlCharSet("utf8")]
[MySqlCollation("utf8_general_ci")]
public partial class Project
{
[Key]
[Column("id", TypeName = "int(11)")]
public int Id { get; set; }
[Required]
[Column("name")]
[StringLength(255)]
public string Name { get; set; }
[Column("description", TypeName = "text")]
public string Description { get; set; }
[Column("homepage")]
[StringLength(255)]
public string Homepage { get; set; }
[Required]
[Column("is_public")]
public bool? IsPublic { get; set; }
[Column("parent_id", TypeName = "int(11)")]
public int? ParentId { get; set; }
[Column("created_on", TypeName = "timestamp")]
public DateTime? CreatedOn { get; set; }
[Column("updated_on", TypeName = "timestamp")]
public DateTime? UpdatedOn { get; set; }
[Column("identifier")]
[StringLength(255)]
public string Identifier { get; set; }
[Column("status", TypeName = "int(11)")]
public int Status { get; set; }
[Column("lft", TypeName = "int(11)")]
public int? Lft { get; set; }
[Column("rgt", TypeName = "int(11)")]
public int? Rgt { get; set; }
[Column("inherit_members")]
public bool InheritMembers { get; set; }
[Column("default_version_id", TypeName = "int(11)")]
public int? DefaultVersionId { get; set; }
[Column("default_assigned_to_id", TypeName = "int(11)")]
public int? DefaultAssignedToId { get; set; }
}
}
- #nullable disable は手作業で加える。
