至急お願いします。LINQ 複数テーブルjoinと複合条件とwhere

Question

###前提・実現したいこと
LINQで複数のテーブルをjoin
いくつかのテーブルはwhere句もつけたいです。
少し複雑ですが、作業が止まってしまっているため、ご教授よろしくお願いします。

###発生している問題・エラーメッセージ

join句のいずれかの式が正しくありません'GroupJoin'の呼び出しで肩を推論できませんでした。

というエラーが出ております。

###該当のソースコード

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],




	

        	
	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)
					};




SQLで書くとこうなります。

	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]

###試したこと
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#

Comments

[A.Ichi]

SQL文でのISNULLは多分typoミスですよね。JOINはできましたよ。

[DENQ]

joinしたlinqの式を教えていただけないでしょうか？解答欄にお願いします。

Answer 1

かなり複雑なので当てずっぽうになりますが、以下の箇所にもプロパティ名を追加してあげるとどうでしょう？

select new {
    em.ID, // 「a = em.ID」, とかに修正（下のプロパティも同様に何か名前をつける）
    em.DATE,
    em.NO,
    em.DSP_NO,

Comments

[DENQ]

回答ありがとうございます。
試してみましたが、ダメみたいでした。

Answer 2

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を追加する
}

Answer 3

[hoge]外してSQLを実行しました。 もしかして例題のSQLは当然うまく行き、問題はプログラムが作成するSQLであれば、私の勘違いです。LINQの環境がないのでコードのデバグできなくてすみません。

 SELECT
 k1.ID,
 k1.DATE,
 k1.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,
 IFNULL((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
;


テーブルは適当です。
create table Table1 (NoFlg int, RTth text, LTth text);
create table Table2 (ID int, TOUROKU_NO int, START_DATE datetime, END_DATE datetime, BCD text);
create table Table3 (ID int, NO int, DSP_NO text, UR int, UL int, DR int, DL int, TOUROKU_1 int, TOUROKU_2 int, DATE datetime, BCD text);
create table Table4 (TOUROKU_NO int, NAME_1 int, NAME_2 int, NAME_3 int, NAME_4 int, NAME_5 int, NAME_6 int, NAME_7 int, NAME_8 int);
create table Table5 (TOUROKU_NO int, NAME_9 int, NAME_10 int, NAME_11 int, NAME_12 int, NAME_13 int, NAME_14 int, NAME_15 int, NAME_16 int);

var q = from em in Table3s
    join thUR in Table1s on em.UR equals thUR.RTth
    join thUL in Table1s on em.UL equals thUL.LTth
    join thDr in Table1s on em.DR equals thDr.RTth
    join thDl in Table1s on em.DL equals thDl.LTth
    join HK   in Table4s on em.TOUROKU_1 equals HK.TOUROKU_NO
    //この下のjoin句で対象のエラーが発生
    join TB2 in Table2s on new { a=em.ID, b=HK.NAME_1 } equals new { a=TB2.ID, b=TB2.TOUROKU_NO }
    where em.DATE >= TB2.START_DATE & em.DATE< TB2.END_DATE
    select new {
     m.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" ? "" : TB2.BCD)
};

Comments

[DENQ]

回答頂いたのに申し訳ありませんが、SQLではなくlinqのjoinの方法を質問させて頂いてます。

Answer 4

「Linq join句のいずれかの式が正しくありません」で検索したらこんなの出てきましたけども。
どうでしょうか。

http://piyopiyocs.blog115.fc2.com/blog-entry-72.html

結局の所、比較項目の元々の名前が違ったからダメだった模様です

名前つけてあげたら無事エラー消滅

Comments

[sk_3122]

上記サイトで書いてあるのはここ ↓ に相当する部分ですかね？

join TB2 in Table2 on new { a = em.ID, b = HK.NAME_1} equals new { a = TB2.ID, b = TB2.TOUROKU_NO} into g

名前ちゃんと付いてますね。違いましたかね… すみません

[DENQ]

ですね。複数のjoinのあとのwhere句をどうにかうまく書きたいのですがどうやっていいのかわからないですね。

[sk_3122]

昔の自分用メモに同じようなエラーがあったので一応貼っておきますね。
全然関係なかったらごめんなさいね。

----------
【LEFT JOIN で ON 句にリテラル値を指定する場合】

var list = from t1 in this.ObjectContext.Tbl1
join t2 in this.ObjectContext.Tbl2
on new { t1.Id1, IsDeleted=(bool?)false } equals new { t2.Id1, t2.IsDeleted }
select new { t1.Name, t2.Name };

【駄目だったケース】

on new { t1.Id1, IsDeleted=false } equals new { t2.Id1, t2.IsDeleted }

// join 句のいずれかの式の型が正しくありません。
// 'Group Join' の呼び出しで型が推論できませんでした。
// → t2.IsDeleted が Nullable だったので (bool?) でキャストしないと駄目らしい。
----------

# 要はキャストとかは関係ないですかね？っていう。
# 関係ないかなー…