0

I'm writing Select-statements with joins in C# with Vs 2015. Now i got a special join with multiple parameters which come - under circumstances - from different tables which complicates all a little bit.

How can i write the following statement correctly?

TestPointToInputMask tpm2 =
    (from m in ent.InputMask
     join tm in ent.TestPointToInputMask on m.inputMaskId equals tm.inputMaskId
     join tp in ent.TestPoint on tm.testPointId equals tp.testPointId
     join tc in ent.TestPointToChapter on tp.testPointId equals tc.testPointId
     join c in ent.Chapter on tc.chapterId equals c.chapterId
     join dc in ent.Description on c.textKey equals dc.textKey and dc.languageId == 1
     join dp in ent.Description on tp.textKey equals dp.textKey and dp.languageId == 1 and dp.text.StartsWith(testPointText)
     where m.inputMaskName.ToUpper() == inputMaskName.ToUpper()
     && tp.testPointNumber.ToUpper() == testPointNumber.ToUpper()
     && dc.text.ToUpper() == chapterText.ToUpper()
     && c.testTypeId == testTypeId
     select tm).FirstOrDefault();

As You can see i have there two joins in which i use "and" which don't work. I tried another version but it's very slow.

TestPointToInputMask tpm2 =
    (from m in ent.InputMask
     join tm in ent.TestPointToInputMask on m.inputMaskId equals tm.inputMaskId
     join tp in ent.TestPoint on tm.testPointId equals tp.testPointId
     join tc in ent.TestPointToChapter on tp.testPointId equals tc.testPointId
     join c in ent.Chapter on tc.chapterId equals c.chapterId
     join dc in ent.Description on c.textKey equals dc.textKey
     join dp in ent.Description on tp.textKey equals dp.textKey
     where m.inputMaskName.ToUpper() == inputMaskName.ToUpper()
     && tp.testPointNumber.ToUpper() == testPointNumber.ToUpper()
     && dc.text.ToUpper() == chapterText.ToUpper()
     && c.testTypeId == testTypeId
     && dc.languageId == 1
     && dp.languageId == 1 && dp.text.StartsWith(testPointText)
     select tm).FirstOrDefault();

Any help would be appreciated.

Patrick Pirzer
  • 1,649
  • 3
  • 22
  • 49
  • [`from a in table1 join b in table2 on new { a.prop1, a.prop2 } equals new { b.prop1, b.prop2 }`](https://stackoverflow.com/questions/5307731/linq-to-sql-multiple-joins-on-multiple-columns-is-this-possible)? (Never name your tables, properties and variables this way) – Rafalon Aug 22 '18 at 10:28
  • Possible duplicate of [LINQ to SQL: Multiple joins ON multiple Columns. Is this possible?](https://stackoverflow.com/questions/5307731/linq-to-sql-multiple-joins-on-multiple-columns-is-this-possible) – Rafalon Aug 22 '18 at 10:31
  • Hello Rafalon. I tried that with "join dc in ent.Description on new { c.textKey, dc.languageId } equals new { dc.textKey, 1}" for the first join with multiple conditions but it's not correct. The compiler says: The name "dc" is not in the range on the left side of "equals". Swap the expressions on both sides of "equals". What's wrong? – Patrick Pirzer Aug 22 '18 at 10:36
  • Your second version is your only option, so that answers your immediate question. If it's slow, you should state your question differently, but remember that performance questions are extremely hard to diagnose from a distance. – Gert Arnold Aug 22 '18 at 10:37
  • You could eventually have written `on new { c.textKey, languageId = 1 } equals new { dc.textKey, dc.languageId }`. But really this should be a `where dc.languageId == 1`... Also, can you explain a little what "*which don't work*" means in your question? – Rafalon Aug 22 '18 at 10:40
  • The `ToUpper()` and `StartsWith` calls are the primary suspects. At least you can do without `ToUpper` because the database collation fully determines the string comparison in the generated SQL query. – Gert Arnold Aug 22 '18 at 10:41

0 Answers0