🗄️
【C#】Izayoi.Data ライブラリー (ORM)

2024/08/28に公開
 はじめにこの記事は データベースの O/R マッパー (ORM) である Izayoi.Data.DbDataMapper と、そのライブラリー群について、使い方を記載したものです。

 対象読者リレーショナルデータベースを扱うITエンジニア

 対象言語C#

 対応データベースIzayoi.Data ライブラリーは以下のリレーショナルデータベースに対応しています。


RDB
パッケージ名


MySQL
MySqlConnector

PostgreSQL
Npgsql

SQL Server
Microsoft.Data.Sqlclient

SQLite
Microsoft.Data.Sqlite

これ以外にも DbCommand, DbDataReader クラスを継承しているパッケージであれば、おおよそ動作すると思います。

 Izayoi.Data パッケージ

パッケージ名
NuGet
GitHub


Izayoi.Data.DbCommandAdapter
Izayoi.Data.DbCommandAdapter
Izayoi.Data

Izayoi.Data.DbDataMapper
Izayoi.Data.DbDataMapper
Izayoi.Data

Izayoi.Data.Query
Izayoi.Data.Query
Izayoi.Data

Izayoi.Data.Repository
Izayoi.Data.Repository
Izayoi.Data


 全体像Izayoi.Data ライブラリーの全体像は以下の図のようになっています。
ADO.NET の DbConnection から DbCommand を通してクエリーを実行し、

結果を読み取るという一覧の流れに組み込まれるような形になっています。
実際の動作も矢印が示す通りで、経路を見て使用する予定のないライブラリーならば、

そのパッケージはインストールしなくてもよいです。

 下準備データベースを準備して、CREATE TABLE します。
SQL Server であれば以下のような感じです。
CREATE TABLE [dbo].[users] (
    [id]         INT           IDENTITY (1, 1) NOT NULL,
    [name]       NVARCHAR (50) NOT NULL,
    [age]        TINYINT       NOT NULL,
    [gender]     TINYINT       NOT NULL,
    [created_at] DATETIME2 (7) NOT NULL,
    [updated_at] DATETIME2 (7) NOT NULL,
    PRIMARY KEY CLUSTERED ([id] ASC)
);
続いて、マッピング クラスが必要ですので、

DBのテーブルに合わせたクラスを作成しておきます。
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

//[Table("users")]
[Table("users", Schema = "dbo")]
public class User
{
    [Key]
    [Column("id")]
    public int Id { get; set; }

    [Column("name")]
    public string Name { get; set; } = string.Empty;

    [Column("age")]
    public byte Age { get; set; }

    [Column("gender")]
    public GenderType Gender { get; set; }

    [Column("created_at")]
    public DateTime CreatedAt { get; set; }

    [Column("updated_at")]
    public DateTime UpdatedAt { get; set; }
}
[Table] 属性にはテーブル名と、あればスキーマ名を書いておきます。

省略するとクラス名がテーブル名になります。
[Column] 属性にはカラム名を書いておきます。

省略するとプロパティ名がカラム名になります。
[Key] 属性をプライマリーキーに付けておきます。

SELECT では使用されませんが、INSERT, UPDATE, DELETE する際に使用されるので忘れずに書いておきます。
マッピングから除外したいプロパティ（テーブルに存在しないもの）があれば [NotMapped] 属性を付与しておきます。

 Izayoi.Data.DbDataMapper１番目に紹介するのは DbDataMapper です。
これは SELECT クエリーで取得できたレコードを、

クラスオブジェクトにデータセットしてくれるものです。
図で示すと以下の赤枠で囲った流れになります。
最も下層に位置しています。
使い方は以下のような感じです
using System.Collections.Generic;
using System.Threading.Tasks;
using Izayoi.Data;
using Microsoft.Data.SqlClient;  // for SQL Server
//using Microsoft.Data.Sqlite;   // for SQLite
//using MySqlConnector;          // for MySQL
//using Npgsql;                  // for PostgreSQL

public class ExampleClass()
{
    private readonly dbConnectionString;

    private readonly DbDataMapper dbDataMapper = new();

    public async Task Method(CancellationToken cancellationToken)
    {
        using SqlConnection dbConnection = new(dbConnectionString);

        dbConnection.Open();

        using SqlCommand dbCommand = dbConnection.CreateCommand();

        dbCommand.CommandText = "SELECT * FROM users";

        using SqlDataReader dbDataReader = await dbCommand.ExecuteReaderAsync(cancellationToken);

        // ここでマッピング
        List<User> users = await dbDataMapper.ReadToObjectsAsync<User>(dbDataReader, cancellationToken);

        dbConnection.Close();
    }
}
micro O/R マッパー (ORM) なので、普通の ADO.NET の使い方の最後にちょっと添える程度ですね。

本当にシンプルです。
既存のプロジェクトにもさっと導入することができると思います。
上記のプログラム例では取得した全レコードを変換していますが、1レコードずつ、または1レコードのみを変換することも可能です。
SQLではなくストアドプロシージャを使用する際には、この機能が活躍しそうです。
あと、このデータマッパーは、シングルトンのようにして１つのインスタンスを使いまわすことが推奨されています。
その理由は、内部にマッピング構造のデータキャッシュを持っており、

2回目以降からはマッピングがより高速になるためです。

 Izayoi.Data.Repository2番目に紹介するのは DbRepository です。

Izayoi.Data ライブラリーの中で最も上層に位置しています。
全てのライブラリーを総合した機能となっており

実際には中層や下層のクラス群を全く意識することなく使用することができます。
ただし、このパッケージ自体は DbRepositoryBase の抽象クラスを提供するのみですので、

基底クラスを継承し、具象クラスをテーブルごとに作るという準備作業があります。
具象クラスの実装の仕方は以下のような形になります。
using Izayoi.Data;
using Izayoi.Data.Query;
using Izayoi.Data.Repository;

public class UserRepository : DbRepositoryBase<User, int>
{
    public UserRepository(IDbDataMapper dbDataMapper, QueryOption queryOption)
        : base(dbDataMapper, queryOption) { }

    public UserRepository(IDbCommandAdapter dbCommandAdapter)
        : base(dbCommandAdapter) { }
}
これだけで、1テーブル（ここでは users テーブル）に関するCRUDがすべて実装されたリポジトリー（データアクセスクラス）が出来上がっています。
気を付けるべきポイントは DbRepositoryBase<TData, TKey> の箇所くらいです。

TKey の型は、プライマリーキー（[Key] 属性のプロパティ）のデータ型です。
また、コンストラクターは生成の仕方が決まっているのであれば

どちらか一方の定義のみでも大丈夫です。
では、実際にこの UserRepository を使ってみましょう。
下準備は以下のようになります。
using System.Collections.Generic;
using System.Threading.Tasks;
using Izayoi.Data;
using Izayoi.Data.Query;
using Microsoft.Data.SqlClient;  // for SQL Server
//using Microsoft.Data.Sqlite;   // for SQLite
//using MySqlConnector;          // for MySQL
//using Npgsql;                  // for PostgreSQL

public class ExampleClass
{
    private readonly string dbConnectionString;

    private readonly DbCommandAdapter dbCommandAdapter;

    private readonly DbDataMapper dbDataMapper;

    private readonly QueryOption queryOption;

    private readonly UserRepository userRepository;

    public ExampleClass()
    {
        queryOption = new QueryOption(RdbKind.SqlServer);

        dbDataMapper = new DbDataMapper();

        dbCommandAdapter = new DbCommandAdapter(dbDataMapper, queryOption);

        userRepository = new UserRepository(dbCommandAdapter);
    }
}
最初の using にて、使用する ADO.NET のネームスペースを記載します。
次にコンストラクターにて、UserRepository を生成するにあたって必要なものを揃えていきます。
必要なのは QueryOption でリレーショナルデータベースの種類 (RdbKind) を合わせるくらいです。
実際の使い方は以下の通りです。
まずは Read (Select) です。
public class ExampleClass
{
    // 省略
    
    public async Task Method1(CancellationToken cancellationToken)
    {
        using SqlConnection dbConnection = new(dbConnectionString);

        dbConnection.Open();

        List<User> users = await userRepository.FetchAllAsync(dbConnection, cancellationToken);

        User? user = await userRepository.FetchAsync(dbConnection, id: 1, cancellationToken);

        dbConnection.Close();
    }
}
SELECT ALL の全件取得と、ID指定の1件取得ができます。
id のデータ型は TKey と同じになります。
続いて Create (Insert), Update, Delete です。
public class ExampleClass
{
    // 省略

    public async Task Method2(CancellationToken cancellationToken)
    {
        using SqlConnection dbConnection = new(dbConnectionString);

        dbConnection.Open();

        var user = new User()
        {
            Id = 0,
            Name = "name1",
            Age = 20,
            Gender = GenderType.Male,
            CreatedAt = DateTime.UtcNow,
            UpdatedAt = DateTime.UtcNow,
        };

        int affectedRowCount;

        affectedRowCount = await userRepository.InsertReturnAsync(dbConnection, user, cancellationToken);

        user.Age = 21;
        user.UpdateAt = DateTime.UtcNow;

        affectedRowCount = await userRepository.UpdateAsync(dbConnection, user, cancellationToken);

        affectedRowCount = await userRepository.DeleteAsync(dbConnection, user, cancellationToken);

        dbConnection.Close();
    }
}
簡単ですね。
この例では、InsertReturnAsync した際、user.Id にDB側で決定された値が格納されます。
続いて、応用に関してです。
パッケージに入っている DbRepositoryBase は単に使い方を示すための基本クラスです。

既定のメソッドに少し手を加えたい場合は override することができます。
それ以外で、メソッド名を変えたいですとか、パラメーターやプロパティを増やしたいですとか、

ログを書き込みたいですとか、他に大幅に手を加えたいのであれば、

元のプログラムを覗いて、それを参考に、独自の DbRepositoryBase を作るのがよいと思います。

 Izayoi.Data.QueryDBマッパーが便利なことはなんとなく掴めたと思いますが、

実際の業務では、単純な CRUD だけではなくて、

何らかの SQL を必要とする場面が出てきます。
そして
DbCommand の CommandText に SQL を書きたくない。
それは多くのプログラマが思っていることです。
生のSQLが最も実行パフォーマンスが高いのは明白なのですが、

クエリー構築は条件分岐がある場合もありますし、

分岐があるとちょっとしたミスも増えますし、

パラメーターの設定とか大変ですし、何より楽したい、

というのがあると思います。
ということで、次に紹介するのが QueryBuilder です。
基本的な使い方は以下のような感じです。
using Izayoi.Data.Query;

public class ExampleClass
{
    public async Task Method()
    {
        var queryOption = new QueryOption();

        var queryBuilder = new QueryBuilder(queryOption);

        var select = new Select()
            .SetFrom("users")
            .AddField("*");

        queryBuilder.Build(select);

        string query = queryBuilder.GetQuery();

        var parameters = queryBuilder.GetParameters();

        // query:
        //   SELECT *
        //   FROM users
        // parameters:
        //   (Empty)

        // 続く
    }
}
あとは、QueryBuilder でビルドしてできたクエリーとパラメーターを

DbCommand に設定することで、簡単にSQLを実行することができます。
using Izayoi.Data.Query;
using Izayoi.Data;
using Microsoft.Data.SqlClient;

public class ExampleClass
{
    public async Task Method()
    {
        // 上の続き

        using SqlConnection dbConnection = new(dbConnectionString);

        using SqlCommand dbCommand = dbConnection.CreateCommand();

        dbCommand.CommandText = query;

        foreach (BindParameter bindParameter in parameters)
        {
            SqlParameter dbParameter = dbCommand.CreateParameter();

            dbParameter.ParameterName = bindParameter.ParameterName;
            dbParameter.DbType = bindParameter.DbType;
            dbParameter.Value = bindParameter.Value;

            dbCommand.Parameters.Add(dbParameter);
        }

        dbConnection.Open();

        using SqlDataReader dbDataReader = await dbCommand.ExecuteReaderAsync(cancellationToken);

        List<User> users = await dbDataMapper.ReadToObjectsAsync<User>(dbDataReader, cancellationToken);

        dbConnection.Close();
    }
}
SELECT を少し複雑にすると以下のような感じです。
using Izayoi.Data.Query;

    {
        var select = new Select()
            .SetFrom("users")
            .AddField("id")
            .AddField("name")
            .AddField("age")
            .AddWhere("id", ">=", 100)
            .AddWhere("age", OpType.IN, new int[] { 20, 30, 40 })
            .AddOrder("age", OType.ASC)
            .AddOrder("id", OType.ASC);

        // query:
        //   SELECT id, name, age
        //   FROM users
        //   WHERE id >= @w_0
        //     AND age IN (@w_1_0, @w_1_1, @w_1_2)
        //   ORDER BY age ASC, id ASC
        // parameters:
        //   [0]:
        //     ParameterName: @w_0
        //     DbType: DbType.Int32
        //     Value: 100
        //   [1]:
        //     ParameterName: @w_1_0
        //     DbType: DbType.Int32
        //     Value: 20
        //   [2]:
        //     ParameterName: @w_1_1
        //     DbType: DbType.Int32
        //     Value: 30
        //   [3]:
        //     ParameterName: @w_1_2
        //     DbType: DbType.Int32
        //     Value: 40
    }
条件に OR を使用する場合は以下のような感じです。
using Izayoi.Data.Query;

    {
        var select = new Select()
            .SetFrom("users")
            .AddField("*")
            .AddWhere('(', "age", "=", 20)
            .AddWhere(CType.OR, "age", "=", 30)
            .AddWhere(CType.OR, "age", "=", 40, ')'); 

        // query:
        //   SELECT *
        //   FROM users
        //   WHERE (age = @w_0
        //       OR age = @w_1
        //       OR age = @w_2)
        // parameters:
        //   [0]:
        //     ParameterName: @w_0
        //     DbType: DbType.Int32
        //     Value: 20
        //   [1]:
        //     ParameterName: @w_1
        //     DbType: DbType.Int32
        //     Value: 30
        //   [2]:
        //     ParameterName: @w_2
        //     DbType: DbType.Int32
        //     Value: 40
    }
テーブル結合や集約を使用する場合は以下のような感じです。
    {
        var select = new Select()
            .SetFrom("posts", "p")
            .AddJoin(JType.LEFT_JOIN, "users", "u", "u.id = p.user_id")
            .AddField("p.user_id")
            .AddField("u.name", "user_name")
            .AddField("COUNT(p.comment)", "post_count")
            .AddGroup("p.user_id")
            .AddGroup("user_name");

        // query:
        //   SELECT p.user_id, u.name AS user_name, COUNT(p.comment) AS post_count
        //   FROM posts AS p
        //   LEFT JOIN users AS u ON (u.id = p.user_id)
        //   GROUP BY p.user_id, user_name
    }
この場合は結果格納用のマップクラスを作成してあげる必要があります。
public class PostCount
{
    [Column("user_id")]
    public int UserId { get; set; }

    [Column("user_name")]
    public string UserName { get; set; } = string.Empty;

    [Column("post_count")]
    public int Count { get; set; }
}
多くのパターンに対応できそうですね。
それでも複雑すぎるSQLはビルダーでは対応できないと思いますので、

その際は生のSQLを書きましょう。

 Izayoi.Data.DbCommandAdapterDbDataMapper と QueryBuilder は便利ですけれど

DbCommand に毎回クエリーやパラメーターを設定するのは大変です。
そこで DbCommand 周りをサポートするのが DbCommandAdapter です。
DbCommandAdapter のメソッドは、

CRUDの処理と結果の返却を簡単に実行できるようになっています。
使い方を書くと記事がだいぶ長くなってしまいますので、

詳細は公式Wikiを参考にしてください。

 おわりに以上が、Izaoi.Data ライブラリーの簡単な説明となります。

最後までお読みいただきありがとうございました。
RDB	パッケージ名
MySQL	MySqlConnector
PostgreSQL	Npgsql
SQL Server	Microsoft.Data.Sqlclient
SQLite	Microsoft.Data.Sqlite
パッケージ名	NuGet	GitHub
Izayoi.Data.DbCommandAdapter	Izayoi.Data.DbCommandAdapter	Izayoi.Data
Izayoi.Data.DbDataMapper	Izayoi.Data.DbDataMapper	Izayoi.Data
Izayoi.Data.Query	Izayoi.Data.Query	Izayoi.Data
Izayoi.Data.Repository	Izayoi.Data.Repository	Izayoi.Data
はじめに

対象読者

対象言語

対応データベース

Izayoi.Data パッケージ

全体像

下準備

Izayoi.Data.DbDataMapper

Izayoi.Data.Repository

Izayoi.Data.Query

Izayoi.Data.DbCommandAdapter

おわりに

Discussion
