LINQ 複数テーブルjoinと複合条件とwhere
下のSQLクエリをLINQにしたいのですが、複雑過ぎて途中で分からなくなってしまいました。
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]
次のようにクエリ式で書いてみたのですが、join句のいずれかの式の型が正しくありません。
というエラーが出てしまいます。
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)
};
テーブル構成は次の通りです。
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],
いろいろと試してみてはいますが、手詰まり状態です。
どなたかご回答いただける方よろしくお願いします。