テーブル値パラメータについて少し整理とメモその２・Dapper編

前回『テーブル値パラメータについて少し整理とメモ - きよくらの備忘録』の続きです。

前回は基本ということで、System.Data.SqlClient、つまり巣のADO.NETでやってみました。が、実際にはORMやMicroORMを利用することが大半でしょう。ということで、私が普段使っているDapperでテーブル値パラメータ (Table-Valued Parameters / TVPs) を使う方法について試してみました。

Dapperでテーブル値パラメータを使う

とりあえずDapperでやってみよう、ということで、素直に以下のように書き換えて実行しました。……が、結論から言うと、例外が発生して実行できませんでした。

// using Dapper; が必要
using (var con = new SqlConnection(connectionString))
{
  con.Open();
  var sql = "SELECT Id, Name, Age FROM Users WHERE Id IN (SELECT Value FROM @tvp);" ;
  var param = new { tvp = tvp };
  var rows = con.Query(sql, param); // ★例外発生！
  foreach ( var row in rows)
  {
    // 値を読みだす
  }
}

例外の内容は以下の通り。

"table 型パラメーター 'tvp' には、有効な型名が必要です。"

このエラーの内容だけ見ると、前回のSqlClientを使う場合にSqlParameterのTypeName プロパティに正しく型名を設定するのと、同様の何かが必要であるように読めます。

その答えはDapperのIssueにありました。

<a href="https://github.com/StackExchange/dapper-dot-net/issues/201">Allow enumerable to be passed as a TVP · Issue #201 · StackExchange/dapper-dot-net</a>github.com

つまるところ、パラメータを設定する際に AsTableValuedParameter拡張メソッドを使用して型名を設定すれば良い模様。ということで、以下のように書いてみると、期待通りに動作しました。

using (var con = new SqlConnection(connectionString))
{
  con.Open();
  var sql = "SELECT Id, Name, Age FROM Users WHERE Id IN (SELECT Value FROM @tvp);" ;
  var param = new { tvp = tvp.AsTableValuedParameter( "dbo.IntValueTableType") }; //型名を指定する
  var rows = con.Query(sql, param);
  foreach ( var row in rows)
  {
    // 値を読みだす
  }
}

また前回に例として挙げた、ストアドプロシージャ / スカラ値関数 / テーブル値関数についても、同じ対応で実行できました。

ストアドプロシージャだけはAsTableValuedParameterが省略できる

が、実はストアドプロシージャの場合、このAsTableValuedParameterによる型名の設定無しでもいけました*1。

using (var con = new SqlConnection(connectionString))
{
  con.Open();

  var sql = "dbo.SummaryValueProc" ;
  var param = new { Values = tvp }; // AsTableValuedParameter 無しでもいける
  var rows = con.Query(sql, param, commandType: CommandType .StoredProcedure);
  foreach ( var row in rows)
  {
    // 値を読みだす
  }
}

この理由については調べれていませんが、受け取るストアド側でパラメータの型情報を持っているのためパラメータ名を使って問題なくマッピングできるから……というあたりでしょうか？

【追記】

よくよくMSDNのサンプルを見たら、ストアドの場合はSqlClientでも、TypeName プロパティを設定していませんでした。ということで、たぶん、上記の推測でだいたいあってるんじゃなかろうかと思います。