LoginSignup
30
29

More than 5 years have passed since last update.

@masakurainGitLab Japan

Dapper のクエリ

Posted at

Dapper のクエリ

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

使うテーブルは Customer です。

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

一つのテーブル

dynamic object を使う

型を指定しない限り dynamic object が使えます。さくっと使いたいときは便利ですね。

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

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

カスタム型を使う

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

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

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

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

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 はコンストラクターを利用できますので簡単に作れます。

使い方は同じですね。

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

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

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

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 をマッピングできます。

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

SqlMapper.AddTypeHandler(new CustomerIdTypeHandler());

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

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

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

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

// 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 にしたいこともあります。さくっと書ける方法はなさそうでした。(知っている人、教えてください!)

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" だけで動く理由は分かりませんでした。

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

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 型で一旦受け取って変換をします。

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

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

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

[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 文なら書かなくてよいです。

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

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

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

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

検索をする

dynamic object を使う

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

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 と同じように使えます。

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 型は以下のとおりです。

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

    public GenderType Gender { get; }
}

動的に条件文を組み立てる

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

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

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

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

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}");
}

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

30
29
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
30
29