Dapper.FastCrud 覚書

はじめに

Dapperは.NETで広く使われているEntityFrameworkと比べて軽量かつSQLベースで柔軟なデータベース操作が可能なマイクロO/Rマッパーです。
DapperはEntityFrameworkに比べて速度とシンプルさがメリットですが、シンプルさがそのままデメリットになり、データベースや他コードの変更に弱く、エンティティ間のリレーションシップも自動マッピングされません。また基本的にデータベース操作は生のSQLを必要があり、Linqをはじめとするデータストリーム機能の利点を生かし切れていません。

このようなデメリットを補強してクエリ操作やマッピング操作を簡略化し、Linqの利点も生かしてより「変更に強いコード」でデータベース操作ができることを目指したのがDapper.FastCrudです。

環境整備

本記事の動作環境

• Windows10 Pro
• Visual Studio 2022
• プロジェクトのターゲットフレームワークは.NET6.0
• SQLServerLocalDB（Visual Studio 2022 に付属）

本記事の例でデータベースはSQLServerLocalDBを使用していますが、メジャーなデータベースであればどれでも対応していると思われます。

必要なパッケージの導入

DapperおよびDapper.FastCrudをNuGetで導入します。
また、Dapper.FastCrudの利用はモデルクラスの作成がほぼ必須となります。DBから自動的にモデルクラスを作成してくれるDapper.FastCrud.ModelGeneratorもNuGetから導入しておきましょう。

また、.NET 6.0 環境ではSQL接続のためのライブラリが標準で入っていません。Microsoft.Data.SqlClientパッケージもNuGetから導入する必要があります。

データベース、テーブルの準備

LocalDBにConsole1AppDBを作成し、以下のような従業員を表現するテーブルを作成します。

EmployeeとSalary, EmployeeとDepartmentテーブルは外部キー制約でつながっています。

Dapper.FastCrud.ModelGenerator

Dapper.FastCrudはエンティティをマッピングするための専用のクラスが必要になります。Dapper.FastCrud.ModelGeneratorは既存のデータベースからエンティティクラスを生成するt4テンプレートを提供します。

Dapper.FastCrud.ModelGeneratorをインストールするとプロジェクトのDataフォルダ下に2つの.ttファイルが生成されているように見えます。

実はこのModelsフォルダや中のファイルは参照リンクになっており、フォルダや中のファイルの実体は別のところにあります。
まず、Modelsフォルダを作成し、続いて同フォルダ内にModelGeneratorConfig.ttファイルを作成します。

作成した空のttファイルに、SampleModelGeneratorConfig.ttファイルの内容を丸ごとコピーします。

保存するとT4テンプレートを実行するかどうかのダイアログが出ますが、現時点ではまだ実行しません。

次に、プロジェクトルートにapp.configというXMLファイルを作成します。これはModelGeneratorがデータベースのConnectionStringを取得するのに必要なファイルです。

（記載例）

app.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="DefaultConnection"
         connectionString="Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=ConsoleApp1DB;Integrated Security=True;"
         providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

現代(.NET 6.0)での設定ファイルはappsettings.jsonなどのjsonファイルになっていますが、.NET Core以前の設定ファイルはXMLファイルでした。ModelGeneratorはその時代の名残でXMLファイルからConnectionString取得しか対応していません。今後jsonファイルからでも取得できるようになるように修正されてほしいところです。

XMLファイルが用意できたら、ModelGeneratorConfig.ttファイルを右クリックし、「カスタムツールの実行」を選択してテンプレートを適用します。

データベースからテーブルやカラムの情報を読み込み、ModelGeneratorConfig.csファイルが生成されます。この中にエンティティクラスが生成されています。

デフォルトの設定では、エンティティクラスの名前は[テーブル名]Entity、カラム名はDB側がスネークケースで命名されている場合はパスカルケースに変換されています。

以上で準備は完了です。

Macで動作させる場合

Visual Studio for Mac ではt4テンプレートをIDEで実行することができません。
代わりにコマンドラインツールなどを使用することで実行できるかもしれません。
(動作未確認)

.NET CoreでのT4の利用と、実行時テキスト生成の挙動を追ってみる

基本操作

Dapper.FactCrudを使いたい全てのクラスファイルに、usingを書いておきます。DapperはDbConnectionを拡張メソッドで機能拡張しています。
また、ModelGeneratorで生成したクラスファイルの名前空間もusingに書いておきます。デフォルトの設定であれば、Dapper.FastCrud.Modelsがエンティティクラスの名前空間になります。

using Dapper;
using Dapper.FastCrud;
using Dapper.FastCrud.Models;

また、program.csの冒頭にDapperで使用するデータベースエンジンの種類を定義しておきます。

program.cs

OrmConfiguration.DefaultDialect = SqlDialect.MsSql;  // SQLServerの場合

基本的な構文は以下のような構成になります。

using Dapper;
using Dapper.FastCrud;
using Dapper.FastCrud.Models;
using Microsoft.Data.SqlClient;

using (var conn = new SqlConnection(CONNECTION_STRING))
{
  // ここにDBに対する操作をするコードを記載する
}

usingディレクティブ内のconnectionのOpenはDapperが自動的に行ってくれます。基本的にはconn.Open()は記載しなくても大丈夫です。

Insert操作(C)

以下、using ( var conn = new SqlConnection(CONNECTION_STRING))内に記載することを前提とします。

    EmployeeEntity entity = new EmployeeEntity()
    {
        FirstName = "タケシ",
        LastName = "加賀",
        SalaryId = 1,
        DepartmentId = 1
    };
    conn.Insert(entity);

挿入したいEntityインスタンスを新しく作成し、Connection.Insertメソッドで挿入できます。
とても簡単ですね！
正常にInsertできなかった場合は例外が発生します。

残念ながら、List等に入った複数のエンティティをまとめてInsertするBulkInsertのような機能はDapper.FastCrudには実装されていません。

Select系(R)

主キーで検索を行う場合

    EmployeeEntity? entity = conn.Get(new EmployeeEntity() { Id = 4 });

Getメソッドが使えます。Getメソッドの引数に主キー値を設定したエンティティを入れると主キーに対応するデータが返ってきます。存在しない場合はnullが返ります。
複合主キーにも対応しています。
また、戻り値の型はジェネリクスで推論されるので以下のような書き方でもOKです。

    var entity = conn.Get(new EmployeeEntity() { Id = 4 });

全件取得

    IEnumerable<EmployeeEntity> entities = conn.Find<EmployeeEntity>();

Findメソッドを引数無しで実行すると全件取得できます。
Findメソッドに取得したいエンティティクラスのジェネリクスが必要です。

主キー以外の条件でSelectを行う場合

主キー以外の条件を使ってSelectを行う場合、Findメソッドの第1引数にコールバック関数を実装する必要があります。
コールバック関数の作り方が若干複雑ですが、細かく分解してみていきましょう。

    // 検索に使うパラメータオブジェクト
    var parameters = new { SearchFirstName = "タケシ" };  // (1)
    // 検索処理
    IEnumerable<EmployeeEntity> entities = conn.Find<EmployeeEntity>( statement =>  // (2)
        statement
        .Where($"{nameof(EmployeeEntity.FirstName):C} = {nameof(parameters.SearchFirstName):P}") 
 // (3)
        .WithParameters(parameters)
    );

(1) 検索条件をプロパティにセットしたクラスオブジェクトと用意します。
プロパティ名がはっきりしていればよいので、無名クラスで問題ありません。

(2) Findメソッドの第1引数に引数1つのコールバック関数を記載します。ラムダ式で記載するのが手っ取り早いです。
仮引数名をstatementとしているのは公式Wikiの説明に合わせています。長いのでstでもsでも構わないでしょう。

(3) statementオブジェクトのWhereメソッドでWHERE句に相当する条件を指定します。Whereメソッドの引数は必ずFormatableString出なければいけません。すなわち$""または$@""でクォートされた文字列である必要があります。通常のstringではコンパイルエラーになります。

Dapper.FastCurdではフォーマット文字列を独自に拡張してSQLのカラム名やパラメータ名にフォーマット文字列を置換する機能が備わっています。
例のコードの{nameof(EmployeeEntity.FirstName):C}は、EmployeeEntityのFirstNameプロパティの名称をnameof関数で文字列として取得し、そのフォーマットを:Cで指定します。:Cはカラム名を意味します。
同様に、{nameof(parameters.SearchFirstName):P}はparameters.SearchFirstNameのプロパティ名をnameof関数で文字列として取得し、そのフォーマットを:Pで指定しています。:Pは PreparedStatement における変数名を表します。
:Cも:Pも冒頭で指定したOrmConfiguration.DefaultDialectのデータベースの種類によって変化します。SQLServerなら@SearchFirstNameになり、SQLiteであれば$SearchFirstNameにフォーマットされます。カラム名もテーブル定義での名称first_nameに変換されます。データベースエンジンが変わってもコードを変更しなくてもよい記載方法となっています。
最初のうちは:Cと:Pだけ覚えておけば特に問題ないでしょう。

なお、この構文はカスタム文字列としてフォーマットしているだけなので、フォーマット後の文字列を直接記載にすることも可能です。

        .Where($"first_name = @SearchFirstName")

長ったらしいnameof関数等を書く必要はなくなりますが、カラム名の名称変更、パラメータ名の変更などのリファクタリングが入った時、使用するデータベースエンジンが変わった時に大変なことになります。極力公式で紹介されているnameofでの書き方を取り入れたほうが良いでしょう。

複数のWHERE条件

WHEREの条件を複数書く場合は、通常のSQLと同様にANDやORを使ってWhereメソッド内のフォーマット文字列に1文で記載します。

    // 検索に使うパラメータオブジェクト
    var parameters = new { SearchFirstName = "タケシ", SearchLastName = "剛田" };
    // WHEREに複数条件を設定したSELECT処理
    IEnumerable<EmployeeEntity> entities = conn.Find<EmployeeEntity>( statement => 
        statement
        .Where($@"{nameof(EmployeeEntity.FirstName):C} = {nameof(parameters.SearchFirstName):P}
                AND {nameof(EmployeeEntity.LastName):C} = {nameof(parameters.SearchLastName):P}")
        .WithParameters(parameters)
    );

フォーマット文字列部分が長くなるので改行をしています。その際は$@""でフォーマット文字列を作成するのが良いでしょう。そのまま改行ができます。

Join操作

関連するテーブルをJOINする場合、statementオブジェクトのIncludeメソッドを使います。

    // 関連テーブルをJOINしたSELECT処理
    IEnumerable<EmployeeEntity> entities = conn.Find<EmployeeEntity>( statement => 
        statement
        .Include<SalaryEntity>()
        .Include<DepartmentEntity>()
    );

今回のテーブル構成の様に、適切に外部キー制約を設定しておけば、ジェネリクスつきのIncludeメソッドを実行するだけでJOINしたいエンティティがEmployeeEntity.SararyプロパティやEmployeeEntity.Departmentプロパティにセットされます。外部キーがユニークであれば単一のクラスが、そうでなければIEnumerable<T>でラッピングされれたプロパティがModelGeneratorで自動生成されます。

INNER JOIN

IncludeメソッドでJOINする際のデフォルトはLEFT OUTER JOINです。INNER JOINしたい場合はIncludeメソッドのコールバック関数で明示的にINNER JOINであることを指定する必要があります。

    // 関連テーブルをINNER JOINしたSELECT処理
    IEnumerable<EmployeeEntity> entities = conn.Find<EmployeeEntity>( statement => 
        statement
        .Include<SalaryEntity>( join => join.InnerJoin())
        .Include<DepartmentEntity>( join => join.InnerJoin())
    );

外部キー制約に依らない条件で行うJOIN

外部キー制約以外の条件で結合を行いたい場合、Includeのコールバック関数にON句に相当する条件を書く必要があります。

    IEnumerable<EmployeeEntity> entities = conn.Find<EmployeeEntity>( statement => 
        statement
        .WithAlias("emp")  // (1)
        .Include<SalaryEntity>( join => join
            .InnerJoin()
            .WithAlias("sal")  // (2)
            .On($@"{nameof(SalaryEntity.Id):of sal} = {nameof(EmployeeEntity.SalaryId):of emp} 
                AND {nameof(SalaryEntity.Amount):of sal} >= {nameof(parameters.MinSalaryAmount):P}")  // (3)
        )
        .WithParameters(parameters)
    );

※この例の条件は本来Whereで行うべきですが、Onの説明のためあえてOnの中に記載しています。

(1) (2) OnメソッドやWhereメソッドで複数のテーブルに対する条件指定を行う場合、必ずテーブル名のエイリアスをWithAliasメソッドで設定する必要があります。

(3) テーブル名のエイリアスを指定した場合、フォーマット識別子でエイリアスを指定できます。この文でいうと:of salや:of empが該当します。こうすることでFastCrudのフォーマッタが適切にエイリアス名を付与してくれます。

また、Onメソッドで結合条件を記載する際は、外部キー制約を自動的に解決してくれません。外部キー制約の結合条件もしっかりOnメソッドの中に含める必要があります。

Update操作(U)

単一のエンティティの場合、Updateメソッドに値を更新したあとのエンティティを引数に入れて実行するだけでOKです。

    var entity = conn.Get(new SalaryEntity() { Id = 1 });
    if (entity == null) throw new Exception();      // Getメソッドは結果がNullになるかもしれないのでNullチェックする
    entity.Amount = 1000000;
    conn.Update(entity);

複数のレコードを一括でUpdateする場合は、Select時のようにコールバック関数に条件を設定と、特定のカラムだけを更新対象にするマッピングオブジェクトの準備が必要になります。

    var param = new { rankMin = 20 };
    var partialUpdateMapping = OrmConfiguration.GetDefaultEntityMapping<SalaryEntity>()
                                               .UpdatePropertiesExcluding(prop => prop.IncludeInUpdates(false),
                                                                          nameof(SalaryEntity.Amount));
    var updateSet = new SalaryEntity() { Amount = 99990 };
    conn.BulkUpdate(updateSet, statement => statement
        .Where($"{nameof(SalaryEntity.Rank):C} >= {nameof(param.rankMin):P}")
        .WithEntityMappingOverride(partialUpdateMapping)
        .WithParameters(param)
    );

コード中のpartialUpdateMappingは、Update対象になるエンティティクラスのプロパティのうち更新の対象にするプロパティ名を設定するオブジェクトです。
このオブジェクトを作ってstatementちゅのWithEntityMappingOverrideにセットしないとupdateSetで値を入れなかったプロパティに対応するカラムの値まで更新されてしまいます（intなら初期値の0、varcharならNull等に更新されてしまう）

正直、このpartialUpdateMappingを作るのが面倒な上に注意事項も多いので、BlukUpdateはDapper.FastCrudで行わずバニラなDapperのExecuteを使った方がよっぽど早いし安全だと思います。
FastCrudっぽくSQL文を組み立てたい場合は、後述するSql.Formatを使ってクエリを組み立てると良いでしょう。

Delete操作(D)

単一データの削除はSelect操作と同じ感覚で実行できます。

    // 主キーを使って単一のレコードを削除する
    bool result = conn.Delete(new SalaryEntity() { Id = 4 });

全件削除や、条件を指定する削除はBulkDeleteメソッドを使ってSelect操作と同じように処理できます。

    // 全件削除する
    int affectedRecordCount = conn.BulkDelete<EmployeeEntity>();

    // 検索に使うパラメータオブジェクト
    var parameters = new { SearchFirstName = "タケシ" };
    // 削除処理
    int affectedRecordCount = conn.BulkDelete<EmployeeEntity>(statement =>
        statement
        .Where($"{nameof(EmployeeEntity.FirstName):C} = {nameof(parameters.SearchFirstName):P}")
        .WithParameters(parameters)
    );

ConnectionとTransaction

ここではDapper.FastCrudにおけるトランザクションの扱い方について説明します。

AttachTransaction

公式Wikiではトランザクションに関する説明は以下の1文しかありません。

AttachToTransaction
Attaches the current command to an existing transaction.

サンプルコードもない...

AttachTransactionの使い方は以下の様に、各CRUDメソッドのコールバック関数にAttachTransactionメソッドをチェインすることで実現できます。引数にTransactionオブジェクトを指定します。

using (var conn = new SqlConnection(CONNECTION_STRING))
{
    conn.Open();    // トランザクションを利用するためには先にConnectionをOpenしておかなければならない
    var entity = conn.Get(new SalaryEntity() { Id = 1 });
    if (entity == null) throw new Exception();      // Getメソッドは結果がNullになるかもしれないのでNullチェックする
    entity.Amount = 1000000;
    // トランザクションの開始
    var tran = conn.BeginTransaction();
    // トランザクションを利用してUpdateを行う
    conn.Update(entity, statement =>
        statement.AttachToTransaction(tran));
    // トランザクションのコミット
    tran.Commit();
}

注意すべき点は、BeginTransactionする前にconnectionをOpenしておかなければいけない点です。
FastCrudはconnectionを明示的にOpenしなくても、各種CRUDメソッド内でOpenしているかどうかをチェックして、既にOpenしていればそのままConnectionを使用し、まだOpenしていなければOpenしてから処理を実行し、その場でCloseしているようです。なのでトランザクションを開始する前にOpenしておかないとトランザクションが適用されず例外が出てしまいます。

TransactionScope

.NETではTransactionScopeクラスを使うことでブロック内のデータベース接続に対し暗黙的にトランザクションを有効にすることができます。
ADO.NETやEntityFrameworkでも利用できる方法です。

using (var tran = new TransactionScope())
using (var conn = new SqlConnection(CONNECTION_STRING))
{
    // ここにデータベースに対する操作を記載する
    // トランザクションのコミット
    tran.Complete();
}

Dapper.FastCrudでもTransactionScopeは有効です。

using (var tran = new TransactionScope())
using (var conn = new SqlConnection(CONNECTION_STRING))
{
    var entity = conn.Get(new SalaryEntity() { Id = 1 });
    if (entity == null) throw new Exception();      // Getメソッドは結果がNullになるかもしれないのでNullチェックする
    entity.Amount = 1000000;
    conn.Update(entity);
    // トランザクションのコミット
    tran.Complete();
}

TransactionScopeオブジェクトがブロック内のConnectionに対し暗黙的にトランザクション適用してくれます。
tran.Completeメソッドを実行すればトランザクションがコミットされ、何もしないか明示的にtran.Disposeメソッドを実行すればロールバックされます。

全てのデータベース操作に対してAttachTransactionを行うのはとても面倒なので、TransactionScopeを使った方法の方が格段に管理が楽になります。

発展的な便利機能

そのほか、知っておくと便利で有利な機能の紹介です。

OrderBy、Take, Skip

OrderByは評価した値で並べ替えを行います。降順で並べ替えをしたい場合はOrderByDescendingを代わりに使います。
SkipとTakeはそれぞれ指定値分のレコード数を結果セットから除去、抽出することができます。
主にページネーションを行う場合や、当該条件のレコードが1件だけほしいという場面で活用できます。

var page = 1;
var pageSize = 10;
var result = conn.Find<SalaryEntity>()
    .OrderBy(e => e.Amount)
    //.OrderByDescending(e => e.Amount)     // 降順でソートしたい場合
    .Skip(page * pageSize)
    .Take(pageSize);

When

第1引数の真偽値により、条件を分岐させることができます。
下記の例ではソートするカラムを文字列で与え、切り替えています。
第1引数の真偽値がfalseだった場合、第3引数のラムダ式でfalseだった場合の条件記述もできます。

string sortColumn = "Salary";
var result = conn.Find<EmployeeEntity>(s => s
    .Include<SalaryEntity>( join => join.WithAlias("sal"))
    .When(sortColumn == "LastName", then => then.OrderBy($"{nameof(EmployeeEntity.LastName):C}"))
    .When(sortColumn == "FirstName", then => then.OrderBy($"{nameof(EmployeeEntity.FirstName):C}"))
    .When(sortColumn == "Salary", then => then.OrderBy($"{nameof(EmployeeEntity.Salary.Amount):of sal}"))
);

Format機能だけを使う

今迄紹介したWhereメソッドやWhenメソッドでは、検索ページによくある複合検索条件が非常に作りづらいです。
このような場合、検索条件ごとに別々にFormattableStringオブジェクトを作成してあとからWhereメソッドで再フォーマットする手法が使えます。

    var param = new { FirstName = "タケシ", LastName = "剛田" };
    // それそれの条件のWhere句を作成する
    FormattableString whereClauseFirstName;
    if (string.IsNullOrEmpty(param.FirstName))
        whereClauseFirstName = $@" 1 = 1 ";
    else
        whereClauseFirstName = $@"{nameof(EmployeeEntity.FirstName):C} = {nameof(param.FirstName):P}";
    FormattableString whereClauseLastName;
    if (string.IsNullOrEmpty(param.LastName))
        whereClauseLastName = $@" 1 = 1 ";
    else
        whereClauseLastName = $@"{nameof(EmployeeEntity.LastName):C} = {nameof(param.LastName):P}";

    var result = conn.Find<EmployeeEntity>(s => s
        .Where($@"{whereClauseFirstName} AND {whereClauseLastName}")
        .WithParameters(param));

条件指定しない句を$@" 1 = 1 "にするのはとても気持ち悪いかもしれませんが、最後にFindする際に不要な条件を除去する良い仕組みが思い浮かびませんでした。オプティマイザからは無視されるはずなのでパフォーマンスに影響はないと考えますがキレイではないですね。

もっと良い方法が見つかれば更新したいと思います。