PetaPocoのクエリでODataを使えるようにする（$top,$skip,$orderby,$filter,$selectとか）

対象読者

• ASP.NET WebAPI 2 を使っている。ODataのことはよくわからない。
• DBはSQL Server(LocalDB含む）、あるいはMySQL、PostgreSQL、Oracleのいずれかだ。
• EntityFrameworkよりSQL文を直接書くMicro-ORMの方が好きだ。特にPetaPocoが好きだ。
• 検索のバリエーションを増やす度にWebAPIメソッドが増えるのが嫌だ。

---

2015年5月11日修正：検索リクエストのみであってもトランザクションを使用することでSQLインジェクションに対応するように修正しました。（PetaPocoのPageメソッド内部では複数のクエリが形を変えて連続的に実行されるため、そのうち1つでもエラーになればロールバックされます）

---

ODataのクエリは便利だけどそれだけでは不便なのでPetaPocoで活用したい。

ASP.NET WebAPI 2 が稼働している環境であれば、ODataクエリを使えるようにするのは実はとても簡単で、WebAPIメソッドに[EnableQuery]アトリビュートを付加するだけです。
参考:Web Api (REST サービス) を検索 (Query) 可能にする (および、OData への対応)
ただしそれだと戻り値が必ずJSON配列になってしまい、JSONオブジェクトに加工して返すことはできません。Web API: The Good PartsによればJSON配列で返すのは避けた方が良いとのことだし、勝手も良くないのでなんとかしたい。でも1つのWebAPIメソッドだけで引数次第で色々な検索ができるという長所は残したい。

PetaPocoならPageオブジェクトを返すことで、クエリ結果と共に対象レコード数、全体のページ数、今のページ位置などの付随情報もクライアント側で取得できるので、うまい具合に両者をミックスしたいところです。そもそもSQLクエリを直接記述する方がEntityFrameworkを使うよりも楽ですし。（個人的に）
参考:PetaPoco - A tiny ORM-ish thing for your POCOs

要件：下記の検索リクエストを全て1つのメソッドでさばけるようにする。ただし検索の実体はPetaPocoを使う。

// Productテーブルのデータを全て返す。
http://localhost:5000/products
// ProductテーブルのCode,Name,Priceフィールドだけ返す。
http://localhost:5000/products?$select=Code,Name,Price
// ProductテーブルのデータをPriceの降順で返す。
http://localhost:5000/products?$orderby=Price DESC
// ProductテーブルのNameが文字列ABCを含むものだけ返す。
http://localhost:5000/products?$filter=Name LIKE '*ABC*'
// Productテーブルのデータを先頭20件飛ばして21件目から10件分返す。
http://localhost:5000/products?$top=10&$skip=20
// いろいろ組み合わせる。日本語が混じる場合は当然ながらエンコードすること。
http://localhost:5000/products?$select=Code,Name,Price&$orderby=Price DESC&$filter=Name LIKE '*ABC*'&$top=10&$skip=20
// なんなら検索時に照合順序を変えることだってできる。
http://localhost:5000/products?$filter=Name COLLATE Japanese_CI_AS LIKE '*ぼーるぺん*'

Let's do it.

ASP.NET WebAPI 2 が稼働している環境に、OData v4 と PetaPoco をインストールします。

Install-Package Microsoft.AspNet.OData
Install-Package PetaPoco

PetaPocoでPageオブジェクトを生成するのに必要なオプションを格納するPOCOを用意します。

public class PetaPocoQueryOptions
{
    public long itemsPerPage { get; set; }
    public long currentPage { get; set; }
    public string select { get; set; }
    public string orderby { get; set; }
    public string filter { get; set; }
}

1つのWebAPIメソッドだけで上に書いたような全ての処理をこなすには、ODataQueryOptionsを引数として受け取り、その値をパクって自分なりにPetaPocoのクエリに組み込む必要があります。

この例では、$top, $skip, $filter, $orderby, $select の値が存在する場合はそれを生かし、存在しなければデフォルト値に置き換えるという処理をしています。

ちなみにHttpConfigurationでODataのEDMを事前に登録したりだとかは一切行っていません。めんどくさいことはやりたくないのでEntityの型もdynamicでごまかします。

// MyControllerクラス（ApiControllerを継承する）

private PetaPoco.Database db = new PetaPoco.Database("Your Connection String");

[Route("products")]
[HttpGet]
public IHttpActionResult GetProducts(ODataQueryOptions<dynamic> odataOptions) // <---ODataQueryOptionsにEntity型を付けないとエラーになるので<dynamic>でお茶を濁す。
{
    try
    {
        Page<dynamic> page = null;
        var options = ParseODataOptionsForPetaPoco(odataOptions, 10, "Code");　// <---ODataQueryOptionsの値をパクってPetaPocoQueryOptionsを生成する。
        var sql = PetaPoco.Sql.Builder.Append(string.Format(@"
          SELECT {0} 
          FROM Product
          WHERE {1}
          ORDER BY {2}
      ", options.select, options.filter, options.orderby));　// <---生SQLを書けるのでナンデモアリ。
        Debug.WriteLine(sql.SQL); // <---実行されるSQLクエリをデバッグ出力する。
        using (var scope = db.GetTransaction()) // <---トランザクションを開始する。
        {
            try
            {
                page = db.Page<dynamic>(options.currentPage, options.itemsPerPage, sql);
                scope.Complete(); // <---Pageメソッドのクエリが全て成功したらコミットする。
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.ToString()); // <---SQLインジェクションがあった場合はここに行き着く。
            }
        }
        return Ok(new { Page = page });
    }
    catch (Exception ex)
    {
        return BadRequest(ex.ToString());
    }
}

private PetaPocoQueryOptions ParseODataOptionsForPetaPoco(ODataQueryOptions odataOptions, long defaultItemsPerPage, string defaultOrderby)
{
    var values = odataOptions.RawValues;
    var skip = values.Skip != null ? long.Parse(values.Skip) : 0;
    var itemsPerPage = values.Top != null ? long.Parse(values.Top) : defaultItemsPerPage;
    var currentPage = skip == 0 ? 1 : skip / itemsPerPage + 1;
    var select = values.Select != null ? values.Select : "*";
    var orderby = values.OrderBy != null ? values.OrderBy : defaultOrderby;
    var filter = values.Filter != null ? values.Filter.Replace("*", "%") : "1 = 1";            
    return new PetaPocoQueryOptions()
    {
        itemsPerPage = itemsPerPage,
        currentPage = currentPage,
        select = select,
        orderby = orderby,
        filter = filter
    };
}

以上、はっきり言ってODataの魔改造であり、自分の利益のために本来の仕様を捻じ曲げているので一般にお勧めできる手法ではないですが、こういうやり方もあるよねっていうことで。