C#: EFCoreで動的にテーブル名・キー・列名等を取得

2023年9月15日
.NET Core(C#), システムエンジニアリング, 実装技術
.NET6, C#, EFCore, Entity Framework Core, カラム名, テーブル名, 主キー

1 概要
2 基本サンプル
3 応用サンプル

概要

xUnit等でデータベース（EFCore）を使ったテストケースを作成する際、エンティティやテーブル定義に基づいてテストデータを作成したい場合があります。そのようなユースケースをサポートするためのサンプルを紹介します。

使用環境は次の通りです。

OS	Windows 10(64ビット)
IDE	Microsoft Visual Studio Community 2022(17.6.0)
言語	C#(10.0) ＋ .NET6
パッケージ	Microsoft.EntityFrameworkCore (7.0.10) Microsoft.EntityFrameworkCore.Design (7.0.10) Microsoft.EntityFrameworkCore.SqlServer (7.0.10)
データベース	SQL Server 2022 Developer (16.0)

完全なソースコードはgithubで公開しています。
- サンプルコードの全てはEfCoreExampleTests.csに含まれています。想定しているテーブル定義はddl_dml.sqlで定義されています。
- サンプルのコードは全て「null 許容未指定」にしており、これは.csprojファイルのNullableディレクティブで設定しています。お使いの環境でサンプルを使用するとnull許容関連の警告が出力される場合がありますが、適宜変更してください。
- ここで掲載しているサンプルコードは、可読性を高めるために一部コメントを消しています。

基本サンプル

テーブル名、カラム名、型の一覧の取得

MEmployeeに対応するテーブル名・カラム名、カラム型、NULL許容をダンプするサンプルです。
```
public void DumpTableColumnsTest()
{
    using var context = new AppDbContext();

    var type = typeof(MEmployee);
    var entityType = context.Model.FindEntityType(type);

    var tableName = entityType.GetTableName();

    var columns = entityType.GetProperties();
    foreach (var prop in columns)
    {
        var colName = prop.GetColumnName();
        var colType = prop.GetColumnType();
        var nullable = prop.IsNullable ? "NULL" : "NOT NULL";

        Console.WriteLine($"{tableName}.{colName}: {colType}, {nullable}");
    }
}
```
- EFCoreでは、テーブル定義の情報はXXXDBContext.OnModelCreating()で実装します。これらの情報は、XXXDBContext.Modelから取得できます。
- エンティティ・テーブルの情報はIEntityTypeで表現されます。
  XXXDBContext.ModelのFindEntityType()やGetEntityTypes()を使って、特定のエンティティや全てのエンティティ・テーブルのIEntityTypeを取得できます。
- プロパティ・カラムの情報はIPropertyで表現されます。
  IEntityTypeのGetProperties()やGetProperties(string)を使って、特定プロパティや全てのプロパティのIPropertyを取得できます。
- カラム名の取得方法に関して、EFCore6のGetColumnName()は非推奨（Obsolete）になっていますが、EFCore7のGetColumnName()は非推奨になっていません。
  EFCore6でカラム名を取得する場合、GetColumnName(StoreObjectIdentifier.Table(tableName)等のように取得できます。

実行結果は次の通りです。

m_employee.user_id: int, NOT NULL
m_employee.address: nvarchar(2048), NULL
m_employee.birthday: datetime2, NOT NULL
m_employee.created_by: varchar(8), NOT NULL
m_employee.created_on: datetime2, NOT NULL
m_employee.employee_no: varchar(10), NOT NULL
m_employee.gender: tinyint, NULL
m_employee.internal_id: uniqueidentifier, NULL
m_employee.name: nvarchar(256), NOT NULL
m_employee.retired: bit, NOT NULL
m_employee.updated_by: varchar(8), NOT NULL
m_employee.updated_on: datetime2, NOT NULL
m_employee.version: rowversion, NOT NULL

主キーの取得

TSalesエンティティ・テーブルの主キープロパティ名・カラム名を出力するサンプルです。

public void DumpPrimaryKeysTest()
{
    using var context = new AppDbContext();
    var entityType = context.Model.FindEntityType(typeof(TSales));

    // エンティティからの主キー情報の取得
    var pkProps = entityType.FindPrimaryKey()?.Properties;
    foreach (var p in pkProps)
    {
        var colName = p.GetColumnName();
        var colType = p.GetColumnType();
        Console.WriteLine($"{p.Name}[{p.ClrType}]: {colName}[{colType}]");
    }

    // 各プロパティを主キーか判定
    var props = entityType.GetProperties();
    foreach(var p in props)
    {
        var isPk = p.IsPrimaryKey();
        Console.WriteLine($"{p.Name}[{p.ClrType}]: {isPk}");
    }
}

主キーの情報はIKeyで表現され、IEntityType.FindPrimaryKey()を実行して取得できます。
エンティティのプロパティ(IProperty)毎に主キーかどうかを判定したい場合、IPropertyのIsPrimaryKey()を使用します。
レアケースだと思いますが、主キーが設定されていないテーブルの場合、FindPrimaryKey()がnullを返却することに注意が必要です。

実行結果は次の通りです。

RegionId[System.Byte]: region_id[tinyint]
Year[System.Int16]: year[smallint]
Month[System.Byte]: month[tinyint]
---
RegionId[System.Byte]: True
Year[System.Int16]: True
Month[System.Byte]: True
CreatedBy[System.String]: False
CreatedOn[System.DateTime]: False
...

外部キーの取得

MOrderDetailの各カラムの外部キー参照先を出力するサンプルです。

public void DumpForeignKeysTest()
{
    using var context = new AppDbContext();
    var entityType = context.Model.FindEntityType(typeof(MOrderDetail));

    // エンティティからの外部キー情報の取得
    var fksFromEntity = entityType.GetForeignKeys();
    foreach (var fk in fksFromEntity) DumpForeignKey(fk);

    // プロパティを指定した外部キー情報の取得
    var prop = entityType.FindProperty(nameof(MOrderDetail.OrderId));
    var fksFromProp = entityType.FindForeignKeys(prop);
    foreach (var fk in fksFromProp) DumpForeignKey(fk);

    // 各プロパティからの外部キー情報の取得
    var props = entityType.GetProperties();
    foreach (var p in props)
    {
        var fks = p.GetContainingForeignKeys();
        foreach (var fk in fks) DumpForeignKey(fk);
    }
}
private void DumpForeignKey(IForeignKey fk)
{
    var fromKeys = fk.Properties.Select(e => e.GetColumnName());
    var toTable = fk.PrincipalEntityType.GetTableName();
    var toKeys = fk.PrincipalKey.Properties.Select(e => e.GetColumnName());
    var fromKeysStr = string.Join(", ", fromKeys);
    var toKeysStr = string.Join(", ", toKeys);
    Console.WriteLine($"[{fromKeysStr}] -> {toTable}[{toKeysStr}]");
}

外部キーの情報はIForeignKeyで表現されます。
エンティティ・テーブルに設定された全ての外部キー情報や特定プロパティの外部キー情報を取得したい場合、IEntityType.GetForeignKeys()を使用します。
参照元となるプロパティ・カラム情報の一覧はIForeignKey.Propertiesプロパティから取得できます。参照先となるエンティティは同様にIEntityTypeで表現され、IForeignKey.PrincipalEntityTypeプロパティから取得できます。（参照先となるエンティティ・キーはPrincipalXXXというネーミングのプロパティです。）
これらの情報の取得方法は、EFCoreソースコードIReadOnlyForeignKey.csのToDebugString()を参考にしています。

実行結果は次の通りです。

[order_id] -> m_order[order_id]
[product_type, product_id] -> m_product[type, id]
---
[order_id] -> m_order[order_id]
---
[order_id] -> m_order[order_id]
[product_type, product_id] -> m_product[type, id]
[product_type, product_id] -> m_product[type, id]

サンプルのMOrderDetailテーブルでは、order_id列からm_orderテーブルのorder_id列に外部キーを設定しています。また、product_type, product_id列からm_productテーブルのtype, id列に外部キーを設定しています。

CREATE TABLE [dbo].[m_order_detail](
	[order_id] [int] NOT NULL,
	[product_type] [smallint] NOT NULL,
	[product_id] [int] NOT NULL,
	[created_by] [varchar](8) NOT NULL,
	[created_on] [datetime2] NOT NULL,
	[updated_by] [varchar](8) NOT NULL,
	[updated_on] [datetime2] NOT NULL,
	[version] [timestamp] NOT NULL,
	CONSTRAINT [pk_m_order_detail] PRIMARY KEY CLUSTERED ([order_id], [product_id]),
	CONSTRAINT [fk_m_order_detail_order_id] FOREIGN KEY ([order_id])
		REFERENCES [m_order] ([order_id]),
	CONSTRAINT [fk_m_order_detail_product_id] FOREIGN KEY ([product_type], [product_id]) 
		REFERENCES [m_product] ([type], [id])
) ON [PRIMARY]
GO

応用サンプル

全テーブルデータの削除

データベース上の全てのテーブルからデータを削除するサンプルです。

データベース上の全てのテーブルに対してtruncate文を実行します。外部キーの参照先テーブルはtruncateできないので、deleteします。
単体テスト実行時、最新のエンティティに対応するDB環境を準備するために、DbContext.DatabaseのEnsureCreated(), EnsureDeleted()を使って都度DBを再構築する方法が考えられます。都度のデータベースの再作成はコストが高いので、２回目以降はこのようなユーティリティメソッドを使ってデータベースを初期状態に戻す使い方を想定しています。

public async Task TruncateTablesTest()
{
    using var context = new AppDbContext();
    var entities = context.Model.GetEntityTypes();

    // 外部キー参照先テーブル一覧(truncate不可テーブル)
    var fkToTables = entities
        .SelectMany(e => e.GetForeignKeys().Select(k => k.PrincipalEntityType.GetTableName()))
        .Where(e => !string.IsNullOrEmpty(e))
        .Distinct();
    // 外部キーなしテーブル一覧(truncate可テーブル)
    var noFkTables = entities
        .Select(e => e.GetTableName())
        .Where(e => !string.IsNullOrEmpty(e))
        .Except(fkToTables);

    var truncates = noFkTables.Select(e => $"truncate table [{e}]");
    var deletes = fkToTables.Select(e => $"delete from [{e}]");
    foreach (var sql in truncates.Concat(deletes))
    {
        Console.WriteLine(sql);
        await context.Database.ExecuteSqlRawAsync(sql);
    }
}

実行結果の例は次の通りです。

truncate table [m_employee]
truncate table [m_order_detail]
truncate table [t_sales]
truncate table [z_test_nopk]
delete from [m_order]
delete from [m_product]

なお、外部キーの参照先テーブルをtruncateすると次のエラー(MSG: 4712)になります。

SqlException : FOREIGN KEY 制約でテーブル 'テーブル名' が参照されているので、このテーブルは切り捨てられません。
(Cannot truncate table 'table-name' because it is being referenced by a FOREIGN KEY constraint.)

既定値ありカラムをnullに更新

既定値が設定されたカラムをnullに更新するサンプルです。

既定値の設定があるプロパティ・カラムでnull値のテストを行いたい場合に使用する想定です。
既定値が設定された項目にnullを設定する場合、データ登録後にUPDATE文でnullに更新する必要があります。そのUPDATE文を自動的に生成するサンプルです。
“update XXX set c1={0}, c2={0}, … where k1={1} and k2={2}, …”等のように、エンティティに設定されたキー値を条件に、該当項目をnullに更新するクエリとなります。

public async Task UpdateNullColumnsTest()
{
    using var context = new AppDbContext();
    var e = new TSales()
    {
        RegionId = 1,
        Year = 2023,
        Revenue = null, // default: 0
        Expense = 500, // default: 0
        Profit = -500 // default: 0
    };
    await context.AddAsync(e);

    // 既定値指定がありnullが設定されているプロパティをnullにするSQLを生成する。
    var (nullUpdateSql, nullUpdatePs) = CreateNullUpdateSql(context, e);

    await context.SaveChangesAsync();

    var affected = await context.Database.ExecuteSqlRawAsync(nullUpdateSql, nullUpdatePs);
    Assert.Equal(1, affected);
}
private (string, object[]) CreateNullUpdateSql(DbContext context, object entity)
{
    var modelType = context.Model.FindEntityType(entity.GetType());
    var modelProps = modelType.GetProperties();
    var piDic = modelType.ClrType.GetProperties().ToDictionary(k => k.Name, v => v);

    // null更新対象カラムの特定
    var nullCols = modelProps
        .Where(e => !string.IsNullOrEmpty(e.GetDefaultValueSql())) // 既定値がある列
        .Where(e => piDic[e.Name].GetValue(entity) == null) // 値がnullのプロパティ
        .Select(e => e.GetColumnName());
    if (!nullCols.Any()) return (null, null);

    // キーカラム・値の抽出
    var keyProps = modelType.FindPrimaryKey()?.Properties;
    var keyCols = keyProps.Select(p => p.GetColumnName());
    var keyVals = keyProps.Select(p => piDic[p.Name].GetValue(entity));

    // SQL文とパラメータの構築
    var table = modelType.GetTableName();
    var setCols = nullCols.Select(c => $"{c} = {{0}}");
    var whrCols = keyCols.Select((k, i) => $"{k}={{{i + 1}}}");
    var sql =
        $"update {table} " +
        $"set {string.Join(", ", setCols)} " +
        $"where {string.Join(" AND ", whrCols)}";
    var prms = new object[] { null }.Concat(keyVals).ToArray();

    Console.WriteLine($"sql=\"{sql}\"");
    Console.WriteLine($"params={{{string.Join(",", prms)}}}");
    return (sql, prms);
}

CreateNullUpdateSql()では、既定値が設定されており、その値がnullのプロパティ・カラムをnull更新対象としたSET句を生成します。エンティティの主キーとなっているプロパティからwhere句を生成します。
SaveChangesAsync()を実行すると、既定値がエンティティに反映されてしまうので、どのプロパティをnullにするのか判別が困難になります。そのため、CreateNullUpdateSql()の呼び出しはSaveChangesAsync()前にしています。

実行結果の例は次の通りです。

sql="update t_sales set revenue = {0} where region_id={1} AND year={2} AND month={3}"

INSERT可能なエンティティの自動生成

エンティティ・テーブル定義に基づいてINSERT可能なEntityを自動的に生成するサンプルです。

NOT NULLカラムが多数あるようなテーブル用のテストデータを作成するのは結構な手間がかかります。業務システムだと数百のカラムがある（設計が良くない）テーブルもあり、手動で値を設定するのは現実的ではありません。
このような問題を解決するためのユーティリティメソッドです。このメソッドで生成したエンティティを雛形として、テストに必要なカラムに値を設定する使い方を想定しています。

public async void CreateInsertableTest()
{
    using var context = new AppDbContext();
    var e = CreateInsertableEntity<MEmployee>(context);
    var p = CreateInsertableEntity<MProduct>(context);
    await context.AddRangeAsync(e, p);
    await context.SaveChangesAsync();
}
public T CreateInsertableEntity<T>(DbContext context) where T : class
{
    // 本来はクラス変数等で別定義
    var excludes = "createdby,createdon,updatedby,updatedon,version"
        .Split(",").ToHashSet(StringComparer.OrdinalIgnoreCase);

    var type = typeof(T);
    var target = (T)Activator.CreateInstance(type);

    var modelType = context.Model.FindEntityType(type);
    var vgs = new[] { ValueGenerated.OnAdd, ValueGenerated.OnAddOrUpdate };
    foreach (var mp in modelType.GetProperties())
    {
        // null許容、ValueGenerated(Identity, rowversion等の値指定不可列)、
        // 監査用情報を格納する業務独自列等は対象外
        var propName = mp.Name;
        if (mp.IsNullable ||
            vgs.Contains(mp.ValueGenerated) || excludes.Contains(propName)) continue;

        // NotNull列は、その型に応じて適当な値を設定
        var propInfo = type.GetProperty(propName);
        var propType = propInfo.PropertyType;
        object value;
        if (propType == typeof(string)) value = "str";
        else if (propType == typeof(byte)) value = (byte)1;
        else if (propType == typeof(short)) value = (short)2;
        else if (propType == typeof(int)) value = 3;
        else if (propType == typeof(long)) value = 4L;
        else if (propType == typeof(float)) value = 5f;
        else if (propType == typeof(double)) value = 6d;
        else if (propType == typeof(decimal)) value = 7m;
        else if (propType == typeof(DateTime)) value = new DateTime(2000, 1, 1);
        else if (propType == typeof(bool)) value = true;
        else throw new NotImplementedException($"unknown type: {propType.FullName}");
        Console.WriteLine($"{propName}({propType.Name}) = {value}");
        propInfo.SetValue(target, value);
    }
    return target;
}

実行結果の例は次の通りです。

Birthday(DateTime) = 2000/01/01 0:00:00
EmployeeNo(String) = str
Name(String) = str
Retired(Boolean) = True
Type(Int16) = 2
Id(Int32) = 3
Price(Decimal) = 7
ProductCode(String) = str
ProductName(String) = str

実行対象テーブルの動的な変更

動的にDBContextのテーブル（DBSet）を切り替えてレコード件数を表示するサンプルです。

public async Task DynamicDbSetTest()
{
    using var context = new AppDbContext();

    await DumpCountAsync<MEmployee>(context);
    await DumpCountAsync<MOrder>(context);
}
private async Task DumpCountAsync<T>(AppDbContext context) where T : class
{
    var count = await context.Set<T>().CountAsync();
    Console.WriteLine($"{typeof(T).Name}: {count}");
}