ここ何週間かLINQ to SQLをカバーしたブログ投稿をシリーズとしてを書いています。 LINQ to SQL とは、 .NET Framework 3.5リリースで出荷されるビルドインのO/RM ( オブジェクトリレーショナルマッピング)のことです。これにより、.NETクラスを使用してリレーショナルデータベースをモデル化することができます。LINQ文を使用してデータベースの検索、またデータの更新・挿入・削除も行うことができます。
以下はLINQ to SQL シリーズの最初の5つパートです。:
これらの以前のLINQ to SQLブログ投稿では、LINQクエリ文を使用してプログラムでデータベースからデータを取得する方法についてご紹介しました。
本日のブログ投稿では、どのようにストアドプロシージャ（SPROC）とユーザ定義関数（UDF）をLINQ to SQLデータモデルと一緒に使用することができるかについてカバーします。本日のブログ投稿は特にデータベースからデータを検索・取得するストアドプロシージャの呼び出し方法についてカバーします。次のこのシリーズのブログ投稿では、データベースからのデータを更新、挿入、削除するストアドプロシージャをオプションとしてどのように使用することができるかをご覧頂きます。
ストアドプロシージャへ、またはストアドプロシージャへではない？それが問題です。
データ層を構築する時にORMにより生成された動的SQLを使用するかストアドプロシージャを使用するかという質問は、開発者、アーキテクト、DB管理者の間でエンドレスな（非常に情熱的な）論議をかもし出すトピックです。多くの非常に頭のいい人達がこのトピックについて書いているので、どちらをとるかについての議論をここで蒸し返すつもりはありません。
.NET 3.5で出荷されるLINQ to SQL ORMは非常に柔軟性があり、配下にあるデータベーススキーマに依存しているオブジェクトモデルを持つデータモデルクラスを作成するために使用することができます。それはビジネスロジックと検証ルールをカプセル化することができ、データモデルが動的SQL、またはストアドプロシージャのどちらを通してデータモデルが格納・保存されたかに関わらず動作します。
LINQ to SQL パート3: データベースの検索の投稿で、どのように以下のようなコードを使用してLINQクエリ文をLINQ to SQLデータモデルに対して書くのかについて述べました。
このようなLINQクエリ文を書く時、LINQ to SQL ORMは必要な動的SQLを実行してクエリに一致したProductオブジェクトを取得します。
この投稿でお分かりになると思いますが、データベースのストアドプロシージャをLINQ to SQLデータコンテキストクラスにマップすることも可能です。それにより、ストアドプロシージャを呼びだすことで同じProductオブジェクトを取得することができます。
クリーンなデータモデル層で動的SQLおよびストアドプロシージャの両方が使用できるこの機能は、非常に強力で、現在取りかかっているプロジェクトで幅広い柔軟性が持てます。
LINQ to SQLを使用してストアドプロシージャをマップし呼びだす手順
パート2: データモデルクラスの定義のチュートリアルで以下のようなLINQ to SQLクラスモデルを作成する時のLINQ to SQL ORMデザイナの使用方法を述べました。
上記でLINQ to SQL ORMデザイナ上に2つのペインがあることがお分かり頂けると思います。左のペインではデータベースにマップするデータモデルクラスを定義します。右のメソッドペインでは、オプションとしてストアドプロシージャ（とユーザ定義関数）をLINQ to SQLデータコンテキストオブジェクトにマップすることもでき、動的SQLをインプレイスで使用してデータモデルオブジェクトを格納することができます。
LINQ to SQL データコンテキストへストアドプロシージャをマップする方法
ストアドプロシージャをデータコンテキストクラスにマップするには、まずVS2008サーバエクスプローラウィンドウに行って、データベースにあるストアドプロシージャを見ます。
上記のストアドプロシージャのいずれかをダブルクリックすれば、開いて編集することができます。例えば、以下のNorthwindにある"CustOrderHist"ストアドプロシージャです。
LINQ to SQLデータコンテキストに上記のストアドプロシージャをマップするには、サーバエクスプローラからLINQ to SQL ORMデザイナ上にドラッグアンドドロップして行うことができます。これは自動的に新しいメソッドを以下のようなLINQ to SQLデータコンテキストクラス上に作成します。
データコンテキストクラス上に作られたメソッドの名前はデフォルトでストアドプロシージャの名前と同じになり、メソッドの返り値の型は"[ストアドプロシージャ名]の結果"の名前付けパターンに従って自動的に作成された型になります。例えば、上記のストアドプロシージャは一連の"CustOrderHistResult"オブジェクトを返します。オプションとしてデザイナで選択してプロパティグリッドで名前を変更することでメソッド名を変更することもできます。
新しくマップしたストアドプロシージャの呼びだし方法
一度データコンテキストクラス上にストアドプロシージャをマップする上記の手順を行えば、簡単にそれを使用してプログラムでデータを取得することができます。必要なことは、ストアドプロシージャからの一連の強く型付けされた結果を取り戻すためにデータコンテキストクラス上にマップしたその新しいメソッドを呼びだすことです。
VBでのストアドプロシージャを呼びだし:
C#でのストアドプロシージャを呼びだし:
上記のコードサンプルのような結果をループさせるプログラムに加え、それらを表示するためにどんなUIコントロールにも結果をバインドすることができます。例えば、以下のコードは<asp:gridview>コントロールにストアドプロシージャの結果をデータバインドします。:
そうするとページ上で顧客の製品履歴が表示されます。:
データモデルクラスにストアドプロシージャメソッドの返り値の型をマップ
上記の"CustOrderHist"ストアドプロシージャの例では、ストアドプロシージャは2つのカラムのデータ（製品の製品名と発注総数）が含まれた一連の製品履歴結果を返しています。LINQ to SQLデザイナは自動的に新しい "CustOrderHistResult"クラスがこの結果を表示するように定義しています。
代替え的に、LINQ to SQLデザイナで定義した既存のデータモデルクラス（例えば、既存のProductもしくはOrderエンティティクラス）にストアドプロシージャからの結果をマップするようにすることもできます。
例えば、データベースに製品情報を返す"GetProductsByCategory"ストアドプロシージャがあると仮定します。
以前のように、"GetProductsByCategory"メソッドをデータコンテキスト上に作成することができます。それは、LINQ to SQLデザイナ上にドラッグすることでこのストアドプロシージャを呼びだします。単にデザイナ上にストアドプロシージャをドロップするするのではなく、データモデルデザイナの既存の"Product"クラスの上にドロップします。
Productクラスの上にストアドプロシージャをドロップするという行為により、LINQ to SQLデザイナに"GetProductsByCategory"メソッドに返り値の結果として"Product"オブジェクトを返させるように指示します。
上記のようにストアドプロシージャに"Product"オブジェクトを返させることのいい点としては、ProductオブジェクトがLINQクエリを通じて返されたかのように、LINQ to SQLが返されたProductオブジェクトへの変更を自動的にトラックします。"SubmitChanges()"メソッドをデータコンテキスト上で呼び出す時、それらのオブジェクトへの変更は自動的にデータベースに保存されます。
例えば、以下のコードを書いて、（ストアドプロシージャを使用して） 特定のCategory内にある全ての製品の価格を取得し、現在の値の90%になるように変更することができます。
最後にSubmitChanges()を呼びだした時、全ての製品の価格を更新処理します。変更のトラッキングおよびSubmitChanges()メソッド、またデータモデルエンティティに検証ビジネスロジックを追加する方法についての詳細は、LINQ to SQL パート4: データベースの更新のチュートリアルをお読みください。
このLINQ to SQLシリーズの次の投稿では、データベースを更新するカスタムのストアドプロシージャとORMによって生成された動的な挿入・更新・削除のSQLを入れ替える方法についてもカバーします。いいところは、更新のためにストアドプロシージャを使用してデータコンテキストを構成したとしても、上記のコードは全く変更されないことです。純粋にマッピング層の変更で、データモデルに対して書かれたコードは気がつかない程度です。
ストアドプロシージャの出力引数を処理
LINQ to SQLはストアドプロシージャで"out"引数を参照引数（ref キーワード）としてマップし、値の型に対してNullableとして引数を宣言します。
例えば、以下の"GetCustomerDetails"ストアドプロシージャを考えた場合、CustomerIDを入力引数としてとり、クエリの結果として返る注文履歴に加え、出力引数として会社名が返されます。
もしLINQ to SQLデザイナで"Order"クラス上に上記のストアドプロシージャをドラッグした場合、以下のコードを書いてそれを呼びだすことができます。
VB:
C#:
上記のコードで、ストアドプロシージャのヘルパーメソッドが一連のOrderオブジェクトを返すだけでなく、ヘルパーメソッドへ出力引数としてCompanyNameも返していることが分かります。
ストアドプロシージャからの複数の結果形式を処理
ストアドプロシージャが複数の結果形式を返すことができる場合、データコンテキスト上のストアドプロシージャメソッドの返り値の型は1つのクラスの形式に対して強く型付けされたものにはなりません。例えば、以下のストアドプロシージャを考えると、入力引数に応じて製品の結果または注文結果が返されます。
LINQ to SQLは、ストアドプロシージャを発生させてIMultipleResultオブジェクトを返すメソッド（この場合は"VariablesShapeSample"を呼びだします。）を定義しているプロジェクトに対して、partialの"NorthwindDataContext"クラスを追加することで、ProductもしくはOrderのどちらの形式でも返すことができるストアドプロシージャのヘルパーメソッドを作成する機能をサポートしています。
VB:
C#:
このメソッドが一度プロジェクトに追加されると、それを呼びだして使用する時に一連のProductまたはOrderに結果を変換することができます。
VB:
C#:
ユーザ定義関数 (UDF)のサポート
ストアドプロシージャに加え、LINQ to SQLはスカラ値またはテーブル値のユーザ定義関数（UDF）、およびそれらに対してインラインのものもサポートしています。データコンテキストにメソッドとして一度追加すれば、これらのUDF関数をLINQクエリ内で使用することができます。
例えば、"MyUpperFunction"と呼ばれる単なるスカラのユーザ定義関数を考えます。
データコンテキスト上のメソッドとしてそれを追加するために、Visual StudioのサーバエクスプローラからLINQ to SQLデザイナ上にドラッグアンドドロップすることができます。
LINQ to SQLデータモデル（以下の"where"内で使用されていることが分かります。）に対してクエリを書いている時、LINQ文内でこのUDF関数インラインを使うことができます。
VB:
C#:
もしここのブログでお話した、LINQ to SQL デバッグビジュアライザを使用している場合、 上記のクエリ文をランタイムにデータベース内でUDFを実行する生のSQLに変換する方法を見ることができます。
まとめ
LINQ to SQLはストアドプロシージャおよびUDFをデータベース内で呼び出す機能をサポートしており、それらをデータモデルに上手く統合します。このブログ投稿ではストアドプロシージャをどのように使用して簡単にデータを取得しデータモデルクラスに格納するかをご紹介しました。このシリーズの次のブログ投稿では、データベースに保存するためにデータコンテキスト上のSubmitChanges()を使用して更新・挿入・削除ロジックをオーバライドするストアドプロシージャを使用する方法についてカバーしたいと思います。
Hope this helps,
Scott