はじめに
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”フィールドが存在して、マッピング用の型を作るにせよ、動的型にマップするにせよ一筋縄ではいかなくなります1。
このような場合に、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;
このようなクエリの結果をマッピングするには少々具合が悪いです2。
しかし、動的型を適宜使うことにより、下記のように記述可能です。
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メソッドを使った動的型へのマッピングすることでも同様のことができるので、この使い分けは状況によって変わるのではないかと思います。