日: 2013年12月15日

Advent LINQ (15): SQL表現可能な式

前回、LINQ to SQLまたはLINQ to Entitiesは、どんなラムダ式でもSQLに変換できるわけではない事を示した。では、どのような式なら変換できるのだろうか？
例えば、stringクラスにはStartsWithメソッドがある。これを使って：

using (var context = new AdventureWorksLT2012_DataEntities())
{
    // Johnで始まる苗字のレコードを抽出
    var customer0 =
        from customer in context.Customer
        where customer.LastName.StartsWith("John")
        select customer;

    Debug.WriteLine(customer0.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 [Extent1].[LastName] LIKE N'John%'

SQL文にはStartsWithメソッドなど存在しない。だからエラーになるかと思いきや、LIKE句に置き換わって、結果として意図通りの抽出が実行されるSQL文が組み立てられた。
これは明らかに、string.StartsWithメソッドを認識し、等価なLIKE句に置き換えていることが伺える。つまり、与えられたExpressionを解析し、その式の中にStartsWithメソッドの呼び出しが含まれていれば、LIKE句を組み立ててSQL文に追加する、と言う事をしているのだろうと予想できる。それならば、EndsWithも同じようにLIKE句に置き換えられるはずだ。実際に試して確認してほしい。
なお、以下のような式を記述すると、生成されるSQL文も変化する。

using (var context = new AdventureWorksLT2012_DataEntities())
{
    // Johnで始まる苗字のレコードを抽出
    var customer1 =
        from customer in context.Customer
        where customer.LastName.StartsWith("John") == true   // <-- 明示的にtrueで判定
        select customer;

    Debug.WriteLine(customer1.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 (1 = (CASE WHEN ([Extent1].[LastName] LIKE N'John%') THEN cast(1 as bit) WHEN ( NOT ([Extent1].[LastName] LIKE N'John%')) THEN cast(0 as bit) END)) AND (CASE WHEN ([Extent1].[LastName] LIKE N'John%') THEN cast(1 as bit) WHEN ( NOT ([Extent1].[LastName] LIKE N'John%')) THEN cast(0 as bit) END IS NOT NULL)

かなりのクソ真面目ぶりだ。SQL Serverのクエリオプティマイザーがこの式をどう評価するか分からないが、LINQクエリを記述する際は、それがどのようなSQL文として実行されるのかは気にかけておいた方が良い。書き方一つでこれだけ変わってしまうのは、人によってはLINQクエリで書く事に抵抗を感じるかもしれない。しかし、長い目で見れば、新しい.NET FrameworkではExpressionの評価次第でシンプルなクエリに変換されるようになるかもしれないし、タイプセーフである事は変わらない。
他にも特別に解釈され、SQL文にマッピングされるLINQ拡張メソッド群が存在する。Sum, Count, Average, Min, Maxといった集計を行うメソッドは、対応するSQL文に変換可能だ。また、Skip, Takeメソッドを使って、抽出結果の部分レコード抽出も可能だ。これらはOFFSET句とFETCH句に変換される。