linqとsqlの結果を同じにしたい


お世話になっております。以前に質問させていただいた内容と被るのですが、解決できていない箇所があるので再度投稿させて頂きます。  
SQLと同じ結果を出したいのですが、linqでleftjoinした際に違う結果になります。  
いろいろと調べてlinqを書いてみたのですが、なかなかうまく行きません。  
どなたかご指摘いただけますでしょうか？よろしくお願いします。

## テーブル定義

[code]

    TMP1
        ID         int
        START_DATE datetime
        TOUROKU_NO int
        CD         string
    
[/code]

## SQL

[code]

    SELECT
    BB1.*,
    CASE WHEN BB2.START_DATE  is NULL THEN GETDATE() ELSE BB2.START_DATE  END AS END_DATE
    FROM TMP1 AS BB1
    LEFT JOIN TMP1 AS BB2 ON (BB1.ID = BB2.ID AND BB1.TOUROKU_NO = BB2.TOUROKU_NO AND BB1.START_DATE  < BB2.START_DATE 
    
[/code]

## SQL結果

[code]

    ID 　　STARTDATE               TOROKU_NO   　　　CD 　　　　　  END_DATE       
    
    7   2010-06-30 00:00:00.000　　 1           03010,                          2011-07-05 00:00:00.000
    7   2010-06-30 00:00:00.000  　 1           03010,                          2011-09-26 00:00:00.000
    7   2010-06-30 00:00:00.000 　　1         　 03010,                          2011-12-06 00:00:00.000
    7   2010-06-30 00:00:00.000　　 2           02010,                          2011-12-06 00:00:00.000
    7   2010-06-30 00:00:00.000　　 3           10010,                          2016-11-01 14:48:08.410
    7   2010-06-30 00:00:00.000 　　4           04441,                          2016-11-01 14:48:08.410
    7   2011-07-05 00:00:00.000 　　1           09010,                          2011-09-26 00:00:00.000
    7   2011-07-05 00:00:00.000 　　1           09010,                          2011-12-06 00:00:00.000
    7   2011-09-26 00:00:00.000 　　1           10010,                          2011-12-06 00:00:00.000
    7   2011-12-06 00:00:00.000　　 1         　 09010,                          2016-11-01 14:48:08.410
    7   2011-12-06 00:00:00.000 　　2           04441,                          2016-11-01 14:48:08.410
    
[/code]

## LINQ

[code]

    var result =
                from orig in TMP1
                 join alias in TMP1
                 on orig.ID equals alias.ID into g
                 from alias in g.DefaultIfEmpty()
                 where orig.TOUROKU_NO == alias.TOUROKU_NO && orig.START_DATE < alias.START_DATE 
                 select new {
                     orig.ID,
                     orig.START_DATE ,
                     orig.TOUROKU_NO,
                     orig.CD,
                     END_DATE = (DBNull.Value.Equals(alias.START_DATE) ? (DateTime)orig.SERVERDATE : (DateTime)alias.START_DATE),
                 };
    
[/code]

## LINQ結果

[code]

    ID 　　STARTDATE               TOROKU_NO   　　　CD 　　　　　  END_DATE       
    7   2010/06/30 0:00:00  　　　　1   　　　　　　　03010,   　　2011/07/05 0:00:00
    7   2010/06/30 0:00:00  　　　　1   　　　　　　　03010,   　　2011/09/26 0:00:00
    7   2010/06/30 0:00:00  　　　　1   　　　　　　　03010,   　　2011/12/06 0:00:00
    7   2010/06/30 0:00:00  　　　　2   　　　　　　　02010,   　　2011/12/06 0:00:00
    7   2011/07/05 0:00:00  　　　　1   　　　　　　　09010,   　　2011/09/26 0:00:00
    7   2011/07/05 0:00:00  　　　　1   　　　　　　　09010,   　　2011/12/06 0:00:00
    7   2011/09/26 0:00:00  　　　　1   　　　　　　　10010,   　　2011/12/06 0:00:00
    
[/code]




@pgrhoさん、@sayuriさん、回答ありがとうございます。  
ご連絡が遅れまして、申し訳ないです。  
先の質問ですが、自己結合を２回行う事によって解決しました。

## LINQ

[code]

        var result1 =
            from orig in TMP1
            join alias in TMP1
            on new { orig.ID, orig.TOUROKU_NO } equals new { alias.ID, alias.TOUROKU_NO } into g
            from alias in g.DefaultIfEmpty()
            where alias == null || orig.START_DATE < alias.START_DATE 
            select new {
                orig.ID,
                orig.START_DATE,
                orig.TOUROKU_NO,
                orig.CD,
                END_DATE = (alias.START_DATE == DateTime.MinValue ? (DateTime)orig.SERVERDATE : (DateTime)alias.START_DATE ),
            };
    
        //もう一度、自己結合
        var result =
            from orig in TMP1
            join alias in result1 
            on new { orig.ID, orig.START_DATE, orig.TOUROKU_NO } equals new { alias.ID, alias.START_DATE, alias.TOUROKU_NO } into g
            from alias in g.DefaultIfEmpty()
            select new {
                orig.ID,
                orig.START_DATE,
                orig.TOUROKU_NO,
                orig.CD,
                END_DATE = ((alias == null) ? (DateTime)orig.SERVERDATE : (DateTime)alias.END_DATE),
            };
    
[/code]

このlinqで試したところ、nullを考慮したうえで、SQLと同等の結果を出す事が出来ました。




まずID同士を結合していますが、これが主キー扱いになっている場合は結合されるレコードが全く同一になるため結合自体が無視されます。

上記をクリアしたうえで外部結合にならないのは`where`で`alias`が`null`の場合を考慮していないためです。

[code]

    where alias == null
       || (orig.TOUROKU_NO == alias.TOUROKU_NO
        && orig.START_DATE < alias.START_DATE)
    
[/code]

## 追記

コメントで触れたログの取り方ですが、コンテキストの`Database`プロパティには`Log`というプロパティがあるので、例えば

[code]

    using (var db = new TesDbContext())
    {
        db.Database.Log = Console.WriteLine;
    }
    
[/code]

のように`string`を引数にとるメソッドを設定すれば`SQL`等のログが出力されます。質問の場合`db`の部分はおそらく`this`になると思います。

`Database.Log`を指定したうえで以下のクエリーを実行すると下記のログが出力されます。

[code]

    var q = from orig in TMP1
            join alias in TMP1 on orig.ID equals alias.ID into g
            from alias in g.DefaultIfEmpty()
            where alias == null || (alias.TOUROKU_NO == orig.TOUROKU_NO && orig.START_DATE < alias.START_DATE)
            select new
            {
                orig.ID,
                orig.START_DATE,
                orig.TOUROKU_NO,
                orig.CD,
                END_DATE = (DateTime?)alias.START_DATE ?? DateTime.Now
            };
    foreach (var e in q) ;
    
[/code]

### ログ

[code]

    SELECT
        [Extent1].[ID] AS [ID],
        [Extent1].[START_DATE] AS [START_DATE],
        [Extent1].[TOUROKU_NO] AS [TOUROKU_NO],
        [Extent1].[CD] AS [CD],
        CASE WHEN ([Extent2].[START_DATE] IS NULL) THEN SysDateTime() ELSE  CAST( [Extent2].[START_DATE] AS datetime2) END AS [C1]
        FROM  [dbo].[E] AS [Extent1]
        LEFT OUTER JOIN [dbo].[E] AS [Extent2] ON [Extent1].[ID] = [Extent2].[ID]
        WHERE ([Extent2].[Key] IS NULL) OR (([Extent2].[TOUROKU_NO] = [Extent1].[TOUROKU_NO]) AND ([Extent1].[START_DATE] < [Extent2].[START_DATE]))
    
[/code]

※[Key]は仮に付加した主キー列です。

このように`LEFT OUTER
JOIN`自体は問題なく行われているので、後は`where`及び`END_DATE`の内容を想定データに合わせて修正すれば仕様は満たせるはずです。

ちなみにsayuriさんの方法だと

[code]

    SELECT
        [Extent1].[ID] AS [ID],
        [Extent1].[START_DATE] AS [START_DATE],
        [Extent1].[TOUROKU_NO] AS [TOUROKU_NO],
        [Extent1].[CD] AS [CD],
        [UnionAll1].[START_DATE] AS [C1]
        FROM  [dbo].[E] AS [Extent1]
        INNER JOIN  (SELECT
            [Extent2].[ID] AS [ID],
            [Extent2].[TOUROKU_NO] AS [TOUROKU_NO],
            [Extent2].[START_DATE] AS [START_DATE]
            FROM [dbo].[E] AS [Extent2]
        UNION ALL
            SELECT
            [Distinct1].[ID] AS [ID],
            [Distinct1].[TOUROKU_NO] AS [TOUROKU_NO],
            SysDateTime() AS [C1]
            FROM ( SELECT DISTINCT
                [Extent3].[ID] AS [ID],
                [Extent3].[TOUROKU_NO] AS [TOUROKU_NO]
                FROM [dbo].[E] AS [Extent3]
            )  AS [Distinct1]) AS [UnionAll1] ON ([Extent1].[ID] = [UnionAll1].[ID]) AND ([Extent1].[TOUROKU_NO] = [UnionAll1].[TOUROKU_NO])
        WHERE [Extent1].[START_DATE] < [UnionAll1].[START_DATE]
    
[/code]

このようなSQLが生成されてしまうので避けた方が良いです。

