RSS

Advent LINQ (11): SQL文を取得する

11 12月

LINQ to SQLやLINQ to Entitiesは、記述したLINQクエリが直接SQL文に変換されて、SQL ServerなどのRDBシステムに発行される。今まで見て来たLINQクエリや拡張メソッドは、LINQ to Objectsと呼ばれ、同じLINQでありながら、その動作は全く異なる。

もし、連載で示してきた独自の拡張メソッドがそのままSQL Serverと連携するなら、以下のような矛盾が考えられる。

  • 独自に記述した拡張メソッドが、ifやwhereなどの「分岐ロジック」を含むとしたら、それはどうやってSQL Server上で動作するのか?
  • 逆に、独自の拡張メソッドが不可能だとしたら、単にSQL Serverからレコードをフェッチして、実際の絞り込み(Where)や並べ替え(OrderBy)は、.NET CLR上でインメモリで行われるのか? そうだとするなら、何百万行もあるレコードをCLR上に読み込むこと自体非現実的で、LINQ to SQLやLINQ to Entitiesに実用性は無いのでは?

LINQ to SQLやLINQ to Entitiesは、もちろん「SQL構文」でクエリを生成し、SQL Server上でクエリが実行され、「その結果だけ」がCLRに返される。LINQ to Objectsだけを知っている状態では、この動作はにわかに信じがたいかもしれない。また、どうしてそのような事が実現出来るのかは、とてもこの連載だけでは説明できないが、とりあえず、実際に生成されるSQL文を確認する事は出来る。

SQL文の確認方法は、LINQ to SQLとLINQ to Entitiesで異なるため、それぞれについて例を示す。

サンプルDB: AdventureWorks LT 2012

LINQ to SQLの場合、データベース接続から「LINQ to SQLクラス」を生成する。すると、dbmlファイルのデザイナーが表示される(中身は空)。

LINQToSQL1

ここに、サーバーエクスプローラーから必要なテーブルをドラッグアンドドロップで落とすと、以下のようにテーブルが表示される(実際はEntityクラス)。

LINQToSQL2

テーブル間に正しく制約条件が定義されていれば、このようにリレーションシップ(矢印)も自動的に定義される。見た目だけでなく、Entitiyクラスの階層構造が自動的にプロパティとして定義されるので、LINQ to SQLやLINQ to Entitiesを使うなら、制約条件を定義する事は重要な作業となる。
データベースファーストな開発であれば、SQL Server Management Studioで、テーブル設計と同時に制約もつければよい。リリースで邪魔なら、最後に制約だけ削除する方法もある。

これで、データベース接続を抽象化するDataContextクラスと、レコードデータを表すEnttiyクラスが生成された。

// dbmlで定義されたDataContextを生成する
using (var context = new AdventureWorksDataContext())
{
	// Customerテーブルから、LastNameが"Johnson"のレコードを抽出し、FirstNameの降順でソートする
	var customers =
		from customer in context.Customer
		where customer.LastName == "Johnson"
		orderby customer.FirstName descending
		select customer;

	// 上記クエリの、実際のSQL文を取得する
	var sqlText = customers.ToString();
	Debug.WriteLine(sqlText);
}

この結果、以下のSQL文が得られる。

SELECT
	[t0].[CustomerID], [t0].[NameStyle], [t0].[Title], [t0].[FirstName],
	[t0].[MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[CompanyName],
	[t0].[SalesPerson], [t0].[EmailAddress], [t0].[Phone], [t0].[PasswordHash],
	[t0].[PasswordSalt], [t0].[rowguid], [t0].[ModifiedDate]
FROM [SalesLT].[Customer] AS [t0]
WHERE [t0].[LastName] = @p0
ORDER BY [t0].[FirstName] DESC

LINQのfrom句やselect句はもちろんだが、where句で記述した絞り込み条件(”Johnson”はパラメータ化されている)やorderby句で記述したソート条件(もちろん、descendingも)反映されている。

LINQ to Entitiesの場合、「ADO.NET Entity Data Model」から生成する。

LINQToEntities1

LINQToEntities2

LINQToEntities3

使い方はほぼ同等。LINQクエリ自体は全く同じ。

using (var context = new AdventureWorksLT2012_DataEntities())
{
	var customers =
		from customer in context.Customer
		where customer.LastName == "Johnson"
		orderby customer.FirstName descending
		select customer;

	Debug.WriteLine(customers.ToString());
}

以下が出力されたSQL句。

SELECT 
	[Extent1].[CustomerID] AS [CustomerID], 
	[Extent1].[NameStyle] AS [NameStyle], 
	[Extent1].[Title] AS [Title], 
	[Extent1].[FirstName] AS [FirstName], 
	[Extent1].[MiddleName] AS [MiddleName], 
	[Extent1].[LastName] AS [LastName], 
	[Extent1].[Suffix] AS [Suffix], 
	[Extent1].[CompanyName] AS [CompanyName], 
	[Extent1].[SalesPerson] AS [SalesPerson], 
	[Extent1].[EmailAddress] AS [EmailAddress], 
	[Extent1].[Phone] AS [Phone], 
	[Extent1].[PasswordHash] AS [PasswordHash], 
	[Extent1].[PasswordSalt] AS [PasswordSalt], 
	[Extent1].[rowguid] AS [rowguid], 
	[Extent1].[ModifiedDate] AS [ModifiedDate]
FROM [SalesLT].[Customer] AS [Extent1]
WHERE N'Johnson' = [Extent1].[LastName]
ORDER BY [Extent1].[FirstName] DESC

もし、これらのSQL文が本当に実行されているのか疑問であるなら、SQL Server Profilerで実際に確認してみると良い。

広告
 
コメントする

投稿者: : 2013/12/11 投稿先 .NET, LINQ

 

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト /  変更 )

Google フォト

Google アカウントを使ってコメントしています。 ログアウト /  変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト /  変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト /  変更 )

%s と連携中

 
%d人のブロガーが「いいね」をつけました。