はじめに

みなさんSQLは書いていますか。僕は書きたくないです！(笑)

先日、C#の.NET Framework MVCに触れる機会がありました。その中で、ORMとしてDapperを指定されました。

通常、.NETを使う際はORMにEntity Framework(EF)を使うようです。EFにはそんなに詳しくないのですが、SQLをLINQっぽい文法で動的に組み立てて遅延実行ができるみたいです。しかし、Dapperは自らをmicro ORMと謳っていて、こいつはSQLのクエリを組み立てる能力はなく、自分でSQLを書かなければならないようです。一方で、使い勝手自体はかなりよく、マッピングしたいクラスを型引数に指定するだけで、プロパティに値を設定したインスタンスを返してくれます。

使い方については公式リファレンスのほか以下のようなページでも分かりやすく書いてあるので目を通してみるといいでしょう。

Dapper
https://dapper-tutorial.net/dapper
Dapperを使ったデータマッピングのはじめ方 │ Web備忘録
https://webbibouroku.com/Blog/Article/dapper

しかし、EloquentやActive Recordの旨味を知ってしまった僕はSQLを書きたくないのです。よって、Dapperを内部に隠ぺいしつつ、SQLを自力で書かないで済むような仕組みを考えます。

Dapperの使い方

Dapperの基本的な使い方は次のような感じです。

class User {
    public long? Id { get; set; }
    public string Name { get; set; }
    public DateTime? Birthday { get; set; }
}

string sql = "SELECT * FROM users WHERE Birthday > :Birthday;";
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    ICollection<User> users = connection.Query<User>(sql, new {Birthday = new DateTime(1990, 1, 1)});
}

なるほど。どうやらConnection.Query<マッピングしたい型>(SQL文, バインド変数の匿名オブジェクト)のように使うらしいです。このQueryメソッドがDBConnectionの拡張メソッドとして定義されているみたいですね。

というわけで、なんとかしてこのSQLを書かずに済む方法を考えましょう。

たまにあるやつ

たまにあるのはWHERE 1 = 1みたいなSQL文をあらかじめ作っておき、StringBuilderとかでSQLを伸長していくやつです。

StringBuilder query = new StringBuilder();
query.Append("SELECT * FROM users WHERE 1=1");

public void AddWhere(String where){
    query.Append($"AND {where}")
}

WHERE 1 = 1という常にtrueになる文言を加えておくことで、あとは機械的にAND WHERE...という句を付け足していけばいいという方法ですね。

ただ、この方法だとやはり柔軟性に欠けますし、JOIN句を付け足したりということもできないし、そもそもWHERE1 = 1なんていう禅問答みたいな句がSQLに入っているというのもなんだか美しくないです。

Eloquentのソースコードを見てみる

database/Builder.php at master · illuminate/database
https://github.com/illuminate/database/blob/master/Query/Builder.php

Builderというクラスを見てみましょう。

public $bindings = [
    'select' => [],
    'from' => [],
    'join' => [],
    'where' => [],
    'groupBy' => [],
    'having' => [],
    'order' => [],
    'union' => [],
    'unionOrder' => [],
];
/**
 * The where constraints for the query.
 *
 * @var array
*/
    public $wheres = [];
// ...中略
/**
    * Add a basic where clause to the query.
    *
    * @param  \Closure|string|array  $column
    * @param  mixed  $operator
    * @param  mixed  $value
    * @param  string  $boolean
    * @return $this
    */
public function where($column, $operator = null, $value = null, $boolean = 'and')
{
    // If the column is an array, we will assume it is an array of key-value pairs
    // and can add them each as a where clause. We will maintain the boolean we
    // received when the method was called and pass it into the nested where.
    if (is_array($column)) {
        return $this->addArrayOfWheres($column, $boolean);
    }

    // ...中略

    if (! $value instanceof Expression) {
        $this->addBinding($this->flattenValue($value), 'where');
    }

    return $this;
}

なにやらwhereやらjoinやらを変数を持っています。これらに句を追加していくのでしょうか。また、それぞれのメソッドは、return thisと自分自身を返しています。これでメソッドチェーンを可能にしているのでしょう。

真似してみる

ということで真似してみたいと思います。しかし、もとのDapperの使い方を見てみましょう。

string sql = "SELECT * FROM users WHERE Birthday > :Birthday;";

ICollection<User> users= connection.Query<User>(sql, new {Birthday = new DateTime(1990, 1, 1)});

うーん、バインド変数を用意しないと駄目みたいですね。が、よくよく考えてみると、別にすぐこのあとに変数に値が代入されるのです。変数の名前なんてhogeでもfugaでもどうでもいいでしょう。この部分はプログラムで機械的にparam0, param1, ...という感じで作ってしまいたいと思います。一方で、そのバインド変数に匿名オブジェクトを用意しないといけません。これは、機械的にバインド変数を作ると同時に作っていくことにしましょう。

というわけでこれを参考にクラスを作っていきます。SQL文をバインド変数とともに組み立てるためのクラスと、実際にSQLを実行するクラスとに分けて作っていくことにします。

public class SelectQueryExecuter
{
    private string table;
    private IList<string> wheres = new List<string>();
    private IList<string> orWheres = new List<string>();
    private IDictionary<string, object> parameters = new Dictionary<string, object>();
    public SelectQueryExecuter(string table)
    {
        this.table = table;
    }
    public SelectQueryExecuter(string table, string alias)
    {
        this.table = $"{table} {alias}";
    }
    public SelectQueryExecuter Where(string column, string operator_, object val)
    {
        int paramsCnt = parameters.Count;
        parameters.Add($"param{paramsCnt}", val);
        wheres.Add($"{column} {operator_} :param{paramsCnt}");
        return this;
    }
    public SelectQueryExecuter WhereIn<T>(string column, IEnumerable<T> val)
    {
        int paramsCnt = parameters.Count;
        parameters.Add($"param{paramsCnt}", val);
        wheres.Add($"{column} IN :param{paramsCnt}");
        return this;
    }
    public SelectQueryExecuter WhereNot(string column, object val)
    {
        if (val != null)
        {
            int paramsCnt = parameters.Count;
            parameters.Add($"param{paramsCnt}", val);
            wheres.Add($"{column} <> :param{paramsCnt}");
        }
        else
        {
            wheres.Add($"{column} IS NOT NULL");
        }
        return this;
    }
    public SelectQueryExecuter WhereBetween<T>(string column, IComparable<T> start, IComparable<T> end)
    {
        int paramsCnt = parameters.Count;
        parameters.Add($"param{paramsCnt}", start);
        parameters.Add($"param{paramsCnt + 1}", end);
        wheres.Add($"{column} BETWEEN :param{paramsCnt} AND :param{paramsCnt + 1}");
        return this;
    }
    public SelectQueryExecuter WhereId(long id)
    {
        return Where($"{table}.id", id);
    }
    public SelectQueryExecuter WhereIdIn(IEnumerable<long> ids)
    {
        return WhereIn($"{table}.id", ids);
    }
    public SelectQueryExecuter OrWhere(string column, object val)
    {
        if (val != null)
        {
            int paramsCnt = parameters.Count;
            parameters.Add($"param{paramsCnt}", val);
            orWheres.Add($"{column} = :param{paramsCnt}");
        }
        else
        {
            orWheres.Add($"{column} IS NULL");
        }
        return this;
    }
    // ...中略
    public string ToQuery()
    {
        var sql = new StringBuilder($"SELECT * FROM {table} ");
        // WHERE
        for (int i = 0; i < wheres.Count; i++)
        {
            if (i == 0) sql.Append($"WHERE {wheres[i]}"); // 最初
            else sql.Append($" AND {wheres[i]} "); // それ以外
        }
        // OR WHERE
        for (int i = 0; i < orWheres.Count; i++)
        {
            if (i == 0) sql.Append($"WHERE {orWheres[i]}"); // 最初
            else sql.Append($" OR {orWheres[i]} "); // それ以外
        }
        return sql.ToString();
    }
    public T GetOne<T>()
    {
        var query = ToQuery();
        return DatabaseManager.ExcecuteQueryAndFetchSingleResult<T>(query, parameters);
    }
    public ICollection<T> Get<T>()
    {
        var query = ToQuery();
        return DatabaseManager.ExcecuteQueryAndFetchResults<T>(query, parameters);
    }
}

public class DatabaseManager{
    // ...中略
    public static Type ExcecuteQueryAndFetchSingleResult<Type>(string query, object param = null)
    {
        using (var ts = new TransactionScope())
        using (var conn = GetConnection())
        {
            conn.Open();
            List<Type> result = conn.Query<Type>(query, param).ToList();
            if (result.Count == 0) return default;
            return result[0];
        }
    }

    public static ICollection<Type> ExcecuteQueryAndFetchResults<Type>(string query, object param = null)
    {
        using (var ts = new TransactionScope())
        using (var conn = GetConnection())
        {
            conn.Open();
            ICollection<Type> result = conn.Query<Type>(query, param).ToList();
            return result;
        }
    }
}

SQL文を組み立てるSelectQueryExecuterでは、テーブル名、WHERE句の要素、OR WHERE句の要素などを保持しておくプライベートなフィールドを持っておきます。

SQLを考えるときって、だいたいどのカラムからデータを取るかということ以前に、どのテーブルからデータを取るかということを考えると思うので、真っ先にこれを書くために、コンストラクタにそれを指定させるようにしました。

public SelectQueryExecuter(string table)
{
    this.table = table;
}

Whereメソッドが呼ばれたときには、現在保持しているバインド変数の数を取得し、それをもとにparam0のようなバインド変数を作成し、バインド変数のオブジェクト(IDictionaryを実装していれば匿名オブジェクトである必要はないそうです)にそれを追加。WHERE句の要素を管理するリストにもそれを追加します。最後に自分自身を返し、メソッドチェーンが続けられるようにします。

public SelectQueryExecuter Where(string column, object val)
{
    if (val != null)
    {
        int paramsCnt = parameters.Count;
        parameters.Add($"param{paramsCnt}", val);
        wheres.Add($"{column} = :param{paramsCnt}");
    }
    else
    {
        wheres.Add($"{column} IS NULL");
    }
    return this;
}

そして、ToQueryメソッドが呼ばれたときにSQL文を構築します。ここは、WHERE 1=1などに頼ることなく、最初の要素かそれ以外かで場合分けしながら愚直に構築していきます。プログラマーなんだからそれくらいは頑張ってやりましょう。

public string ToQuery()
{
    var sql = new StringBuilder($"SELECT * FROM {table} ");
    // WHERE
    for (int i = 0; i < wheres.Count; i++)
    {
        if (i == 0) sql.Append($"WHERE {wheres[i]}"); // 最初
        else sql.Append($" AND {wheres[i]} "); // それ以外
    }
    // OR WHERE
    for (int i = 0; i < orWheres.Count; i++)
    {
        if (i == 0) sql.Append($"WHERE {orWheres[i]}"); // 最初
        else sql.Append($" OR {orWheres[i]} "); // それ以外
    }
    return sql.ToString();
}

最後にGetメソッドです。GetメソッドでSQL文を実際に実行します。型引数には、マッピングしたいクラスを指定します。今回は、結果を1件だけ取得したいときはGetOneメソッドを、リストで取得したいときはGetメソッドを呼ぶようにしました。

public ICollection<T> Get<T>()
{
      var query = ToQuery();
      return DatabaseManager.ExcecuteQueryAndFetchResults<T>(query, parameters);
}

これによって次のようにクエリーをメソッドチェーンで書くことができるようになりました。

User user = new SelectQueryExecuter("users")
    .Where("is_deleted", false)
    .Where("mail_address", mailAddress)
    .GetOne<User>();

言うまでもないですが、

SELECT *
FROM users
WHERE is_deleted = false
AND mail_address = :param0

と等価です。

かなりEloquentやActive Recordみが出てきたんじゃないでしょうか。

機能を増やす

これが分かれば簡単です。この要領でJOINもできるようにしましょう。

private IList<string> joins = new List<string>();
private IList<string> leftJoins = new List<string>();

のようなプライベート変数を用意し、

public SelectQueryExecuter Join(string table, string condition)
{
    joins.Add($"{table} ON {condition}");
    return this;
}
public SelectQueryExecuter Join(string table, string alias, string condition)
{
    joins.Add($"{table} {alias} ON {condition}");
    return this;
}
public SelectQueryExecuter LeftJoin(string table, string condition)
{
    leftJoins.Add($"{table} ON {condition}");
    return this;
}
public SelectQueryExecuter LeftJoin(string table, string alias, params string[] conditions)
{
    leftJoins.Add($"{table} {alias} ON {string.Join(" AND ", conditions)}");
    return this;
}

のようにメソッドを生やします。JOINの場合はWHEREで条件を絞ればそのレコードごと落ちますが、LEFT JOINの場合はレコードごと落ちると困るという場合があるので（論理削除のときなど）、conditionsで複雑な条件設定ができるようにしています。

これにより次のようなクエリーをメソッドチェーンで書くことができるようになりました。

ICollection<Book> books = new SelectQueryExecuter("books", "b")
    .Join("authors", "a", "a.id = b.author = id")
    .Where("a.gender", 1)
    .Select("books.*") // Selectメソッドを追加しカラムも設定できるようにした
    .Get<Book>();

親も一緒にマッピングする

だんだんQueryBuilderが立派になってきたので、さらに欲が湧いてきます。親も一緒にマッピングしたくなってきました。

公式リファレンスを見ると次のようにしろと書いてあります。

Result Multi Mapping
https://dapper-tutorial.net/result-multi-mapping

string sql = "SELECT * FROM Invoice AS A INNER JOIN InvoiceDetail AS B ON A.InvoiceID = B.InvoiceID;";

using (var connection = My.ConnectionFactory())
{
    connection.Open();

    var invoices = connection.Query<Invoice, InvoiceDetail, Invoice>(
            sql,
            (invoice, invoiceDetail) =>
            {
                invoice.InvoiceDetail = invoiceDetail;
                return invoice;
            },
            splitOn: "InvoiceID")
        .Distinct()
        .ToList();
}

あるいは、以下のようなQiitaの記事を見てもいいかもしれません。

DapperのQueryを使ってマルチマッピングしてみる - Qiita
https://qiita.com/Tokeiya/items/dda1096d03dfbf5fe431

子モデルのプロパティに親モデルを持たせて、マッピング時に代入していくわけですね。

まず、SQLを実行するクラスであるDatabaseManagerに、複数の型引数を受け取れるようにメソッドを追加します。

public static ICollection<TResult> ExcecuteQueryAndFetchResults<TFirst, TSecond, TResult>(string query, Func<TFirst, TSecond, TResult> mappingFunc, object param = null)
{
    using (var ts = new TransactionScope())
    using (var conn = GetConnection())
    {
        conn.Open();
        ICollection<TResult> result = conn.Query<TFirst, TSecond, TResult>(query,
            map: (x, y) => mappingFunc(x, y), param).ToList();

        return result;
    }
}

public static ICollection<TResult> ExcecuteQueryAndFetchResults<TFirst, TSecond, TThird, TResult>(string query, Func<TFirst, TSecond, TThird, TResult> mappingFunc, object param = null)
{
    using (var ts = new TransactionScope())
    using (var conn = GetConnection())
        conn.Open();
        ICollection<TResult> result = conn.Query<TFirst, TSecond, TThird, TResult>(query,
            map: (x, y, z) => mappingFunc(x, y, z), param).ToList();
        return result;              
    }
}

次にSelecuQueryExecuterにGetEagerWithParentというメソッドを追加して、公式リファレンスに書いてあるのと等価な処理をリフレクションを使って書きます。

// 親1
public ICollection<TChild> GetEagerWithParent<TChild, TParent>(TChild child, TParent parent, string parentPropName)
{
    PropertyInfo parentPropInfo = child.GetType().GetProperty(parentPropName);
    Func<TChild, TParent, TChild> mappingFunc = (_child, _parent) =>
    {
        parentPropInfo.SetValue(_child, _parent);
        return _child;
    };
    var query = ToQuery();
    return DatabaseManager.ExcecuteQueryAndFetchResults(query, mappingFunc, parameters);
}
// 親2
public ICollection<TChild> GetEagerWithParent<TChild, TParent1, TParent2>(TChild child, TParent1 parent1, string parentPropName1, TParent2 parent2, string parentPropName2)
{
    PropertyInfo parentPropInfo1 = child.GetType().GetProperty(parentPropName1);
    PropertyInfo parentPropInfo2 = child.GetType().GetProperty(parentPropName2);

    Func<TChild, TParent1, TParent2, TChild> mappingFunc = (_child, _parent1, _parent2) =>
    {
        parentPropInfo1.SetValue(_child, _parent1);
        parentPropInfo2.SetValue(_child, _parent2);
        return _child;
    };
    var query = ToQuery();
    return DatabaseManager.ExcecuteQueryAndFetchResults(query, mappingFunc, parameters);
}

ほんとは親の数を可変長にしてもっと柔軟に書きたかったのですが、可読性がさらに悪化するのと、そこまで大量のマッピングを要求されることがないだろうと思ったのでべた書きにしました。

GetEagerWithParentメソッドの引数は、(最終的にマッピングしたいクラスのインスタンス, 親としてマッピングしたいクラスのインスタンス, 親を代入するためのプロパティ名, ...)というふうにしました。これによって次のようにクエリーを書くことができるようになりました。

ICollection<Book> books = new SelectQueryExecuter("books", "b")
    .Join("authors", "a", "a.id = b.author = id")
    .Where("a.gender", 1)
    .Select("books.*, authors.*")
    .GetEagerWithParent(new Book(), new Author(), "Author");

いい感じですね。

子も一緒にマッピングしたい

親のマッピングができれば次は子供のマッピングです。子供のマッピングはかなり厄介です。公式リファレンスには次のようなやり方が紹介されています。

string sql = "SELECT TOP 10 * FROM Orders AS A INNER JOIN OrderDetails AS B ON A.OrderID = B.OrderID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{            
    var orderDictionary = new Dictionary<int, Order>();


    var list = connection.Query<Order, OrderDetail, Order>(
        sql,
        (order, orderDetail) =>
        {
            Order orderEntry;

            if (!orderDictionary.TryGetValue(order.OrderID, out orderEntry))
            {
                orderEntry = order;
                orderEntry.OrderDetails = new List<OrderDetail>();
                orderDictionary.Add(orderEntry.OrderID, orderEntry);
            }

            orderEntry.OrderDetails.Add(orderDetail);
            return orderEntry;
        },
        splitOn: "OrderDetailID")
    .Distinct()
    .ToList();

    Console.WriteLine("Orders Count:" + list.Count);

    FiddleHelper.WriteTable(list);
    FiddleHelper.WriteTable(list.First().OrderDetails);
}

keyが親のid、valueが親のインスタンスとなるDicitonaryを用意しておき、SQLの結果レコードを1行走査するごとに、新たな親が見つかればDicitionaryに追加、既存の親ならそのインスタンスをDictionaryから取得。そのインスタンスのList型の子プロパティに子インスタンスを追加していくようです。とても面倒ですね。

が、嘆いても仕方ないので、この処理をやはりリフレクションをもりもりに使いながら、ラップします。すなわちこうです。

// 子1
public ICollection<TParent> GetEagerWithChildren<TParent, TChild>(TParent parent, TChild child, string childrenPropName)
{
    var result = new Dictionary<long, TParent>();
    PropertyInfo parentIdPropInfo = parent.GetType().GetProperty("Id");
    PropertyInfo childrenPropInfo = parent.GetType().GetProperty(childrenPropName);
    Func<TParent, TChild, TParent> mappingFunc = (_parent, _child) => {
        long parentId = (long)parentIdPropInfo.GetValue(_parent);
        if (!result.ContainsKey(parentId))
        {
            result.Add(parentId, _parent);
        }
        TParent parentInDict = result[parentId];
        ICollection<TChild> children = (ICollection<TChild>)childrenPropInfo.GetValue(parentInDict);
        if (children == null) childrenPropInfo.SetValue(parentInDict, new List<TChild>());
        if (_child != null) ((ICollection<TChild>)childrenPropInfo.GetValue(parentInDict)).Add(_child);
        return default;
    };
    var query = ToQuery();
    DatabaseManager.ExcecuteQueryAndFetchResults(query, mappingFunc, parameters);
    return result.Values;
}
// 子2
public ICollection<TParent> GetEagerWithChildren<TParent, TChild1, TChild2>(TParent parent, TChild1 child1, string childrenPropName1, TChild2 child2, string childrenPropName2)
{
    var result = new Dictionary<long, TParent>();
    PropertyInfo parentIdPropInfo = parent.GetType().GetProperty("Id");
    PropertyInfo childrenPropInfo1 = parent.GetType().GetProperty(childrenPropName1);
    PropertyInfo childrenPropInfo2 = parent.GetType().GetProperty(childrenPropName2);

    Func<TParent, TChild1, TChild2, TParent> mappingFunc = (_parent, _child1, _child2) => {
        long parentId = (long)parentIdPropInfo.GetValue(_parent);
        if (!result.ContainsKey(parentId))
        {
            result.Add(parentId, _parent);
        }
        TParent parentInDict = result[parentId];

        ICollection<TChild1> children1 = (ICollection<TChild1>)childrenPropInfo1.GetValue(parentInDict);
        if (children1 == null) childrenPropInfo1.SetValue(parentInDict, new List<TChild1>());
        if (_child1 != null) ((ICollection<TChild1>)childrenPropInfo1.GetValue(parentInDict)).Add(_child1);

        ICollection<TChild1> children2 = (ICollection<TChild1>)childrenPropInfo2.GetValue(parentInDict);
        if (children2 == null) childrenPropInfo2.SetValue(parentInDict, new List<TChild2>());
        if (_child2 != null) ((ICollection<TChild2>)childrenPropInfo2.GetValue(parentInDict)).Add(_child2);

        return default;
    };
    var query = ToQuery();
    DatabaseManager.ExcecuteQueryAndFetchResults(query, mappingFunc, parameters);
    return result.Values;
}

うんざりするほど長くなりましたが、これのおかげで次のようなクエリーが書けるようになりました。

ICollection<Book> books = new SelectQueryExecuter("books", "b")
    .Join("authors", "a", "a.id = b.author = id")
    .Where("a.gender", 1)
    .Select("authors.*, books.*")
    .GetEagerWithChidren(new Author(), new Book(), "Books");

プログラマーは手抜きをするためなら全力で努力をする生き物です。

モデルからSelectQueryExecuterを呼びたい

ここまで行くと、さらにEloquentやActive Recordに寄せてみたくなります。つまりモデルから直接クエリーを発行するのです。

SelectQueryExecuterにモデルを受け付けるようなメソッドを生やしておきます。

public SelectQueryExecuter SetModel<T>(Model<T> model) where T: Model<T>
{
    this.table = model.Table;
    return this;
}

そして次のような抽象基底クラスを定義します。Table名の実装を強制します。Modelクラス自体がジェネリックになっているのは、FindメソッドやAllメソッドでこのクラスを継承した具象クラスのインスタンスを返したいからです。

public abstract class Model<T> where T: Model<T>
{
    public abstract string Table { get; }
    public long? Id { get; set; }

    public virtual SelectQueryExecuter DefaultQuery() 
    {
        return new SelectQueryExecuter().SetModel(this);
    }

    public T Find(long id)
    {
        return DefaultQuery().Where("id", id).GetOne<T>();
    }

    public ICollection<T> All()
    {
        return DefaultQuery().Get<T>();        
    }
    // 中略
}

これにより、次のようなクエリーを発行することができるようになりました。

User user = new User().Find(42);

DefaultQueryには必要に応じて論理削除や並び順などの処理を書いておくといいでしょう。よくできたORMのGlobalScopeみたいなことができます。

public class User: Model<User>{
    // 中略
    public override SelectQueryExecuter DefaultQuery()
    {
        return base.DefaultQuery().Where("is_deleted", false).OrderBy("user_no");
    }
    // 略
}

SelectQueryExecuterはGetメソッドを呼ばれない限りSQLは実行されず、順番に縛られずにQueryの状態を保持することができるので、頻繁にJOINするおきまりのテーブルがあるならばそれをあらかじめ定義したSelectQueryExecuterを返すメソッドをモデル内に作るのもいいでしょう。LocalScopeみたいなことができます。

public SelectQueryExecuter GetJoinedQuery(){
    return DefaultQuery().LeftJoin("departments", "departments.id = users.department_id");
}

UPDATEにも対応する

最後にUPDATEにも対応しましょう。まず、Dapperの公式リファレンスを見てみましょう。

string sql = "UPDATE Categories SET Description = @Description WHERE CategoryID = @CategoryID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{            
    var affectedRows = connection.Execute(sql,new {CategoryID = 1, Description = "Soft drinks, coffees, teas, beers, mixed drinks, and ales"});

    Console.WriteLine(affectedRows);
}

やり方はSELECTのときと全く同じで、Executeというメソッドの第一引数にSQL文、第二引数に匿名オブジェクトを指定するみたいですね。辻褄さえ合っていれば、第二引数は匿名オブジェクトである必要はなく実際のクラスのインスタンスでも全く問題ありません。

DBがスネークケースで、C#のクラスのプロパティはパスカルケースということが多いでしょうから、スネークケースをパスカルケースに変換するstringの拡張メソッドを作っておきます。

public static class StringExtension
    public static string ToPascal(this string snake) 
    {
        return snake.Split(new[] { "_" }, StringSplitOptions.RemoveEmptyEntries)
            .Select(s => char.ToUpper(s[0]) + s.Substring(1, s.Length - 1))
            .Aggregate((s1, s2) => s1 + s2);
    }
}

そしてUpdateQueryExecuterメソッドを作成します。

public class UpdateQueryExecuter
{
    private string table;
    private string[] columns;
    private object parameters;

    public UpdateQueryExecuter(string table){
        this.table = table;
    }
    public UpdateQueryExecuter Columns(string columns)
    {
        this.columns = columns.Split(",").Select(column => column.Trim()).ToArray();
        return this;
    }
    public string ToQuery()
    {
        if (!string.IsNullOrEmpty(rawQuery))
        {
            return rawQuery;
        }
        var sql = new StringBuilder($"UPDATE {table} ");
        for(int i = 0 ; i < columns.Length; i++)
        {
            string column = columns[i];
            if (i == 0) sql.Append($" SET {column} = :{column.ToPascal()} "); // 最初
            else if (i == columns.Length - 1) sql.Append($", {column} = :{column.ToPascal()} ");　// 最後
            else sql.Append($", {column} = :{column.ToPascal()}");
        }
        sql.Append("WHERE id = :Id");
        return sql.ToString();
    }
    public void Execute(object paramObj)
    {
        string query = ToQuery();
        parameters = paramObj;
        DatabaseManager.ExecuteQuery(query, parameters);
    }
}

UPDATE文はSELECTと比べて自由度が圧倒的に少なくシンプルです。

これによって次のようなクエリーを書くことができるようになりました。

User user = GetUpdatedUser();
new UpdateQueryExecuter("users").Columns("name,mail_address,password").Execute(user);

もっとORMっぽく

いちいち毎回カラムを指定するのが面倒です。というわけでModel抽象クラスに次のようなフィールドとメソッドを追加します。

public virtual ICollection<string> DefaultColumns { get; } = new List<string>();

public void Save(){
    new UpdateQueryExecuter(Table).Columns(DefaultColumns).Execute(this);
}

具象クラスではこれを必要に応じてオーバーライドします。マスターデータならいらないと思いますが、たいていの場合はオーバーライドが必要でしょう。

public class User : Model<User>{
    public virtual ICollection<string> DefaultColumns { get; } = new List<string>() {"name, mail_address", "password"};
}

これで次のようにモデルがDBに保存できるようなりました。簡単ですね。

user.Save();

おわりに

SQLを書きたくないがゆえに、Dapperを使いつつSQLを書かずに済む方法を提案しました。初めからEF使えという話ではありますが。しかし、自分でこのようなQueryBuilderを一から実装すると、ブラックボックスにならず、また愛着も湧くというものです。必要に応じて拡張ができるのも嬉しいところです。

今回は全ての紹介をすることはせず一部の紹介にとどめましたが、同様の手順で他のメソッドを追加したり、INSERTやDELETEも同じように実行することが可能です。皆さんも自分だけのQueryExecuterを作ってみてくださいね！　それではみなさん、よきORMライフを！