LINQ練習 #3 「LINQ to SQL 条件式」

2009-02-27 20:01:00

どうも、悠希です。
Visual Studio 2008から追加された機能、LINQを使っていく為に色々とメモがてら書いていきます。
今回は「LINQ to SQL」における条件式の仕様について扱おうかと思います。

当初の予定ではメソッドの使用について書こうかと考えていましたが、範囲が広いので分割してお届けする予定です。

IQueryable/IEnumerableの違い

まず、LINQ to SQLの場合「IQueryable」と「IEnumerable」の違いを意識しておく方が好ましいです。
IQueryableの場合はSQLを構築する為の式情報を保持していると考え、IEnumerableの場合はデータ自体を保持していると考えるといいかと思います。

IEnumerableの場合には各種メソッドの使用は全く問題なく使用していけるのですが、IQueryableの場合は.NETのメソッドをSQL関数に変換する必要性があるため、制限がかかってくることとなります。

IEnumerableがなぜ問題ないかというと、IEnumerableはコレクションを示すからです。
System.Collections.ArrayListやSystem.Collections.Generic.Listなどのインターフェースを調べることができる人は確認するとわかりますが、これらコレクションにはIEnumerableが実装されています。
インターフェースの定義自体はMSDNのリファレンスを確認していただけると分かりますが、System.Collections名前空間に存在しており、このインターフェースがコレクションを示すことが分かります。

IQueryableの場合、内部にSystem.Linq.Expressions.Expressionというクラスを保持しており、それに対して式ツリーと呼ばれる条件の表現を追加していく事によってSQL発行時点までは式情報を持っており、データを持っていない形式になっています。
その為、実際にデータを使用する段階まではSQLを発行せずにどんどん条件を追加していく事が可能になっています。

準備

今回の説明にあたって、前回のBooksテーブルのレイアウトに1列追加しています。

列名 データ型 サイズ NULL許容 一意 主キー
ISBN nvarchar 13 いいえ はい はい
Name nvarchar 100 いいえ いいえ いいえ
Price bigint 8 はい いいえ いいえ
ISBN Name Price
4891006048 LINQテクノロジ入門~Microsoft Visual Studio 2008による新たなクエリ構築技法~ 3360
4877832009 すぐに使える実例で学ぶLINQ実践サンプル集―ASP.NET3.5対応 NULL

LIKE句

LIKE句が使えるのはサイズが2以上のCHAR、VARCHARなどの文字列型のフィールドのみとなります。

C#
using (SamplesContext context = new SamplesContext("Samples.sdf"))

{

    context.Log = Console.Out;

    //ISBN Like '48%'

    var likeQuery1 = from t in context.Books

                     where t.ISBN.StartsWith("48")

                     select t;

    foreach(var row in likeQuery1) {

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price);

    }

    //ISBN Like '%48'

    var likeQuery2 = from t in context.Books

                     where t.ISBN.EndsWith("48")

                     select t;

    foreach(var row in likeQuery2) {

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price);

    }

    //ISBN Like '%00%'

    var likeQuery3 = from t in context.Books

                     where t.ISBN.Contains("00")

                     select t;

    foreach (var row in likeQuery3)

    {

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price);

    }

}
Visual Basic
Using context As SamplesContext = New SamplesContext("Samples.sdf")

    context.Log = Console.Out

    'ISBN Like '48%'

    Dim likeQuery1 = From t In context.Books _

                     Where t.ISBN.StartsWith("48")

    For Each row In likeQuery1

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price)

    Next

    'ISBN Like '%48'

    Dim likeQuery2 = From t In context.Books _

                     Where t.ISBN.EndsWith("48")

    For Each row In likeQuery2

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price)

    Next

    'ISBN Like '%00%'

    Dim likeQuery3 = From t In context.Books _

                     Where t.ISBN.Contains("00")

    For Each row In likeQuery3

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price)

    Next

End Using

上記のとおり、文字列型のフィールド(.NET上ではString型として扱われる)に対して「String.StartsWith」「String.EndsWith」「String.Contains」を使う事によってLIKE句を使用する事が可能です。
String.StartsWithは前方一致、String.EndsWithは後方一致、String.Containsは部分一致のLIKE句を生成します。

また、LIKE句で「_」(任意の1文字)、「[~]」(文字セットの1文字)、「[^~]」(文字セット以外の1文字)を使いたい場合はLike演算子を使って書きますが、これは残念ながらVisual Basicのみ利用可能です。

Visual Basic
Using context As SamplesContext = New SamplesContext("Samples.sdf")

    context.Log = Console.Out

    'ISBN Like '48%'

    Dim likeQuery1 = From t In context.Books _

                     Where t.ISBN Like "48*"

    For Each row In likeQuery1

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price)

    Next

    'ISBN Like '48_'

    Dim likeQuery2 = From t In context.Books _

                     Where t.ISBN Like "48?"

    For Each row In likeQuery2

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price)

    Next

    'ISBN Like '48[0-9]'

    Dim likeQuery3 = From t In context.Books _

                     Where t.ISBN Like "48#"

    For Each row In likeQuery3

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price)

    Next

    'ISBN Like '48[79]'

    Dim likeQuery4 = From t In context.Books _

                     Where t.ISBN Like "48[79]"

    For Each row In likeQuery4

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price)

    Next

    'ISBN Like '48[8-9]'

    Dim likeQuery5 = From t In context.Books _

                     Where t.ISBN Like "48[8-9]"

    For Each row In likeQuery5

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price)

    Next

    'ISBN Like '48[^1-7]'

    Dim likeQuery6 = From t In context.Books _

                     Where t.ISBN Like "48[!1-7]"

    For Each row In likeQuery6

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price)

    Next

    'ISBN Like '48%048'

    Dim likeQuery7 = From t In context.Books _

                     Where t.ISBN Like "48*048"

    For Each row In likeQuery7

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price)

    Next

End Using

IS NULL/IS NOT NULL

個人的にはテーブルレイアウト上でのNOT NULL指定を推奨していますが、そうもいかなくてNULLが入る事も多々あるので必要ですね。ですがとても簡単です。

C#
using (SamplesContext context = new SamplesContext("Samples.sdf"))

{

    context.Log = Console.Out;

    //IS NULL

    var isnullQuery = from t in context.Books

                      where t.Price == null

                      select t;

    foreach (var row in isnullQuery)

    {

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price);

    }

    //IS NOT NULL

    var isnotnullQuery = from t in context.Books

                         where t.Price != null

                         select t;

    foreach (var row in isnotnullQuery)

    {

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price);

    }

}
Visual Basic
Using context As SamplesContext = New SamplesContext("Samples.sdf")

    context.Log = Console.Out

    'IS NULL

    Dim isnullQuery = From t In context.Books _

                      Where t.Price Is Nothing

    For Each row In isnullQuery

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price)

    Next

    'IS NOT NULL

    Dim isnotnullQuery = From t In context.Books _

                         Where t.Price IsNot Nothing

    For Each row In isnotnullQuery

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price)

    Next

End Using

ご覧の通り、普通のNULLチェックと同じです。この構文がSQLに変換される際にIS NULL/IS NOT NULLに変換してくれます。

IN句

IN句は感覚でLINQを扱っていると存在しないかと思ってしまうのですが、対応がなされています。
ただし、ちょっとした工夫が必要となります。

C#
using (SamplesContext context = new SamplesContext("Samples.sdf"))

{

    context.Log = Console.Out;

    //ISBN IN ('4891006048', '4877832009')

    var inQuery = from t in context.Books

                  where new[] {"4891006048", "4877832009"}.Contains(t.ISBN)

                  select t;

    foreach (var row in inQuery)

    {

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price);

    }

    //ISBN NOT IN ('4891006048', '4877832009')

    var notinQuery = from t in context.Books

                     where !new[] { "4891006048", "4877832009" }.Contains(t.ISBN)

                     select t;

    foreach (var row in notinQuery)

    {

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price);

    }

}
Visual Basic
Using context As SamplesContext = New SamplesContext("Samples.sdf")

    context.Log = Console.Out

    Dim inList As String() = {"4891006048", "4877832009"}

    'ISBN IN ('4891006048', '4877832009')

    Dim inQuery = From t In context.Books _

                  Where inList.Contains(t.ISBN)

    For Each row In inQuery

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price)

    Next

    'ISBN NOT IN ('4891006048', '4877832009')

    Dim notinQuery = From t In context.Books _

                  Where Not inList.Contains(t.ISBN)

    For Each row In notinQuery

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price)

    Next

End Using

上記の通り配列に対象のデータを設定し、その値に対してContainsメソッドを利用しています。このようにする事によってIN句を使う事ができます。
また、配列ではなくList型でも利用可能です。

EXISTS句

少し制限もあるのですが、EXISTS句も利用可能です。

C#
using (SamplesContext context = new SamplesContext("Samples.sdf"))

{

    context.Log = Console.Out;

    var existsQuery = from t in context.Books

                      where (

                          from inq in context.Books

                          where inq.Price != null

                          select inq.ISBN

                      ).Contains(t.ISBN)

                      select t;

    foreach (var row in existsQuery)

    {

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price);

    }

    var notexistsQuery = from t in context.Books

                         where !(

                             from inq in context.Books

                             where inq.Price != null

                             select inq.ISBN

                         ).Contains(t.ISBN)

                         select t;

    foreach (var row in notexistsQuery)

    {

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price);

    }

}
Visual Basic
Using context As SamplesContext = New SamplesContext("Samples.sdf")

    context.Log = Console.Out

    Dim existsQuery = From t In context.Books _

                      Where ( _

                          From inq In context.Books _

                          Where inq.Price IsNot Nothing _

                          Select inq.ISBN _

                      ).Contains(t.ISBN)

    For Each row In existsQuery

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price)

    Next

    Dim notexistsQuery = From t In context.Books _

                      Where Not ( _

                          From inq In context.Books _

                          Where inq.Price IsNot Nothing _

                          Select inq.ISBN _

                      ).Contains(t.ISBN)

    For Each row In notexistsQuery

        Console.WriteLine("ISBN:{0}/Name:{1}/Price:{2}", row.ISBN, row.Name, row.Price)

    Next

End Using

Exists句はサブクエリにあたる式ツリーに対してContainsメソッドを使用する事によって利用可能なのですが、Containsメソッドを利用する為に複数値の値でデータが抽出できているかどうかを判断する事ができません。
今回の例の場合はキーが1列なのでサブクエリで抽出できるキー一覧に指定のキーが存在するかどうかで問題なく動作しますが、複数キーの場合には少し注意が必要です。

複数キーのテーブルに対してExists句を利用する場合には確実に存在する事が解っている項目をサブクエリの抽出列に設定する必要があります。
リテラルで固定値を返して、比較する方法も見てみたのですが、どうも上手く変換されないようなので注意してください。

最後に

さて、今回は値の比較の際に使われる構文をいろいろ書きました。単純な比較であればあまり考えずに使えるのですが、少し足を踏み入れると分かりにくくなったりします。
ですが、使い方さえ知ってしまえば大した苦労もなく使っていけるかと思います。

BETWEENが抜けているぞ!と思われるかもしれませんが、残念ながらLINQ to SQLでは使う事が出来ないようです。
ですのでBETWEENを使う要件がある場合には「列 >= 値 AND 列 <= 値」の形式で指定する必要があります。
ここは少し残念・・・

次回は予定から外れて、結合やグループ化について書いていこうかと考えています。

※このエントリは ブロガーにより投稿されたものです。朝日インタラクティブ および ZDNet Japan編集部の見解・意向を示すものではありません。
  • 新着記事
  • 特集
  • ブログ