⚙️

LINQ to Entities クイックリファレンス

2022/01/01に公開

tech

はじめに

この記事は C# Advent Calendar 2012 の参加記事です。

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 が null の場合に Name プロパティを参照していて NullReferenceException が発生してしまいそうに見えます。しかし実際は式ツリーとして解釈されるだけで実際に実行されるわけではないので問題ありません。逆に、単体テストなどでモックして IEnumerable として評価する場合は例外が発生しますので、注意が必要です。

GroupJoin メソッドでは、結合されるエンティティはコレクションになるので、プロパティを参照するにはさらに SelectMany します。

context.Employees
    .GroupJoin(
        context.Departments,
        employee => employee.DepartmentId,
        department => department.Id,
        (employee, department) => 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 と棲み分けしするのがよさそうです。