この記事は「C# Advent Calendar 2012」の参加記事です。
LINQ to Entities におけるクエリの記述方法は LINQ to Entities でのクエリ に記載があるのですが「SQL ではこうやりたいのに!」というときの逆引きリファレンスがなかったので、まとめてみました。
選択
基本は比較演算子がそのまま使えますが、後述するように、一部例外があります。
説明 | SQL | LINQ to Entities |
等しい | x = 'y' | Where(item => item.x == y) または Where(item => item.x.CompareTo(y) == 0) |
等しくない | x <> 'y' | Where(item => item.x != y) または Where(item => item.x.CompareTo(y) != 0) |
以上 | x >= 'y' | Where(item => item.x >= y) または x.Where(item => item.x.CompareTo(y) >= 0) |
以下 | x <= 'y' | Where(item => item.x <= y) または x.Where(item => item.x.CompareTo(y) <= 0) |
で始まる | x LIKE 'y%' | Where(item => item.x.StartsWith(y)) |
で終わる | x LIKE '%y' | Where(item => item.x.EndsWith(y)) |
に一致する | x LIKE '%y%' | Where(item => item.x.Contains(y)) |
を含む | x IN ('y','z') | Where(item => new [] { y, z }.Contains(x)) |
文字列の場合、>= や <= の演算子に対するオーバーロードがないので、CompareTo メソッドで比較する必要があります。もちろん、数値や日付でも CompareTo メソッドで比較することはできますが、あまり直観的ではないのでおすすめはしないです。
SQL Server Compact の場合は、StartsWith は LIKE ではなく CHARINDEX で変換されるようです。また、EndsWith を使うと例外が発生してしまいます。
結合
ナビゲーション プロパティがものすごく便利ですが、設計上の理由で外部キーが使えない場合などは、クエリで結合する必要があります。
説明 | SQL | LINQ to Entities |
内部結合 | INNER JOIN | Join(y, left => left.z, right => right.z, (left, right) => ...) |
外部結合 | OUTER JOIN | GroupJoin(y, left => left.z, right => right.z, (left, right) => ...) |
Join メソッドを使うほかに、ナビゲーション プロパティを参照することで自動的に内部結合が行われます。つまり、以下のコードはどちらも同じになります。
// Join メソッドを使う場合 context.Employees.Join( context.Department, employee => employee.DepartmentId, department => department.Id, (employee, department) => { EmployeeName = employee.Name, DepartmentName = department.Name }); // ナビゲーションプロパティを参照する場合 context.Employees .Select(employee => new { EmployeeName = employee.Name, DepartmentName = employee.Department.Name });
上記のコードでは、Department が null の場合に Name プロパティを参照していて NullReferenceException が発生してしまいそうに見えますが、式ツリーとして解釈されるだけで実際に実行されるわけではないので問題ありません。逆に、単体テストなどでモックして IEnumerable として評価する場合は、例外が発生しますので、注意が必要です。
GroupJoin メソッドでは、結合されるエンティティはコレクションになるので、プロパティを参照するにはコレクションを FirstOrDefault してからさらに Select してあげるとよいようです。
context.Employees .GroupJoin( context.Departments, employee => employee.DepartmentId, department => department.Id, (employee, department) => new { Employee = employee, Department = department.FirstOrDefault(), }) .Select(x => new { EmployeeName = x.Employee.Name, DepartmentName = x.Department.Name, DepartmentAddress = x.Department.Address, });
は次の SQL を生成します。
SELECT 1 AS [C1], [Extent1].[Name] AS [Name], [Limit1].[Name] AS [Name1], [Limit1].[Address] AS [Address] FROM [dbo].[Employees] AS [Extent1] OUTER APPLY (SELECT TOP (1) [Extent2].[Name] AS [Name], [Extent2].[Address] AS [Address] FROM [dbo].[Departments] AS [Extent2] WHERE [Extent1].[DepartmentId] = [Extent2].[Id] ) AS [Limit1]
なんか OUTER JOIN じゃないんですけど…。
集合
だんだん複雑になってきました…。
説明 | SQL | LINQ to Entities |
和集合 | x UNION y | x.Union(y) |
差集合 | x EXCEPT y | x.Except(y) |
積集合 | x INTERSECT y | x.Intersect(y) |
連結 | x UNION ALL y | x.Concat(y) |
Union メソッドは重複するデータは排除してしまうため (Distinct と同じ)、重複を許容する場合は Concat メソッドを使用する必要があります。これを知らずにずいぶん悩んでしまいました…。
Except メソッドは x にあって y にない要素を、Intersect メソッドは x にも y にもある要素をそれぞれ抽出します。
Except にしろ Intersect メソッドにしろ (さっきの Join メソッドもそうですが)、比較するキーを指定する場合は IEqualityComparer インターフェイスを実装したクラスを作成する必要があるのですが、それはあまりに面倒なので、@neuecc さんの記事で紹介されているような方法を使うのがいいと思います。
並べ替え
並べ替えとは直接関係ないメソッドも紹介しますが、理由は後述します。
説明 | SQL | LINQ to Entities |
昇順 | ORDER BY x | OrderBy(item => item.x) |
昇順 | ORDER BY x DESC | OrderByDescending(item => item.x) |
最初の n 件 | TOP n | Take(n) |
n 件読み飛ばし | ROW_NUMBER() OVER(...) WHERE ROW_NUMBER >= n | Skip(n) |
Skip メソッドは必ず OrderBy メソッドに続けて指定する必要があります。OrderBy メソッドがない場合、以下のような実行時例外が発生します。
メソッド 'Skip' は、LINQ to Entities では並べ替え済みの入力に対してのみサポートされます。メソッド 'Skip' の前にメソッド 'OrderBy' を呼び出す必要があります。
これは Skip メソッドが SQL では OVER 句 (SQL Server Compact では OFFSET FETCH 句) に置き換えられるためのようです。できればコンパイル時に解決してほしいとは思いますが、LINQ to Object との整合性も考えると致し方ないようです。
まとめ
とりあえずこれだけあればだいたいのクエリは書けるはず!まあ LINQ to Entities はラムダ式に ToString 書けないとか (CONVERT に置き換えてくれてもいいのに) 他にもいろいろはまりどころがあったりします。万能というわけではないのでうまく SQL と棲み分けしてあげるのがいいと思います!