ASP.NET MVC の ORM として PetaPoco を使う（SQL Server CE, Oracle）

.NET PetaPoco ASP.NET MVC

ASP.NET MVC で実際を行っている上で、ORM は何がいいのかなーと調べていたら Micro-ORM という言葉をキャッチ。社内でも「Entity Framework はちょっと重厚すぎるし、ある規模を超えると極端に遅いから結局SQLを直で叩くよね」なんて話しを聞いていた。


知ったのは、以下の記事から。
Arroyocode Blog - Micro ORM Data Mapping with PetaPoco and ASP.NET MVC 4

InfoQでも取り上げられている。

PetaPocoは.NETアプリケーションのためのシンO/Rマッパー (ORM) だ。NHibernateやEntity Frameworkといった本格的なORMとは違って、機能の豊富さよりも使いやすさやパフォーマンスに重点が置かれている。PetaPocoはC#ファイル1つでできており、強く型付けされたPOCOで動作し、T4 Templateなどを使ったクラス生成をサポートする。

PetaPocoには次のような非常に興味深い機能がある。

・SQL Server、SQL Server CE、MySQL、PostgreSQL、Oracleデータベースで動作する
・Insert/Delete/Update/Save/IsNewのためのヘルパーメソッド
・簡単なトランザクションのサポート
・ページ化されたリクエスト。自動的にレコード全体を分析し、特定のページをフェッチする。
・パラメータ置き換えのサポート。オブジェクトプロパティから名前付きパラメータを取り出せる。
・低摩擦のSQL Builderクラスを含む。
・部分的レコード更新
・データベーススキーマからPOCOクラスを生成するためのT4 Templateを含む。

PetaPoco: .NET向けMicro ORM


日本語だと@neueccさんの記事で触れられている。ここで他の Micro-ORM も知った。パフォーマンステストをされており、参考になった。（本当は鵜呑みにせず自分で測らないとダメなんだけどね。。）
neue cc - C#のMicro-ORM(Dapper, Massive, PetaPoco)について


他のプロダクトも見てみたけど、例えば Dapper はなんとなく記法が好みでなかったりで、PetaPoco を触ってみた。
ASP.NET MVC + PetaPoco のサンプルコードは最初に挙げたリンクの記事についているので、それを参考に。


サクッと試したかったので DB は SQL Server Compact で試した。
チュートリアル: Visual Studio での SQL Server Compact の使用

# 本当は Oracle を使って出来ている既存プロダクトに対して使いたかったんだけど、Oracle を対象に使うのは色々嵌った。Oracle に対して使うにはしっかり事前検証が必要だと思う。これは後述。

web.config に追記して。（Oracle用は無視してください）

  <connectionStrings>
    <add name="OracleConn"
         connectionString="DATA SOURCE=XXX;USER ID=XXX;PASSWORD=XXX"
         providerName="Oracle.DataAccess.Client" />
    <add name="SSCEConn"
         connectionString="Data Source=|DataDirectory|\FlowerShop.sdf"
         providerName="System.Data.SqlServerCe.4.0" />
  </connectionStrings>


基本的には公式ページに書いてある内容を一通り確認。

// 単項目取得
var count = db.ExecuteScalar<int>("SELECT Count(*) FROM Products");
var name = db.ExecuteScalar<string>("SELECT Name FROM Products WHERE ID=@0", 1);

// １レコード取得（該当データがなかったら null）
var product = db.SingleOrDefault<Products>("SELECT * FROM Products WHERE ID=@0", 1);

// テーブル名、カラム名の省略
var products = db.Fetch<Products>("WHERE UnitPrice = @0", 29.99);

// 名前付きパラメータ
products = db.Fetch<Products>("WHERE UnitPrice = @UnitPrice", new { UnitPrice = 29.99 });

// SQLビルダー
var sql = Sql.Builder
    .Select("*")
    .From("Products")
    .Where("ID < @0", 100)
    .OrderBy("ID DESC");
products = db.Fetch<Products>(sql);

// 新規作成
product = new Products();
product.Name = "新しいお花";
product.UnitPrice = 50;
product.UnitsInStock = 100;
db.Insert(product);

// 更新(レコード全体更新)
product.UnitPrice = 80;
db.Update(product);

// 更新(レコード部分更新)
db.Update<Products>("SET UnitsInStock=@0 WHERE ID=@1", 490, 1);

// 削除1
db.Delete(product);
// 削除2
db.Delete<Products>("WHERE ID=@0", 123);

// ページング
// 5 record/page として、2ページ目に該当する情報を取得
var pageInfo = db.Page<Products>(2, 5, "SELECT * FROM Products ORDER BY Products.ID");
// pageInfo.CurrentPage   : 現在のページ
// pageInfo.ItemsPerPage  : 1ページ当たりのレコード数
// pageInfo.TotalPages    : 全ページ数
// pageInfo.TotalItems    : 全レコード数
// pageInfo.Items         : 該当ページのレコード一覧

// 実行ログ
var sqlLog = db.LastSQL;
var argsLog = db.LastArgs;
var commandLog = db.LastCommand;
// SELECT * FROM Products ORDER BY Products.ID
// OFFSET @0 ROWS FETCH NEXT @1 ROWS ONLY
// 	 -> @0 [Int64] = "5"
// 	 -> @1 [Int64] = "5"

return pageInfo.Items;

うん、いい感じ。


で、上記で触れた Oracle に対して使う場合なんだけど、まずこの PetaPoco ですが、Nugetで

PM> Install-Package PetaPoco

とするとインストールは出来るんだけど、どうやら落ちてくるモジュールが古いみたい。github master branch にコミットされたバグ修正版ではないモジュールが落ちてきます。Nuget で入れたままのものだと Oracle では動かない。

具体的にはこのコミット。
Fix to uppercase escaped table and column names for Oracle (#55) · a645099 · toptensoftware/PetaPoco · GitHub
最新版を Github から手に入れておきましょう。（因みに先に挙げたサンプルコードは最新版使ってた。）

またこのコミットに紐づくコメント(Oracle expects upper-case table / column identifiers when escaped · Issue #55 · toptensoftware/PetaPoco · GitHub)を見ると、 Oracle に対してはあまりテストがされていないみたい。

I've committed a fix to github master branch, but haven't been able to test it as I don't have access to Oracle here right now.

だって。環境ないのかな。これで少し嵌った。


あと他にサンプル通り動かなかったのが２点。
一つは Insert 時の PrimaryKey 自動採番が Oracle では出来ないので、SEQUENCE を作っておく必要がある。PetaPoco - Oracle Support and more... -Topten Software

もう一つは、ページングAPIを叩くときのSQLがサンプル通りだとOracleでは動かない。まぁこれは一回実行するとExceptionのメッセージが出るのですぐにわかる。
PetaPoco.csの以下の部分。

if (_dbType == DBType.Oracle && sqlSelectRemoved.StartsWith("*"))
	throw new Exception("Query must alias '*' when performing a paged query.\neg. select t.* from table t order by t.id");


とこんな感じ。でもやっぱり本体が小さいのはいいね！何かあったとき調査しやすい。
サンプル実装はしばらくこいつをお供にしようと思う。