LINQのメソッド構文で内部結合、外部結合した時のメモ

はじめに

LINQのメソッド構文を使用して内部結合、外部結合した時のメモ。
複数結合するとソースの記述が複雑になったり、発行されるSQLが複雑になったりするので、なるべく簡潔に。

環境

C#
.Net Core 2.1.507
SQLServer 2017 - 14.0.1000.169 (X64)

前提

テーブルA,B,Cを内部結合し、それぞれに対してテーブルD,E,Fを外部結合する。
テーブルA,B,Cは運用テーブルとその詳細テーブルで、テーブルD,E,Fはマスタテーブルというイメージ。

・テーブルA　　　　　　→外部結合→テーブルD
→内部結合→テーブルB　　→外部結合→テーブルE
→内部結合→テーブルC　　→外部結合→テーブルF

よくある書き方

var list = 
    // TableA
    dbContext.TableA

    // TableAとTableBを内部結合
    .Join(dbContext.TableB,
        a => a.Id,
        b => b.A_Id,
        (a, b) => new { a, b })

   // TableA,BとTableCを内部結合
   .Join(dbContext.TableC,
        ab => ab.b.Id,
        c => c.B_Id,
        (ab, c) => new { ab, c })

    // TableAとTableDを外部結合
    .GroupJoin(dbContext.TableD,
        abc => abc.ab.a.key,
        d => d.key,
        (abc, d) => new { abc, d })
    .SelectMany(d => d.d.DefaultIfEmpty(), 
        (abc, d) => new { abc, d })

    // TableBとTableEを外部結合
    .GroupJoin(dbContext.TableE,
        abcd => abcd.abc.abc.ab.b.key,
        e => e.key,
        (abcd, e) => new { abcd, e })
    .SelectMany(e => e.e.DefaultIfEmpty(), 
        (abcd, e) => new { abcd, e })

    // TableCとTableFを外部結合
    .GroupJoin(dbContext.TableF),
        abcde => abcde.abcd.abcd.abc.abc.c.key,
        f => f.key,
        (abcde, f) => new { abcde, f })
    .SelectMany(f => f.f.DefaultIfEmpty(), 
        (abcde, f) => new { abcde, f })

    // 各テーブルから必要なデータを取得
    .Select(abcdef => new {
        a_id = abcdef.abcde.abcde.abcd.abcd.abc.abc.ab.a.Id,
        b_id = abcdef.abcde.abcde.abcd.abcd.abc.abc.ab.b.Id,
        c_id = abcdef.abcde.abcde.abcd.abcd.abc.abc.c.Id,
        d_id = abcdef.abcde.abcde.abcd.abcd.d.Id,
        e_id = abcdef.abcde.abcde.e.Id,
        f_id = abcdef.f.Id,
    })

    .ToList();

だいたいこんな感じ。テーブルが増えると最後のSelect部分がひどいことになる。
結合するテーブルが増えるたびに修正が必要になるのも気に入らない。

別名を定義してみる

var list =
    // TableA
    dbContext.TableA

    // TableAとTableBを内部結合
    .Join(dbContext.TableB,
        a => a.Id,
        b => b.A_Id,
        (a, b) => new { a, b })

   // TableA,BとTableCを内部結合
   .Join(dbContext.TableC,
        x => x.b.Id,
        c => c.B_Id,
        (x, c) => new { x.a, x.b, c })          // A,B,Cが同階層に並ぶよう定義する。

    // TableAとTableDを外部結合
    .GroupJoin(dbContext.TableD,
        x => x.a.key,
        d => d.key,
        (x, d) => new { x, d })
    .SelectMany(d => d.d.DefaultIfEmpty(), 
        (x, d) => new { x.x.a, x.x.b, x.x.c, d })         // A,B,C,Dが同階層に並ぶよう定義する。以下同文。

    // TableBとTableEを外部結合
    .GroupJoin(dbContext.TableE,
        x => x.b.key,
        e => e.key,
        (x, e) => new { x, e })
    .SelectMany(e => e.e.DefaultIfEmpty(), 
        (x, e) => new { x.x.a, x.x.b, x.x.c, x.x.d, e })

    // TableCとTableFを外部結合
    .GroupJoin(dbContext.TableF,
        x => x.c.key,
        f => f.key,
        (x, f) => new { x, f })
    .SelectMany(f => f.f.DefaultIfEmpty(), 
        (x, f) => new { x.x.a, x.x.b, x.x.c, x.x.d, x.x.e, f })

    // 各テーブルから必要なデータを取得
    .Select(x => new {
        a_id = x.a.Id,
        b_id = x.b.Id,
        c_id = x.c.Id,
        d_id = x.d.Id,
        e_id = x.e.Id,
        f_id = x.f.Id,
    })

    .ToList();

これなら最後のSelectはきれいに表現できる。
結局、途中でいちいち別名を定義することになるが・・・まあまだマシか。

検索条件を指定

var list =
    // TableA
    dbContext.TableA

    // TableAとTableBを内部結合
    .Join(dbContext.TableB,
        a => a.Id,
        b => b.A_Id,
        (a, b) => new { a, b })

   // TableA,BとTableCを内部結合
   .Join(dbContext.TableC,
        x => x.b.Id,
        c => c.B_Id,
        (x, c) => new { x.a, x.b, c })          // A,B,Cが同階層に並ぶよう定義する。

    // TableAとTableDを外部結合
    .GroupJoin(dbContext.TableD,
        x => x.a.key,
        d => d.key,
        (x, d) => new { x, d })
    .SelectMany(d => d.d.DefaultIfEmpty(), 
        (x, d) => new { x.x.a, x.x.b, x.x.c, d })         // A,B,C,Dが同階層に並ぶよう定義する。以下同文。

    // TableBとTableEを外部結合
    .GroupJoin(dbContext.TableE,
        x => x.b.key,
        e => e.key,
        (x, e) => new { x, e })
    .SelectMany(e => e.e.DefaultIfEmpty(), 
        (x, e) => new { x.x.a, x.x.b, x.x.c, x.x.d, e })

    // TableCとTableFを外部結合
    .GroupJoin(dbContext.TableF,
        x => x.c.key,
        f => f.key,
        (x, f) => new { x, f })
    .SelectMany(f => f.f.DefaultIfEmpty(), 
        (x, f) => new { x.x.a, x.x.b, x.x.c, x.x.d, x.x.e, f })

    // 検索条件が指定されていたら絞り込む
    .Where(x =>
       (key1 == null ? true : key1 == x.a.key1)
    && (key2 == null ? true : key2 == x.a.key2)
    )

    // 各テーブルから必要なデータを取得
    .Select(x => new {
        a_id = x.a.Id,
        b_id = x.b.Id,
        c_id = x.c.Id,
        d_id = x.d.Id,
        e_id = x.e.Id,
        f_id = x.f.Id,
    })

    .ToList();

ついでに、検索条件が指定されていたら絞り込むようにWhere句を追加。
これもよく使うので。。。

発行されたSQL

exec sp_executesql 
N'SELECT [a].[id] AS [a_id], [b].[id] AS [b_id], [c].[id] AS [c_id], [t].[id] AS [d_id], [t0].[id] AS [e_id], [t1].[id] AS [f_id]
FROM [TableA] AS [a]
INNER JOIN [TableB] AS [b] ON [a].[id] = [b].[a_id]
INNER JOIN [TableC] AS [c] ON [b].[id] = [c].[b_id]
LEFT JOIN (
    SELECT [x].*
    FROM [TableD] AS [x]
) AS [t] ON [a].[key] = [t].[key]
LEFT JOIN (
    SELECT [x0].*
    FROM [TableE] AS [x0]
) AS [t0] ON [b].[key] = [t0].[key]
LEFT JOIN (
    SELECT [x1].*
    FROM [TableF] AS [x1]
) AS [t1] ON [c].[key] = [t1].[key]
WHERE (@__key1_0 IS NULL OR (@__key1_0 IS NOT NULL AND (@__key1_1 = [a].[key1])))
 AND (@__key2_2 IS NULL OR (@__key2_2 IS NOT NULL AND (@__key2_3 = [a].[key2])))'
,N'@__key1_0 nvarchar(4000),@__key1_1 nvarchar(20),@__key2_2 nvarchar(4000),@__key2_3 nvarchar(15)'
,@__key1_0=N'AAAAAAAA',@__key1_1=N'AAAAAAAA',@__key2_2=N'BBBBBBBB',@__key2_3=N'BBBBBBBB'

実際に発行されたSQL。
見慣れた形式になっている。
（メソッド構文の書き方によってはとんでもなく無駄に長いSQLになるので。。。）