29885


下のSQLクエリをLINQにしたいのですが、複雑過ぎて途中で分からなくなってしまいました。

[code]

        SELECT
                    [ID],
                    [DATE],
                    [NO],
                    [DSP_NO],
                    (CASE v1.[RTth] WHEN '0' THEN '' ELSE 'R'+v1.[RTth] END) +
                    (CASE v2.[LTth] WHEN '0' THEN '' ELSE 'L'+v2.[LTth] END) +
                    (CASE v3.[RTth] WHEN '0' THEN '' ELSE 'r'+v3.[RTth] END) +
                    (CASE v4.[LTth] WHEN '0' THEN '' ELSE 'l'+v4.[LTth] END) AS [BI], 
                    ISNULL((CASE c1.[NAME_1]  WHEN '0' THEN '' ELSE b1.[BCD]  END) +
                    (CASE c1.[NAME_2]  WHEN '0' THEN '' ELSE b2.[BCD]  END) +
                    (CASE c1.[NAME_3]  WHEN '0' THEN '' ELSE b3.[BCD]  END) +
                    (CASE c1.[NAME_4]  WHEN '0' THEN '' ELSE b4.[BCD]  END) +
                    (CASE c1.[NAME_5]  WHEN '0' THEN '' ELSE b5.[BCD]  END) +
                    (CASE c1.[NAME_6]  WHEN '0' THEN '' ELSE b6.[BCD]  END) +
                    (CASE c1.[NAME_7]  WHEN '0' THEN '' ELSE b7.[BCD]  END) +
                    (CASE c1.[NAME_8]  WHEN '0' THEN '' ELSE b8.[BCD]  END) +
                    (CASE c2.[NAME_9]  WHEN '0' THEN '' ELSE b9.[BCD]  END) +
                    (CASE c2.[NAME_10] WHEN '0' THEN '' ELSE b10.[BCD] END) +
                    (CASE c2.[NAME_11] WHEN '0' THEN '' ELSE b11.[BCD] END) +
                    (CASE c2.[NAME_12] WHEN '0' THEN '' ELSE b12.[BCD] END) +
                    (CASE c2.[NAME_13] WHEN '0' THEN '' ELSE b13.[BCD] END) +
                    (CASE c2.[NAME_14] WHEN '0' THEN '' ELSE b14.[BCD] END) +
                    (CASE c2.[NAME_15] WHEN '0' THEN '' ELSE b15.[BCD] END) +
                    (CASE c2.[NAME_16] WHEN '0' THEN '' ELSE b16.[BCD] END) , '') AS [NAME_ID]
                    FROM Table3 AS k1
                    LEFT JOIN Table1 AS v1 ON k1.[UR] = v1.[NoFlg]
                    LEFT JOIN Table1 AS v2 ON k1.[UL] = v2.[NoFlg]
                    LEFT JOIN Table1 AS v3 ON k1.[DR] = v3.[NoFlg]
                    LEFT JOIN Table1 AS v4 ON k1.[DL] = v4.[NoFlg]
                    LEFT JOIN table4 AS c1 ON k1.[TOUROKU_1] = c1.[TOUROKU_NO]
                    LEFT JOIN Table2 AS b1  ON k1.[ID] = b1.[ID]  AND c1.[NAME_1]  = b1.[TOUROKU_NO] AND k1.[DATE] >= b1.[START_DATE] AND k1.[DATE] < b1.[END_DATE]
                    LEFT JOIN Table2 AS b2  ON k1.[ID] = b2.[ID]  AND c1.[NAME_2]  = b2.[TOUROKU_NO] AND k1.[DATE] >= b2.[START_DATE] AND k1.[DATE] < b2.[END_DATE]
                    LEFT JOIN Table2 AS b3  ON k1.[ID] = b3.[ID]  AND c1.[NAME_3]  = b3.[TOUROKU_NO] AND k1.[DATE] >= b3.[START_DATE] AND k1.[DATE] < b3.[END_DATE]
                    LEFT JOIN Table2 AS b4  ON k1.[ID] = b4.[ID]  AND c1.[NAME_4]  = b4.[TOUROKU_NO] AND k1.[DATE] >= b4.[START_DATE] AND k1.[DATE] < b4.[END_DATE]
                    LEFT JOIN Table2 AS b5  ON k1.[ID] = b5.[ID]  AND c1.[NAME_5]  = b5.[TOUROKU_NO] AND k1.[DATE] >= b5.[START_DATE] AND k1.[DATE] < b5.[END_DATE]
                    LEFT JOIN Table2 AS b6  ON k1.[ID] = b6.[ID]  AND c1.[NAME_6]  = b6.[TOUROKU_NO] AND k1.[DATE] >= b6.[START_DATE] AND k1.[DATE] < b6.[END_DATE]
                    LEFT JOIN Table2 AS b7  ON k1.[ID] = b7.[ID]  AND c1.[NAME_7]  = b7.[TOUROKU_NO] AND k1.[DATE] >= b7.[START_DATE] AND k1.[DATE] < b7.[END_DATE]
                    LEFT JOIN Table2 AS b8  ON k1.[ID] = b8.[ID]  AND c1.[NAME_8]  = b8.[TOUROKU_NO] AND k1.[DATE] >= b8.[START_DATE] AND k1.[DATE] < b8.[END_DATE]
                    LEFT JOIN table5 AS c2 ON k1.[TOUROKU_2] = c2.[TOUROKU_NO]
                    LEFT JOIN Table2 AS b9  ON k1.[ID] = b9.[ID]  AND c2.[NAME_9]  = b9.[TOUROKU_NO]  AND k1.[DATE] >= b9.[START_DATE]  AND k1.[DATE] < b9.[END_DATE]
                    LEFT JOIN Table2 AS b10 ON k1.[ID] = b10.[ID] AND c2.[NAME_10] = b10.[TOUROKU_NO] AND k1.[DATE] >= b10.[START_DATE] AND k1.[DATE] < b10.[END_DATE]
                    LEFT JOIN Table2 AS b11 ON k1.[ID] = b11.[ID] AND c2.[NAME_11] = b11.[TOUROKU_NO] AND k1.[DATE] >= b11.[START_DATE] AND k1.[DATE] < b11.[END_DATE]
                    LEFT JOIN Table2 AS b12 ON k1.[ID] = b12.[ID] AND c2.[NAME_12] = b12.[TOUROKU_NO] AND k1.[DATE] >= b12.[START_DATE] AND k1.[DATE] < b12.[END_DATE]
                    LEFT JOIN Table2 AS b13 ON k1.[ID] = b13.[ID] AND c2.[NAME_13] = b13.[TOUROKU_NO] AND k1.[DATE] >= b13.[START_DATE] AND k1.[DATE] < b13.[END_DATE]
                    LEFT JOIN Table2 AS b14 ON k1.[ID] = b14.[ID] AND c2.[NAME_14] = b14.[TOUROKU_NO] AND k1.[DATE] >= b14.[START_DATE] AND k1.[DATE] < b14.[END_DATE]
                    LEFT JOIN Table2 AS b15 ON k1.[ID] = b15.[ID] AND c2.[NAME_15] = b15.[TOUROKU_NO] AND k1.[DATE] >= b15.[START_DATE] AND k1.[DATE] < b15.[END_DATE]
                    LEFT JOIN Table2 AS b16 ON k1.[ID] = b16.[ID] AND c2.[NAME_16] = b16.[TOUROKU_NO] AND k1.[DATE] >= b16.[START_DATE] AND k1.[DATE] < b16.[END_DATE]
    
[/code]

次のようにクエリ式で書いてみたのですが、join句のいずれかの式の型が正しくありません。  
というエラーが出てしまいます。

[code]

        var q = from em in Table3
                        join thUR in Table1 on em.UR equals thUR.RTth
                        join thUL in Table1 on em.UL equals thUL.LTth
                        join thDr in Table1 on em.DR equals thDr.RTth
                        join thDl in Table1 on em.DL equals thDl.LTth
                        join HK in table4 on em.TOUROKU_1 equals HK.TOUROKU_NO
              //この下のjoin句で対象のエラーが発生
                        join TB2 in Table2 on new { a = em.ID, b = HK.NAME_1} equals new { a = TB2.ID, b = TB2.TOUROKU_NO} into g
                        where em.DATE >= g.START_DATE & em.DATE< g.END_DATE
                            select new {
                            em.ID,
                            em.DATE,
                            em.NO,
                            em.DSP_NO,
                            BI = (thUR.RTth == "0" ? "" : "R" + thUR.RTth) +
                                  (thUL.LTth == "0" ? "" : "L" + thUL.LTth) +
                                  (thDr.RTth == "0" ? "" : "r" + thDr.RTth) +
                                  (thDl.LTth == "0" ? "" : "l" + thDl.LTth),
                            NAME_ID = (HK.NAME_1 == "0" ? "" : from c in g select c.BCD)
                        };
    
[/code]

テーブル構成は次の通りです。

[code]

     Table1
        [NoFlg],
        [RTth],
        [LTth]    
    
    Table2
    [ID],
    [START_DATE],    
    [TOUROKU_NO],
    [BCD],
    [END_DATE]
    
    
    Table3
    [ID],
    [DATE],
    [NO],
    [DSP_NO],
    [UR],
    [UL],
    [DR],
    [DL],
    [TOUROKU_1],
    [TOUROKU_2],
    [TOUROKU_3],
    [TOUROKU_4]
    
    table4
    [TOUROKU_NO],
    [NAME_1],
    [NAME_2],
    [NAME_3],
    [NAME_4],
    [NAME_5],
    [NAME_6],
    [NAME_7],
    [NAME_8],
    
    table5
    [TOUROKU_NO],
    [NAME_9],
    [NAME_10],
    [NAME_11],
    [NAME_12],
    [NAME_13],
    [NAME_14],
    [NAME_15],
    [NAME_16],
    
[/code]

いろいろと試してみてはいますが、手詰まり状態です。  
どなたかご回答いただける方よろしくお願いします。


CC BY-SA 3.0
2016-10-25T07:36:36.067
29884
2016-10-25T09:15:55.853
2016-10-25T07:43:32.703
8000
19310
question
c# sql linq
LINQ　複数テーブルjoinと複合条件とwhere


`DbSet`を使用して`Select`の中で副問い合わせを行えばよいのではないかと思います。

[code]

    from em in Table3
    join c1 in table4 on em.TOUROKU_1 equals c1.TOUROKU_NO
    join c2 in table4 on em.TOUROKU_2 equals c2.TOUROKU_NO
    select new
    {
       em.ID,
       em.DATE,
       em.NO,
       em.DSP_NO,
       BI = Table1.Where(v => em.UR == v.NoFlg && v.RTth != "0").Select(v => "R" + v.RTth).FirstOrDefault()
           + Table1.Where(v => em.UL == v.NoFlg && v.LTth != "0").Select(v => "R" + v.LTth).FirstOrDefault()
           + Table1.Where(v => em.DR == v.NoFlg && v.RTth != "0").Select(v => "r" + v.RTth).FirstOrDefault()
           + Table1.Where(v => em.DL == v.NoFlg && v.LTth != "0").Select(v => "l" + v.LTth).FirstOrDefault(),
       NAME_ID = Table2.Where(v => em.ID == v.ID
                                && c1.NAME_1 == v.TOUROKU_NO
                                && v.TOUROKU_NO != "0"
                                && em.DATE >= v.START_DATE
                                && em.DATE < v.END_DATE)
                        .Select(v => v.BCD)
                        .FirstOrDefault() // b1
                + Table2.Where(v => em.ID == v.ID
                                && c1.NAME_2 == v.TOUROKU_NO
                                && v.TOUROKU_NO != "0"
                                && em.DATE >= v.START_DATE
                                && em.DATE < v.END_DATE)
                        .Select(v => v.BCD) // b2
                        .FirstOrDefault()
     // TODO:b3-b16を追加する
    }
    
[/code]

ただ上記の方針で生成されるクエリーの効率が良いかどうかは分かりません。もしログ出力を確認して問題があるのであれば、`Database.SqlQuery<TItem>`を使用することもできます。


CC BY-SA 3.0
2016-10-25T07:46:26.573
29885
2016-10-25T09:15:55.853
2016-10-25T09:15:55.853
5750
5750
29884
answer