30414


お世話になっております。以前に質問させていただいた内容と被るのですが、解決できていない箇所があるので再度投稿させて頂きます。  
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]


CC BY-SA 3.0
2016-11-08T03:36:54.897
30201
2016-11-15T05:53:41.803
2016-11-08T07:35:52.440
19310
19310
question
c# linq
linqと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が生成されてしまうので避けた方が良いです。


CC BY-SA 3.0
2016-11-08T03:43:51.530
30202
2016-11-10T01:00:10.693
2016-11-10T01:00:10.693
5750
5750
30201
answer


ID & TOROKU_NOごとに着目して、START_DATEに

[code]

    START_DATE
    2016/11/01
    2016/11/02
    2016/11/03
    2016/11/04
    2016/11/05
    2016/11/06
    2016/11/07
    
[/code]

と7行存在していた場合は

[code]

    START_DATE  END_DATE
    2016/11/01  2016/11/02
    2016/11/01  2016/11/03
    2016/11/01  2016/11/04
    2016/11/01  2016/11/05
    2016/11/01  2016/11/06
    2016/11/01  2016/11/07
    2016/11/02  2016/11/03
    2016/11/02  2016/11/04
    2016/11/02  2016/11/05
    2016/11/02  2016/11/06
    2016/11/02  2016/11/07
    2016/11/03  2016/11/04
    2016/11/03  2016/11/05
    2016/11/03  2016/11/06
    2016/11/03  2016/11/07
    2016/11/04  2016/11/05
    2016/11/04  2016/11/06
    2016/11/04  2016/11/07
    2016/11/05  2016/11/06
    2016/11/05  2016/11/07
    2016/11/06  2016/11/07
    2016/11/07  GETDATE()    // LEFT JOINでNULL行となった部分
    
[/code]

と22行の結果を求めているのでしょうか？
このパターンはO(n^2)で結果行が増殖します。DBが結果行を用意するコスト、DB→アプリケーション間の通信コスト、アプリケーションでの受信コストが膨大になるためアプリケーション側で行生成することをお勧めします。

* * *

以下、誤りがあったので書き直しました。

> SQLと同じ結果を出したいのですが、linqでleftjoinした際に違う結果になります。

[LINQのjoin句は非等結合をサポートしていません](https://msdn.microsoft.com/ja-
jp/library/bb311040.aspx#%E9%9D%9E%E7%AD%89%E7%B5%90%E5%90%88)。そのため

[code]

    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]

の`BB1.START_DATE <
BB2.START_DATE`をそもそも表現できません。LINQで表現するためにはまず同等の結果となる別のSQLを組み立てる必要があります。

[code]

    -- LEFT JOINのうち結合に成功する部分
    SELECT s.ID, s.START_DATE, s.TOUROKU_NO, s.CD, e.START_DATE AS END_DATE
    FROM TMP1 AS s, TMP1 AS e
    WHERE s.ID=e.ID AND s.TOUROKU_NO=e.TOUROKU_NO AND s.START_DATE < e.START_DATE
    
    UNION ALL
    
    -- LEFT JOINのうち結合に成功せずNULLが挿入される部分
    -- BB1.START_DATE < BB2.START_DATEが成立しない、つまりBB1.START_DATEが最大値となるもの
    SELECT t.ID, t.START_DATE, t.TOUROKU_NO, t.CD, GETDATE()
    FROM (
        SELECT ID, TOUROKU_NO, MAX(START_DATE) AS START_DATE
        FROM TMP1
        GROUP BY ID, TOUROKU_NO
    ) AS s
    INNER JOIN TMP1 AS t
    ON s.ID=t.ID AND s.TOUROKU_NO=t.TOUROKU_NO AND s.START_DATE=t.START_DATE
    
[/code]

あとはこのSQLに対応したLINQを記述します。

[code]

    var tmp1 = TMP1.ToArray();
    var normals =
        from s in tmp1
        from e in tmp1
        where s.ID == e.ID && s.TOUROKU_NO == e.TOUROKU_NO && s.START_DATE < e.START_DATE
        select new {
            s.ID,
            s.START_DATE,
            s.TOUROKU_NO,
            s.CD,
            END_DATE = e.START_DATE,
        };
    var rests =
        from r in tmp1
        group r.START_DATE by new { r.ID, r.TOUROKU_NO } into g
        join t in tmp1
        on new { g.Key.ID, g.Key.TOUROKU_NO, START_DATE = g.Max() }
        equals new { t.ID, t.TOUROKU_NO, t.START_DATE }
        select new {
            t.ID,
            t.START_DATE,
            t.TOUROKU_NO,
            t.CD,
            END_DATE = DateTime.Now,
        };
    var result = normals..Concat(rests);
    
[/code]

`ToArray()`を外せばDB側で実行できるクエリーを生成してくれることでしょう。

* * *

[SQL
Fiddle](http://sqlfiddle.com/#!6/e031e/3)で書いてみました。pgrhoさんのコードでは7行しか得られておらず、そもそもの問題を解決していません。


CC BY-SA 3.0
2016-11-09T21:49:30.903
30288
2016-11-10T23:56:27.507
2016-11-10T23:56:27.507
4236
4236
30201
answer


@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と同等の結果を出す事が出来ました。


CC BY-SA 3.0
2016-11-15T05:53:41.803
30414
2016-11-15T05:53:41.803
2020-06-17T08:14:45.997
-1
19310
30201
answer