NPocoの紹介 -機能編- クエリ自動生成

# NPocoの紹介 -機能編- クエリ自動生成
最初の記事は[こちら](https://qiita.com/epsilon_87_myo/items/1c0f75064b9b6bcc9e1e)

今回はクエリ自動生成によるデータ操作をします。

データベースは`PostgreSQL`を使用しました。

## テーブルの用意

今回は以下のような単純なテーブルを用意しました。

### emp

|列名        | 型          | 主キー|
|------------|------------|-------|
|emp_id      |varchar(5)  | 1     |
|first_name  |varchar(10) |    |
|family_name |varchar(10) |    |
|dept_code   |varchar(5)  |    |
|created_at  |timestamp   |    |
|updated_at  |timestamp   |    |

## マッピング用のクラスを作成
属性を用いて上記の `emp` テーブルに対応するクラスを作成します。

[Fluent Mappings](https://github.com/schotime/NPoco/wiki/Fluent-mappings-including-conventional)による定義も可能なようです

```cs:emp.cs
[TableName("emp")]
[PrimaryKey("emp_id", AutoIncrement = false)]
public class Emp
{
    [Column("emp_id")]
    public string EmpId { get; set; }

    [Column("first_name")]
    public string FirstName { get; set; }

    [Column("family_name")]
    public string FamilyName { get; set; }

    [Column("dept_code")]
    public string DeptCode { get; set; }

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

    [Column("updated_at")]
    public DateTime? UpdatedAt { get; set; }

    public override string ToString()
    {
        return $"[EmpId={EmpId}, FirstName={FirstName}, FamilyName={FamilyName}, DeptCode={DeptCode}, CreatedAt={CreatedAt}, UpdatedAt={UpdatedAt}]";
    }
}
```

### TableName 属性
テーブル名に対応する属性です。

### PrimaryKey 属性
主キーを表す属性です。少し複雑なので解説します。ソースは[こちら](https://github.com/schotime/NPoco/blob/d707af2bf59f5c70c1d147bc6974d3cdd52bb05b/src/NPoco/PrimaryKeyAttribute.cs)

`NPoco`は複合主キーに対応しており、複合主キーの場合は カンマ区切り文字列 または 文字列の配列を渡します。

```cs
//文字列で指定
[PrimaryKey("col1,col2")]

//配列で指定
[PrimaryKey(new string[] { "col1", "col2" })]
```

またデフォルトでは`AutoIncrement` プロパティが`true` となっているため、主キーの値が自動生成ではない場合はこのプロパティを`false` にしておく必要があります。

シーケンスによる自動採番にも対応しており `SequenceName` にシーケンス名を渡すことにより、`Oracle` などのシーケンスによる自動採番にも対応しています。
(`PostgreSQL` の場合は シーケンスによる自動採番はデフォルトでは対応していないので自分で`PostgreSQLDatabaseType` を継承した新たなものを用意する必要があります。
`SQL Server` でシーケンスを使う場合は`UseOutputClause` を`true` にする必要があります。)



### Column 属性
列名に対応する属性です。

### その他の属性
最低限上記の属性で足りますが、それ以外の属性については[こちら](https://github.com/schotime/NPoco/wiki/Mapping)を参照してください。


## Insertメソッド

```cs
Emp insEmp = new Emp
{
    EmpId = "E0001",
    FirstName = "太郎",
    FamilyName = "Nぽこ",
    DeptCode = "001",
    CreatedAt = DateTime.Now,
    UpdatedAt = DateTime.Now,
};
database.Insert(insEmp);
```

以下のクエリが実行されます。

```
INSERT INTO "emp" ("emp_id","first_name","family_name","dept_code","created_at","updated_at") VALUES (@p0,@p1,@p2,@p3,@p4,@p5)
         -> @p0 [String] = "E0001"
         -> @p1 [String] = "太郎"
         -> @p2 [String] = "Nぽこ"
         -> @p3 [String] = "001"
         -> @p4 [DateTime] = "2020/06/16 22:25:06"
         -> @p5 [DateTime] = "2020/06/16 22:25:06"
```

## Updateメソッド

### 列指定なしの場合

```cs
//主キー指定で既存データの取得
Emp updEmp = database.SingleOrDefaultById<Emp>("E0001");
updEmp.FirstName = "次郎";
updEmp.UpdatedAt = DateTime.Now;
database.Update(updEmp);
```

以下のクエリが実行されます。

```
UPDATE "emp" SET "first_name" = @p0, "family_name" = @p1, "dept_code" = @p2, "created_at" = @p3, "updated_at" = @p4 WHERE "emp_id" = @p5
         -> @p0 [String] = "次郎"
         -> @p1 [String] = "Nぽこ"
         -> @p2 [String] = "001"
         -> @p3 [DateTime] = "2020/06/16 22:25:06"
         -> @p4 [DateTime] = "2020/06/16 22:29:13"
         -> @p5 [String] = "E0001"
```

2項目しか変更していないにも関わらず、`set` では全ての列を更新していることが確認できます。

Update時に更新対象の列を限定したいときは次に説明する更新対象列指定のUpdateメソッドを使います。
また`snapshot` の機能を使うことで、差分更新を自動ですることができます。

### 列指定ありの場合

第2引数に更新対象列の一覧を渡すことで、列を限定することができます。

```cs
Emp updEmp = database.SingleOrDefaultById<Emp>("E0001");
//スナップショットの取得
var snapshot = database.StartSnapshot(updEmp);

updEmp.FirstName = "次郎";
updEmp.UpdatedAt = DateTime.Now;

//更新対象列を指定したアップデート
//UpdatedColumnsメソッドで変更のある列の一覧を取得できる。
database.Update(updEmp, snapshot.UpdatedColumns());
```

以下のクエリが実行されます。

```
UPDATE "emp" SET "first_name" = @p0, "updated_at" = @p1 WHERE "emp_id" = @p2
         -> @p0 [String] = "次郎"
         -> @p1 [DateTime] = "2020/06/16 22:35:16"
         -> @p2 [String] = "E0001"
```

変更対象の2列のみが`set` で指定されていることが確認できます。

またスナップショットの機能を使っている場合は

```cs
database.Update(updEmp, snapshot);
```

と直接スナップショットを引数で渡すことも可能です。



このほか更新系には`UpdateMany` というものも存在しますが、こちらは省略します。

## Delete メソッド

```cs
Emp delEmp = database.SingleOrDefaultById<Emp>("E0001");
database.Delete(delEmp);
```

以下のクエリが実行されます。

```
DELETE FROM "emp" WHERE "emp_id" = @p0
         -> @p0 [String] = "E0001"
```

主キーによる削除が行われます。

## DeleteMany メソッド
単純な条件指定の削除であれば、SQLを書かなくても可能です。

```cs
database.DeleteMany<Emp>()
        .Where(x => x.DeptCode == "X01")
        .Where(x => x.FirstName.StartsWith("太"))
        .Execute();
```

以下のクエリが実行されます。

```
DELETE  FROM "emp" WHERE ("dept_code" = @p0) AND upper("first_name") like @p1 escape '\'
         -> @p0 [String] = "X01"
         -> @p1 [String] = "太%"
```

linqのような感じでクエリを組み立てることができます。

## SingleById, SingleOrDefaultById メソッド
主キーによる検索を行います。

`SingleById` は結果が存在しなければ例外をスローします。

```cs
database.SingleById<Emp>("E0001");
database.SingleOrDefaultById<Emp>("E0001");
```

以下のクエリが実行されます。(２つとも同じ)

```
SELECT "emp_id" AS "EmpId", "first_name" AS "FirstName", "family_name" AS "FamilyName", "dept_code" AS "DeptCode", "created_at" AS "CreatedAt", "updated_at" AS "UpdatedAt" FROM "emp" WHERE "emp_id" = @p0
         -> @p0 [String] = "E0001"
```

複合主キーの場合は[このように](https://github.com/schotime/NPoco/wiki/Composite-Primary-Keys)使うそうです。（使ったことないです..）

## Query<T> メソッド
このメソッドを使うことで 単純な検索クエリであれば、クエリを書かなくとも検索することが可能です。

```cs
database.Query<Emp>()
    .Where(x => x.DeptCode == "001")
    .OrderBy(x => x.EmpId)
    .ToList();
```

以下のクエリが実行されます。

```
SELECT "E"."emp_id" as "EmpId", "E"."first_name" as "FirstName", "E"."family_name" as "FamilyName", "E"."dept_code" as "DeptCode", "E"."created_at" as "CreatedAt", "E"."updated_at" as "UpdatedAt"
FROM "emp" "E"
WHERE ("E"."dept_code" = @p0)
ORDER BY "EmpId" ASC
         -> @p0 [String] = "001"
```

ソートの操作は`OrderBy`, `OrderByDescending`, `ThenBy`, `ThenByDescending` で可能です。

また`Limit` によるページング操作も可能です。

終端操作は
- `ToList`
- `Single`
- `SingleOrDefault`
- `First`
- `FirstOrDefault`

などが可能です。

その他の操作は[こちら](https://github.com/schotime/NPoco/wiki/Simple-linq-queries)

## 終わりに
今回は生のSQLを書かずに操作する方法について紹介しました。