C#でDapperで動的Where句をSQL Builderを使わずに実現したい

# 1. はじめに
* Dapperを使用してDBのデータを取得したい
* 動的Where句をSQL Builderでロジックに記述せずにSQLだけで実現したい
* 動的Where句で、かつIN句の場合に発生した問題(口述）をなんとか回避したい
 
# 2. 開発環境
* C#
* .NET 6
* Visual Studio 2022
* SQLite
* DB Browser for SQLite
* Dapper (NuGet)
* System.Data.SQLite.Core (NuGet)
* ToString.Fody (Nuget)

# 3. 開発環境の準備
## 3.1. Dapperのインストール
* NuGetより```Dapper```をインストールする
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2852081/e7067879-d8d2-8ffd-9e11-06cd6284a847.png)

## 3.2. System.Data.SQLite.Coreのインストール
* NuGetより```System.Data.SQLite.Core```をインストールする
* DBは今回SQLiteを使用するため準備する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2852081/83b699ee-a528-d112-cd0d-8e5166065c7d.png)

## 3.3. ToString.Fodyのインストール
* NuGetより```System.Data.SQLite.Core```をインストールする
* DBの検索結果を簡単に確認するために準備する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2852081/39fbf129-e6d2-3a53-f21b-c67e154cd2bf.png)

## 3.3. DB Browser for SQLiteのインストール
* 下記サイトよりインストーラーをダウンロードして、インストールする
* SQLiteのDB参照のために準備する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2852081/2fe4f8ee-34ae-623d-04d0-dfaebb4ec700.png)

https://sqlitebrowser.org/dl/

# 4. テストDBの作成
## 4.1. データベースの作成
* DB Browser for SQLiteを起動して、新しいデータベース作成をクリックする
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2852081/b05c094b-109e-c7ae-61cb-ca2854006a53.png)

* 任意のフォルダ、ファイル名で保存する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2852081/23026fc2-5213-61b1-2fa5-ad282d6e3e3f.png)

## 4.2. テーブルの作成
* SQLの実行タブよりCREATE文を入力して、実行ボタンをクリックする
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2852081/c57981eb-acaf-8568-9bde-b7c278fbf371.png)

```SQL:
CREATE TABLE "Person" (
	"Id"	varchar(8) NOT NULL,
	"Name"	varchar(40) NOT NULL,
	"Age"	INTEGER,
	PRIMARY KEY("Id")
)
```

## 4.4. テストデータの投入
* データ閲覧タブより新規レコード挿入ボタンをクリックする
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2852081/07fb09fe-efa9-2836-6786-ea33d0150cda.png)

* カラムに値を入力後に、変更を書き込みするボタンをクリックしてコミットする
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2852081/0ef6b893-724a-c8a6-e1d7-e16c0676e5db.png)


# 5. サンプルコード（固定SQL）
## 5.1. DTOクラス
* ```[ToString]```属性を追加することでプロパティを含めて出力することができる
```CSharp:PersonDto.cs
[ToString]
public class PersonDto
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public int Age { get; set; }
}
```

## 5.2. DBアクセスクラス（抜粋）
* 通常の値のDynamic Object以外にIN句はList型を渡すと勝手に展開してくれるので便利だ
```CSharp:
using Dapper;
using System.Data.SQLite;
using System.Diagnostics;

// プロジェクト直下の同クラスと同じ階層にDBを格納
string ConnectionString = @"Data Source=../../../test.db;";

// SQLiteDB接続
using (var connection = new SQLiteConnection(ConnectionString))
{
    // DBオープン
    connection.Open();

    // トランザクションを開始（今回は未使用）
    using (var tarn = connection.BeginTransaction())
    {
        // SQL文
        string sql = "SELECT * FROM Person WHERE Id IN @Id";
        
        // Dynamic Object作成（IN句の場合）
        var condtion = new
        {
            Id = new List<int>() { 1, 3 }
        };

        // Dapperで検索
        var result = connection.Query<PersonDto>(sql, condtion);

        // 検索結果を確認
        foreach (var person in result)
        {
            Debug.Print(person.ToString());
        }
    }

    // DBクローズ
    connection.Close();
}
```

## 5.3. 動作確認
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2852081/509d76a6-eef8-dce6-f621-f421175f6e39.png)

# 6. サンプルコード（動的Where句）
## 6.1. DBアクセスクラス（抜粋）
```CSharp:
using Dapper;
using System.Data.SQLite;
using System.Diagnostics;

// プロジェクト直下の同クラスと同じ階層にDBを格納
string ConnectionString = @"Data Source=../../../test.db;";

// SQLiteDB接続
using (var connection = new SQLiteConnection(ConnectionString))
{
    // DBオープン
    connection.Open();

    // トランザクションを開始（今回は未使用）
    using (var tarn = connection.BeginTransaction())
    {
        // SQL文
        string sql = @"SELECT * FROM Person WHERE (@Id IS NULL OR @Id = '' OR Id = @Id)";
        
        // Dynamic Object作成
        var condtion = new
        {
            Id = ""
        };

        // Dapperで検索
        var result = connection.Query<PersonDto>(sql, condtion);

        // 検索結果を確認
        foreach (var person in result)
        {
            Debug.Print(person.ToString());
        }
    }

    // DBクローズ
    connection.Close();
}
```

## 6.2. 動作確認
* Dynamic Objectの変数がNULLまたは空白の場合、Where条件対象から外すことができる
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2852081/fb8857c1-c805-4de8-a600-070526efdcc2.png)

# 7. サンプルコード（動的Where句かつIN句）
## 7.1. 問題点
* 上記と同様な考え方でコードを記述するとエラーが発生する
* 実行後のSQLをよく見るとIN句のDynamic Objectの変数が展開されて、@Id1,@Id2...になって元の@Idがなくなっている
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2852081/ef1b67de-dd0a-90b5-28ef-591d15c38a3e.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2852081/a1aadeb4-bb1c-0021-c8fc-882e7e483738.png)

## 7.2. 今回対応した方法
* 動的Where句のIN句の条件対象を判定するDynamic Objectの変数を追加する
* SQLで追加した変数で条件を判定するように変更する

```CSharp:
// SQL文
string sql = @"SELECT * FROM Person WHERE (@Id_IsWhere IS NULL OR @Id_IsWhere = '' OR Id IN @Id)";

// Dynamic Object作成
    var condtion = new
    {
        // IN句がある場合
        Id = new List<int>() { 1, 3 },
        Id_IsWhere = "Add"
        // IN句がない場合
        // Id = null
     };

     // Dapperで検索
     var result = connection.Query<PersonDto>(sql, condtion);
```

# 8. 参考文献

https://www.web-dev-qa-db-ja.com/ja/c%23/%E3%83%A2%E3%83%87%E3%83%AB%E3%82%92%E6%B8%A1%E3%81%99%E3%81%A8%E3%81%8D%E3%81%ABdapper%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%A6%E5%8B%95%E7%9A%84%E3%81%AAwhere%E5%8F%A5%E3%82%92%E4%BD%9C%E6%88%90%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95/828031291/

https://qiita.com/masakura/items/3409a766e46580a5ad99