Dapper のクエリ

# Dapper のクエリ
.NET ファミリで使える O/R マッパー である Dapper の使い方をまとめてみた。

使うテーブルは `Customer` です。

| カラム | 型 | 説明
| ---- | ---- | ----
| id | integer | Id
| firstName | string | 名前
| lastName | string | 名字
| gender | int | 0: 不明、1: 男性、2: 女性、9: 適用不能


## 一つのテーブル
### dynamic object を使う
型を指定しない限り dynamic object が使えます。さくっと使いたいときは便利ですね。

```csharp
var customers = _connection.Query("select * from customers");

foreach (var customer in customers)
{
    Console.WriteLine($"{customer.lastName} {customer.firstName}");
}
```


### カスタム型を使う
テーブルとマップしたカスタム型を用意すると補完が使えて便利です。

```csharp
var customers = _connection.Query<Customer>("select * from customers");

foreach (var customer in customers)
{
    Console.WriteLine($"{customer.LastName} {customer.FirstName}");
}
```

カスタム型はこんなかんじで、`enum` 型にもサポートされています。

```csharp
public enum GenderType
{
    Unknown = 0,
    Male = 1,
    Female = 2,
    NotApplicable = 9,
}

public sealed class Customer
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public GenderType Gender { get; set; }
}
```


### カスタム型を使う (コンストラクター)
不変オブジェクトで返して欲しいときもよくあります。Dapper はコンストラクターを利用できますので簡単に作れます。

使い方は同じですね。

```csharp
var customers = _connection.Query<Customer>("select * from customers");

foreach (var customer in customers)
{
    Console.WriteLine($"{customer.LastName} {customer.FirstName}");
}
```

カスタム型をこう書きます。

```csharp
public sealed class Customer
{
    public Customer(long id, string firstName, string lastName, long gender)
    {
        Id = id;
        FirstName = firstName;
        LastName = lastName;
        Gender = (GenderType) gender;
    }

    public long Id { get; }
    public string FirstName { get; }
    public string LastName { get; }
    public GenderType Gender { get; }
}
```

`long` 型に変更しているのは、SQLite3 の仕様で、データベースの型が `Int64` 型になっているからです。


### Value Object (単一カラム)
`SqlMapper.AddTypeHandler()` メソッドを使ってデータベースの型とカスタム型をマッピングできます。これを使うことで、単一カラムと Value Object をマッピングできます。

アプリケーションの開始時にマッパーを登録しておきます。

```csharp
SqlMapper.AddTypeHandler(new CustomerIdTypeHandler());
```

使う側はカスタム型の例と同じです。

```csharp
var customers = _connection.Query<Customer>("select * from customers");

foreach (var customer in customers)
{
    Console.WriteLine($"{customer.LastName} {customer.FirstName}");
}
```

型マッピングはこんな感じです。

```csharp
// Value Object
public struct CustomerId
{
    public CustomerId(long value)
    {
        Value = value;
    }

    public long Value { get; }
    public override string ToString() => Value.ToString();
}

// マッパー
public class CustomerIdTypeHandler : SqlMapper.TypeHandler<CustomerId>
{
    public override void SetValue(IDbDataParameter parameter, CustomerId value)
    {
        parameter.DbType = DbType.Int64;
        parameter.Value = value.Value;
    }

    public override CustomerId Parse(object value)
    {
        return new CustomerId((long) value);
    }
}

public class Customer
{
    public CustomerId Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public GenderType Gender { get; set; }
}
```


### Value Object (複数カラム)
氏名のように複数カラムを一つの Value Object にしたいこともあります。さくっと書ける方法はなさそうでした。(知っている人、教えてください!)

```csharp
const string sql = "select * from customers";
var customers = _connection.Query<Customer, CustomerName, Customer>(sql, (customer, name) =>
    {
        customer.Name = name;
        return customer;
    }, splitOn: "firstName");

foreach (var customer in customers)
{
    Console.WriteLine(customer.Name.ToString());
}
```

`splitOn` に `"firstName"` だけで動く理由は分かりませんでした。

カスタム型は以下のようになります。

```csharp
public struct CustomerName
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public override string ToString() => $"{LastName} {FirstName}";
}

public sealed class Customer
{
    private int Id { get; set; }
    public CustomerName Name { get; set; }
    public GenderType Gender { get; set; }
}
```


### ビルダーを使う
O/R マッパーに合わせてカスタム型を書くと、アプリケーションがデータベースの影響を受けやすくなります。O/R マッパーの機能に合わせるのではなく、テーブルと対応する型を用意して変換するとこの呪縛から逃れられます。

カスタム型ではなく、テーブルに対応した `CustomerBuilder` 型で一旦受け取って変換をします。

```csharp
const string sql = "select * from customers";
var customers = _connection.Query<CustomerBuilder>(sql).ToCustomers();

foreach (var customer in customers)
{
    Console.WriteLine(customer.Name);
}
```

カスタム型・テーブルに対応した型などは以下のとおりです。

```csharp
[Table("customers")]
public sealed class CustomerBuilder
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public GenderType Gender { get; set; }

    public Customer ToCustomer()
    {
        return new Customer(new CustomerId(Id), new CustomerName(FirstName, LastName), Gender);
    }
}

public sealed class CustomerName
{
    public CustomerName(string firstName, string lastName)
    {
        FirstName = firstName;
        LastName = lastName;
    }

    public string FirstName { get; }
    public string LastName { get; }
}

public sealed class Customer
{
    public Customer(CustomerId id, CustomerName name, GenderType gender)
    {
        Id = id;
        Gender = gender;
    }

    public CustomerId Id { get; }
    public CustomerName Name { get; }
    public GenderType Gender { get; }
}

public static class CustomerBuilderExtensions
{
    public static IEnumerable<Customer> ToCustomers(this IEnumerable<CustomerBuilder> builders)
    {
        return builders?.Select(builder => builder.ToCustomer());
    }
}
```

クエリを投げるところのコードは短くなったんですが、それ以外が長いですね。

`SqlMapper.AddTypeHandler()` メソッドで登録したものを使う、複数カラムはこの方法を使うあたりが落としどころだと考えています。


### Dapper.Contrib
Dapper.Contribを使うと簡単な SQL 文なら書かなくてよいです。

```csharp
var customers = _connection.GetAll<Customer>();

foreach (var customer in customers)
{
    ConsoleWriteLine($"{customer.LastName} {customer.FirstName}");
}
```

Dapper の戻り値は `IEnumerable<T>` なので、LINQ を使う場合は注意が必要です。以下のコードは Entity Framework と異なり、全てのレコードを取得した後にフィルターをかける効率が悪い処理になります。

```csharp
var customers = _connection.GetAll<Customer>()
    .Where(customer => customer.Gender =- GenderType.Male)
    .ToList();
```


## 検索をする
### dynamic object を使う
Dapper は dynamic object を渡すと、データベースのパラメーターに変換してくれます。ADO.NET ってパラメーターを作るのがしこたまめんどくさいので助かります。

```csharp
var sql = "select * from customers where gender = @Gender";
var customers = _connection.Query(sql, new { Gender = GenderType.Male });

foreach (var customer in customers)
{
    Console.WriteLine($"{customer.LastName} {customer.FirstName}");
}
```


### カスタム型を使う
カスタム型でも dynamic object と同じように使えます。

```csharp
var sql = "select * from customers where gender = @Gender";
var customers = _connection.Query(sql, new Condition(GenderType.Male));

foreach (var customer in customers)
{
    Console.WriteLine($"{customer.LastName} {customer.FirstName}");
}
```

検索に必要な `Condition` 型は以下のとおりです。

```csharp
public sealed class Condition {
    public Condition(GenderType gender) {
        Gedner = gender;
    }

    public GenderType Gender { get; }
}
```


### 動的に条件文を組み立てる
検索では性別が指定されなかった場合は全てのデータを、指定されたときはその性別をというのをよくやります。SQL 文を動的に組み立てることになります。

```csharp
var sql = "select * from customers" + (gender != null ? " where gender = @Gender" : "");
```

でも、この方法は検索条件が増えると結構大変でして。SQL Injection の静的検査ツールが脆弱性ありとマークする可能性もあり、あまり使いたくない手段です。

個人的によくやるのが以下の方法です。

```csharp
const string sql = @"
select * from customers
    where (gender = @Gender or @Gender is null)
";
var customers = _connection.Query(sql, new {Gender = (object) null});

foreach (var customer in customers)
{
    Console.WriteLine($"{customer.lastName} {customer.firstName}");
}
```

インデックス効くんかな? (笑)