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) => ...).SelectMany(...) |
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.Name プロパティを参照しており、親プロパティが null の場合に NullReferenceException が発生しそうに見えます。実際には式ツリーとして解釈されるだけで実行されるわけではないため、問題ありません。逆に、単体テストなどでモックして IEnumerable インターフェースとして評価する場合は例外が発生するため、注意が必要です。
GroupJoin メソッドでは、結合されるエンティティはコレクションになるため、プロパティを参照するにはさらに SelectMany メソッドを使用します。
context.Employees
.GroupJoin(
context.Departments,
employee => employee.DepartmentId,
department => department.Id,
(employee, departments) => new
{
Employee = employee,
Departments = departments.DefaultIfEmpty(),
})
.SelectMany((x => x.Departments, (x, y) => new
{
EmployeeName = x.Employee.Name,
DepartmentName = y.Name
}));
集合
ここからは少し複雑になります。
| 説明 | 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 インターフェイスを実装したクラスを作成する必要があります。これは手間がかかるため、以下の記事で紹介されている方法を利用することをおすすめします。
並べ替え
並べ替えとは直接関係しないメソッドも紹介しますが、理由は後述します。
| 説明 | 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 と使いわけてご利用ください。
Discussion