DapperのQueryMutilpeを使って結合テーブルの分割マッピングをしてみる

#はじめに
SqlMapper.GridReaderメソッドを使うと、結合したテーブルを比較的容易に扱えたので、簡単な使用例とともに、説明していきたいと思います。

2015/01/22追記
Queryを使ったもっと楽な方法があったので、追補がてら新しく書いています。

#仕込み
##利用環境
利用環境は以下の通りです。

1. C# 5.0
2. Dapper 1.38.0.0
3. System.Data.SQLite 1.0.94.0
4. Option　3.0.0

##サンプルデータベースのスキーマ
今回使うデータベースのスキーマは、以下の通りです。

CREATE TABLE Categories (
    Id   INTEGER PRIMARY KEY,
    Name TEXT    NOT NULL
                 UNIQUE
);

CREATE TABLE Items (
    Id         INTEGER PRIMARY KEY,
    CategoryId INTEGER NOT NULL
                       REFERENCES Categories (Id) ON UPDATE CASCADE
                                                  ON DELETE CASCADE,
    Name       TEXT    NOT NULL
                       UNIQUE
);

Categoriesテーブルの中には以下のレコードが入力済みです。

Id	Name
1	ウォッチ
2	クロック
3	修理
4	その他

Itemsテーブルの中には、以下のレコードが入力済みです。

Id	CategoryId	Name
1	1	クォーツアナログ
2	1	クォーツデジタル
3	1	自動巻
4	1	手巻き
5	2	置き時計
6	2	掛け時計
7	2	目覚まし時計
8	3	ベルト交換
9	3	電池交換

##C#側の仕込み
は以下の通り。

public class Category
{
	public int? Id { get; set; }
	public string Name { get; set; }
}

public class Item
{
	public int? Id { get; set; }
	public int CategoryId { get; set; }

	public string Name { get; set; }
}

#実際に使ってみる

SELECT *
  FROM Categories
       INNER JOIN
       Items ON Categories.Id = Items.CategoryId;

こんなクエリのマッピングを楽して受けようと言う話です。
上記のようなクエリの場合、Itemsテーブルにも、Categoriesテーブルにも”Id”フィールドと、“Name”フィールドが存在して、マッピング用の型を作るにせよ、動的型にマップするにせよ一筋縄ではいかなくなります¹。
このような場合に、QueryMultipleを利用することで、簡単に複数のテーブル内容が詰まっているクエリ結果から、既存の型に分割してマッピングすることができます。
実際には以下のように行います。

		private static void Main(string[] args)
		{
			SQLiteConnectionStringBuilder bld = new SQLiteConnectionStringBuilder
			{
				DataSource = "Sample.db3",
				DefaultIsolationLevel = IsolationLevel.ReadCommitted
			};

			using (SQLiteConnection conn = new SQLiteConnection(bld.ToString()))
			{
				SqlMapper.GridReader multiQueryResult =
					conn.QueryMultiple("SELECT * FROM Categories INNER JOIN Items ON Categories.Id=Items.CategoryId");

				var result = multiQueryResult.Read((Category c, Item i) => new {Category = c.Name, Item = i.Name});

				foreach (var elem in result)
				{
					Console.WriteLine("Category:" + (elem.Category ?? "NULL") + "\tItem:" + (elem.Item ?? "NULL"));
				}
			}
		}

実行結果は以下の通りとなります。

Category:ウォッチ Item:クォーツアナログ
Category:ウォッチ Item:クォーツデジタル
Category:ウォッチ Item:自動巻
Category:ウォッチ Item:手巻き
Category:クロック Item:置き時計
Category:クロック Item:掛け時計
Category:クロック Item:目覚まし時計
Category:修理 Item:ベルト交換
Category:修理 Item:電池交換

このように、CategoryとItemに分割されてマッピングされていることがわかります。

##詳細
流れとしては、

1. Queryではなく、QueryMultipleメソッドを使用して、クエリを発行する
2. 上記の戻り値はIENumerableではなくSqlMapper.GridReaderなので、こいつにReadを実行して実際読み込む。

と言う流れになります。
この際、注意すべきは、上記Readメソッドに、”string splitOn”と言う引数があり、ここはデフォルトで、”Id"と言う値となっています。
この引数によって、連結テーブルを分割してマッピングしますから、各テーブルで識別子が異なるような場合は、クエリ式で統一された名前のエイリアスをフィールドに付与するなど、適宜調整が必要になるでしょう。

また、下記のように外部結合した場合は、当然、nullに対する対応が必要なので、その点も注意しなければならないと思います。

SELECT * FROM Categories LEFT JOIN Items ON Categories.Id=Items.CategoryId

対応例

		private static void Main(string[] args)
		{
			SQLiteConnectionStringBuilder bld = new SQLiteConnectionStringBuilder
			{
				DataSource = "Sample.db3",
				DefaultIsolationLevel = IsolationLevel.ReadCommitted
			};


			using (SQLiteConnection conn = new SQLiteConnection(bld.ToString()))
			{
				SqlMapper.GridReader multiQueryResult =
					conn.QueryMultiple("SELECT * FROM Categories LEFT JOIN Items ON Categories.Id=Items.CategoryId");

				var result = multiQueryResult.Read((Category cat, Item item) =>
				{
					Option<Category> c = cat.ToOption();
					Option<Item> i = item.ToOption();
		
					return new
					{
						Category = c.Match(() => "N/A", x => x.Name),
						Item = i.Match(() => "N/A", x => x.Name)
					};
				});

				foreach (var elem in result)
				{
					Console.WriteLine("Category:" + (elem.Category ?? "NULL") + "\tItem:" + (elem.Item ?? "NULL"));
				}
			}
		}

上記の結果は以下の通りです

Category:ウォッチ Item:クォーツアナログ
Category:ウォッチ Item:クォーツデジタル
Category:ウォッチ Item:手巻き
Category:ウォッチ Item:自動巻
Category:クロック Item:掛け時計
Category:クロック Item:目覚まし時計
Category:クロック Item:置き時計
Category:修理 Item:ベルト交換
Category:修理 Item:電池交換
Category:その他 Item:N/A

#応用例と言う名の蛇足
上記のサンプルでは、すでに型がある状態で、分割マッピングしていました。
しかしこの方法では、

SELECT Categories.Id,
       Categories.Name,
       Items.Id,
       Items.Name
  FROM Categories
       INNER JOIN
       Items ON Categories.Id = Items.CategoryId;

このようなクエリの結果をマッピングするには少々具合が悪いです²。
しかし、動的型を適宜使うことにより、下記のように記述可能です。

		private static void Main(string[] args)
		{
			SQLiteConnectionStringBuilder bld = new SQLiteConnectionStringBuilder
			{
				DataSource = "Sample.db3",
				DefaultIsolationLevel = IsolationLevel.ReadCommitted
			};


			using (SQLiteConnection conn = new SQLiteConnection(bld.ToString()))
			{
				SqlMapper.GridReader multiResult = conn.QueryMultiple(@"SELECT Categories.Id,
												   Categories.Name,
												   Items.Id,
												   Items.Name
											  FROM Categories
												   INNER JOIN
												   Items ON Categories.Id = Items.CategoryId;");

				var result = multiResult.Read((dynamic cat, dynamic item) => new
				{
					CategoryId = cat.Id,
					CategoryName = cat.Name,
					ItemId = item.Id,
					ItemName = item.Name
				});


				foreach (var elem in result)
				{
					Console.WriteLine("CatId:" + elem.CategoryId + "\tCatName:" + elem.CategoryName);
					Console.WriteLine("\tItemId:" + elem.ItemId + "\tItemName:" + elem.ItemName);
					Console.WriteLine();
				}
			}

#まとめ
QueryMultipleを使うことで、結合テーブルを楽に呼び出せると思います。
但し、最後の応用例に関しては、SQL側でエイリアスを付けてQueryメソッドを使った動的型へのマッピングすることでも同様のことができるので、この使い分けは状況によって変わるのではないかと思います。

1. 簡単なのはクエリ式内でフィールドにエイリアスを付けることでしょうか。 ↩

2. 別の解法として、フィールド名にエイリアスを付けてQueryメソッドを使って動的に読み出すコトもできます。 ↩