LINQ(メソッド構文)を用いた複雑めなSQL発行時のメモ

こんにちは！井上です。

EF＆LINQでちょっと複雑なSQLを書きたいときのメモ。

環境

.NET Framework 4.5.1
Entity Framework 6
MySQL 5.6
C#

複数テーブルの結合（INNER JOIN）

var lists = db.TableA
    .Join(db.TableB, a => a.id, b => b.id, (a, b) => new
    {
        a.id,
        a.value1,
        b.value2
    })
    .Join(db.TableC, ab => ab.id, c => c.id, (ab, c) => new
    {
        ab.id,
        ab.value1,
        ab.value2,
        c.value3
    });

結合を先に書くパターン

var lists = db.TableA
    .Join(db.TableB, a => a.id, b => b.id, (a, b) => new { a, b })
    .Join(db.TableC, ab => ab.a.id, c => c.id, (ab, c) => new
    {
        ab.a.id,
        ab.a.value1,
        ab.b.value2,
        c.value3
    });

複数テーブルの結合（LEFT JOIN）

var targetData = db.TableA
    .GroupJoin(db.TableB, a => a.id, b => b.id, (a, b) => new { a, b })
    .SelectMany(x => x.b.DefaultIfEmpty(), (a, b) => new 
    { 
        a.a.id,
        a.a.value1,
        b.value2
    })
    .GroupJoin(db.TableC, ab => ab.id, c => c.id, (ab, c) => new { ab, c })
    .SelectMany(x => x.c.DefaultIfEmpty(), (ab, c) => new 
    { 
        ab.ab.id,
        ab.ab.value1,
        ab.ab.value2,
        c.value3
    });

結合を先に書くパターン

var lists = db.TableA
    .GroupJoin(db.TableB, a => a.id, b => b.id, (a, b) => new { a, b })
    .SelectMany(x => x.b.DefaultIfEmpty(), (a, b) => new { a = a.a, b })
    .GroupJoin(db.TableC, ab => ab.a.id, c => c.id, (ab, c) => new { ab, c })
    .SelectMany(x => x.c.DefaultIfEmpty(), (ab, c) => new { ab = ab.ab, c });

複数キーで結合したい場合

結合する項目名および順序は揃える必要があります。

db.TableA.Join(
    db.TableB, 
    a => new { a.id, key = a.value1 }, 
    b => new { b.id, key = b.value2 },
    ( a, b ) => new { a, b });

グルーピング

GroupByは指定キーのレコードの下に指定キーの条件を満たすレコードがぶら下がってるイメージです。GroupByした結果をとりたい場合は後述する纏めのようになります。

グルーピングしたboolカラム値の結果を取得

Any(),All()等を用います。
x.Any(y => y.boolカラム値) とすると、何れかがtrueの場合、trueが返ります。
x.All(y => y.boolカラム値) とすると、全てがtrueの場合、trueが返ります。

纏め

3テーブルを結合しつつグルーピング、ソートしてページングした結果を取得

var lists = db.TableA
    .GroupJoin(db.TableB, a => a.id, b => b.id, (a, b) => new { a, b })
    .SelectMany(x => x.b.DefaultIfEmpty(), (a, b) => new { a = a.a, b })
    .GroupJoin(db.TableC, ab => ab.a.id, c => c.id, (ab, c) => new { ab, c })
    .SelectMany(x => x.c.DefaultIfEmpty(), (ab, c) => new { ab = ab.ab, c });
    .GroupBy(x => x.ab.a.id)
    .Select(x => new
    {
        id = x.Min(y => y.ab.a.id),
        value1 = x.Min(y => y.ab.a.value1),
        value2 = x.Min(y => y.ab.b.value2),
        value3 = x.Any(y => y.c.value3)
    })
    .OrderBy(x => x.id)
    .Skip(1).Take(20)
    .Select(x => new
    {
        x.id,
        x.value1,
        x.value2,
        x.value3
    }).ToList();

本末転倒ですが、あまりに複雑なSQLの場合はExecuteSqlCommandメソッドで実行するなりビュー/ストアドを用いた方がシンプルかつ確実なクエリ発行に繋がるため良いかもしれません。
尚、テーブル間に外部キーが定義されていれば、JOINすら不要になり、複雑なLINQも不要になります。