いつもお世話になってます。  
下記のSQLと同じ結果をLINQで取り出したいのですが、期待値が取れずに苦戦しています。

[こちら](https://stackoverflow.com/questions/9980568/row-number-over-partition-
by-xxx-in-
linq)の情報をもとに作成をしてみたのですが、パーテーションが3つあるという点が違うのと、foreachで入れ子にしている部分で間違いが起きてるのかなと予想しているのですが、なかなか期待値が取れずにいます。

知りたい事。  
LINQでのROW_NUMBER() OVER (PARTITION BY )で複合キーは可能なのか。  
LEFT JOINした後でグループ化をSQLではしているので、その順番でやっているが正しいのかどうか？  
foreachの入れ子は正しいのか。  
です。  
皆さま、ご教授いただけると幸いです。

## テーブル定義

```

    TMP1
        ID         int
        START_DATE datetime
        TOUROKU_NO int
        CD         string
    
    TMP2
        ID         int
        START_DATE datetime
        TOUROKU_NO int
        CD         string
        END_DATE   datetime
    
```

## SQL

```

    SELECT
        BB4.[ID],
        BB4.[START_DATE],
        BB4.[TOUROKU_NO],
        MAX(CASE WHEN BB4.[RN] = 1 THEN BB4.[CD] ELSE '' END) AS [CD],
        MAX(CASE WHEN BB4.[RN] = 1 THEN BB4.[END_DATE] ELSE '' END) AS [END_DATE]
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY BB3.[ID], BB3.[START_DATE], BB3.[TOUROKU_NO] ORDER BY BB3.[END_DATE]) AS [RN]
        FROM (
            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])
        ) AS BB3
    ) AS BB4
    GROUP BY BB4.[ID], BB4.[START_DATE], BB4.[TOUROKU_NO]
    
```

## LINQ

```

    //自己結合
         var joinTable = from a in TMP1
            join a2 in TMP1
            on a.ID  equals  a2.ID into g
             from a2 in g.DefaultIfEmpty()
            where a.TOUROKU_NO == a2.TOUROKU_NO & a.START_DATE < a2.START_DATE
            select new {
                a.ID,
                a.START_DATE,
                a.TOUROKU_NO,
                a.CD,
                END_DATE = (DBNull.Value.Equals(a2.START_DATE) ? (DateTime)a2.SERVERDATE : (DateTime)a2.START_DATE)
            };
    
    
    //グループ化
    var groupTable = from a in joinTable
                     group a by new { a.ID, a.START_DATE, a.TOUROKU_NO } into g
                     select new {
                         ID = from m in g
                                    select m.ID,
                         START_DATE = g.Key.START_DATE,
                         TOUROKU_NO = g.Key.TOUROKU_NO,
                         CD = from m in g
                               select m.CD,
                         END_DATE = from m in g
                                    orderby m.END_DATE
                                    select m.END_DATE
                     };
    
    foreach (var item in groupTable) {
        foreach (var id in item.ID) {
            foreach (var cd in item.CD) {
                foreach (var end_date in item.END_DATE) {
                    var Row = DataTable.NewRow();
                    Row[0] = id.ToString();
                    Row[1] = DatePicker(item.START_DATE.ToString());
                    Row[2] = Convert.ToInt32(item.TOUROKU_NO);
                    Row[3] = cd.ToString();
                    Row[4] = DatePicker(end_date.ToString());
                    DataTable.Rows.Add(Row);
                }
            }
        }
    }
    
```

追記

## SQL1

```

    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])
    
```

## SQL1の結果

```

    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.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
    10  2010-04-28 00:00:00.000 1           02310,                          2012-01-23 00:00:00.000
    10  2010-04-28 00:00:00.000 1           02310,                          2010-10-25 00:00:00.000
    10  2010-04-28 00:00:00.000 1           02310,                          2011-02-07 00:00:00.000
    10  2010-04-28 00:00:00.000 1           02310,                          2011-10-12 00:00:00.000
    10  2010-10-25 00:00:00.000 1           02310,                          2012-01-23 00:00:00.000
    
```

## LINQ1

SQL1をlinqに変換

```

    var joinTable = from a in TMP1
            join a2 in TMP1
            on a.ID  equals  a2.ID into g
             from a2 in g.DefaultIfEmpty()
            where a.TOUROKU_NO == a2.TOUROKU_NO & a.START_DATE < a2.START_DATE
            select new {
                a.ID,
                a.START_DATE,
                a.TOUROKU_NO,
                a.CD,
                END_DATE = (DBNull.Value.Equals(a2.START_DATE) ? (DateTime)a2.SERVERDATE : (DateTime)a2.START_DATE)
            };
    
                foreach (var item in joinTable) {
                        ID         = item.ID.ToString();
                        START_DATE = item.START_DATE;
                        TOUROKU_NO = item.TOUROKU_NO;
                        CD         = item.CD.ToString();
                        END_DATE   = item.END_DATE;
                    }
    
```

## LINQ1の結果

```

    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
        10  2010/04/28 0:00:00  　　　　1   　　　　　　　02310,   　　2010/10/25 0:00:00
        10  2010/04/28 0:00:00  　　　　1   　　　　　　　02310,   　　2011/02/07 0:00:00
        10  2010/04/28 0:00:00  　　　　1   　　　　　　　02310,   　　2011/10/12 0:00:00
        10  2010/04/28 0:00:00  　　　　1   　　　　　　　02310,   　　2012/01/23 0:00:00
        10  2010/10/25 0:00:00  　　　　1   　　　　　　　02310,   　　2011/02/07 0:00:00
        10  2010/10/25 0:00:00  　　　　1   　　　　　　　02310,   　　2011/10/12 0:00:00
        10  2010/10/25 0:00:00  　　　　1   　　　　　　　02310,   　　2012/01/23 0:00:00
        10  2011/02/07 0:00:00  　　　　1   　　　　　　　02310,   　　2011/10/12 0:00:00
        10  2011/02/07 0:00:00  　　　　1   　　　　　　　02310,   　　2012/01/23 0:00:00
    
    leftjoinで自己結合したいのですが、上記のSQLの結果のようにいきませんでした。
    いかようにlinqを書けばSQL１と同じ値になるのでしょうか？
    
```

## SQL2

```

        SQL2
            SELECT
                *,
            ROW_NUMBER() OVER (PARTITION BY BB3.[id], BB3.[START_DATE], BB3.[TOUROKU_NO] ORDER BY BB3.[END_DATE]) AS [RN]
            FROM (
                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])
            ) AS BB3
    
```

## SQL2の結果

```

    ID  STARTDATE               TOROKU_NO   CD                                      END_DATE             RN
    7   2010-06-30 00:00:00.000 1           03008,                          2011-07-05 00:00:00.000      1
    7   2010-06-30 00:00:00.000 1           03009,                          2011-09-26 00:00:00.000      2
    7   2010-06-30 00:00:00.000 1           03009,                          2011-12-06 00:00:00.000      3
    7   2010-06-30 00:00:00.000 2           02009,                          2011-12-06 00:00:00.000      1
    7   2010-06-30 00:00:00.000 3           10009,                          2016-11-01 14:50:38.060      1
    7   2010-06-30 00:00:00.000 4           04440,                          2016-11-01 14:50:38.060      1
    7   2011-07-05 00:00:00.000 1           09010,                          2011-09-26 00:00:00.000      1
    7   2011-07-05 00:00:00.000 1           09010,                          2011-12-06 00:00:00.000      2
    7   2011-09-26 00:00:00.000 1           10010,                          2011-12-06 00:00:00.000      1
    7   2011-12-06 00:00:00.000 1           09010,                          2016-11-01 14:50:38.060      1
    7   2011-12-06 00:00:00.000 2           04441,                          2016-11-01 14:50:38.060      1
    10  2010-04-28 00:00:00.000 1           02310,                          2010-10-25 00:00:00.000      1
    10  2010-04-28 00:00:00.000 1           02310,                          2011-02-07 00:00:00.000      2
    10  2010-04-28 00:00:00.000 1           02310,                          2011-10-12 00:00:00.000      3
    10  2010-04-28 00:00:00.000 1           02310,                          2012-01-23 00:00:00.000      4
    10  2010-10-25 00:00:00.000 1           02310,                          2011-02-07 00:00:00.000      1
    10  2010-10-25 00:00:00.000 1           02310,                          2011-10-12 00:00:00.000      2
    10  2010-10-25 00:00:00.000 1           02310,                          2012-01-23 00:00:00.000      3
    10  2011-02-07 00:00:00.000 1           02310,                          2011-10-12 00:00:00.000      1
    10  2011-02-07 00:00:00.000 1           02310,                          2012-01-23 00:00:00.000      2
    10  2011-02-07 00:00:00.000 2           02310,03020,18010,　　　　　　　　　　　　2016-11-01 14:50:38.060        1
    10  2011-02-07 00:00:00.000 3           20010,02030,                    2016-11-01 14:50:38.060      1
    10  2011-02-07 00:00:00.000 4           01210,                          2016-11-01 14:50:38.060      1
    10  2011-02-07 00:00:00.000 5           02010,                          2016-11-01 14:50:38.060      1
    10  2011-02-07 00:00:00.000 6           20010,                          2016-11-01 14:50:38.060      1
    10  2011-02-07 00:00:00.000 7           02030,                          2016-11-01 14:50:38.060      1
    10  2011-10-12 00:00:00.000 1           02310,                          2012-01-23 00:00:00.000      1
    10  2012-01-23 00:00:00.000 1           02310,                          2016-11-01 14:50:38.060      1
    11  2010-04-28 00:00:00.000 1           02310,                          2010-10-25 00:00:00.000      1
    11  2010-04-28 00:00:00.000 1           02310,                          2011-02-07 00:00:00.000      2
    11  2010-04-28 00:00:00.000 1           02310,                          2011-06-27 00:00:00.000      3
    11  2010-04-28 00:00:00.000 1           02310,                          2011-10-12 00:00:00.000　　　4
    11  2010-04-28 00:00:00.000 1           02310,                          2012-01-23 00:00:00.000      5
      　 11  2010-10-25 00:00:00.000 1           02310,                          2011-02-07 00:00:00.000      1
    11  2010-10-25 00:00:00.000 1           02310,                          2011-06-27 00:00:00.000      2
    
```

## SQL3

```

        SQL3
        SELECT
            BB4.[id],
            BB4.[START_DATE],
            BB4.[TOUROKU_NO],
            MAX(CASE WHEN BB4.[RN] = 1 THEN BB4.[CD] ELSE '' END) AS [CD],
            MAX(CASE WHEN BB4.[RN] = 1 THEN BB4.[END_DATE] ELSE '' END) AS [END_DATE]
            From(
            SELECT
                *,
            ROW_NUMBER() OVER (PARTITION BY BB3.[id], BB3.[START_DATE], BB3.[TOUROKU_NO] ORDER BY BB3.[END_DATE]) AS [RN]
            FROM (
                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])
            ) AS BB3
        ) AS BB4
            GROUP BY BB4.[id], BB4.[START_DATE], BB4.[TOUROKU_NO]
    
```

## SQL3の結果

こちらの結果が最終的に取得したいデータです。

```

    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 2           02010,                          2011-12-06 00:00:00.000
    7   2010-06-30 00:00:00.000 3           10010,                          2016-11-01 14:53:38.593
    7   2010-06-30 00:00:00.000 4           04441,                          2016-11-01 14:53:38.593
    7   2011-07-05 00:00:00.000 1           09010,                          2011-09-26 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:53:38.593
    7   2011-12-06 00:00:00.000 2           04441,                          2016-11-01 14:53:38.593
    10  2010-04-28 00:00:00.000 1           02310,                          2010-10-25 00:00:00.000
    10  2010-10-25 00:00:00.000 1           02310,                          2011-02-07 00:00:00.000
    10  2011-02-07 00:00:00.000 1           02310,                          2011-10-12 00:00:00.000
    10  2011-02-07 00:00:00.000 2           02310,03020,18010,              2016-11-01 14:53:38.593
    10  2011-02-07 00:00:00.000 3           20010,02030,                    2016-11-01 14:53:38.593
    10  2011-02-07 00:00:00.000 4           01210,                          2016-11-01 14:53:38.593
    10  2011-02-07 00:00:00.000 5           02010,                          2016-11-01 14:53:38.593
    10  2011-02-07 00:00:00.000 6           20010,                          2016-11-01 14:53:38.593
    10  2011-02-07 00:00:00.000 7           02030,                          2016-11-01 14:53:38.593
    10  2011-10-12 00:00:00.000 1           02310,                          2012-01-23 00:00:00.000
    10  2012-01-23 00:00:00.000 1           02310,                          2016-11-01 14:53:38.593
    11  2010-04-28 00:00:00.000 1           02310,                          2010-10-25 00:00:00.000
    11  2010-10-25 00:00:00.000 1           02310,                          2011-02-07 00:00:00.000
    11  2011-02-07 00:00:00.000 1           02310,                          2011-06-27 00:00:00.000
    11  2011-06-27 00:00:00.000 1           02310,                          2011-10-12 00:00:00.000
    11  2011-10-12 00:00:00.000 1           02310,                          2012-01-23 00:00:00.000
    
```

皆さまなにとぞご教授の程よろしくお願いします。
CC BY-SA 3.0
2016-11-01T01:18:39.163
30024
2017-08-16T15:05:01.630
2017-05-23T12:38:55.250
-1
19310
question
c# sql linq
ROW_NUMBER() OVER (PARTITION BY) をLINQに変換したい
## linqでのROW_NUMBER() OVER (PARTITION BY )で複合キーは可能なのか。

`ROW_NUMBER()`分析関数は基本的にEntityFrameworkではサポートされていませんが、今回はグループ化の対象とパーティションが一致しているので

> foreach (var end_date in item.END_DATE) {

に`LINQ to Object`の`Enumerable.Select`拡張メソッドを追加すれば

```

    item.END_DATE.Select((d, i) => new { RN = i + 1, end_date = d})
    
```

のように行番号を得られます。あるいは単純に

```

    var rn = 0;
    foreach (var end_date in item.END_DATE) {
        rn++;
    
```

と変数を利用することもできます。

## leftjoinした後でグループ化をSQLではしているので、その順番でやっているが正しいのかどうか？

SQLを精査したわけではないですが、基本的には問題ないはずです。

## foreachの入れ子は正しいのか。

仕様が分かりませんが、おそらく

> foreach (var id in item.ID) {

の`item.ID`はグループ化キーであるためすべて同値であり不要だと思います。

## 追記

質問文のSQLは「各グループ内で最小の`END_DATE`を持つレコード」が抽出できれば良いのだと思いますので、以下のようなLINQで大体目的は達せられるかと思います。

```

    //グループ化
    var groupTable = from a in joinTable
                     // (グループキー＋)END_DATEでソートする
                     orderby a.ID, a.START_DATE, a.TOUROKU_NO, a.END_DATE
                     group a by new { a.ID, a.START_DATE, a.TOUROKU_NO } into g
                     select g.FirstOrDefault();
    
```

## 追記2

`joinTable`部分は副問い合わせで

```

    var joinTable = from orig in TMP1
                    select new
                    {
                        orig.ID,
                        orig.START_DATE,
                        orig.TOUROKU_NO,
                        orig.CD,
                        END_DATE = TMP1.Where(alias => orig.ID == alias.ID
                                            && orig.TOUROKU_NO == alias.TOUROKU_NO
                                            && orig.START_DATE < alias.START_DATE)
                                        .Min(_ => (DateTime?)_.START_DATE)
                                       ?? DateTime.Now
                    };
    
```

のように書けます。なお上記の2クエリーでは`END_DATE`の`?? DateTime.Now`と`select
g.FirstOrDefault()`がネックになりますので、

  * `END_DATE == START_DATE`の判定をもっと後で行う。
  * `group a`の`a`を専用の型にし、`MIN(END_DATE)`を含めて`select g.FirstOrDefault()`を不要にする。

などの方針を取るとより高速になるかと思います。
CC BY-SA 3.0
2016-11-01T02:32:31.743
30025
2016-11-10T04:03:47.610
2016-11-10T04:03:47.610
5750
5750
30024
answer