###前提・実現したいこと
LINQで複数のテーブルをjoin
いくつかのテーブルはwhere句もつけたいです。
少し複雑ですが、作業が止まってしまっているため、ご教授よろしくお願いします。
###発生している問題・エラーメッセージ
join句のいずれかの式が正しくありません'GroupJoin'の呼び出しで肩を推論できませんでした。
というエラーが出ております。
###該当のソースコード
C#
1 2Table1 3[NoFlg], 4[RTth], 5[LTth] 6 7Table2 8[ID], 9[START_DATE], 10[TOUROKU_NO], 11[BCD], 12[END_DATE] 13 14 15Table3 16[ID], 17[DATE], 18[NO], 19[DSP_NO], 20[UR], 21[UL], 22[DR], 23[DL], 24[TOUROKU_1], 25[TOUROKU_2], 26[TOUROKU_3], 27[TOUROKU_4] 28 29table4 30[TOUROKU_NO], 31[NAME_1], 32[NAME_2], 33[NAME_3], 34[NAME_4], 35[NAME_5], 36[NAME_6], 37[NAME_7], 38[NAME_8], 39 40table5 41[TOUROKU_NO], 42[NAME_9], 43[NAME_10], 44[NAME_11], 45[NAME_12], 46[NAME_13], 47[NAME_14], 48[NAME_15], 49[NAME_16], 50 51 52 53 54 55 56 57 var q = from em in Table3 58 join thUR in Table1 on em.UR equals thUR.RTth 59 join thUL in Table1 on em.UL equals thUL.LTth 60 join thDr in Table1 on em.DR equals thDr.RTth 61 join thDl in Table1 on em.DL equals thDl.LTth 62 join HK in table4 on em.TOUROKU_1 equals HK.TOUROKU_NO 63 //この下のjoin句で対象のエラーが発生 64 join TB2 in Table2 on new { a = em.ID, b = HK.NAME_1} equals new { a = TB2.ID, b = TB2.TOUROKU_NO} into g 65 where em.DATE >= g.START_DATE & em.DATE< g.END_DATE 66 select new { 67 em.ID, 68 em.DATE, 69 em.NO, 70 em.DSP_NO, 71 BI = (thUR.RTth == "0" ? "" : "R" + thUR.RTth) + 72 (thUL.LTth == "0" ? "" : "L" + thUL.LTth) + 73 (thDr.RTth == "0" ? "" : "r" + thDr.RTth) + 74 (thDl.LTth == "0" ? "" : "l" + thDl.LTth), 75 NAME_ID = (HK.NAME_1 == "0" ? "" : from c in g select c.BCD) 76 }; 77 78 79 80 81SQLで書くとこうなります。 82 83 SELECT 84 [ID], 85 [DATE], 86 [NO], 87 [DSP_NO], 88 (CASE v1.[RTth] WHEN '0' THEN '' ELSE 'R'+v1.[RTth] END) + 89 (CASE v2.[LTth] WHEN '0' THEN '' ELSE 'L'+v2.[LTth] END) + 90 (CASE v3.[RTth] WHEN '0' THEN '' ELSE 'r'+v3.[RTth] END) + 91 (CASE v4.[LTth] WHEN '0' THEN '' ELSE 'l'+v4.[LTth] END) AS [BI], 92 ISNULL((CASE c1.[NAME_1] WHEN '0' THEN '' ELSE b1.[BCD] END) + 93 (CASE c1.[NAME_2] WHEN '0' THEN '' ELSE b2.[BCD] END) + 94 (CASE c1.[NAME_3] WHEN '0' THEN '' ELSE b3.[BCD] END) + 95 (CASE c1.[NAME_4] WHEN '0' THEN '' ELSE b4.[BCD] END) + 96 (CASE c1.[NAME_5] WHEN '0' THEN '' ELSE b5.[BCD] END) + 97 (CASE c1.[NAME_6] WHEN '0' THEN '' ELSE b6.[BCD] END) + 98 (CASE c1.[NAME_7] WHEN '0' THEN '' ELSE b7.[BCD] END) + 99 (CASE c1.[NAME_8] WHEN '0' THEN '' ELSE b8.[BCD] END) + 100 (CASE c2.[NAME_9] WHEN '0' THEN '' ELSE b9.[BCD] END) + 101 (CASE c2.[NAME_10] WHEN '0' THEN '' ELSE b10.[BCD] END) + 102 (CASE c2.[NAME_11] WHEN '0' THEN '' ELSE b11.[BCD] END) + 103 (CASE c2.[NAME_12] WHEN '0' THEN '' ELSE b12.[BCD] END) + 104 (CASE c2.[NAME_13] WHEN '0' THEN '' ELSE b13.[BCD] END) + 105 (CASE c2.[NAME_14] WHEN '0' THEN '' ELSE b14.[BCD] END) + 106 (CASE c2.[NAME_15] WHEN '0' THEN '' ELSE b15.[BCD] END) + 107 (CASE c2.[NAME_16] WHEN '0' THEN '' ELSE b16.[BCD] END) , '') AS [NAME_ID] 108 FROM Table3 AS k1 109 LEFT JOIN Table1 AS v1 ON k1.[UR] = v1.[NoFlg] 110 LEFT JOIN Table1 AS v2 ON k1.[UL] = v2.[NoFlg] 111 LEFT JOIN Table1 AS v3 ON k1.[DR] = v3.[NoFlg] 112 LEFT JOIN Table1 AS v4 ON k1.[DL] = v4.[NoFlg] 113 LEFT JOIN table4 AS c1 ON k1.[TOUROKU_1] = c1.[TOUROKU_NO] 114 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] 115 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] 116 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] 117 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] 118 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] 119 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] 120 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] 121 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] 122 LEFT JOIN table5 AS c2 ON k1.[TOUROKU_2] = c2.[TOUROKU_NO] 123 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] 124 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] 125 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] 126 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] 127 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] 128 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] 129 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] 130 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]
###試したこと
http://nyaffun.seesaa.net/category/12673984-1.html
こちらのサイトで紹介してある、二つのサイトを見ながらやりましたが、うまく行きませんでした。
最後のselect newの NAME_IDの三項演算子の部分でfrom c in g select c.BCDとしてますが、この式はビルドが通るか不安があります。
HK.NAME_1の値が0ならから文字それ以外ならtable2のBCDの値を取得したいです。
以上よろしくお願いします。
###補足情報(言語/FW/ツール等のバージョンなど)
より詳細な情報
.net freamwork 3.5
C#
回答4件
あなたの回答
tips
プレビュー